Asp. NET connect database and get Data Method Summary _ Practical Tips

The examples in this article describe the ASP.net connection database and the method of obtaining data. Share to everyone for your reference, specific as follows:

* Usage of the Connection object Sqlconnection,sqlcommand,sqldataadapter
* How to format data access

1. Get Data:

Reference the two namespaces
using System.Data.SqlClient;
Using System.Data;
Initializes the connection object
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = "User id=sa;initial catalog=databasename;data source= (local); password=111111 ";
Open the Connection
if (conn). state = = connectionstate.closed)
{
  Conn. Open ();
}
Initialize command
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtype = CommandType.Text;
Cmd.commandtext = "SQL statement";
An action to perform data inserts, updates, and deletes, and returns the number of rows affected.
int i = cmd. ExecuteNonQuery ();
if (i>0) {MessageBox.Show ("successful Operation");}
An operation that queries the maximum value, such as simply returning a single piece of data, and returns the first column of data in the first row.
Object obj = cmd. ExecuteScalar ();
If you want to get a collection of data, we often use data adapter
datatable dt = new DataTable ();
SqlDataAdapter adapter = new SqlDataAdapter ();
Adapter. SelectCommand = cmd;
Adapter. Fill (DT);

2. Bind data to Data controls

String str = "Data source=.;i Nitial Catalog=gridview; User Id=sa; password=111111 ";
String sql = "SELECT * from UserName";
SqlConnection conn = new SqlConnection (str);
Conn. Open (); Use SqlDataAdapter (data adapter) without writing
//sqlcommand comm = new SqlCommand (SQL, conn);
SqlDataAdapter dr = New SqlDataAdapter (comm);
SqlDataAdapter dr = New SqlDataAdapter (sql,conn);//The above two sentences can be merged into this
DataSet ds = new DataSet ();//create DataSet;
Dr. Fill (DS); Populate DataSet this
. Gridview1.datasource = ds;
This. Gridview1.databind ();//The data source is bound to the control,
//conn. Close (); Closes the database connection
if (conn). State==connectionstate.open)//Determine database connection status, connect
{
Conn. Close ();
}

3. Use SqlDataReader:

To create a SqlDataReader, you must call the ExecuteReader method of the SqlCommand object instead of using the constructor directly.

String str = "Data source=.;i Nitial Catalog=gridview; User Id=sa; password=111111 ";
String sql = "SELECT * from UserName";
SqlConnection conn = new SqlConnection (str);
Conn. Open ();
SqlCommand comm = new SqlCommand (SQL, conn);
DataSet ds = new DataSet ();
SqlDataReader dr = Comm. ExecuteReader ();
if (Dr. Read ())
{
  //The following two kinds of data//this can be obtained
  . TextBox1.Text = Dr. GetString (1);
  This. TextBox2.Text = Dr. GetInt32 (3). ToString ();
  This. TextBox1.Text = Dr. GetString (Dr. GetOrdinal ("Name"));
  This. TextBox2.Text = Dr. GetInt32 (Dr. GetOrdinal ("Age")). ToString ();
}
Loop output while
(Dr. Read ())
{
  Response.Write (dr["Name"]);
  Response.Write (dr["age"));
  Response.Write ("<br/>");
}
Dr. Close ();
IF (Conn. state = = ConnectionState.Open)
{
  Conn. Close ();
}

SqlDataReader: Provides a way to read only incoming streams of rows from a SQL Server database

Add: ASP.net database connection web.config configuration

The SQL Server. NET Data Provider Connection string consists of a collection of attribute name/value pairs. Each attribute/value pair is separated by a semicolon.

propertyname1=value1;
propertyname2=value2;
Propertyname3=value3;
.....

Similarly, the connection string must contain the SQL Server instance name:

Copy Code code as follows:

Data Source=servername;

With a local SQL Server (localhost), if you want to run using a remote server, you should assign the correct server to the data Source property in the sample object. In addition, you must specify one of the two supported authentication methods (Windows authentication and SQL Server Authentication). Windows authentication uses the Windows logon user identity to connect to the database, and SQL authentication requires that you explicitly specify the SQL Server user ID and password. To use Windows authentication, you must include the integrated security attribute in the connection string:

Data Source=servername;
Integrated security=true;

By default, the Integrated security property is False, which means that Windows authentication is disabled. If you do not explicitly set the value of this property to true, the connection will use SQL Server authentication, so you must provide a SQL Server user ID and password. Other values that are recognized by the integrated security attribute are only the SSPI (Support Provider Interface, the Secure Support Provider interface). On all Windows NT operating systems, including Windows NT 4.0, 2000, XP, all support value SSPI. It is the only interface that can be used when using Windows Authentication, which is equivalent to setting the Integrated security property value to True.

