CSharp connecting a local SQL Server database

This article details how to use the connection object to connect to a database. For the different. NET data provider, ADO. NET uses different connection objects to connect to the database. These connection objects shield us from specific implementation details and provide a unified approach to implementation.

There are four types of connection: Sqlconnection,oledbconnection,odbcconnection and OracleConnection.

The objects of the SqlConnection class are connected to the SQL Server database; The OracleConnection class objects connect to the Oracle database;

The objects of the OleDbConnection class connect to databases that support OLE DB, such as access, while objects of the OdbcConnection class connect to any ODBC-enabled database. All communication with the database is ultimately done through the connection object.

SqlConnection class

Connection is used for "dialog" with the database and is represented by a particular provider's class (such as SqlConnection). Although the SqlConnection class is for SQL Server, many of the properties, methods, and events of this class are similar to those of OleDbConnection and OdbcConnection. This chapter will focus on SqlConnection specific properties and methods, and other connection classes you can refer to the appropriate Help documentation.

Note: Different namespaces are required to be imported using different connection objects. The OleDbConnection namespace is System.Data.OleDb. The SqlConnection namespace is System.Data.SqlClient. The OdbcConnection namespace is System.Data.Odbc. The OracleConnection namespace is System.Data.OracleClinet.

SqlConnection properties:

Property Description

ConnectionString its return type is string, gets or sets the string used to open the SQL Server database.

ConnectionTimeout its return type is int, which gets the time to wait before terminating an attempt and generating an error when attempting to establish a connection.

Database whose return type is string, gets the name of the databases to use when the current database or connection is opened.

DataSource its return type is string and gets the name of the instance of SQL Server to connect to.

State whose return type is ConnectionState, gets the current connection status: Broken, Closed, connecting, fetching, or open.

ServerVersion whose return type is string, gets the version of the SQL Server instance that contains the client connection.

PacketSize gets the size, in bytes, of the network packet used to communicate with the instance of SQL Server. This property only applies to SqlConnection types

SqlConnection method:

Method description

Close () whose return type is void, closes the connection to the database.

CreateCommand () whose return type is SqlCommand, creates and returns a SqlCommand object associated with SqlConnection.

Open () whose return type is void, opens the database connection with the properties specified by the connection string property

SqlConnection event:

Event description

StateChange occurs when an event state changes. (inherited from DbConnection.) ）

InfoMessage occurs when SQL Server returns a warning or informational message.

Tip: You can use an event to let an object notify another object in some way of something. For example, when we select the Start menu in a Windows system, an event occurs when the mouse is clicked, informing the operating system that the Start menu is displayed.

To connect to a SQL Server database by using a SqlConnection object

We can use the SqlConnection () constructor to generate a new SqlConnection object. This function is overloaded, that is, we can call different versions of the constructor. The constructor for SqlConnection () is shown in the following table:

Description of the constructor function

SqlConnection () Initializes a new instance of the SqlConnection class.

SqlConnection (String) Initializes a new instance of the SqlConnection class if given a string that contains a connection string.

Assuming that we have imported the System.Data.SqlClient namespace, you can generate a new SqlConnection object with the following statement:

SqlConnection mysqlconnection = new SqlConnection ();

Program code Description: In the program code of the above syntax example, we generated a new SqlConnection object by using the "new" keyword, and named it mysqlconnection.

Now we can connect to the database in two ways, that is, using Integrated Windows authentication and using SQL Server Authentication to log on to the database.

Example of Integrated Windows authentication syntax

String connectionstring= "Server=localhost;database=northwind;

Integrated SECURITY=SSPI ";

Program code Description: In the program code of the above syntax example, we set up a connection string for the SQL Server database. Where server represents the name of the computer that is running SQL Server, because in this book, ASP. NET program and the database system are located on the same computer, so we can replace the current computer name with localhost. Database represents the name of the databases used, which is set to a sample database--northwind that comes with SQL Server. Since we want to use Integrated Windows authentication, set integrated security as SSPI.

The Windows Authentication mode in SQL Server 2005 is as follows:

Note: When using the Integrated Windows authentication method, we do not need to enter the username and password, but instead pass the user name and password entered into Windows when you log in to SQL Server. SQL Server then checks the user manifest to see if it has permission to access the database. and the database connection string is case insensitive.

Example of syntax with SQL Server authentication

