Summary of ASP. NET database connection and data retrieval methods, asp.net Database

Summary of ASP. NET database connection and data retrieval methods, asp.net Database

This article describes how ASP. NET connects to the database and obtains data. We will share this with you for your reference. The details are as follows:

* Connection object usage: SqlConnection, SqlCommand, and SqlDataAdapter
* Data Access Method

1. Get data:

// Reference the two namespaces using System. data. sqlClient; using System. data; // initialize 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 () ;}// initialization command SqlCommand cmd = new SqlCommand (); cmd. connection = conn; cmd. commandType = CommandType. text; cmd. commandText = "SQL statement ";// It is used to insert, update, and delete data, and returns the number of affected rows. Int I = cmd. executeNonQuery (); if (I> 0) {MessageBox. show ("operation successful");} // the operation that is used to query the maximum value and so on. Only one data row is returned. The data in the first column in the first row is returned. Object obj = cmd. executeScalar (); // if you want to obtain a data set, we often use the data adapter DataTable dt = new DataTable (); SqlDataAdapter adapter = new SqlDataAdapter (); adapter. selectCommand = cmd; adapter. fill (dt );

2. Bind data to the Data Control

String str = "Data Source = .; initial 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 preceding two sentences can be combined into this DataSet ds = new DataSet (); // create a DataSet; dr. fill (ds); // Fill the dataset this. gridView1.DataSource = ds; this. gridView1.DataBind (); // bind the data source to the control, // conn. close (); Close the database connection if (conn. state = ConnectionState. open) // determine the database connection status and whether to connect to {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 = .; initial 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 types of data can be obtained // this. 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 method to read rows from the SQL Server database only into the stream.

Supplement: asp.net database connection web. config Configuration

The SQL Server. NET Data Provider connection string contains a set 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 codeThe Code is as follows: Data Source = ServerName;

If you want to use a remote Server to run SQL Server (localhost), you should assign the correct Server to the Data Source attribute in the sample object. In addition, you must specify either of the two supported authentication methods (Windows Authentication and SQL Server Authentication. Windows Authentication uses Windows login user identity to connect to the database, and SQL authentication requires that the SQL Server user ID and password be explicitly specified. 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 attribute is False, which means Windows authentication is disabled. If the value of this attribute is not explicitly set to True, the connection uses SQL Server Authentication. Therefore, the SQL Server user ID and password must be provided. The Integrated Security attribute can recognize other values only SSPI (Security Support Provider Interface, Security Support Provider Interface ). all Windows NT operating systems, including Windows NT 4.0, 2000, and XP, support SSPI. It is the only interface that can be used for Windows authentication. It is equivalent to setting the value of Integrated Security to True.

In Windows Authentication mode, SQL Server uses the Windows security subsystem to verify the validity of user connections. Even if the user ID and password are explicitly specified, SQL Server does not check the user ID and password in the connection string. Because only Windows NT, 2000, and XP support SSPI, if you are using these operating systems, you can only use Windows integrated security policies to connect to SQL Server. No matter which operating system you use, when using SQL Server Authentication, you must specify the user ID and password in the connection string:

Data Source=ServerName;User ID=donaldx;Password=unbreakable

By default, SQL Server. NET Data Provider connects to the default database of the specified user. When creating a user in the database, you can set the default database of the user. In addition, you can change your default database at any time. For example, the default database of the system administrator is master. To connect to different databases, specify the Database Name:

Data Source=ServerName;Integrated Security=SSPI;Initial Catalog=Northwind

Each authentication method has its advantages and disadvantages. Windows Authentication uses a single user information library source. Therefore, you do not need to configure users for database access separately. The connection string does not contain the user ID and password, which eliminates the risk of exposing the user ID and password to unauthorized users. Users and their roles can be managed in Active Directory without explicitly configuring their properties in SQL Server.

The disadvantage of Windows authentication is that it requires the user to connect to SQL Server through the Secure Channel supported by the Windows security subsystem. If the application needs to connect to SQL Server through an insecure network (such as the Internet), Windows authentication will not work. In addition, this authentication method also partially transfers the responsibility for managing database access control from the DBA to the system administrator, which may be a problem in the identified environment.

In general, some aspects will be enhanced to use Windows authentication when designing common applications. Most companies' databases reside on robust Windows server operating systems that support Windows authentication. The separation of the data access layer and the data presentation layer also promotes the application of data access code encapsulated in the middle layer component idea. The middle layer component usually runs in the internal network with a database server. In this design, you do not need to establish a database connection through an insecure channel. In addition, Web Services also greatly reduce the need to directly connect to databases in different domains.

Database Connectivity has developed into a standard aspect of application development. Database connection strings are now a standard prerequisite for each project. I found that in order to find the required syntax, I often need to copy the connection string from another application or perform a search. This is especially true when interacting with SQL Server because it has too many connection string options. Now let's take a look at the many aspects of the connection string.

Connection string

During Object Instantiation or establishment, database connection strings are passed to necessary objects through attributes or methods. The format of the connection string is a list of key/value parameter pairs divided by semicolons. List A contains an example in C # That describes how to connect to SQL Server by creating A SqlConnection object (the actual connection string is allocated through the ConnectionString attribute of the object ). List B contains the VB. NET version.

List

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 StringcString = "Data Source=server;Initial Catalog=db;User ID=test;Password=test;"Dim conn As SqlConnection = New SqlConnection()conn.ConnectionString = cStringconn.Open()

The connection string specifies the database server and database, and the username and password required to access the database. However, this format does not apply to all database interactions. It does have many available options, many of which have synonyms.

With Data Source, Initial Catalog, User ID, Password, and other elements, the following options are available:

Application Name: the Name of the Application. If it is not specified, its value is. NET SqlClient Data Provider ).
AttachDBFilename/extended properties (extended attribute)/Initial File Name (Initial File Name): Name of the main File that can be connected to the database, including the full path Name. The database name must be specified with the keyword database.

