C # access a database using LINQ

C #'s LINQ method is undoubtedly very convenient to access the database. The following describes the basic method for accessing MSSQL using the LINQ method.

 

 

First declare the namespace using System. Data. SqlClient;

 

Then use the SqlConnection class connection. The SqlCommand class executes the SQL command and returns the result to the SqlDataReader class or other classes.

 

 

 

Basic usage:

 

SqlConnection

 

Input a database connection string during construction. You can also define it and set it through the ConnectionString attribute.

 

Use Open () to connect to the database, and Close () to Close the connection.

 

 

 

SqlCommand class

 

The SQL command and SqlConnection class object are passed in during construction. You can also define the object and set it through the CommandText and Connection attributes.

 

Run the SQL command and obtain the result SqlDataReader ExecuteReader ()

 

Run the SQL command and obtain the affected number of rows: int ExecuteNonQuery ()

 

 

 

SqlDataReader class

 

Obtain the total number of FieldCount attributes.

 

Obtain the column name string GetName (int I) of column I)

 

Obtain the Data Type GetFieldType (int I) of column I)

 

Determines whether column I is empty. bool IsDBNull (int I)

 

You can obtain the data Object GetValue (int I) in column I or directly sr [I]. ToString ()

 

Forward to the next column bool Read ()

 

Close void Close ()

 

 

 

 

 

For each associated SqlConnection, only one SqlDataReader can be opened at a time. Before the first connection is closed, any attempt to open the other will fail. Similarly, when SqlDataReader is used, the associated SqlConnection is busy providing services for it until Close () is called.

 

 

 

The following sample code:

 

<Pre class = "csharp" name = "code"> <span style = "font-size: 16px;"> // connect to the database

String SQLCONNECT = @ "server = PC-200907281720 \ SQLEXPRESS; database = master; uid = morewindows; pwd = 12345 ";

SqlConnection conn = new SqlConnection (SQLCONNECT );

Conn. Open ();

 

// After connecting to the database, you can execute the SQL command.

// Use ExecuteReader () of the SqlCommand class to return the execution result

String SQLCOMMAND = "select CName, names, C4.LastLogin from C4, S4 where C4.LastLogin = S4.LastLogin order by CName, names ";

SqlCommand sqlcmd = new SqlCommand (SQLCOMMAND, conn );

SqlDataReader sr = sqlcmd. ExecuteReader ();

 

Console. WriteLine ("Number of columns:" + sr. FieldCount );

Console. WriteLine ("the column types are :");

Int nSqlCol = sr. FieldCount;

For (int I = 0; I <nSqlCol; ++ I)

Console. Write (sr. GetFieldType (I) + "");

Console. WriteLine ();

 

While (sr. Read ())

{

For (int I = 0; I <nSqlCol; I ++)

Console. Write (sr [I]. ToString () + "");

Console. WriteLine ();

}

Sr. Close ();

 

// Return the number of affected rows through ExecuteNonQuery () of the SqlCommand class.

String SQLCOMMAND2 = "update dbo. Messages set MessageNum = '15' where MessageID = '2 '";

SqlCommand sqlcmd2 = new SqlCommand (SQLCOMMAND2, conn); // you can also use sqlcmd. ConnectionString = SQLCOMMAND2

Int nResult = sql00002.executenonquery ();

Console. WriteLine ("affected rows:" + nResult );

 

Conn. Close (); </span>

 

 

Reprinted please indicate the source, original address: http://blog.csdn.net/morewindows/article/details/6858216