Ado. Net Learning

I have been learning some knowledge about ADO. net over the past few days. Now I will make a summary of the access to the ADO. NET database so that I can exchange and learn with you.

To use the database access object provided by ADO. net, you must first Program To reference two namespaces: system. Data and system. Data. sqlclient. Note that the two namespaces are different. The former does not include the latter. The system. Data namespace provides access to classes that represent the ADO. net structure. You can use ADO. Net to generate components to effectively manage data from multiple data sources. For example, the dataset and able classes we often use are provided by the classes in this namespace. The system. Data. sqlclient namespace is the. NET Framework data provider of the SQL Server database. The commonly used sqlconnection, sqlcommand, sqldataadapter, and sqldatareader objects all provide this namespace.

Sqlconnection. It is used to connect to the SQL Server database and manage transactions on the database.

Sqlcommand. It is used to issue SQL commands to the SQL Server database. Executenonquery () can be used to perform directory operations (such as querying the database structure or creating database objects such as tables), or by executing update, insert, or delete statements, change the data in the database without using dataset.

Sqldatareader. It is used to read only data record streams from the SQL server data source.

To create a sqldatareader, you must call the executereader method of the sqlcommand object instead of using the constructor directly. In addition, sqldatareader can read data from the database only when the database connection is maintained (the SQL connection object shows the execution of open.

Dataset. It is used to store, remotely process, and program single-layer data, XML data, and relational data. Dataset disconnects from the database after the sqldataadapter object calls the fill () method. It is like a copy of the database in the memory. We can program any operation on the data in it without worrying about the data resident location. All files from databases, XML files,CodeOr user input data can be added to the DataSet object. Because dataset has been changed, these changes can be tracked and verified before the source data is updated. The modification is reflected to the database only when sqldataadapter executes Update.

Sqldataadapter. A bridge between a database and a DataSet object. You can open a database connection implicitly. It is used to push data into dataset and make the data consistent with the database.

The following example shows how to obtain the names of all albums in the album table.

Generally, we place the access and operation code for a table in the SQL Server database in a CS file, that is, in the same class.

First, we declare the reference variables of the objects we use in the ADO. NET data handler class. Note that the Declaration is not instantiated.

Private sqlconnection sqlconn;

Private sqlcommand sqlcmd;

Private sqldatareader sqlreader;

/// <Summary>
/// Obtain all album names in the album table
/// </Summary>
Public void getalbum ()
{

String SQL = "select title from album ";

Sqlconn = new sqlconnection ();
Sqlconn. connectionstring = configurationmanager. connectionstrings ["Mu"]. connectionstring;

Sqlcmd = new sqlcommand ();
Sqlcmd. Connection = sqlconn;
Sqlcmd. commandtext = SQL;

Try
{
Sqlcmd. Connection. open ();
Sqlreader = sqlcmd. executereader ();

// Sqldatareader. hasrows attributes
// Obtain a value indicating whether the sqldatareader contains one or more rows

If (sqlreader. hasrows)
{

// Sqldatareader reads () only one record forward at a time
While (sqlreader. Read ())
{
Console. writeline ("album name" + sqlreader ["title"]. tostring ());
}
}
Else
{
Console. writeline ("no album name information found! ");
}
}
Catch (sqlexception exp)
{
Console. writeline ("the database is not connected or the data row read is locked. Make sure that the database is connected to the SQL Server database or the data row read is not locked! Details: {0} ", exp. Message );
}
Finally
{
Sqlcmd. Connection. Close ();
}
}

In vs2008, the DataSet object has been made into a control to improve development efficiency. We only need to drag the control and perform graphical settings, as long as a small amount of code is written in main, this can be achieved, greatly reducing the amount of code written!

To obtain the details of an album, you only need to add the following code in main:

Albumtableadapter adapter = new albumtableadapter ();
Mu. albumdatatable datatable = Adapter. getalbum ();
If (datatable. Rows. Count = 0)
{
Console. writeline ("no query result found! ");
}

Else
{
Mu. albumrow datarow = datatable [0];
Try
{
Console. writeline (string. format ("album No.: {0} \ n album name: {1} \ n singer name: {2} \ n price: {3} \ n label: {4} \ N Release Date: {5} \ n ranking: {6} ", datarow. asin, datarow. title, datarow. artist, datarow. price, datarow. rdate, datarow. label, datarow. rank ));
}
Catch (exception exp)
{
Console. writeline (exp. Message );
}
}