Post: Ado. Net adapter operation data

For Learning ADO. for net data access technology, dataadapter may often confuse them, especially those who are used to developing dataadapter controls. NET data access control, we do not even need to write a line Code You can complete all kinds of data access and operations you need. However, while enjoying fast and convenient operations, you will always feel a little bit inactive. What does the dataadapter control do for us? To clarify this point, it is not only pleasant but also helpful for us to better understand ADO.. NET data access mechanism. net for us to generate a lot of inexplicable code about data access, many of which we may not be able to use, so it is really uncomfortable, even under visual2005. net has used the local class mechanism to hide the code.

In this articleArticleIn, I manually write code to complete data access using dataadapter, so that new users can understand the running conditions behind dataadapter. Here, I use the sqlserver2005express database as the data source server, so I need to use the sqldataadapter Data Access Object. Other oledbdataadapter and other objects are similar to this, I also hope this article will play a minor role for beginners. First, create a database in sqlserver2005express as a data source. The implementation of this process can be completed through data definition statements or directly using a visual interface. The data is displayed using the datagridview control. The detailed code is as follows:

Public partial class form3: Form

{

Private sqlconnection mycon;

Private sqldataadapter myada;

Private sqlcommand mycmd;

Private sqlcommandbuilder mycbd;

Private dataset myset;

Public form3 ()

{

Initializecomponent ();

Mycon = new sqlconnection ("Data Source = localhost \ sqlexpress; initial catalog = shop; persist Security info = true; user id = sa; Password = ");

Myada = new sqldataadapter ();

Mycmd = new sqlcommand ("select * From userinfo", mycon );

Myada. selectcommand = mycmd;

Mycbd = new sqlcommandbuilder (myada );

Myset = new dataset ();

Myada.Tablemappings. Add ("userinfo", "userinfo ");

Myada.Tablemappings[0]. columnmappings. Add ("userid", "user ");

Myada.Tablemappings[0]. columnmappings. Add ("username", "User Name ");

Myada.Tablemappings[0]. columnmappings. Add ("userage", "Age ");

Myada.Tablemappings[0]. columnmappings. Add ("usersex", "gender ");

Myada.Tablemappings[0]. columnmappings. Add ("useraddress", "Address ");

}

Private void form3_load (Object sender, eventargs E)

{

Try

{

Myada. Fill (myset, "userinfo ");

}

Catch (sqlexception ex)

{

MessageBox. Show (ex. tostring ());

}

Finally

{

Mycon. Close ();

}

Datagridview1.datasource = myset. Tables ["userinfo"];

}

 

Private void bt_update_click (Object sender, eventargs E)

{

Try

{

// Update the changed data to the data table

Myada. Update (myset. Tables [0]. getchanges ());

MessageBox. Show ("data update successful! ");

// Datatable accepts changes to prepare for the next change

Myset. Tables [0]. acceptchanges ();

}

Catch (sqlexception ex)

{

Ex. tostring ();

}

}

 

Private void bt_delete_click (Object sender, eventargs E)

{

// Delete the currently selected row from datatable

Myset. Tables [0]. Rows [maid. currentrow. Index]. Delete ();

If (MessageBox. Show ("are you sure you want to delete the data of the current row? "," ", Messageboxbuttons. okcancel) = dialogresult. OK)

{

Try

{

// Update the changed data to the data table

Myada. Update (myset. Tables [0]. getchanges ());

MessageBox. Show ("data deleted successfully! ");

// Datatable accepts changes to prepare for the next change

Myset. Tables [0]. acceptchanges ();

}

Catch (sqlexception ex)

{

MessageBox. Show (ex. tostring ());

}

}

Else

{

// Cancel the change to the able

Myset. Tables [0]. rejectchanges ();

}

}

}

OK. The above code has been able to update and delete the database. Is it concise? What you write is friendly, so you are familiar with it. The following is an analysis.

Private sqlconnection mycon;

Private sqldataadapter myada;

Private sqlcommand mycmd;

Private sqlcommandbuilder mycbd;

Private dataset myset;

Let's look at these statements. What are they doing? declare several variables of the reference type and let them reference them? Let's take a look at the name of the variable type. For example, mycon is a sqlconnection type, so it must be used to reference a connection type, but at this moment they haven't actually referenced anything, just like a child born from a mother, ha ha, has not yet been born, friends and family together, are happy for this, first give the name. Go down,

Mycon = new sqlconnection ("Data Source = localhost \ sqlexpress; initial catalog = shop; persist Security info = true; user id = sa; Password = ");

Myada = new sqldataadapter ();

Mycmd = new sqlcommand ("select * From userinfo", mycon );

