ADO uses a common database operation class (SQL Server)

I. Web. config configuration

<ConnectionStrings> <add name= "Constr_name" connectionString= "server=192.168.1.139;uid=sa;pwd=123456; Trusted_connection=no;database=dabasename " providerName=" System.Data.SqlClient "/></  ConnectionStrings>
Note: constr_name (custom name), Server (SQL Server address), UID (login name), pwd (password), trusted_connection (whether to log in with Windows account), database (db name) , ProviderName (database type, SQL Server for System.Data.SqlClient)

Ii. references

using Microsoft.Practices.EnterpriseLibrary.Data;

Iii. creating a DB instance

Private Database _database; this. _database = Databasefactory.createdatabase (); // created in the constructor of the Dal class

Example 1, get all users with IDs greater than 10

User User =NULL; List<User> list =NewList<user>();
// read data  stringsql ="SELECT * FROM [User] WHERE ID > @id;";D bcommand Command=_database. Getsqlstringcommand (SQL);
_database. Addinparameter (Command, "id", Dbtype.int32, ten); // add parameter  DataTable DT= _database. ExecuteDataset (command). tables[0];
// Write data to object   for(inti =0; i < dt. Rows.Count; i++) {User=NewUser (); if(dt. rows[i]["ID"] !=dbnull.value) User.ID= Convert.ToInt32 (dt. rows[i]["ID"]); if(dt. rows[i]["name"] !=dbnull.value) User.Name= dt. rows[i]["name"].    ToString (); List. ADD (user);}

Example 2, using transactions, inserting user data

intresult =0;stringsql ="INSERT into [User] VALUES (@Id, @Name);";using(DbConnection conn =_database. CreateConnection ()) {Conn.    Open (); Dbtransaction Tran=Conn. BeginTransaction (); // start a transaction     Try{command=_database.        Getsqlstringcommand (SQL); _database. Addinparameter (Command,"Id", Dbtype.int32,1); _database. Addinparameter (Command,"Name", Dbtype.string,'name1'); Result+ = _database. ExecuteNonQuery (command, Tran);//Insert the first piece of data_database. Addinparameter (Command,"Id", Dbtype.int32,2); _database. Addinparameter (Command,"Name", Dbtype.string,'name2'); Result+ = _database. ExecuteNonQuery (command, Tran);//Insert a second piece of data        if(Result = =2) Tran.commit ();//two data are inserted successfully commits the transaction        Else{tran. Rollback ();//Otherwise, rollback the transaction and cancel all the inserted data        }    }    Catch(System.Exception ex) {tran. Rollback ();//error, ROLLBACK TRANSACTION    }    finally    {                if(Conn! =NULL) Conn.    Close (); }}

ADO uses a common database operation class (SQL Server)