C # Access database

C # access to the database is undoubtedly very convenient, the following gives the basic method of C # access to MSSQL.

First declare the namespace using System.Data.SqlClient;

Then using the SqlConnection class connection, the SqlCommand class executes the SQL command, and the result is returned to the SqlDataReader class or other class.

Introduction of basic usage of various types:

SqlConnection class

Passing in the connection database string at construction time can also be defined and set by the ConnectionString property.

Connect the database with open () and close the connection with close ().

SqlCommand class

Passing in SQL commands and SqlConnection class objects at construction time can also be defined and set by the CommandText property and the Connection property.

Execute SQL command and get results SqlDataReader ExecuteReader ()

Execute SQL command and get the number of rows affected int ExecuteNonQuery ()

SqlDataReader class

Get the total number of columns FieldCount property

Gets the column name of column I, string GetName (int i)

Get column I data type getfieldtype (int i)

Determine if column I is empty bool IsDBNull (int i)

The data given in column I, Object GetValue (int i) can also be directly sr[i]. ToString ()

Forward to next column bool Read ()

Close void Close ()

For each associated SqlConnection, only one SqlDataReader can be opened at a time, and any attempt to open another will fail until the first one is closed. Similarly, when using SqlDataReader, the associated SqlConnection is busy servicing it until Close () is called.

1     //connecting to a database2     stringSQLCONNECT =@"server=pc-200907281720\sqlexpress;database=master;uid=morewindows;pwd=12345"; 3SqlConnection conn =NewSqlConnection (SQLCONNECT); 4 Conn.  Open (); 5       6     //After you connect to the database, you can execute the SQL command.7     //use ExecuteReader () of the SqlCommand class to return the result of execution8     stringSQLCOMMAND ="Select CName, names, C4. Lastlogin from C4, S4 where C4. Lastlogin = S4. Lastlogin ORDER by CName, names"; 9SqlCommand sqlcmd =NewSqlCommand (SqlCommand, conn); TenSqlDataReader sr =sqlcmd.  ExecuteReader ();  One        AConsole.WriteLine ("Number of columns:"+Sr.     FieldCount);  -                -Console.WriteLine ("the column types are:");  the     intNsqlcol =Sr.  FieldCount;  -      for(inti =0; i < Nsqlcol; ++i) -Console.Write (Sr. GetFieldType (i) +" ");  - Console.WriteLine ();  +        -      while(Sr.) Read ()) +     {   A      for(inti =0; i < Nsqlcol; i++)   atConsole.Write (Sr[i]. ToString () +" ");  - Console.WriteLine ();  -     }   - Sr.  Close ();  -        -     //returns the number of rows affected by the ExecuteNonQuery () of the SqlCommand class.  in     stringSQLCOMMAND2 ="UPDATE dbo. Messages set messagenum= ' where messageid= ' 2 '";  -SqlCommand SQLCMD2 =NewSqlCommand (SQLCOMMAND2, conn);//You can also use SQLCMD. ConnectionString = SQLCOMMAND2 instead to     intNresult =SQLCMD2.  ExecuteNonQuery ();  +Console.WriteLine ("number of rows affected:"+nresult);  -        theConn. Close ();

C # Access database