Connect Timeout (Connection Timeout)/Connection Timeout (Connection Timeout): the length of time (in seconds) for a Connection to the server to wait before termination. The default value is 15.

Connection Lifetime: when a Connection is returned to the Connection pool, its creation time is compared with the current time. If the time span exceeds the validity period of the connection, the connection will be canceled. The default value is 0.

Connection Reset: Indicates whether a Connection is Reset when it is removed from the Connection pool. A pseudo-valid server does not need to run back and forth after obtaining a connection. The default value is true.

Current Language: the name of the SQL Server Language record.

Data Source/Server/Address/Addr/Network Address: The name or Network Address of the SQL Server instance.

Encrypt (encryption): When the value is true, if an authorization certificate is installed on the Server, SQL Server uses SSL encryption for all data transmitted between the client and the Server. The accepted values include true, false, yes, and no ).

Enlist: indicates whether the connection pool program automatically registers the connection in the current transaction context of the Creation thread. The default value is true.

Database/Initial Catalog: name of the Database.

Integrated Security/Trusted Connection: Indicates whether Windows authentication is used to connect to the database. It can be set to "true", "false", or "true-to-peer" sspi. Its default value is "false.

Max Pool Size (maximum capacity of the Connection Pool): the maximum number of connections allowed by the connection Pool. The default value is 100.

Min Pool Size (minimum capacity of the Connection Pool): the minimum number of connections allowed by the connection Pool. The default value is 0.

Network Library/Net: The Network Library used to establish a connection to an SQL Server instance. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol/RPC), dbmsvinn (Banyan Vines), dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP ). The dynamic Connection Library of the Protocol must be installed to an appropriate connection. The default value is TCP/IP.

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

Password/Pwd: Password corresponding to the account name.

Persist Security Info: used to determine whether Security information is available once a connection is established. If the value is true, sensitive data such as the user name and password is available, and false data is unavailable. Resetting the connection string will reconfigure the values of all connection strings, including passwords. The default value is false.

Pooling: determines whether to use the connection pool. If the value is true, the connection will be obtained from the appropriate connection pool, or, if necessary, the connection will be created and then added to the appropriate connection pool. The default value is true.

User ID: the account name used to log on to the database.

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

Some preliminary experiences after solving some basic problems during SQL Server connection

The student made a question bank system, using ASP applications written in C #, and SQL Server2000 for the database. An error occurs when it is placed on the server. After repeated adjustments, we found the solution, which is actually very simple. Looking back, I found that my lack of understanding about the SQL Server connection statements and user permissions. Next I will share some of my experiences and relevant information I found on the Internet, so that it is superficial to learn from later users.

1. SQL Server connection method

Taking the local server (LocalHost) and database (Northwind) as an example, you can use 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");

TIPS:

A. the Server and Database, Data Source and Initial Catalog pairs can be used to replace each other (Laugh)

B. the default value of Integrated Security is False. Uid and Pwd must be provided to log on to the database as an SQL Server user. If it is set to True, Yes, or SSPI, Uid and Pwd cannot appear, log on to the database in the Windows user province. It is strongly recommended that the latter form be used for higher security.

C. Integrated Security and Persist Security Info appear at the same time. The latter is set to False to ensure information Security.

For more string connection instructions, see MSDN:
Http://msdn.microsoft.com/library/default.asp? Url =/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassConnectionStringTopic. asp

There should be no problem after writing the connection string skillfully. I am looking at other people's programs. To tell the truth, some things are really unclear. But after the connection fails, you have to solve it. Therefore, you must understand the meaning of these keywords and modify them before testing.

2. SQL Server user settings

Question 1: Use a connection string

Copy codeThe Code is 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 information:

Cause: the SQL Server authentication method needs to be set to a mix of SQL Server Authentication and Windows Integration authentication. If you are only set to the latter method, the above problems will occur.

Solution: run the SQL Server Enterprise Manager, click the Server, right-click the Server, select Properties, select security, and change the authentication method.

Problem 2: Use a connection string

Copy codeThe Code is as follows: SqlConnection conn = new SqlConnection ("Data Source = LocalHost; Integrated Security = SSPI; Initial Catalog = Northwind ;");

Error:

User "computername \ IWAM_servername" Login Failed

Cause: the SQL Server login user does not include IWAM_servername

Solution: run the SQL Server Enterprise Manager, click the Server, select Security, select login, add IWAM_servername to the new login, and assign corresponding permissions. For example, you can only access the Northwind database, set the database role to public and db_owner.

3. Connection Security

It is best to use SSPI integrated security to connect to the database, while sa user connection may pose security risks. I think it is mainly because when installing SQL Server, the sa password is often set to be empty for access convenience. Once a hacker makes sa an administrator, all access permissions to the system can be obtained. For database security, you can set the SQL server access user to pass the windows integrated verification, set the sa security password, and enhance the database security. Of course, after being set to windows integration verification, the database performance and access flexibility will inevitably be affected. The administrator can set different verification methods for each database, instead of setting SQL server in a unified manner.

I hope this article will help you design your asp.net program.