First, ADO. The models and objects in net
1, eexcutenonquery------> Implement non-query operation (Increase and deletion)
SQL statement query non-parameterized
//Connection String stringConstr =@"server=.; Database=mydatabase;uid=sa;pwd=sa"; //the SQL statement to execute stringsql ="INSERT INTO MyDataBase (id,name,age,sex) VALUES (1, ' Caocao ', $, ' m ')"; //Create a Connection object using(SqlConnection conn =NewSqlConnection (CONSTR)) { //The first form of writing;//Create execution object (Execute SQL statement)//using (SqlCommand cmd = new SqlCommand ())//{ //cmd. Connection = conn; //cmd.commandtext = SQL; // //Open Database//Conn. Open (); // //returns the number of rows affected//int count = cmd. ExecuteNonQuery (); //Console.WriteLine ("{0} Line effect", count); //} //the second method of passing in the constructor is the SQL statement and the connection object using(SqlCommand cmd =NewSqlCommand (SQL, conn)) {Conn. Open (); intCount =cmd. ExecuteNonQuery (); Console.WriteLine ("{0} rows affected", Count); }} console.readkey ()
2. Excutescalar Query First line column (return object type)
//Prompt user to enter user nameConsole.Write ("Enter User Id:"); stringUserName =Console.ReadLine (); //Prompt user to enter passwordConsole.Write ("Enter Password:"); stringUserpwd =Console.ReadLine (); //Connection String stringConstr =@"server=.; Databae=mydatabase;uid=sa;pwd=sa"; //SQL statements stringsql =string. Format ("Select COUNT (*) from Users where Username= ' {0} ' and Userpwd={1}", UserName, userpwd); intCount =0; using(SqlConnection conn =NewSqlConnection (CONSTR)) { using(SqlCommand cmd =NewSqlCommand (SQL, conn)) {Conn. Open (); //returns the first line of the column is the object typeCount = (int) cmd. ExecuteScalar (); } } if(Count >0) {Console.WriteLine ("Login Successful"); } Else{Console.WriteLine ("Logon Failure"); }
3, Executerader read data row by line
//Connection String stringConstr =@"server=.; Database=mydatabase;uid=sa;pwd=sa"; //SQL statement queries the data for the entire table (row by line) stringsql ="SELECT * from Data"; using(SqlConnection conn =NewSqlConnection (CONSTR)) { using(SqlCommand cmd =NewSqlCommand (SQL, conn)) {Conn. Open (); //read data back and forth in one row of dataSqlDataReader reader =cmd. ExecuteReader (); using(reader)//reads down one line at a time until it finishes reading{Console.WriteLine ("{0},{1},{2}", reader[0], reader[1], reader[2]); } while(Reader. Read ())//iterate through each row of data until it is finished reading{List<string> list =Newlist<string> ();//creates a row of string types per read line for(inti =0; I < reader. FieldCount; i++)//iterate through each column of data in each row{list. ADD (Reader[i]. ToString ()); } Console.WriteLine (string. Join (",", list)); } } }
4. SqlDataAdapter reading Data set
stringConstr =@"server=.; Database=mydatabase;uid=sa;pwd=sa"; stringsql ="SELECT * Top from Data"; DataSet DS=NewDataSet (); using(SqlDataAdapter adapter =NewSqlDataAdapter (SQL, CONSTR)) {Adapter. Fill (DS); } //Take out the data in the dataset//traverse out each table in the DataSet DS for(inti =0; I < DS. Tables.count; i++) {DataTable dt=ds. Tables[i]; //traverse each row in the DataTable DT for(intj =0; J < dt. Rows.Count; J + +) {DataRow row=dt. Rows[i]; List<string> list =Newlist<string>(); for(intK =0; K < row. Itemarray.length; k++) {list. ADD (row. ITEMARRAY[K]. ToString ()); } Console.WriteLine (string. Join (",", list)); } }
Ado. The models and objects in net