Ado. NET of learning

Ado. NET of several objects

• Connection: managing connections to databases

• Command : execute commands against the database

• DataReader: Data Flow Reader , the returned data is fast and is just a "forward" stream of data. Cannot be instantiated, can only be created by command

• Dateset: caching data, manipulating data

• DataAdapter: A bridge between data adapters, databases, and datasets

ado namespace

Note: The graph is taken from the network

Common is the SQL data source

Reference namespaces:

using System.Data.SqlClient;

First, Connection class

Connected to the database,Connection is used by the command object so that it can know which data source is executing the command.

Connection has two important properties : ConnectionString and state, connection strings and connection states

　　　　　　 two important methods : open () and close (), used to turn database connections on and off

using (SqlConnection conn = newsqlconnection ("DataSource (local); Integrated security=sspi;initial catalog= Northwind"))    {            Conn. Open ();       }

Conn. State.tostring (): Current connection state, return value is closed or open

Second, Command object

Command has two important properties : CommandType, which indicates whether CommandText is an SQL statement, a stored procedure or a table operation, and the default value is CommandType. Text;　

Two values: (1)CommandType.StoredProcedure, executed in stored procedure mode

(2)commandtype.text SQL statement execution

(3)CommandType.TableDirect, dealing directly with a table

CommandText, value is SQL statement or stored procedure noun, default is SQL statement