String connectionString = "Server=localhost;database=northwind;uid=sa;pwd=sa";

Program code Description: In the above syntax example of the program code, using a known user name and password authentication for the database login. The UID is the specified database user name, and PWD is the specified user password. For security reasons, do not include the user name and password in your code, and you can improve the security of your program by using the previous Integrated Windows authentication method or by encrypting the connection string in the Web. config file.

The SQL Server authentication mode in SQL Server 2005 is as follows:

If you use other data providers, the resulting connection string also has a similar form. For example, we want to connect to an Oracle database in OLE DB with the following connection string:

String connectionString = "Data source=localhost;initial catalog=sales;

Use id=sa;password=;p Rovider=msdaora ";

Program code Description: In the program code of the above syntax example, the database connection is implemented through an OLE DB provider specifically for the Oracle database. The data source represents the name of the computer running the Oracle database, and initial catalog represents the database name used. Provider indicates that the OLE DB provider used is MSDAORA.

The connection string for the Access database is in the following form:

String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;

@ "Data Source=c:\datasource\northwind.mdb";

Program code Description: In the program code of the above syntax example, the database connection is implemented through an OLE DB provider specifically for an Access database. This uses the OLE DB provider as microsoft.jet.oledb.4.0, and the database is stored in the C:\DataSource directory with a database file of Northwind.mdb.

Now we can send the database connection string to the SqlConnection () constructor, for example:

String connectionString = "Server=localhost;database=northwind;uid=sa;pwd=sa";

SqlConnection mysqlconnection = new SqlConnection (connectionString);

or write

SqlConnection mysqlconnection =new SqlConnection (

"Server=localhost;database=northwind;uid=sa;pwd=sa");

In the previous example, a new SqlConnection object was generated by using the "new" keyword. So we can also set the ConnectionString property of the object and assign it a database connection string. This is the same as the ability to have the database connection string descendants of the SqlConnection () constructor.

SqlConnection mysqlconnection = new SqlConnection ();

mysqlconnection.connectionstring = "Server=localhost;database=northwind;uid=sa;pwd=sa";

Note: The ConnectionString property can only be set when the connection object is closed.

To open and close a database connection

After you build the connection object and set its ConnectionString property to the appropriate details for the database connection, you can open the database connection. You can call the open () method of the Connection object for this purpose. The method is as follows:

Mysqlconnection.open ();

Once the database is connected, we can call the close () method of the Connection object to close the database connection. For example:

Mysqlconnection.close ();

The following is an instance program that shows how to connect a SQL Server Northwind database with a SqlConnection object, and displays some properties of the SqlConnection object.

The sample program code is as follows:

public partial class _default:system.web.ui.page

02 {

protected void Page_Load (object sender, EventArgs e)

04 {

05//Establish database connection string

The string connectionString = "Server=localhost;database=northwind;

Integrated SECURITY=SSPI ";

08//Pass the connection string into the constructor of the SqlConnection object

SqlConnection mysqlconnection = new SqlConnection (connectionString);

Ten try

11 {

12//Open connection

Mysqlconnection.open ();

14//Use the Label control to display the ConnectionString property of the Mysqlconnection object

Lblinfo.text = " The ConnectionString property of the Mysqlconnection object is: " +

Mysqlconnection.connectionstring + "
";

Lblinfo.text + = " Mysqlconnection Object ConnectionTimeout property is " +

Mysqlconnection.connectiontimeout + "
";

Lblinfo.text + = " Mysqlconnection object's Database property is " +

Mysqlconnection.database + "
";

Lblinfo.text + = " Mysqlconnection Object DataSource property is " +

Mysqlconnection.datasource + "
";

Lblinfo.text + = " Mysqlconnection Object PacketSize property is " +

Mysqlconnection.packetsize + "
";

Lblinfo.text + = " Mysqlconnection Object ServerVersion property is " +

Mysqlconnection.serverversion + "
";

Lblinfo.text + = " Mysqlconnection the current state of the object is " +

Mysqlconnection.state + "
";

29}

catch (Exception err)

31 {

Lblinfo.text = "Error reading database";

Lblinfo.text + = Err. Message;

34}

Finally

36 {

37//Close connection to the database

Mysqlconnection.close ();

Lblinfo.text + = "
the status of the Mysqlconnection object after closing the connection is: ";

Lblinfo.text + = mySqlConnection.State.ToString ();

41}

42}

43}

