ADO. NET learning notes getting started, ado.net learning notes

ADO. NET learning notes getting started, ado.net learning notes

This article Reprinted from: http://www.youarebug.com/forum.php? Mod = viewthread & tid = 57 & page = 1 & extra = # pid63

 

This is my note in learning ADP. NET. It can be used as an introduction to ADO. NET or a review tutorial. Connection string: DataSource = localhost; AttchDBFilename = | DataDirectory | \ Database1.mdf; InitialCatalog = UserDate; Integrated Security = True parameter description: Data Source indicates the Data Source. The value is the server address and Instance name, if it is an official version, SQLEXPRESS is not required. If it is a free version, SQLEXPRESS must be added, that is, the local connection can be written as ". \ SQLEXPRESS "; AttchDBFilename indicates the address of the attached database. DataDirectory indicates the App_data directory under the current project directory, which is the default storage path of the database. Initial catpress indicates the database, the value is the name of the database to be connected to. Note: If you want to use the Sqlconnection object, you must import it to System. data. sqlclient namespace AD O. NET connections and other resources all implement the IDisposable interface, and you can use using for resource management. You can also use try ...... The catch statement is included in blocks, but using is the simplest. Code statement:

Using (SqlConnection con = new SqlConnection ("Data Source = localhost; Initial Catalog = UserDate; Integrated Security = True") {Program Statement block ;}

Or:

 

SqlConnection cnn = new SqlConnection (); // create an instance of the SqlConnection object cnn. connectionString = "Data Source = localhost; Initial Catalog = UserDate; Integrated Security = True"; cnn. open ();

 

Note: The function of using is to release resources in a timely manner. At the end of curly brackets, the program will automatically release the memory applied by the statement to achieve the optimum of the program. I. Pass SqlCommand Object execution SQL StatementThe SqlCommand object can execute SQL statements to complete database addition, deletion, modification, query, and other data operations. In the SqlCommand class, the main attribute is CommandText, which is used to receive the SQL statement to be executed, such as cmd. commandText = "INSERT into B1 (name, company, position, shijian) VALUES (@ Name, @ Company, @ Position, @ Datetime)", in addition to the CommandText attribute, there are mainly Connection attributes, it is used to receive database connections and the Transaction attribute to receive transactions. There are three more common methods. The first is the ExecuteNonQuery () method, which is mainly used to submit SQL statements without query results, such as UPDATE, INSERT, and DELETE statements, the returned value is the number of rows affected by SQL statements in the database. The second value is the ExecuteReader () method, which is mainly used to submit SELECT statements. The returned value is a data stream that contains the query results of SELECT statements, you can use the SqlDataReader object to receive the query results, and then call its Read () method to Read the query results row by row. The third method is the ExexuteScalar () method, which is mainly used to submit SELECT statements. However, its return value is the first column in the first row of the query result, so it is suitable for Aggregate Queries such as COUNT. II, SQL Injection and parameterized QuerySQL Injection illegally retrieves database content through special SQL statements. To prevent the system from being vulnerable to SQL injection attacks, You Can parameterize the submitted data before executing SQL statements in SqlCommand, parameterization processing is mainly completed through the Add method of the Parameters attribute of the SqlCommand object. There are two methods for parameterization processing: Run Insert Operation", There is another Code as follows:

cmd.CommandText = "INSERT INTO b1(name, company, position, shijian) VALUES(@Name, @Company, @Position, @Datetime)";                cmd.Parameters.Add(new SqlParameter("Name", Name.Text));                cmd.Parameters.Add(new SqlParameter("Company", Company.Text));                cmd.Parameters.Add(new SqlParameter("Position", Position.Text));                cmd.Parameters.Add(new SqlParameter("Datetime", DateTime.Parse(Datetime.Text)));                cmd.ExecuteNonQuery();

Note: In the above Code, you must note that the data of each parameter is defined in the first code, but in the code above, data is not defined for parameters. Therefore, data must be converted in a timely manner when data is provided. For example, "shijian" is defined as the DateTime type in the database, however, the data obtained in the text box is of the string type. Therefore, you must call the Parse method of DateTime to convert the data. If the data in the database is of the int type, the corresponding conversion should also be performed during the assignment. Iii. Execution Insert OperationFirst, declare a SqlCommand object. Second, use the CreateCommand () function of SqlConnection to create a SqlCommand object, and assign the SQL statement to the CommandText attribute of SqlCommand. Finally, call the ExexuteNonQuery () method of the SqlCommand class to execute the CommandText SQL statement. Code statement:

using(SqlCommand cmd = con.CreateComand()){        cmd.CommandText = “INSERT INTO b1(name, company, position) VALUES(“whh”, “UVGE”, “coder”)”;cmm.ExecuteNonQuery();}

Or:

 

Using (SqlCommand cmd = con. createComand () {// sets the SQL statement cmm. commandText = "insert into b1 (name, company, position, shijian) VALUES (@ Name, @ Company, @ Position, @ Datetime)"; // create a parameter, define the data type, and maintain the same cmm as the corresponding fields in the database. parameters. add ("@ Name", SqlDbType. NVarChar); cmm. parameters. add ("@ Company", SqlDbType. NVarChar); cmm. parameters. add ("@ Position", SqlDbType. NVarChar); cmm. parameters. add ("@ Datetime", SqlDbType. dateTime); // set the parameter values. These values come from the input information cmm in the previous section. parameters ["@ Name"]. value = Name. text; cmm. parameters ["@ Company"]. value = Company. text; cmm. parameters ["@ Position"]. value = Position. text; cmm. parameters ["@ Datetime"]. value = Datetime. text; // execute the SQL statement cmm. executeNonQuery ();}

 

Note: The Return Value of the ExecuteNonQuery () method of the SqlCommand class is the number of rows affected by the currently executed SQL statement. Iv. Execution Select StatementFirst, declare the SqlCommand object. Second, use the CreateCommand () function of SqlConnection to create a SqlCommand object, and assign the SQL statement to the CommandText attribute of SqlCommand. Third, call the ExecuteReader () method of the SqlCommand class to execute the CommandText SQL statement. Fourth, declare the SqlDataReader object and use it to undertake the data stream returned by the ExecuteReader () method. Fifth, use the Read () method of the SqlDataReader class to Read one sentence from the data stream row by row, extract the data of the required fields to process the code statement:

Using (SqlCommand cmd = con. createComand () {Cmd. commandText = "SELECT statement"; using (SqlDataReader reader = cmd. executeReader () {While (reader. read () {String name = reader. getString (reader. getOrdinal ("name"); // data processing statement block ;}}}

Or:

Using (SqlCommand cmd = con. createComand () {using (SqlDataReader reader = cmd. executeReader () {While (reader. read () {String name = reader. ["name"]. toString (); // data processing statement block ;}}}

Note: 1. the User is the keyword of SQL SERVER. Avoid it in the table name. If the User table is required, you can use statements such as SELECT * FROM [User] to query the content of the table. 2. ExecuteScalar () of the SqlCommand class () the method returns the data in the first column of the first row of the query result. 3. You want to obtain the id (primary key) of the currently inserted data while inserting the data, you can use the output syntax and ExecuteScalar method, which are available in Versions later than SQLSERVER 2005. The specific syntax is as follows:

cmd.CommandText = “INSERT INTO T_Users(UserName,PassWord) OUTPUT inserted.Id Values(“admin”, “123456”)”;int id = Convert.ToInt32(cmd.ExecuteScalar());

V, Using And CloseUsing manages resources for classes that implement the IDisposable interface. As long as the class is in its scope, using automatically manages resources. When the program executes out of its scope, the program automatically closes and dispose resources to ensure timely release and recovery of resources. However, Close only closes the current connection or resource. As long as there is no dispose afterwards, you can continue the open connection, that is, close does not release the resource, and only closes the connection, when close and dispose are connected, it works the same as using, but considering Exception Handling, it is best to use using instead of close and dispose. 6. Operate data through DatasetsDataset is the most common way to access data, the main process is to connect to the database, perform data queries, fill the query results into the dataset, display and operate the data in the dataset, and submit the operated dataset to the database, these operations are used to modify the database. This process is mainly implemented through DataSet, DataAdapter, DataTable, DataRow, DataColumn, SqlCommandBuilder, and other classes. According to the process mentioned above, perform a step-by-step analysis: 1. Data Query: The data query here is not implemented through the Command object, but through an instance of the DataAdapter class. The specific code is as follows:

SqlDataAdapter sqldadp = new SqlDataAdapter("SELECT name, company, position, shijian FROM [UserDate].[dbo].[b1] WHERE shijian > '2010-01-21'", con)

In the above Code, con is an instance of the SqlConnection object, that is, database link 2. Fill in the data to the DataSet, And the DataSet is an instance of the DataSet object. The Code is as follows:

DataSet order_daset = new DataSet (); // create the DataSet object instance sqldadp. Fill (order_daset); // Fill the DataSet object

Note that the parameters and methods in the filling statement use an instance of the SqlDataAdapter object to fill the instance of the DataSet object. 3. Display and operate the data in the DataSet, this step is also the most important and troublesome step in this technology. It mainly displays and modifies data through the instance of the DataTable object, and the DataTable class has many attributes and methods, common attributes include: Columns can obtain the data type, size, field name, and other information of fields in the DataSet. DataSet can obtain the DataSet to which the current able instance belongs, rows can obtain data of a specified row and column by subscript and field name (similar to a two-dimensional array). Common methods include NewRow () to create a blank row, you can use the AcceptChanges () method to submit changes to the database when adding data. The Clear () method can clearly understand all data in the DataTable object. The Code is as follows:

DataTable dt = new DataTable (); // create a data table object dt = order_daset.Tables [0]; // fill the data table Response. write ("<table border = '1'> <tr> <th> name </th> <th> company </th> <th> position </th> <th>> Start time </th> </tr> "); for (int I = 0; I <dt. rows. count; I ++) {// retrieve the content of the data table row by field and display it on the page. write ("<tr>"); Response. write ("<td>" + dt. rows [I] ["name"]. toString () + "</td>"); Response. write ("<td>" + dt. rows [I] ["company"]. toString () + "</td>"); Response. write ("<td>" + dt. rows [I] ["position"]. toString () + "</td>"); Response. write ("<td>" + DateTime. parse (dt. rows [I] ["shijian"]. toString ()). toString ("yyyy-MM-dd") + "</td>"); Response. write ("</tr>");} Response. write ("</table> ");

Note: The above code mainly shows the DataSet display function. It should be noted that the statements for filling the data table are filled by the data table whose Tables attribute of the DataSet object is subscript 0, this is very important.
The following describes how to modify a dataset and submit it to the database. to modify a dataset, you can use DataRow and an instance of the SqlCommandBuilder object, sqlCommandBuilder automatically generates INSERT, UPDATE, and DELETE statements based on the DataAdapter object, so that the DataAdapter object updates the database content. The Code is as follows:

 

// Create a DataAdapter object instance SqlDataAdapter sqldadp = new SqlDataAdapter ("SELECT name, company, position, shijian FROM [UserDate]. [dbo]. [b1] ", con); SqlCommandBuilder scb = new SqlCommandBuilder (sqldadp); // generate the insert, delete, and update statements DataSet ds = new DataSet (); // create the DataSet object sqldadp. fill (ds); // Fill DataSetDataRow Myrow = ds. tables [0]. newRow (); // create a blank record object in the data table // accept the data to be inserted from the front-end page. Myrow ["name"] = Name. text; Myrow ["company"] = Company. tex T; Myrow ["position"] = Position. text; Myrow ["shijian"] = Datetime. text; // Add the received data to the ds able ds. tables [0]. rows. add (Myrow); // update sqldadp in the database. update (ds); Response. write ("added successfully! <Br> ");

 

Note: The most important method in the above Code is the Update () method of the SqlDataAdapter object. The function of this method is to submit the changes made to the database, this part of the code differs from the preceding code. The instance of the DataRow object receives a row in the able, rather than the entire DataTable. The code is: DataRow Myrow = ds. tables [0]. newRow (), which means to create a new blank row based on the original DataTable, and the following Code assigns a value to this blank row and submits updates. Similarly, if it is to update data, it is also to obtain a row of the DataTable, then assign values to the data, modify, and submit. If it is to delete the operation, it is to get a row through the DataTable, use DataRow to accept and then call the Remove Method of the Rows attribute of the DataTable to delete the data. The Code is as follows:

 

DataTable dt = ds.Tables[0];dt.Rows.Remove(Myrow);

 

VII. Transaction Processing of Network DataTransaction Processing aims to prevent multiple users on the network from damaging the consistency and integrity of data for concurrent database operations. Transaction processing is mainly completed through the SqlTransaction object. The main methods include Commit () the Rollback () method is mainly used to commit transactions and complete real database query and update operations. The Rollback () method is mainly used to roll back the transactions when database operations such as data query and update exceptions occur. The transaction processing of ADO. NET mainly implements the transaction object through the SqlCommand object. The Code is as follows:

Using (SqlConnection con = new SqlConnection ("Data Source = localhost; Initial Catalog = UserDate; Integrated Security = True") {SqlTransaction transql = null; // declare the instance SqlCommand cmd = new SqlCommand (); try {con. open (); // create a database link transql = con. beginTransaction (); // start transaction processing cmd. connection = con; // set the Command object link cmd. transaction = transql; // set the Transaction of the Command object // set the SQL statement cmd with parameters. commandText = "Insert into b1 (name, company, position, shijian) VALUES (@ Name, @ Company, @ Position, @ Datetime )"; // assign a value to the parameter (note the Data Type of the parameter in the database) cmd. parameters. add (new SqlParameter ("Name", Name. text); cmd. parameters. add (new SqlParameter ("Company", Company. text); cmd. parameters. add (new SqlParameter ("Position", Position. text); cmd. parameters. add (new SqlParameter ("Datetime", DateTime. parse (Datetime. text); // note the data type conversion/ /Execute the SQL statement cmd. ExecuteNonQuery (); // submit the transaction transql. Commit (); Response. Write ("added successfully! <Br> ");} catch {// if the program throws an exception, perform transaction Rollback transql. Rollback ();}}

Note: In the above Code, note that the SqlTransaction instance receives the BeginTransaction () Return Value of the SqlConnection instance, and the Transaction attribute of the SqlCommand instance receives the SqlTransaction instance, that is, the return value of BeginTransaction () of the SqlConnection instance, and the entire transaction process is written by the SqlCommand instance, that is, the transactions corresponding to the Code in the SqlCommand instance are automatically generated by the SqlTransaction instance, provided that transql = con is required. beginTransaction (); cmd. transaction = transql; these two sentences of code are finally submitted or rolled back to the Transaction using the SqlTransaction Commit () method or Rollback ().