Update database in C # (1)

During this period of time, I have been learning database programming in C # winform, but I have never figured out how to update the database. I just know how to simply view the database. Well, I didn't know anyone next to it. net is so painful. I can only do anything on my own, even if I don't discuss anything about it. I also want to know a brother on the Internet. If you have any questions, please ask him. Now we have figured out one of the simplest ways to add and update databases.

Today, I finally figured out how to use dataset to update the database. The summary is as follows:

To operate dataset to add, update, and delete databases, you must understand several classes: sqldataadapter class and sqlcommandbuilder class.

* Sqldataadapter class: indicates a set of data commands used to fill the dataset and update the SQL Server database and connect to a database. The bridge between dataset and SQL Server, used to retrieve and save data.SqldataadapterMap fill by using appropriate Transact-SQL statements for the data source (it can be changedDatasetTo match the data in the data source) and update (it can change the data in the data source to matchDatasetTo provide this bridge. In general, the role of this class is to bind the data in the database to the dataset and then perform operations on the dataset. Dataset can be seen as the temporary storage of several tables found in the database in the memory. It does not need to establish a connection with the database all the time, so that the server will not be overwhelmed, because the fill method in the sqldataadapter class has good features: If the Conn is not used. open (), then the conn will be opened when the fill () Statement is executed, and then the conn will be closed after use, if I use Conn. if the open () Statement is executed, the connection will not be closed after the fill () Statement is executed, because other statements may need to be executed during the connection. In this case, the connection must be closed manually. modify the table in dataset, that is, modify the table in the database, but a specific command sqlcommandbuilder is required.

* sqlcommandbuilder class: automatically generates a single table command for the following purposes: coordinates the changes made to dataset with the associated SQL Server database. Sqldataadapter does not automatically generate the transact-SQL statements required for dataset changes and the coordination between the associated SQL Server instances. However, if the selectcommand attribute of sqldataadapter is set, you can create a sqlcommandbuilde r object to automatically generate a Transact-SQL statement for updating a single table. Sqlcommandbuilder then generates any other unconfigured Transact-SQL statements. Once the dataadapter attribute is set, sqlcommandbuilder registers itself as the listener for the rowupdating event. Only one sqldataadapter can be associated with one sqlcommandbuilder object (or the opposite) at a time. To generate insert, update, or delete statements, sqlcommandbuilder automatically uses the selectcommand attribute to retrieve the required metadata. If selectcommand is changed after metadata retrieval (for example, after the first update), The refreshschema method should be called to update metadata. Sqlcommandbuilder also uses the connection, commandtimeout, and transaction attributes referenced by selectcommand. If you modify any of these attributes or replace selectcommand itself, you should call refreshschema. Otherwise, the insertcommand, updatecommand, and deletecommand attributes retain their previous values. If you call dispose, the association between sqlcommandbuilder and sqldataadapter is removed, and the generated command is no longer used. When using the sqlcommandbuilder class, be sure to ensure that the database table to be operated must have a primary key. Otherwise, the command will not be generated automatically and an exception will occur.

InstanceCode:

String STR = "Server = localhost; uid = sa; Pwd =; database = test ";
Sqlconnection conn = new sqlconnection (STR );
Sqldataadapter adapter = new sqldataadapter ("select * from stores", Conn );
// Adapter. selectcommand = new sqlcommand ("select * from stores", Conn );
Sqlcommandbuilder SCB = new sqlcommandbuilder (adapter); // automatically generate various commands
Conn. open ();
Dataset DS = new dataset ();
Adapter. Fill (DS, "stores ");
Conn. Close ();
// Console. writeline (Ds. Tables ["stores"]. Rows [0] [0]. tostring ());
// Ds. Tables ["stores"]. Rows [0] [0] = "1234 ";
Datatable dt = Ds. Tables ["stores"];

// Datarow myrow = DT. newrow (); // Add a new row to the table. This must be the case.
Myrow ["stor_id"] = 8345;
Myrow ["stor_name"] = "King ";
Myrow ["stor_address"] = "hazu ";
Myrow ["city"] = "Wuhan ";
Myrow ["state"] = "AB ";
Myrow ["Zip"] = "12345 ";
DT. Rows. Add (myrow); // Ds. Tables ["stores"]. Rows. Add (myrow); // You can also

// DT. rows. add (new object [] {"0101", "Sha", "hazu", "Wuhan", "BC", "1532 "}); // you can use this statement to add a row. You do not need to pay for the attribute column above.

Adapter. Update (DS, "stores ");
Console. writeline ("modified successfully ");
Console. Read ();

The preceding code is used to add a line. For other codes, such as modifying or deleting a line, the corresponding command is automatically generated if sqlcommandbuilder exists.

There is also a way to modify the information in the database, that is, process storage. I will study it tomorrow. Well, I am so tired. Now I have no time to study C # for graduate students. But I still don't want to give up completely. I still want to learn it in a controlled manner.