Myada. selectcommand = mycmd;

Mycbd = new sqlcommandbuilder (myada );

Myset = new dataset ();

In this case, your credit is huge. The objects referenced by the variables mentioned above are now born, like the children born, they have a precious life from this moment on. In fact, the code used to drag controls through the toolbox can basically be replaced by the code so far. For example, if you drag a sqlconnection control and set the connection information attribute, it is equal to the following two sentences of code. However, today we are really constructing our own systems. If we drag a sqlconnection control, it is automatically constructed by the Microsoft. NET environment. The methods are different, the effects are the same, and the mood is different.

Private sqlconnection mycon;

Mycon = new sqlconnection ("Data Source = localhost \ sqlexpress; initial catalog = shop; persist Security info = true; user id = sa; Password = ");

This is a key sentence. It is necessary to explain it.

Mycbd = new sqlcommandbuilder (myada );

What is this? Let's talk about the usage of sqlcommandbuilder first. We can literally translate SQL into SQL, which is called the Structured Query Language; command, nameorder; builder, construction, and construction, not very good. People have brought an ER and all those who have learned E-paper know that this is a noun. Then translate it into a constructor, and the builder can say it. The SQL command constructor understands that it is used to construct SQL commands. For example, if you need an update to update the database, you can use this statement to construct the constructor. So how does the SQL command constructor generate the SQL statement command that we need? How does it know what we want? In fact, it does not know. We gave it a prompt, but it is too clever. Just like this, when we prompt information, we will draw a different picture. Based on this prompt, we can infer our needs. So where can we get a prompt? Look at this myada. selectcommand = mycmd; we gave the SQL command for query and selectcommand FOR THE dataadaper data adapter. We also gave the dataadaper data adapter (myada here) as a parameter, I threw it to sqlcommandbuilder. This is good. It was caught by sqlcommandbuilder, so it is easy and pleasant. According to the selectcommand information of the caught dataadaper, all other SQL operation commands are generated. A friend may have asked, Do I need this command constructor to generate SQL operation command lines? Of course it is okay. If we always think that what it automatically creates does not meet our requirements, we can write it flexibly, as shown below:

Myada. deletecommand = new sqlcommand ("delete * From userinfo where .....")

After the above ups and downs, everything is ready, and our data adapter can use its own tool sqlcommand to operate the database; and so on, a bit of a problem, the field names in our data table start with English.ProgramRunning is also displayed in English by default. As a Chinese speaker, this cannot be forgiven. Our square words look pleasing to the eye. Check the following code:

Myada.Tablemappings. Add ("userinfo", "userinfo ");

Myada.Tablemappings[0]. columnmappings. Add ("userid", "user ");

Myada.Tablemappings[0]. columnmappings. Add ("username", "User Name ");

Myada.Tablemappings[0]. columnmappings. Add ("userage", "Age ");

Myada.Tablemappings[0]. columnmappings. Add ("usersex", "gender ");

Myada.Tablemappings[0]. columnmappings. Add ("useraddress", "Address ");

Microsoft also respects us and provides the tablemappings attribute through dataadaper, using it, we can easily convert the strange field names in the database into what we need. This is called ing. Now it's almost the same. We have to start to take action. We have to take the database into consideration, and we have made so many preparations at the beginning, so we can easily proceed. The first step is to query data from a data table, that is, the commonly used select, myada. Fill (myset, "userinfo"); that's enough. Is that simple? Yes. What did myada do for us? It does a lot of work. First, it needs to automatically open our database connection, which is the connection object defined in the Code above. After the selectcommand command for finding the myada data adapter, run the selectcommand command to query the data in the data table, and put the extracted data into a able (userinfo here) in dataset (myset here). After everything is done, it will also be very responsible to close the connection opened by itself, haha, a good guy! The query is complete, the update is complete, and the deletion is also closed. After the data is edited in the datagridview control, the changes are first reflected in the datatable of the associated dataset, the next step is to use the data adapter. You can call its update universal method directly and write the changes to the source data table. here, the omnipotent sqlcommand of dataadaper is still behind it. We have already configured it for them. Here we will hand over everything to the update method. here is myset. tables [0]. acceptchanges (), which is very useful. If you want to update a record and then update the second record, an exception will occur because after an update, dataset suspends this update and repeats the previous pending change at the next update. Therefore, after each update, you must call acceptchanges to clear the pending changes.

standing by ADO. net to operate on the database is really too convenient, there are a lot of methods, skilled will find that how I write can achieve operations on the database, a very casual feeling. I 've been messing around with dataadapter for a long time, but it's just a little bit of a piece of code. Let's explore the in-depth content together.