Program code Description: In the program code of the above example, we use try Catch finally to treat the database connection with exception. An exception is thrown when the database cannot be connected, and an error message is displayed, as shown in the catch code block. In this program, regardless of whether or not an exception occurs, the connection of the database can be closed through the finally block, thus saving computer resources and improving the efficiency and scalability of the program.

Execution Result:

Of course, we can also use a more convenient way to achieve the functions of the above program. This is to include the SqlConnection object in the using chunk, which automatically calls the Dispose () method to release the system resources that the SqlConnection object consumes, eliminating the need to use the close () method of the SqlConnection object.

The sample program code is as follows:

public partial class _default:system.web.ui.page

02 {

protected void Page_Load (object sender, EventArgs e)

04 {

String connectionString = "Server=localhost;database=northwind;

Integrated SECURITY=SSPI ";

SqlConnection mysqlconnection = new SqlConnection (connectionString);

using (mysqlconnection)

09 {

Ten Mysqlconnection.open ();

Lblinfo.text = " The ConnectionString property of the Mysqlconnection object is: " +

Mysqlconnection.connectionstring + "
";

Lblinfo.text + = " Mysqlconnection Object ConnectionTimeout property is " +

Mysqlconnection.connectiontimeout + "
";

Lblinfo.text + = " Mysqlconnection object's Database property is " +

Mysqlconnection.database + "
";

Lblinfo.text + = " Mysqlconnection Object DataSource property is " +

Mysqlconnection.datasource + "
";

Lblinfo.text + = " Mysqlconnection Object PacketSize property is " +

Mysqlconnection.packetsize + "
";

Lblinfo.text + = " Mysqlconnection Object ServerVersion property is " +

Mysqlconnection.serverversion + "
";

Lblinfo.text + = " Mysqlconnection the current state of the object is " +

Mysqlconnection.state + "
";

25}

Lblinfo.text + = "
the status of the Mysqlconnection object after closing the connection is: ";

Lblinfo.text + = mySqlConnection.State.ToString ();

28}

29}

Program code Description: In the program code of the above example, the use of using (mysqlconnection) Form to make the code more concise, and its greatest advantage is no need to write finally block code, you can automatically close the connection to the database.

Connection pool

It is time-consuming to open and close the database. To do this, ADO. NET automatically stores database connections in the connection pool. Connection pooling can significantly improve the performance and efficiency of your programs because we do not have to wait for a completely new database connection process, but rather to take advantage of ready-made database connections. Note that when you close a connection by using the close () method, the connection is not actually closed, but the connection is marked as unused and placed in the connection pool for the next reuse.

If you provide the same details in the connection string, that is, the same database, user name, password, and so on, you can directly obtain and return the connection in the pool. You can then use this connection to access the database.

When using the SqlConnection object, you can specify Max pool size in the connection string to indicate the maximum number of connections allowed by the connection pool (default is 100), or you can specify a min pool size to indicate the minimum number of connections allowed by the connection pool (default is 0). The following code specifies that the max pool size of the SqlConnection object is 10,min pool size of 5.

