Talking about how to use ADO.NET database operation

  CSDN recently reviewed in the post, many problems are found on the operational aspects of the database, most of which involves the use of DBConnection, DBCommand, DataAdapter and DataReader these libraries.    Following on the development of my experience, and we share experiences in this regard. 

  First of all first want to say is that DBConnection, its role is equivalent to the procedures and the establishment of a database server access, the lack of it, then all of the operation of the database have become empty talk.    DBConnection targets in the operation of the time, attention should be paid to the following: 

  l set ConnectionString (connection string), which can be compared to choose the path of direction, if the wrong direction, you can not build access roads.    For different database, the connection string settings are also different, the following description given. 


  Connection string 

  SQL Server 

  data source = database server name; initial catalog = database name; user id = connected user name; password = password; 


  Provider = Microsoft.JET.OLEDB.4.0; data source = database files (specified path); user id = Admin; Jet OLEDB: Database Password = password; 

  Note: "Jet OLEDB: Database Password = password" This part is optional, that is, if there is no password, this part can be removed. 


  l called Open and Close way to open database connectivity and close database connection, the database connection will appear abnormal, it should TRY-CATCH for capture. 

  l through the State attributes to view the current state of the database connection.    For example: If not for some time operated database connectivity is likely to be closed system, so each database operation is the best judgement of the first database linking the State properties, for example, in SQL Server can be as follows: 

  if (sqlConn.State == ConnectionState.Closed | | sqlConn.State == ConnectionState.Broken) 

  / / Connection is not available 

  return false; 


  / / Connection is available 

  return true; 

  When the database can be connected-is the case, further operation of the database. 

  Next we should talk about DBCommand this category, all of the operation of the database are conducted through its completed.    If the DBConnection to a pathway, then DBCommand is in this pathway running back and forth between the vehicles.    The lack of it, all in the process of the operation of the database can not be transmitted to the database server.    So can say, DBConnection and DBCommand the basis of ADO.NET.    DBCommand in the operation conducted when the need to pay attention to the following points. 

  l and CommandType set CommandText property, under normal circumstances does not require set CommandType attributes, but if the storage process, you need to set CommandType attribute to StoredProcedure. 

  l proposal to use more parameters, and reduce the string of Mosaic, the first such procedure can reduce clerical error, which could have been avoided statements borers.    The parameters can be used as follows: 

  SqlCommand myComm = new SqlCommand (); 

  myComm.CommandText = "SELECT * FROM UserInfo WHERE UserName = @ UserName"; 

  myComm.Parameters.Add ( "@ UserName", yourValue); 

  l distinction ExecuteNonQuery method and ExecuteReader method, the former are mainly dealing with enquiries types of statements, the number is to return to the affected number, but its "INSERT" is the phrase can not afford any role, while the latter is handling enquiries Statements, but needs the help of DataReader to support operations. 

  l Do not forget the final after use, call Dispose method DBCommand object to the release. 

  For enquiries and to obtain the records set, alone DBCommand is unable to complete, so the use of DataReader to get by for the record.    For example: 

  SqlCommand myComm = new SqlCommand (); 

  myComm.CommandText = "SELECT * FROM UserInfo WHERE UserName = @ UserName"; 

  myComm.Parameters.Add ( "@ UserName", yourValue); 

  SqlDataReader myReader = myComm.ExecuteReader (); 

  while (myReader.Read ()) 


  myReader.GetValue (index); / / Get value through specific index 


  myReader.Close (); 

  myComm.Dispose (); 

  So in DataReader in the more common two ways, that is, Read and GetValue, the former is to determine whether the reading records, which is access to records of the value of a field, its object is to return to the type object, the need for conversion can be In the field to the value. 

  It is a pity that, DataReader Nengdu Chu and his party recorded a time, so use it when the search results to read line by line, which it DBConnection is exclusive, that is not at this time with a DBConnection to do with the other Database operations. 

  To mention the fact that, in ADO.NET provides a better, more convenient tool for data operation, that is, DataAdapter category.    For a DataAdapter object, can set up four DBCommand, respectively SelectCommand, InsertCommand, DeleteCommand, UpdateCommand.    This four DBCommand do not need all the initialization, the main process is to see the specific requirements, if it is only used for, then only need to set the SelectCommand, and the general operation of the same DBCommand (see previous DBCommand Some explanation). 

  The DataAdapter operation to return to the record set by DataSet need for auxiliary operations, such as: 

  l was set for the record 

  DataAdapter.Fill (DataSet, "yourTableName"); / / "yourTableName" is the name using in data set 

  l update the database 

  DataAdapter.Update (DataSet, "yourTableName"); 

  It should be noted here is: 

  1. DataSet to the first change, and if after Fill, the database is nothing; 

  2. In addition to setting InsertCommand, DeleteCommand, UpdateCommand, if we do not set the corresponding the statement, the database did not like any impact. 

  This specific aspects of how to set up, you can see the following examples set in the DataAdapter part of the code. 

  Relatively speaking, DataAdapter to poor efficiency of some, but to avoid a number of operations, read record set, was recorded on the type of data conversion, and so on.    At the same time its DBConnection possession of the database is only in operation when the operation is the immediate release of End.    Therefore, strongly recommended to use more DataAdapter, and less use DataReader. 

  At the end of the article, it is necessary to note, the above mentioned operation of the database is only the virtual class name, if true operating procedures for the database, according to the database will have different choices to create a different type of object.    Specified as follows: 


  Sub-category list 

  SQL Server 

  SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter 


  OracleConnection, OracleCommand, OracleDataReader, racleDataAdapter 


  OleDbConnection, OleDbCommand, OleDbDataReader, OleDbDataAdapter 

  Note: DataSet has nothing to do with the type of database. 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button