. Net Database Operations

Online objects: Connection used to establish a connection to the database command means that the data manipulation commands executed Parameter represent the parameters in the data Manipulation command DataReader used to read the data in read-only, transaction to implement the transaction data Adapter is used to load data for a data container and return the updated data back to the database offline object: The DataSet data container, like a database, accommodates multiple DataTable and relational DataTable data containers, like a data table, The DataRow and Datacolunn constitute a DataRow representing a row of records in a DataTable DataColumn a column representing a DataTable, as if the field DataView is similar to a database view, used to build a DataTable for multiple The view DataRelation represents the relationship between the individual DataTable and provides a way to browse the parent table records and the child table records Constraint the primary key and foreign key constraints representing the DataTable

One, connect the database with the Connection object   string sconnectionstring= "";   sconnectionstring=string. Format ("sever=.;D atabase= database name; User Id=sa; Password=sa "; Connection timeout=2);//Trusted Connection trusted_connection=true   Sqlconnection conn=new Sqlconnection (sConnectionString) ;   Try   {    Conn. Open ();     if (Conn. State==connectionstate.open)     Response.Write ("Database connection succeeded");  }   catch (SqlException SqlException)   {    Response.Write (sqlexception.message);  }   finally   {    if (conn. State==connectionstate.open)     Conn. Close ();  } Get string connection from Web. config: configurtionmanager.connectionstring["con"]. ToString

