C # how to connect to the database,

C # how to connect to the database,

The technology used by ASP. NET to connect to a database is ADO. NET. It is a bunch of classes used to submit SQL statements to the database. The SQL Server 2008 database is connected here, and other databases are used in similar ways, that is, the called class names are different.

First, configure the database connection string on the Web. config (add application Configuration file App. config locally, add System. Configuration. dll Assembly locally) and add the following Node Code:

<ConnectionStrings>

<Add name = "connstr" connectionString = "Data Source =.; Initial Catalog = Milk; User Id = sa; Password = 521521521;"> </add>

</ConnectionStrings>

Name is the name of the link string. ConnectionString is the link string. The IP address of the server where the Data Source database is located. Initial Catalog is the database name. The User Id is a database User, and sa is the administrator account with the highest permissions. you need to use it with caution. Instead, you need to create a dedicated restricted account for the database. Password is the Password.

When the program uses the database, it first extracts the connection string of the configuration file and assigns a value to a variable. The Code is as follows:

Public static readonly string connstr =

ConfigurationManager. ConnectionStrings ["connstr"]. ConnectionString;

ConfigurationManager is a static class that provides access to the client application configuration file.

Then open the database connection and use using to automatically release the connection after use:

SqlConnection is a sealed class that indicates an open connection to the SQL Server database. Next, execute the SQL statement. First, define the SqlCommand class used to send commands to the database. Then, confirm that the connection object to be executed is conn, and then confirm the SQL statement to be executed. The usage example is as follows:

// SqlConnection is the object for establishing a connection to the database.

Using (SqlConnection conn = new SqlConnection (connstr ))

{

Conn. Open (); // Open the connection

// Create an object SqlCommand that sends a Command to the database through a connection

Using (SqlCommand cmd = conn. CreateCommand ())

{

Cmd. CommandText = "Insert into T_Student * (Name, Age) values ('xxx', 18)"; // The SQL statement to be executed by CommandText

Cmd. ExecuteNonQuery (); // Execute

}

}

ExecuteNonQuery () is generally used to execute Update, Delete, and Insert statements.

ExecuteScalar () is used to execute results returned from one row and one column. It returns the object type. Example:

Using (SqlConnection conn = new SqlConnection (connstr ))

{

Conn. Open (); // Open the connection

// Create an object SqlCommand that sends a Command to the database through a connection

Using (SqlCommand cmd = conn. CreateCommand ())

{

Cmd. CommandText = "select Name from T_Student where Name =" XXX "; // The SQL statement to be executed by CommandText

Cmd. ExecuteScalar (); // Execute

}

}

ExecuteReader () is used to return multiple rows of results. SqlDataReader of the return type needs to be released. Example:

Using (SqlConnection conn = new SqlConnection (connstr ))