In Windows Authentication mode, SQL Server uses the Windows security subsystem to authenticate user connections. SQL Server does not check the user ID and password in the connection string, even if the user ID and password are specified. Because only Windows NT, 2000, and XP support SSPI, if you are using these operating systems, you can only use Windows Integrated Security policy to connect to SQL Server. Regardless of which operating system you use, when you use SQL Server authentication, you must specify a user ID and password in the connection string:

Data Source=servername;
User id=donaldx;
Password=unbreakable

By default, the SQL Server. NET Data Provider connection Specifies the user's default database, and when a user is created in the database, the user's default database can be set. In addition, you can change the user's default database at any time. For example, the default database for the system administrator is master. If you want to connect to a different database, you should specify the name of the database:

Data Source=servername;
Integrated SECURITY=SSPI;
Initial Catalog=northwind

Each kind of authentication has its advantages and disadvantages. Windows authentication uses a single user repository source, so there is no need to configure users separately for database access. The connection string does not contain a user ID and password, thereby eliminating the risk of exposing the user ID and password to an unauthorized user. You can manage users and their roles in Active Directory without having to explicitly configure their properties in SQL Server.

The disadvantage of Windows authentication is that it requires customers to connect to SQL Server through a secure channel supported by the Windows security subsystem. If the application sequence requires that SQL Server,windows authentication be connected over an insecure network, such as the Internet, it will not work. In addition, this method of authentication partly shifts the responsibility for managing database access control from the DBA to the system administrator, which may be a problem in a defined environment.

In general, when designing a generic application, some aspects will be enhanced in order to use Windows authentication. Most corporate databases reside on a more robust Windows server operating system that supports Windows authentication. The separation of the data access layer and the data presentation layer also facilitates the encapsulation of data access code in the middle-tier component idea, which is typically run in an internal network with a database server. When this design is done, there is no need to establish a database connection through an unsecured channel. In addition, Web services make it much less necessary to connect directly to databases in different domains.

The connectivity of the database has developed into a standard aspect of application development. The database connection string is now a standard prerequisite for each project. I find myself often copying connection strings from another application or doing a search in order to find the syntax I need. This is especially true when interacting with SQL Server because it has too many connection string options. Now let's look at a number of aspects of the connection string.

Connection string

During an object instantiation or establishment, the database connection string is passed to the necessary object through a property or method. The format of the connection string is a semicolon-bounded list of key/value parameter pairs. Listing a includes an example in C # that shows how to connect to SQL Server with the method of creating the SqlConnection object (the actual connection string is assigned through the object's ConnectionString property). The vb.net version is included in List B.

List A

String cString = "Data source=server;initial catalog=db; User id=test; Password=test; ";
Sqlconnectionconn = new SqlConnection ();
Conn. ConnectionString = cString;
Conn. Open ();

List B

Dim cString as String
cString = "Data source=server;initial catalog=db;" User id=test; password=test; "
Dim conn As SqlConnection = New SqlConnection ()
Conn. ConnectionString = cString
Conn. Open ()

The connection string specifies the database server and database, as well as the user name and password required to access the database. But this format does not apply to all database interactions, it does have a number of options available, many of which have synonyms.

Together with elements such as data source, Initial Catalog (initial catalog), user ID (user ID), and password (password), the following options are available:

Application name (application name): the names of the applications. If it is not specified, its value is. NET SqlClient data Provider.
Attachdbfilename/extended Properties (extended property)/initial file name (initial file name): The name of the primary files that can connect to the database, including the full path name. The database name must be specified with a keyword database.

Connect Timeout (Connection timeout)/connection Timeout (Connection timeout): The length of time (in seconds) that a connection to the server waits before terminating, the default value is 15.

Connection Lifetime (Connection Life time): When a connection is returned to the connection pool, its creation time is compared to the current time. If the time span exceeds the lifetime of the connection, the connection is canceled. The default value is 0.

Connection Reset (Connection reset): Indicates whether a connection is reset when it is removed from the connection pool. A pseudo-effective when you get a connection, you don't have to do an extra server back and forth, and the default value is true.

Current Language: Name of the SQL Server language record.

Data source/server (server)/address (address)/addr (address)/network Addressing (network address): The name or network address of an instance of SQL Server.

Encrypt (encryption): When the value is true, SQL Server uses SSL encryption for all data transferred between the client and the server if the server has an authorization certificate installed. The accepted value has true (true), False (false), yes (yes), and no (NO).

Enlist (Registration): Indicates whether the connection pooling program automatically enlists the connection in the current transaction context of the creation thread, and its default value is true.

Database (database)/initial Catalog (initial catalog): The name of the databases.

Integrated security (Integrated Secure)/trusted Connection (Trusted connection): Indicates whether Windows authentication is used to connect to the database. It can be set to true, pseudo, or SSPI with true equivalence, and its default value is pseudo.

Max Pool Size (maximum connection pool capacity): The maximum number of connections allowed by the connection pool, with a default value of 100.

Min Pool Size (minimum capacity of the connection pool): The minimum number of connections allowed by the connection pool, with a default value of 0.