Second, enumerate all available data sources protected void Page_Load (object sender, EventArgs e) {if (!      IsPostBack) {Sqldatasourceenumerator instance = sqldatasourceenumerator.instance; Ddl_server.datasource = instance.      Getdatasource ();      ddl_server.datatextfiled = "ServerName";   Ddl_server.databind (); Note: Enumerating the SQL Server server samples available on the current network is a very slow operation, please use caution.

Iii. use SqlCommand to execute SQL statements when using online objects, close the active connection as early as possible. We use the using{} statement to automatically release the active connection. The code is as follows: using (Sqlconnection conn=new Sqlconnection (sConnectionString)) {Conn.Open () using (Sqlcommand cmd=new Sqlcommand  (Ssql,conn)) {cmd.  ExecuteNonQuery (); The connection object no longer requires close () after a using, and the using statement automatically invokes the Dispose () method of the corresponding object at the end of the code to dispose of the object resource.

The primary method of the SqlCommand object is: ExecuteNonQuery () executes the SQL statement and returns the affected rows, often used to perform an operation that does not return any results. For example, inserting a exevutereader () executes a query statement and returns SqlDataReader, often used to return the recordset's operations. For example, Query ExecuteScalar () executes a query and returns the first row of the first column of the result, often used to return an operation that returns a value. For example, select COUNT (*) from Tbclass

The threat posed by SQL injection requires the use of single quotes for character data in SQL statements, and no need for digital data. If we do not detect whether the input numeric parameter is really a number, or if the input character parameter is not detected as a single quote, there is a good chance of a vulnerability and a famous "SQL injection attack".

SQL two techniques used in SQL statements: 1, SQL statements can be executed together, separated by semicolons 2, "-" will comment out the following statements, so "'" will be ignored, does not raise the single quotation mark mismatch error.

Use parameters to prevent SQL injection protected void Btn_seachclass_click (0bject Sender,eventargs e) {string[email protected]"Server=.; Database=forum;   Trusted_connection=true "; using (SqlConnection conn=new SqlConnection (sConnectionString)) {Conn.     Open (); using (SqlCommand cmd=new SqlCommand ("SELECT count (*) from Tbclass where[email protected]", conn)) {cmd.        Parameters.Add ("@ClassName", sqldbtype.varchar,50); Cmd. parameters["@ClassName"].        Value=tb_classname.text; Response.Write (String. Format ("total {0} Records meet requirements <br>", CMD. ExecuteScalar ().     ToString ())); }}} Note: All parameters specified in the SQL statement or stored procedure must correspond to all parameters in the Parameters property. The Add () method of the parameter collection has multiple overloads, and a AddWithValue () method can assign a value to the parameter at the same time. Cmd. Parameters.addwithvalue ("@ClassName", Tb_classname.text)

Stored procedure: The SqlParameter object has a direction method. For parameters in SQL statements, this value does not make sense, it is used to specify the direction of the stored procedure parameters. Its value is defined by the ParameterDirection enumeration, which has the following 4 types: Input inputoutput Output returnvalue

Stored procedure Template: CREATE PROCEDURE db. StoredProcedure1/* (@parameter1 int =5, @parameter2 datatype output) */as/*set NOCOUNT on */RETURN example: CREATE proced URE db. StoredProcedure1 (@ClassName varchar (50),//input parameter @BoardName varchar (50),//input parameter @ClassID Varc Har (/output parameter) as declare @BoardCount int; Set @ClassID = (Select ClassID from Tbclass where[email protected]); Insert into Tbboard (boardname,boardclassid) vaules (@BoardName, @ClassID); Set @BoardCount = (Select count (*) from Tbboard); RETURN @BoardCount; Execute stored procedure with SqlCommand object protected void Btn_addboard_click (object sender, EventArgs e) {   string sConnectionString = @ "sever= (local);d atabase=forum; Trusted_connection=true ";    using (Sqlconnection conn=new Sqlconnection (sConnectionString))    {     Conn.Open ();      using (Sqlcommand com=new Sqlcommand ("Createboard", conn))   //createboard is the stored procedure name      {       cmd.commandtype=command.type.storedprocedure;        cmd. Parameters.Add ("@ClassName", sqldbtype.varchar,50);        cmd. parameters["@ClassName"]. Value=tbclassname.text;        cmd. parameters["@ClassName"]. Direction=parameterdirection.input;      &nbsP Cmd. Parameters.Add ("@BoardName", sqldbtype.varchar,50);        cmd. parameters["@BoardName"]. Value=tbboardname.text;        cmd. parameters["@BoardName"]. Direction=parameterdirection.input;        cmd. Parameters.Add ("@ClassID", sqldbtype.varchar,50);        cmd. parameters["ClassID"]. Direction=parameterdirection.output;        cmd. Parameters.Add ("@BoardCount", SqlDbType.Int);        cmd. parameters["@BoardCount"]. Direction=parameterdirection.reternvalue;        cmd. ExecuteNonQuery ();        foreach (SqlParameter parameter in cmd. Parameters)        {          Response.Write (String. Format (parameter name: {0}, Parameter direction: {1}, Parameter value:{2}<br> ", parameter. Parametername,parameter. Direction.tostring (), parAmeter. Value));       }     }   } Note the following points: 1, CommandType enumeration. Used to enumerate all command types, the default is CommandType.Text, which is used to execute SQL statements. If the SqlCommand CommandText is set to a stored procedure name, the CommandType is specified as CommandType.StoredProcedure. 2, Parameter collection. We need to add all the parameters required for the stored procedure to the SqlCommand SqlParameterCollection collection, and the parameter names, argument types, and parameter sizes should correspond to the parameters declared in the stored procedure. The direction of the parameter is defined with the ParameterDirection enumeration.

Stored procedures and transactions: Create procedure Transfer as BEGIN TRAN/* content */Commit Tran return the method of declaring a variable in a stored procedure is: DECLARE @ variable name data type (example:in T vachar  and so on)

Use DataReader to access data one, read single record set string sconnection = @ "server= (local);d atabase=forum; Trusted_connection=true "; using (SqlConnection conn=new SqlConnection (sconnection)) {   Conn. Open ();    using (SqlCommand cmd=new SqlCommand ("SELECT * from Tbboard", conn))    {     using (SqlDataReader dr=cmd. ExecuteReader ())      {        if (Dr. HasRows)//Whether the recordset is empty         {           System.Text.StringBuilder htmlstr=new System.Text.StringBuilder ()//use StringBuilder to construct a string with high efficiency            for (int i=0;i<=dr. fieldcount;i++)           {             htmlstr.append (String. Format ("{0}", Dr. GetName (i)));          }           while (Dr. Read ())           {             for (int i=0;i<dr. fieldcount;i++)             {                htmlstr.apend (String. Format ("{0}", Dr. GetValues (i));//construct record lines            }                          }           Response.Write (HTMLSTR);        }     }   }} Note: 1, The DataReader object cannot be instantiated with the New keyword, and a DataReader object can be obtained through the Command object's ExecuteReader () method. 2, DataReader is a row to read the record forward, so we often use while (Dr. Read ()) to traverse all rows. The Read () method allows DataReader to read a record and advance to the next record, False if it has reached the bottom of the record. 3, DatareadSome properties of the object are as follows:   fieldcount   Gets the number of columns for the current row. Our program needs to traverse all the columns, and we can use this property to get the number of columns.   hasrows      Indicates whether the DataReader contains one or more rows. This property is commonly used before reading the contents of a recordset to determine if the recordset has a record. 4. Some important methods of Dataread objects are as follows:   GetInt16 (), GetString (), Getdatatime () and other GetXXX () methods (where xxx represents a type of. net). You can use these methods to read the values of a column in a row and directly to the corresponding. NET type is returned.   GetValue () and getname () .  use the GetValue () method to read the value of a column in a row, which is similar to getxxx () above, except that it returns a value of type object. GetName () returns the column name of a column in a row. Second, read multi-recordset DataReader supports reading of multiple recordsets, and you can use the NextResult () method to move to the next record set. 1, DataReader's construction method can also accept the CommandBehavior enumeration as a parameter, We can use commandbehavior.closeconnection so that the connection object associated with DataReader is closed immediately after the recordset has been read and is faster than a using block of code. 2. You should use DataReader's IsDBNull (column index number) method to determine whether a column has a value before actually reading a column value of a row, lest the exception be caused by a type conversion. String sconnection = @ "server= (local);d atabase=forum; Trusted_connection=true "; using (SqlConnection conn=new SqlConnection (sconnection)) {   Conn. Open ();    using (SqlCommand cmd=new SqlCommand ("SELECT * from Tbboard", conn))    {     using (SqlDataReader dr=cmd.ExecuteReader (commandbehavior.closeconnection)      {         if (Dr. HasRows)//Whether the recordset is empty         {           do           {

           System.Text.StringBuilder htmlstr=new System.Text.StringBuilder ()//use StringBuilder to construct a string with high efficiency             for (int i=0;i<=dr. fieldcount;i++)            {              htmlstr.append (String. Format ("{0}", Dr. GetName (i)));           }             while (Dr. Read ())            {              for (int i=0;i<dr. fieldcount;i++)              {                 htmlstr.apend (String. Format ("{0}", Dr. GetValues (i)));//construct record line &NBSP;&NBSP;&NBSP;&NBsp;        }                          }             Response.Write (HTMLSTR);                    } while (Dr. NextResult ())           }     }   }}

DataSet data Container One, create a dataset we build a local copy of the Tbclass table and Tbboard table in the database//Set up a database dataset Forum=new DataSet ("Forum"); Again to build two data tables DataTable tbclass=new DataTable ("Tbclass"); DataTable tbboard=new DataTable ("Tbborad"); Add two tables to database Forum.Tables.Add (Tbclass); FORUM.TABLES.ADD (Tbborad); Establish two columns of Tbclass DataColumn classid=new DataColumn ("ClassID", typeof (System.String)); DataColumn classname=new DataColumn ("ClassName", typeof (System.String)); Set ClassID is not empty classid.allowdbnull=false; Add the column to the Tbclass table TbClass.Columns.Add (ClassID); TBCLASS.COLUMNS.ADD (ClassName); Set the primary key of the Tbclass table Tbclass.primarykey=new Datacolumn[]{classid}; Establish Tbboard's three-column DataColumn boardid=new DataColumn ("Boardid", typeof (System.String)); DataColumn boardname=new DataColumn ("Boardname", typeof (System.String)); DataColumn boardclassid=new DataColumn ("Boardclassid", typeof (System.String)); Set Boardid is not empty boardid.allowdbnull=false; Add the column to the Tbboardid table TbBoard.Columns.Add (Boardid); TBBOARD.COLUMNS.ADD (Boardname); TBBOARD.COLUMNS.ADD (BOARDCLASSID); Set TBBOard table's primary key tbboard.primarykey=new Datacolumn[]{boardid}; Description: 1. Don't forget to add it to the dataset after you've created the DataTable, and don't forget to add it to the DataTable after you've created the DataColumn. 2. For the Primaykey property of a DataTable, it needs to assign an array of DataColumn. This is because a table may have several primary keys that make up a federated primary key, and for Tanki tables, the array has only one member.   then build the database//Add 5 records for each of the two tables for (int i=0;1<=5;i++) { //instantiate rows of the Tbclass table   DataRow Tbclassrow=tbclass.newrow (); tbclassrow["ClassID"]=guid.newguid (); tbclassrow["ClassName"]=string. Format ("Classification {0}", i); Add the line to the Tbclass table TbClass.Rows.Add (Tbclassrow); Instantiate the row of the Tbboard table DataRow = Tbboard.newrow (); tbboardrow["Boardid"]=guid.newguid (); tbboardrow["Boardname"]=string. Format ("section {0}", i); tbboardrow["Boardclassid"]=tbclassrow["ClassID"]; Add the line to Tbboard table TbBoard.Rows.Add (Tbboardrow); }

Second, Access DataSet 1, suppose we have a DataTable dt, you can use DT. rows[row index number [column index number] Gets the value of a column in a row. For example, we want to get DT three row two column of data, use dt[2][1]. 2. We have two methods for iterating through the collection: Using foreach and for, and for the latter, we need to know the total item number of the collection, and you can use the collection's Count property. If the traversal needs to be ordered, we can only use for.

Third, use DataRelation to implement parent-child table DATASET.RELATIONS.ADD ("Association name", Parent Association primary key field, sub-association foreign key Field)//Build Parent-child relationship Forum.Relations.Add ("Rela         Tionbetweenclassandboard ", ClassID, Boardclassid); Gets the parent table from the relationship datatable dtparent = forum.relations["Relationbetweenclassandboard"].         parenttable; Gets the child table from the relationship DataTable Dtchild = forum.relations["Relationbetweenclassandboard"]. childtable;

       //build output string         System.Text.StringBuilder htmlstr = new System.Text.StringBuilder ();        //Table start         htmlstr.append ("< Table border= ' 1 ' cellpadding= ' 5 ' cellspacing= ' 0 ' style= ' font-size:9pt;font: Arial ' > ');

       //Traverse all rows in the parent table         for (int i = 0; i < DtParent.Rows.Count; i++)         {            //Parent table data row start             htmlstr.append ("<tr  style= ' background-color= #f0f0f0 ' > ');            //Traverse columns in parent table row              for (int j = 0; J < DtParent.Columns.Count; J + +)      & nbsp;      {                 if (!dtparent.rows[i]. IsNull (j))                      htmlstr.append (String. Format ("<td>{0}</td>", Dtparent.rows[i][j]));            }             //parent table Data line end             Htmlstr.append (" </tr> ");            //traversing all rows in a child table              for (int j = 0; J < Dtparent.rows[i]. GetChildRows ("Relationbetweenclassandboard"). Length; J + +)             {                //Sub-table data row start                  htmlstr.append ("<tr>");                //Traversing columns in a child table row                  for (int k = 0; K < Dtparent.colUmns. Count; k++)                 {                     if (! Dtparent.rows[i]. GetChildRows ("Relationbetweenclassandboard") [j]. IsNull (k))                          htmlstr.append (String. Format ("<td>{0}</td>", Dtparent.rows[i]. GetChildRows ("Relationbetweenclassandboard") [j][k]);                }                 //Sub-table data line end                  htmlstr.append ("</tr>");            }         }        //Table End         Htmlstr.append ("< /table><br> ");         Response.Write (HTMLSTR);

Use DataAdapter to get data using the DataTableMapping class to make friendly name mappings to DataTable and DataColumn DataTableMapping Dtmclass=da. TableMappings.Add ("Table", "Forum classification table"); DTMCLASS.COLUMNMAPPINGS.ADD ("ClassID", "Category ID"); DTMCLASS.COLUMNMAPPINGS.ADD ("ClassName", "classification name"); DataTableMapping Dtmboard=da. TableMappings.Add ("Table", "forum section"); DTMBOARD.COLUMNMAPPINGS.ADD ("Boardid", "section id"); DTMBOARD.COLUMNMAPPINGS.ADD ("Boardname", "section name"); DTMBOARD.COLUMNMAPPINGS.ADD ("Boardclassid", "Category ID");

. Net Database Operations