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.) ）

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 a SqlCommand object
Cmd.commandtype = CommandType.Text;
Cmd.commandtext = "SELECT * FROM products = @ID"; SQL statements
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 ();//Execute SQL, return 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 SqlCommand
SqlCommand Mysqlcommand = new SqlCommand ();
Mysqlcommand.commandtype = CommandType.Text;
Mysqlcommand.commandtext = "SELECT * from Product";
Mysqlcommand.connection = sqlcnt;

Create SqlDataAdapter
SqlDataAdapter 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 the SqlCommand object, while hiding the binding of the SqlCommand object to the SqlDataAdapter object
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); Assigning SqlCommandBuilder functionality to MyDataAdapter
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);
DataSet myDataSet = new DataSet ();//Create DataSet
Mydataadapter.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);
DataSet myDataSet = new DataSet ();
Mydataadapter.fill (myDataSet, "product");

DataTable 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);
DataSet myDataSet = new DataSet ();
Mydataadapter.fill (myDataSet, "product");

Modify a DataSet
DataTable myTable = mydataset.tables["Product"];
foreach (DataRow myrow in mytable.rows) {
myrow["name"] = myrow["name"] + "commodity";
}

Commit the changes to the dataset to the 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);
DataSet myDataSet = new DataSet ();
Mydataadapter.fill (myDataSet, "product");
DataTable myTable = mydataset.tables["Product"];

Add a row
DataRow myrow = Mytable.newrow ();
myrow["name"] = "the Czech special";
myrow["price"] = 13.2;
myrow["id"] = 100; If the ID is auto-grow, it can not be set here, even if the setting is not valid
MYTABLE.ROWS.ADD (Myrow);

Commit the changes to 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);
DataSet myDataSet = new DataSet ();
Mydataadapter.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 (); Releasing a DataSet object
Mydataadapter.dispose (); Releasing the SqlDataAdapter object
Mydatareader.dispose (); Releasing the SqlDataReader object
Sqlcnt.close (); To close a database connection
Sqlcnt.dispose (); Releasing the database Connection object

C # connecting SQL databases and manipulating databases