Network library (Network Library)/net (network): A network library that is used to establish a connection to an instance of SQL Server. Supported values include: DBNMPNTW (Named pipes), DBMSRPCN (MULTIPROTOCOL/RPC), Dbmsvinn (Banyan vines), DBMSSPXN (ipx/spx), and DBMSSOCN ( TCP/IP). The dynamic link library for the protocol must be installed to the appropriate connection, and the default is TCP/IP.

Packet Size: The size of the network packet used to communicate with the database. The default value is 8192.

Password (password)/pwd: the password corresponding to the account name.

Persist Security Info (keep secure information): Used to determine if security information is available once the connection is established. If the value is true, the data that is sensitive to security, such as user name and password, is available, and if the value is pseudo, it is not available. Resetting the connection string will reconfigure the values of all connection strings, including passwords. The default value is pseudo.

Pooling (pool): Determines whether connection pooling is used. If the value is true, the connection is obtained from the appropriate connection pool, or, if necessary, the connection is created and then added to the appropriate connection pool. The default value is true.

User ID (User ID): The name of the account used to log in to the database.

Workstation ID (Workstation ID): The name of the workstation that is connected to SQL Server. The default value is the name of the local computer.

Some initial insights after solving some of the basic issues with SQL Server connectivity

Students do a question bank system, written in C # ASP application, database with SQL Server2000, to me to see. There is a problem after putting it on the server. After repeated adjustments found the solution, in fact, very simple. Looking back, the discovery was caused by a lack of understanding of SQL Server's connection statements and user permissions. The following will be some of my experience and online information on the collection of relevant data, thought that the successor, of course, very superficial.

1. How SQL Server is connected

With the local server (LocalHost), Database (Northwind), for example, you can have some of the following connection methods

SqlConnection conn=new SqlConnection ("server=localhost;integrated security=sspi;database=northwind");
SqlConnection conn = new SqlConnection ("Data source=localhost;integrated security=sspi;initial catalog=northwind;");
SqlConnection conn = new SqlConnection ("Data source=localhost;initial catalog=northwind;integrated Security=SSPI; Persist Security Info=false; Workstation Id=xurui; Packet size=4096; ");
SqlConnection myconn = new SqlConnection ("Persist Security info=false;integrated Security=sspi;database=northwind; Server=localhost ");
SqlConnection conn = new SqlConnection ("UID=SA; Pwd=***;initial Catalog=northwind;data Source=localhost; Connect timeout=900 ");

Experience:

A.server and Database,data source are paired with initial catalog, which can be substituted for each other (laughed)

The b.integrated security default value is False, and you need to provide UID and PWD to log into the database as a SQL Server user, and if you set it to True,yes or SSPI, this does not appear in UID and PWD. The database will be logged into the Windows user Province. The latter form is strongly recommended for higher security.

C.integrated and Persist Security info appear simultaneously, and the latter is set to false to keep information secure.

More string connection instructions see MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/ Frlrfsystemdatasqlclientsqlconnectionclassconnectionstringtopic.asp

Connection string writing should be proficient after there should be no problem, I was looking at someone else's program, to tell the truth some dongdong is really not clear. But the problem is not connected, you have to solve it. So you have to understand the meaning of these keywords, modify and then test.

2. User settings for SQL Server

Problem one, using the connection string

Copy Code code as follows:

SqlConnection conn = new SqlConnection ("UID=SA; Pwd=***;initial Catalog=northwind;data Source=localhost; Connect timeout=900 ");

Error:

User "sa" Login failed, no trusted SQL Server connection
Find the solution after checking the data:

Reason: SQL Server authentication needs to be set to a hybrid of SQL Server authentication and Windows Integrated authentication, and if set to the latter, it will appear like the problem

Resolve: Run SQL Server Enterprise Manager, click on the server, in the right-click menu to select Properties, choose Security, change the authentication method can be

Problem two, using the connection string

Copy Code code as follows:

SqlConnection conn = new SqlConnection ("Data source=localhost;integrated security=sspi;initial catalog=northwind;");

Error:

User "Computername\iwam_servername" Login failed

Reason: IWAM_servername is not included in the login user of SQL Server

WORKAROUND: Run SQL Server Enterprise Manager, click on the server, select Security, select Login, new login to join IWAM_servername, and with the appropriate permissions, such as access to the Northwind database, the role of the database is set to public and db_ Owner

3, about the security of the connection

It is best to connect to the database using SSPI integrated security The way the SA user is connected is a security risk, I think, mainly because when SQL Server is installed, the password for the SA is often set to be null for easy access, and once the hacker makes the SA administrator, all access to the system can be obtained. So for database security, you can set SQL Server access users to only Windows Integrated authentication, set SA security password, and enhance the security of the database. Of course, when set to Windows Integrated authentication, the performance and access flexibility of the database is bound to be affected, and administrators can set different authentication methods for each database without having to set up a unified approach to SQL Server.

I hope this article will help you to ASP.net program design.