C # Database programming

Tag: Source It's adapter project local data DAP next open

The use of the database is a very important technology, it is almost related to all programming languages. C # provides access to the ADO database in SQL Server and OLE db two. In comparison, the former is a connection to a SQL Server database, so accessing the SQL Server database can be very efficient, and the latter is relatively inefficient for a wide range of data operations. If you do not involve a special case of the database, their approximate approach is the same.

First of all, to connect to the database, you must have a Connection object connection, you can have SqlConnection and OleDbConnection, which connection object to use, depending on the type of database you need to connect to. Here are two ways to connect the code.

SQL Server data Access

String strconn = "Integrated security=sspi;initial catalog=mydatabase;data Source=mydatasource";

Integrated Security can be set to: true, false, yes, no, these four meanings are clear, can also be set to: SSPI, equivalent to true, it is recommended to use this instead of true. Integrated Security=sspi This indicates that the current Windows system user is logged on to the SQL Server server and an error occurs if the SQL Server server does not support this way of logging on. The Initial catalog is followed by the database data source followed by the resources.

SqlConnection conn = new SqlConnection (strconn);//Create Connection object

Conn. Open ();//Opens the connection, in the actual combat project, will wrap it up, plus Conn. The judgment of state = = Connectionstate.close

Ole DB data access

String strconn = "Provider=sqloledb;data source=localhost;initial catalog=mydatabase;integrated Security=SSPI";

OleDbConnection conn=new OleDbConnection (strconn);

Conn. Open ();

If the above code does not throw an exception, it means that the database has been successfully connected. The next step is to create DataAdapter to complete the work of accessing the database. DataAdapter's work is the basis of the dataset, which is to create a dataset and the middle tier of the database to coordinate access. According to the same type of database can be divided into SqlDataAdapter and OleDbDataAdapter two kinds of adapters.

SqlDataAdapter adapter = new SqlDataAdapter ();

String strSQL = "SELECT * from TableName";

Adapter. SelectCommand = new SqlCommand (strsql,conn);

SqlCommandBuilder MYCB = new SqlCommandBuilder (adapter);

DataSet myDataSet = new DataSet ();

Adapter. Fill (myDataSet, "table name");

This code uses the SqlDataAdapter, DataSet, SqlCommand, SqlCommandBuilder four objects. SqlDataAdapter's role is to be responsible for communication with the database access, while connected to the dataset, it has four very important command objects (also divided into SqlCommand and OleDbCommand), are access to the database must be used, The SelectCommand, InsertCommand, UpdateCommand, DeleteCommand objects are respectively. These command objects are specifically used to complete the query, insert, UPDATE, delete operations on the database, they are like four ministers, under the control of DataAdapter, respectively, in charge of their respective things. Among them, SelectCommand is the eldest of their four, which can be automatically constructed to generate another three. The process of constructing builds is to apply CommandBuilder. Before that, we just need to specify the SelectCommand object in DataAdapter.

C # Database programming