SqlConnection mysqlconnection = new SqlConnection ("Server=localhost;database=northwind;

Integrated SECURITY=SSPI; " + "Max Pool size=10;min pool size=5");

Program code Description: In the program code of the above example, the program initially generates 5 SqlConnection objects in the pool. Up to 10 SqlConnection objects can be stored in a pool. If the objects in the pool are all in use when you open a new SqlConnection object, the request waits for a SqlConnection object to close before the new SqlConnection object can be used. If the request waits longer than the number of seconds specified by the ConnectionTimeout property, an exception is thrown.

Below is a program to show the performance benefits of the connection pool. In applying this procedure we will first refer to the System.Data.SqlClinet and System.Text namespaces.

The sample program code is as follows:

public partial class _default:system.web.ui.page

02 {

protected void Page_Load (object sender, EventArgs e)

04 {

05//Set connection pool with a maximum number of connections of 5 and a minimum of 1

SqlConnection mysqlconnection =new SqlConnection (

"Server=localhost;database=northwind;integrated Security=sspi;" +

"Max Pool size=5;min pool size=1");

09//Create a new StringBuilder object

Ten StringBuilder htmstr = new StringBuilder ("");

one for (int count = 1; count <= 5; count++)

12 {

13//Use the Append () method to append the string to the end of the StringBuilder object

Htmstr.append ("Connection object" +count);

Htmstr.append ("
");

16//Set a start time for a connection

* DateTime start = DateTime.Now;

Mysqlconnection.open ();

19//time Spent in connection

TimeSpan Timetaken = Datetime.now-start;

Htmstr.append ("Connection time is" +timetaken.milliseconds+ "milliseconds");

Htmstr.append ("
");

Htmstr.append ("Mysqlconnection object's status is" + Mysqlconnection.state);

Htmstr.append ("
");

Mysqlconnection.close ();

26}

27//Displays the contained string of the StringBuilder object in the Label control

Lblinfo.text = Htmstr.tostring ();

29}

30}

Program code Description: In the program code of the above example, we will repeat 5 times in the connection pool to open a SqlConnection object, DateTime.Now represents the current time. Timetaken represents the time interval between the start of a connection and the opening of a connection. As you can see, opening the first connection takes longer than opening a subsequent connection because the first connection is actually connected to the database. After being closed, the connection is stored in the connection pool. When you open the connection again, it is very fast as long as it is read directly from the pool.

Tip: The String object is immutable. Each time you use one of the methods in the System.String class, you create a new string object in memory, which requires a new space to be allocated for the new object. The overhead associated with creating a new string object can be very expensive in situations where you need to perform repeated modifications to the string. If you want to modify a string without creating a new object, you can use the System.Text.StringBuilder class. For example, using the StringBuilder class can improve performance when you concatenate many strings together in a loop. The Append method can be used to add a string representation of text or an object to the end of a string represented by the current StringBuilder object.

in ASP. NET 2.0, a new declarative expression syntax that resolves to a connection string value at run time is used to refer to the database connection string by name. The connection string itself is stored under the configuration section in the Web. config file so that it is easy to maintain for all pages in the application in a single location.

The sample program code is as follows:

Integrated security=true;database=pubs; Persist Security Info=true "

Providername= "System.Data.SqlClient"/>

Integrated Security=true;database=northwind; Persist Security Info=true "

Providername= "System.Data.SqlClient"/>

Program code Description: In the program code of the above example, we set up two database connection strings under the configuration node in the Web. config file, pointing to the pubs and Northwind two sample databases, respectively. Note that the data source control, such as the SqlDataSource Control, is introduced in 2.0, and we can set the ConnectionString property of the SqlDataSource control to an expression <%$ connectionstrings:pubs%>, The expression is resolved by the ASP. NET parser to a connection string at run time. You can also specify an expression for the ProviderName property of SqlDataSource, such as <%$ ConnectionStrings:Pubs.ProviderName%>. Its specific usage and new features will be described in detail in a later chapter. Now there's a basic understanding.

Of course, we can also read the database connection string directly from the configuration file in the following way. First we need to refer to the using System.Web.Configuration namespace, which contains the classes used to set up the ASP.

String connectionString =configurationmanager.connectionstrings["Northwind"]. ConnectionString;

Program code Description: In the program code of the above example, we can use connectionstrings["Northwind" to read the corresponding Northwind string. Similarly, you can use the connectionstrings["pubs"] to read the corresponding pubs string.

———————————————————————————————————————————————

First you should distinguish between Windows authentication and the validation of SQL itself.
Windows authentication is that SQL Server uses the Windows-brought authentication system, and if you specify that a group of Windows within SQL Servers has access permissions, the Windows users who join this group have access to the database. This validation has the disadvantage that if you are not in domain mode, you cannot join a remote computer user, so if you use the C/S method to write a program, use Windows Authentication to not allow the local computer's Windows account to access the remote database server.

SQL authentication is much simpler, which is to use SQL Server's Enterprise Manager to define itself as a user, specify user permissions, and so on. This account information is maintained by SQL Server itself, so the information is not lost and not reset after SQL Server changes the computer.

So if your project is in a larger network, and the security requirements are high, you should establish a domain, use Windows authentication, and, in conjunction with your system administrator, configure the Windows account to access SQL Server in detail. If you use a small network, and this network is only used for projects, there is no high security requirements, then use SQL Server Authentication, and updates, upgrades, etc. are convenient.

Windows authentication is different from the database join string that SQL Server validates.