C # connecting SQL databases and manipulating databases

1. Overview

ADO provides a wealth of database operations that can be divided into three steps:

• First, connect the database with the SqlConnection object;
• Second, establish the SqlCommand object, which is responsible for the execution of SQL statements and the invocation of stored procedures;
• Third, operate on the results returned after SQL or stored procedure execution.

There are two types of operations that return "results":

• One is to use SqlDataReader directly a row of reading data set;
• The second is DataSet Federated SqlDataAdapter to operate the database.

Compare the two:

• SqlDataReader time with the remote database server to maintain a connection, the remote data through a "stream" in the form of one-way transmission to the client, it is "read-only". Because of the direct access to the database, it is more efficient, but it is inconvenient to use.
• The dataset fetches data locally from the data source and builds a micro database locally (containing tables, rows, columns, rules, relationships between tables, etc.), during which you can disconnect from the server, and use the SqlDataAdapter object to manipulate the local micro database. At the end of the SqlDataAdapter one-time update to the remote database server. This approach is easier to use. But the performance is slightly less than the first kind. (In general, the performance of both can be negligible.) ）

A very well-known map of the structure of the ADO:

2, the connection string of the wording

String connectstring = "Data source=.;i Nitial catalog=student;integrated security=true ";

3. SqlConnection object

namespaces: System.Data.SqlClient.SqlConnection;

Returns the database connection object, the parameter string. Instantiate the Connection object and open the connection

SqlConnection sqlcnt = new SqlConnection (connectstring); Sqlcnt.open ();

After use is complete, you need to close the connection object

Sqlcnt.close ();

4. SqlCommand object

namespaces: System.Data.SqlClient.SqlCommand;

The SqlCommand object is used to perform database operations in three ways:

• SQL statement: Command.commandtype = CommandType.Text;
• Stored procedure: Command.commandtype = CommandType.StoredProcedure;
• Whole sheet: command.commandtype = commandtype.tabledirect;

Instantiate a SqlCommand object

SqlCommand command = new SqlCommand (); command. Connection = sqlcnt;            Binding SqlConnection objects

Or create directly from SqlConnection

SqlCommand command = Sqlcnt.createcommand ();     

Common methods:

• Command. ExecuteNonQuery (): Returns the affected function, such as increment, delete, change operation;
• Command. ExecuteScalar (): Executes the query and returns the result of the first row column;
• Command. ExecuteReader (): Returns a data stream (SqlDataReader object).

Common Operations ① Execute SQL

SqlCommand cmd = conn. CreateCommand ();              Create SqlCommand Object cmd.commandtype = Commandtype.text;cmd.commandtext = "SELECT * FROM products = @ID";   SQL statement cmd. Parameters.Add ("@ID", SqlDbType.Int); cmd. parameters["@ID"]. Value = 1;                    Assigning parameters to parameter SQL statements

② Calling stored Procedures

SqlCommand cmd = conn. CreateCommand ();                      Cmd.commandtype = system.data.commandtype.storedprocedure;cmd.commandtext = "Stored procedure name";

③ whole Sheet

SqlCommand cmd = conn. CreateCommand ();    Cmd.commandtype = System.data.commandtype.tabledirect;cmd.commandtext = "Table name"

5. SqlDataReader Object

namespaces: System.Data.SqlClient.SqlDataReader;

The SqlDataReader object provides read-only one-way data, one-way: The next data can only be read sequentially, read-only: The data in DataReader is read-only, cannot be modified, and the data in the dataset can be arbitrarily read and modified.

It has a very important method, is read (), the return value is a Boolean value, the function is to advance to the next data, a strip of return data, when the Boolean value is true when the execution, false when the jump. Such as

SqlCommand command = new SqlCommand (); command. Connection = Sqlcnt;command.commandtype = Commandtype.text;command.commandtext = "SELECT * from Users"; SqlDataReader reader = command. ExecuteReader ();//Executes SQL, returning a "stream" while (reader. Read ()) {    Console.Write (reader["username"]);//print out the user name of each user}

6, DataSet object 6.1 SqlDataAdapter;

namespaces: System.Data.SqlClient.SqlDataAdapter;

SqlDataAdapter is a bridge between the SqlCommand and the dataset, instantiating SqlDataAdapter objects:

SqlConnection sqlcnt = new SqlConnection (connectstring); Sqlcnt.open ();//create Sqlcommandsqlcommand Mysqlcommand = new SqlCommand (); mysqlcommand.commandtype = Commandtype.text;mysqlcommand.commandtext = "SELECT * from Product"; Mysqlcommand.connection = sqlcnt;//Create Sqldataadaptersqldataadapter mydataadapter = new SqlDataAdapter (); Mydataadapter.selectcommand = mysqlcommand;//The SqlCommand object to be executed for SqlDataAdapter object binding

The above SQL can be simplified to

SqlConnection sqlcnt = new SqlConnection (connectstring); Sqlcnt.open ();//Hides the definition of SqlCommand object, The binding of the SqlCommand object to the SqlDataAdapter object is also hidden SqlDataAdapter mydataadapter = new SqlDataAdapter ("SELECT * from Product", sqlcnt );

Properties and Methods