Two ways to create a command object

 using  (SqlConnection conn = Newsqlconnection (  " datasource (local); Integrated security=sspi;initial catalog=northwind   "  = conn. CreateCommand (); //     This is a better way 
  sqlcommand.commandtext= "select * from Student"; //  //    sqlcommand sqlcmd=new SqlCommand ();     // sqlcmd.commandtext = "SELECT * from student";  // sqlcmd. Connection=conn;  

}

Three important methods:

• ExecuteReader (): Returns DataReader object, DataReader object description see below

using(SqlConnection conn = Newsqlconnection ("DataSource (local); Integrated security=sspi;initial Catalog=northwind") {SqlCommand command=Conn. 　　CreateCommand (); Command.commandtext="SELECT * Form Student";
　　 //commandbehavior.closeconnetion closes the DataReader, it also closes the connection connection associated with it.  　　using(SqlDataReader dr =command. ExecuteReader (commandbehavior.closeconnection)) { while(Dr. Read ()) {//start reading the data, what you want to do next. 　　　　　　 stringstr = Dr. GetSqlString (0).　　　　ToString (); }}}

using(SqlConnection conn = Newsqlconnection ("DataSource (local); Integrated security=sspi;initial Catalog=northwind") {SqlConnection conn = new SqlConnection (str);//Create connectionSqlCommand cmd = conn. CreateCommand ();//create commandCmd.commandtext = "SELECT * from person";//Set ACTION statementConn. Open ();//Open ConnectionSqlDataReader reading datausing(SqlDataReader reader =cmd. ExecuteReader ()) {  while (reader.  Read ()) {Console.WriteLine (reader[0] + ":" + reader[1]); //Output The first column of the current row, the second column of data    }} cmd.commandtext = "Select Count (*) from person"; Object obj = cmd. ExecuteScalar (); //Query only the first column of the first row Console.WriteLine ((int) obj); Conn. Close (); //Close connection  }

• ExecuteScalar (): Returns the value of the first column of the first row of data, such as COUNT (*), or null if there is no data

• ExecuteNonQuery (): Returns the number of affected rows for increment, delete, change

using(SqlConnection conn = Newsqlconnection ("DataSource (local); Integrated security=sspi;initial Catalog=northwind"))    {         SqlCommand cmd = conn. CreateCommand ();//Create commandCmd.commandtext ="Update person Set personname = ' ado Modify ' WHERE PersonId = @Id";//Set ACTION StatementCmd. Parameters.Add ("@Id", SqlDbType.Int);//Add parameter, description typeCmd. parameters["@Id"]. Value =1;//Setting parameter valuesConn. Open ();//Open Connection    inti = cmd. ExecuteNonQuery ();//execute command, ExecuteNonQuery by name, only for non-query statementsConn. Close ();//Close ConnectionConsole.WriteLine (i);//output affects the number of rows

Add the parameters you need directly using the parameter collection, and recommend this notation
sqlparameter[] Parameters = new sqlparameter[]
//{
New SqlParameter ("@name", sqldbtype.nvarchar,100) {Value = "yang"},
New SqlParameter ("@age", sqldbtype.int,2) {Value = 888},
New SqlParameter ("@address", sqldbtype.nvarchar,20) {Value = "Jiang Su"},
//};
Ommand. Parameters.addrange (Parameters);
}

Third, DataReader class

Common methods:

(1) Read () returns BOOL, if there is another line, and moves to the next row of the result set

(2) getordinal (string column name), return serial number, by column name to get the index number of the forefront, so that if the next time your listing order changes will not be related

(3) GetName (int serial number): Gets the column name, the sequence number of the specified column name, and returns a string

int Nameid= Dr. GetOrdinal ("name"); string columnName = Dr. GetName (NAMEID);
Knowing the column name gets the value
using (SqlDataReader reader = cmd. ExecuteReader ())

 {while (reader. Read ())     {     Console.WriteLine (Reader. GetString (SDR). GetOrdinal ("name

}

(4) NextResult (), when the query is a batch query, use this method to read and remove a result set, the return value is bool, true if there are multiple result sets ; false otherwise

using(SqlConnection conn =NewSqlConnection (strconnstring))
{Conn.　　 Open (); using(SqlCommand cmd =NewSqlCommand ())
{cmd. Connection=Conn; //using semicolons ";" Concatenate multiple SQL statements, and then throw them into the database again to execute, "batch" read or update//The purpose of the database.       This technique also applies to "batch" execution of INSERT, UPDATE, DELETE. Cmd.commandtext="SELECT Field 1, field 2 from data table 1; SELECT * from data table 2"; using(SqlDataReader dr =cmd. ExecuteReader ())
{ while(Dr. Read ())
{ This. DROPDOWNLIST1.ITEMS.ADD (Dr. GetSqlString (0). ToString () + Dr. GetSqlInt32 (1).　　 　　　　ToString ()); } Dr.  　　　　　NextResult ();  while(Dr. Read ())
{ This. DROPDOWNLIST2.ITEMS.ADD (Dr. GetString (0) + Dr. GetInt32 (1)); } }}}

Iv. SqlDataAdapter data Adapter

There are four overloads of

No parameter SqlDataAdapter (SqlCommand)   //Execute Command object instance SqlDataAdapter (String, SqlConnection)//   can only specify query statement ② Connection object Instance SqlDataAdapter (String, ConnectionString)//Initializes a new instance of the SqlDataAdapter class with SelectCommand and a connection string

An important method:Fill (), data is populated into a dataset

 using  (SqlConnection conn = Newsqlconnection (  " datasource (local); Integrated security=sspi;initial catalog=northwind   "  = new   DataSet (); Conn.    Open (); SqlCommand command  = Conn.    CreateCommand (); Command.commandtext  =  select name,age,address    From myinformation   " ; SqlDataAdapter dataAdapter  = new      SqlDataAdapter (command);  DataAdapter.Fill (DataSet);  //  fill data } 

Adding data to the operation

using(SqlConnection conn = Newsqlconnection ("DataSource (local); Integrated security=sspi;initial Catalog=northwind"))    {            using(SqlConnection conn =NewSqlConnection (ConnectionString ())) {Conn.     Open (); //construct a query statement, or you can specify SqlCommand, where the method of transformation has manySqlDataAdapter da =NewSqlDataAdapter ("Select Lastname,firstname from dbo. Employees", conn); DataSet DS=NewDataSet (); Da.     Fill (DS); //It 's important to translate the data you added in the dataset into SQL statements to update the database     SqlCommandBuilderCmdbuilder =New SqlCommandBuilder(DA); //add a row, instantiate a row object, and note that the row is created with NewRowDataRow row = ds. tables[0].     NewRow (); row[0] ="Yang"; row[1] ="Ghost Head"; Ds. tables[0]. Rows.Add (row);//Add to TableDa. Update (DS);//Compare tables and databases in the dataset to update }     }

Update action

using(SqlConnection conn = Newsqlconnection ("DataSource (local); Integrated security=sspi;initial Catalog=northwind"))    {            using(SqlConnection conn =NewSqlConnection ("") {SqlDataAdapter da=NewSqlDataAdapter ("SQL statements or command objects that you define yourself", conn); DataSet DS=NewDataSet (); Da.    Fill (DS); //a very important word.SqlCommandBuilder Cmdbuilder =NewSqlCommandBuilder (DA); Ds. tables[0]. rows[ A][1] ="";//Modifying Datada.    Update (DS); //call the Update method, which implicitly calls the AcceptChanges method to update the data in the data set//If you continue to use this data set without calling this method, an exception will occur in later useDs. AcceptChanges ();//This sentence can not be written.}  }

At last

For some additions and deletions, should be added to the transaction, to prevent errors can roll back data

using (SqlConnection conn = newsqlconnection ("DataSource (local); Integrated security=sspi;initial catalog= Northwind"))    {    // transaction     sqltransaction Mytran = con. BeginTransaction ();     Tyr   {        // Normal code execution   .......          
        //COMMIT TRANSACTION         mytran.commit ();     }    Catch (Exception e)  {       // error, transaction rollback        mytran.rollback ();   }  }

ADO. Net of learning