Ado. NET Learning

2. Ado. Net-Important classes
3. Some experience with. NET access to MySQL database!

   stringConnstr=setting.instance (). GetConnectionString ("MYSQL"); Mysqlconnection Conn=Newmysqlconnection (CONNSTR); Conn. Open ();stringquery ="INSERT INTO Myfirst (id,name) VALUES (? Id? Name)"; Mysqlcommand cmd=NewMysqlcommand (query, conn); Mysqlparameter para1=NewMysqlparameter ("? Id", Dbtype.int32);//It's important to note that you have to use the number.Mysqlparameter para2=NewMysqlparameter ("? Name", dbtype.string);//It's important to note that you have to use the number.Para1. Value=5;p Ara2. Value="DDD"; cmd. Parameters.Add (para1); cmd. Parameters.Add (PARA2); cmd. ExecuteNonQuery ();

5. When designing an application, consider the level of functionality required by the application to determine whether to use a DataSet or a DataReader.

   You    do not need to cache data to use Datareader: in your application for the following scenarios. •    The result set to be processed is too large to fit in memory. •    Once you need to quickly access data in a forward-only, read-only manner.

6. Populate the DataSet with multiple tables, create a datatablemapping, map "Customers1" to "Orders", and create additional table mappings for the other tables that follow. For example:

   // C # New SqlDataAdapter ("select * from Customers; SELECT * from Orders; " , MyConnection);d A. TableMappings.Add ("Customers1""Orders"   New"Customers");

8. ExecuteScalar and ExecuteNonQuery

   If you want to returna single value like the result of Count (*), Sum (price), or AVG (Quantity), you can use Command.executescalar.　　　　ExecuteScalar returns the value of the first column of the first row, returning the result set as a scalar value. Use ExecuteNonQuery when using SQL statements that do not return rows, such as modifying data (such as INSERT, UPDATE, or DELETE) or returning only output parameters or return values. 

10. Passing Null as the parameter value

    In a command against a database, nullcannot be used when sending null values as parameter values. And you need to use DBNull.Value. For example:

    // C # New SqlParameter ("@Name"= DBNull.Value;

11. When a transaction is executed, the connection is explicitly opened before the transaction is started, and the connection is closed after the commit. For example:

    //C # Public voidrunsqltransaction (SqlDataAdapter da, SqlConnection myconnection, DataSet ds) {myconnection.open (); SqlTransaction Mytrans=myconnection.begintransaction (); Mycommand.transaction=Mytrans; Try{da.    Update (DS);    MyCommand.Transaction.Commit (); Console.WriteLine ("Update successful."); }  Catch(Exception e) {Try{mytrans.rollback (); }    Catch(SqlException ex) {if(Mytrans.connection! =NULL) {Console.WriteLine ("An exception of type"+ ex. GetType () +"was encountered and attempting to roll back the transaction.");    }} Console.WriteLine (E.tostring ()); Console.WriteLine ("Update failed."); } myconnection.close ();}

12. Using the "using" statement in C #

    For C # Programmers, a convenient way to ensure that Connection and DataReader objects are always turned off is to use the using statement. The using statement automatically calls Disposeof the object being "used" when it leaves its scope of action. For example:

    //C #stringConnString ="Data source=localhost;integrated security=sspi;initial catalog=northwind;";using(SqlConnection conn =NewSqlConnection (connstring)) {SqlCommand cmd=Conn.  CreateCommand (); Cmd.commandtext="SELECT CustomerId, CompanyName from Customers"; Conn.  Open (); using(SqlDataReader dr =cmd. ExecuteReader ()) { while(Dr. Read ()) Console.WriteLine ("{0}\t{1}", Dr. GetString (0), Dr. GetString (1)); }}

13. The code below is an example of using MySQL Connector Net. Note: The prefix of his parameter is "? "Instead of" @ ". The problem is quite special. Corelab inside the parameter prefix is "@".

     stringConnStr = String.Format ("Server={0};user id={1}; password={2}; database={3}; pooling=false;port=3308","localhost","Root","","Test"); Try{mysqlconnection myconn=Newmysqlconnection (CONNSTR);                MyConn.Open (); Mysqlcommand cmd=Myconn.createcommand (); Cmd. Parameters.Add ("? DocName", Mysqldbtype.varchar, -); Cmd. parameters[0]. Value ="Test by Code"; Cmd. parameters[0]. SourceColumn ="DocName"; Cmd.commandtext="update T_docs set docname=? DocName where docid=4"; Cmd. ExecuteNonQuery ();

15. Sources of knowledge and experience sharing

Ado. NET Learning