• Mydataadapter.selectcommand property: SqlCommand variable, encapsulates the SELECT statement;
• Mydataadapter.insertcommand property: SqlCommand variable, encapsulating INSERT statement;
• Mydataadapter.updatecommand property: SqlCommand variable, encapsulates the UPDATE statement;
• Mydataadapter.deletecommand property: SqlCommand variable, encapsulates the DELETE statement.
• Mydataadapter.fill (): Fills the execution result into the dataset, hiding operations such as opening SqlConnection and executing SQL.

6.2 SqlCommandBuilder;

Namespace: System.Data.SqlClient.SqlCommandBuilder.

The operation of the dataset (change, increment, delete) is only locally modified, and the SqlCommandBuilder object is required to commit to the database. Used to update data once after the client has finished editing the data. The specific usage is as follows:

SqlCommandBuilder Mysqlcommandbuilder = new SqlCommandBuilder (mydataadapter);  For MyDataAdapter to give SqlCommandBuilder function mydataadapter.update (mydataset, "table name");                   After committing the changed dataset to the database, the second parameter is the stored table name in the dataset, not the actual table name in the database (which in most cases is consistent).

6.3 DataSet

Namespace: System.Data.DataSet.

datasets, local micro-databases, can store multiple tables.

The first step in using a dataset is to populate the DataSet object with the data set (table) returned by SqlDataAdapter:

SqlDataAdapter mydataadapter = new SqlDataAdapter ("SELECT * from Product", sqlcnt);D ataset mydataset = new DataSet ();//Create Datasetmydataadapter.fill (myDataSet, "product");//fill in the dataset with the returned data set as "table", the table name can be different from the real table name of the database, and does not affect the subsequent operations such as add, delete, change, etc.

① accessing data in a dataset

SqlDataAdapter mydataadapter = new SqlDataAdapter ("SELECT * from Product", sqlcnt);D ataset mydataset = new DataSet (); Mydat Aadapter.fill (myDataSet, "product");D atatable myTable = mydataset.tables["Product"];foreach (DataRow myrow in mytable.rows) {    foreach (DataColumn myColumn in Mytable.columns) {        Console.WriteLine (Myrow[mycolumn]);// Iterate through each cell in the table    }}

② modifying data in a dataset

SqlDataAdapter mydataadapter = new SqlDataAdapter ("SELECT * from Product", sqlcnt);D ataset mydataset = new DataSet (); Mydat Aadapter.fill (myDataSet, "product");//modify datasetdatatable myTable = mydataset.tables["Product"];foreach (DataRow Myrow in mytable.rows) {    myrow["name"] = myrow["name" + "Commodity";} Commit the changes to the dataset to "database" SqlCommandBuilder Mysqlcommandbuilder = new SqlCommandBuilder (MyDataAdapter); Mydataadapter.update (myDataSet, "product");

Note: In operations such as modify, delete, table product must define a primary key, and the field in select must also contain a primary key, otherwise the "dynamic SQL generation of UpdateCommand is not supported for SelectCommand that does not return any key column information." "Error

③ Add a row

SqlDataAdapter mydataadapter = new SqlDataAdapter ("SELECT * from Product", sqlcnt);D ataset mydataset = new DataSet (); Mydat Aadapter.fill (myDataSet, "product");D atatable myTable = mydataset.tables["Product"];//add a line datarow myrow = Mytable.newrow (); myrow["name"] = "Czech"; myrow["price"] = 13.2;//myrow["id"] = 100; If the ID is "autogrow", it can not be set here, even if the setting is invalid MYTABLE.ROWS.ADD (myrow);//commits the modification of the dataset to the database SqlCommandBuilder Mysqlcommandbuilder = New SqlCommandBuilder (MyDataAdapter); Mydataadapter.update (myDataSet, "product");

④ Delete a row

SqlDataAdapter mydataadapter = new SqlDataAdapter ("SELECT * from Product", sqlcnt);D ataset mydataset = new DataSet (); Mydat Aadapter.fill (myDataSet, "product");//delete First row datatable myTable = mydataset.tables["Product"];mytable.rows[0]. Delete (); SqlCommandBuilder Mysqlcommandbuilder = new SqlCommandBuilder (mydataadapter); Mydataadapter.update (MyDataSet, " Product ");

Property

• Tables: Gets the collection of tables contained in the DataSet.
• Relations: Gets a collection of relationships that are used to link the tables and allow browsing from the parent table to the child tables.
• Haseroors: Indicates whether the value of the DataSet object has been initialized.

Method

• Clear clears all data from all tables in the DataSet object.
• Clone copies the structure of the DataSet object into another DataSet object, and the copied content includes all structures, relationships, and constraints, but does not contain any data.
• Copy copies the data and structure of the DataSet object into another DataSet object. Two DataSet objects are exactly the same.
• CreateDataReader returns a datatablereader with a result set for each DataTable object in the same order as the table in the Tables collection.
• Dispose frees the resources that the DataSet object occupies.
• Reset initializes the DataSet object.

7. Releasing Resources

After the use of resources should be closed in time to close the connection and release, the following methods:

Mydataset.dispose ();        Releases the DataSet object Mydataadapter.dispose ();    Release SqlDataAdapter object Mydatareader.dispose ();     Release SqlDataReader object Sqlcnt.close ();             Close database connection sqlcnt.dispose ();           Releasing the database Connection object

C # connecting SQL databases and manipulating databases