{

Conn. Open (); // Open the connection

// Create an object SqlCommand that sends a Command to the database through a connection

Using (SqlCommand cmd = conn. CreateCommand ())

{

Cmd. CommandText = "select * from T_Student where Age <18 ";

Using (SqlDataReader reader = cmd. ExecuteReader ()

{

While (reader. Read ())

{

String name = reader. GetString (1); // obtain the value of the first column

Int age = reader. GetIn32 (2); // obtain the value of the 2nd Column

Console. WriteLine (name );

Console. WriteLine (age. ToString ());

}

}

}

}

The Read method returns the bool type. The query result is stored in the database, but not on the client. Before the initial Pointer Points to the first data entry, the Reader pointer moves down one entry every time it is called. If it is not moved to the last entry, the system returns true. Reader's GetString \ GetInt32 and other methods only accept Integer Parameters, that is, the serial number. Use the GetOrdinal method to dynamically obtain the serial number based on the column name.

 

0th Columns	First column	Column 2	Column 3
Id	Name	Age	Holobby
1	XXX	18	Hook up with sister
2	Wang Xu	30	Hook up with sister

Figure 1 Database T_Student table

 

To avoid database injection vulnerabilities, Microsoft has set query parameters, for example:

Cmd. CommandText = "select * from T_Student where Age <@ Age ";

Cmd. Parameters. Add (new SqlParameter ("@ Age", 19 ));

Here, @ Age is set as the query parameter, but the @ parameter cannot replace the table name, field name, select, and other keywords.

 

SqlDataReader is related to the connection. The query results in SqlDataReader are not placed in the program, but in the database server. SqlDataReader is just equivalent to a cursor, pointing to where to read.

ADO. NET provides a DataSet mechanism, DataSet, which has local memory. It contains several able and DataTable contains several rows of DataRow. Usage:

DataSet dataset = new DataSet ();

SqlDataAdapter adapter = new SqlDataAdapter (cmd );

Adapter. Fill (dataset );

SqlDataAdapter is a class that helps us to fill the SqlCommand query results into DataSet. DataSet is equivalent to a local list set (small database ). The Traversal method is as follows:

DataTable table = dataset. Tables [0]; // generally, there is only one Tables. When multiple select statements are executed simultaneously, there are multiple Tables.

DataRowCollection rows = table. Rows;

For (int I = 0; I <rows. Count; I ++)

{

DataRow row = rows [I];

Int age = (int) row ["Age"]; // traverses the age

}

Basically all the steps are: Open the link -- create command -- execute -- process the execution result. Therefore, you can write a public class for your own use to avoid repeated code. The detailed code is as follows:

Public static class SqlHelper

{

Public static readonly string connstr =

ConfigurationManager. ConnectionStrings ["connstr"]. ConnectionString;

 

Public static SqlConnection OpenConnection () // establish a connection

{

SqlConnection conn = new SqlConnection (connstr );

Conn. Open ();

Return conn;

}

 

Public static int ExecuteNonQuery (string plain text,

Params SqlParameter [] parameters) // note that variable-length parameters are simplified.

{

Using (SqlConnection conn = new SqlConnection (connstr ))

{

Conn. Open ();

Return ExecuteNonQuery (conn, plain text, parameters );

}

}

 

Public static object ExecuteScalar (string plain text,

Params SqlParameter [] parameters)

{

Using (SqlConnection conn = new SqlConnection (connstr ))

{

Conn. Open ();

Return ExecuteScalar (conn, plain text, parameters );

}

}

 

Public static DataTable ExecuteDataTable (string plain text,

Params SqlParameter [] parameters)

{

Using (SqlConnection conn = new SqlConnection (connstr ))

{

Conn. Open ();

Return ExecuteDataTable (conn, plain text, parameters );

}

}

 

Public static int ExecuteNonQuery (SqlConnection conn, string plain text,

Params SqlParameter [] parameters)

{

Using (SqlCommand cmd = conn. CreateCommand ())

{

Cmd. CommandText = plain text;

Cmd. Parameters. AddRange (parameters );

Return cmd. ExecuteNonQuery (); // return the number of rows executed

}

}

 

Public static object ExecuteScalar (SqlConnection conn, string plain text,

Params SqlParameter [] parameters)

{

Using (SqlCommand cmd = conn. CreateCommand ())

{

Cmd. CommandText = plain text;

Cmd. Parameters. AddRange (parameters );

Return cmd. ExecuteScalar ();

}

}

 

Public static DataTable ExecuteDataTable (SqlConnection conn, string plain text,

Params SqlParameter [] parameters)

{

Using (SqlCommand cmd = conn. CreateCommand ())

{

Cmd. CommandText = plain text;

Cmd. Parameters. AddRange (parameters );

Using (SqlDataAdapter adapter = new SqlDataAdapter (cmd ))

{

DataTable dt = new DataTable ();

Adapter. Fill (dt );

Return dt;

}

}

}

 

Public static object ToDBValue (this object value)

{

Return value = null? DBNull. Value: value;

}

 

Public static object FromDBValue (this object dbValue)

{

Return dbValue = DBNull. Value? Null: dbValue;

}

}

Encapsulation Method principle: Put unchanged in the method, put changed in the parameter, SqlHelper class returns a row and a column using ExecuteScaler, executeNonQuery is generally used to execute the Update \ Delete \ Insert statement. ExecuteDataTable is only used to execute SQL statements with fewer query results. The returned value is DataTable.

In the database, NULL and "" are different, and NULL and 0 are different. NULL indicates "unknown" in the database ". Assume that a table has an empty field Name. If several instance names are NULL,

Select * from T_Student where Name = NULL cannot find any data.

Select * from T_Student where Name is NULL. You can find all data whose names are NULL.

Requirement: If no name is input, the name should be NULL. If no age is input, the age should be NULL.

Problem: If the SqlParameter parameter value is null, it indicates that the parameter value is not provided and an error is returned.

Solution:. NET provides DBNull. Value to indicate NULL in the database. DBNull. Value is of the object type. So we need to use this:

Object objName;

String name = tbName. Text;

If (name. Length <0)

{

ObjName = DBNull. Value;

} Else

{

ObjName = name;

}

Change the SqlParameter parameter to objName.

At the same time, when reading the database, Null exists, and the Value returned to. NET is also DBNull. Value. Therefore, you must assign values after reading the data as follows:

If (row ["Name"] = DBNull. Value)

{

Name = null;

}

Else

{

Name = (string) row ["Name"];

}

But there is also a problem. If it is of the int type, it cannot be null. In this case, int? Age.

The values 0, NULL, and "" must be strictly distinguished; otherwise, it is difficult to find problems.

The two functions in SqlHelper mentioned last time are encapsulation of the above two usage and can be directly used in the SqlParameter parameter.

 

NULL data can be used for database input.

Public static object ToDBValue (this object value)

{

Return value = null? DBNull. Value: value;

}

Used for output

Public static object FromDBValue (this object dbValue)

{

Return dbValue = DBNull. Value? Null: dbValue;

}

This is mainly used in SqlParameter to implement input and output of NULL data.