Ado. NET implementation of SQL Server database additions and deletions

Understand the last one of the introduction of the ADO, we can come to the database for the basic operations such as pruning and checking! The following is a concrete implementation of each operation.

First define the database connection object and connection string in the header of the custom class:

1         string " Data source=sc-201607131829;initial catalog=animal;integrated security=true " ; 2 3         SqlConnection Conn;

　　

1. Query operation of the database, return a dataset

1  PublicDataTable doselect ()2         {3 4             stringsql ="SELECT * from Detial";5 6             using(conn =NewSqlConnection (connectionString))7             {8 9 Conn. Open ();Ten  OneSqlDataAdapter da =NewSqlDataAdapter (SQL, conn); A  -DataSet ds =NewDataSet (); -  theDa. Fill (DS);//Populating a DataSet -  -                 returnDs. tables[0]; -  +             } -}

2. Database insert operation, return Boolean value

　　

1  Public BOOLDoinsert (stringNamestringSkinstringweight)2         {3 4             stringsql ="INSERT INTO detial (name,skin,weight) VALUES (@name, @skin, @weight)";5 6sqlparameter[] Newanimal = {7                   NewSqlParameter ("name", name),8                   NewSqlParameter ("Skin", skin),9                   NewSqlParameter ("Weight", skin)Ten             }; One  A             using(conn =NewSqlConnection (connectionString)) -             { -SqlCommand com =NewSqlCommand (SQL, conn); the                 Try -                 { -                     if(Newanimal! =NULL) -                     { +                         foreach(SqlParameter parameterinchnewanimal) -                         { + com. Parameters.Add (parameter); A  at                         } -                     } - Conn. Open (); -  -                     intInfluence =com. ExecuteNonQuery (); -  in                     if(Influence >0) -                     { to  +                         return true; -                     } the                     Else *                     { $ Panax Notoginseng                         return false; -                     } the                 } +                 Catch(Exception Exception) A                 { the                     return false; +                 } -             } $}

　　

3. Database delete operation, return Boolean value

1  Public BOOLDoDelete (stringname)2         {3 4             stringsql ="Delete from detial where name = @name";5 6Sqlparameter[] Deleteparameter = {NewSqlParameter ("name", name)};7 8             using(conn =NewSqlConnection (connectionString))9             {Ten  OneSqlCommand com =NewSqlCommand (SQL, conn); A                  -                 Try -                 { the  -                     if(Deleteparameter! =NULL) -                     { -                         foreach(SqlParameter parameterinchdeleteparameter) +                         { - com. Parameters.Add (parameter); +                         } A                          at                     } -  - Conn. Open (); -  -                     intInfluence =com. ExecuteNonQuery (); -  in                     if(Influence >0) -                     { to  +                         return true; -                     } the                     Else *                     { $ Panax Notoginseng                         return false; -                     } the                 } +                 Catch(Exception Exception) A                 { the                     return false; +                 } -             } $}

4. Database update operation, returns a Boolean value

　　

1  Public BOOLDoUpdate (stringName,stringskin) {2 3             stringsql ="Update detial Set skin = @skin where name = @name";4sqlparameter[] Updateparameter = {5                                        NewSqlParameter ("name", name),6                                        NewSqlParameter ("Skin", skin)7             };8 9             using(conn =NewSqlConnection (connectionString)) {Ten  OneSqlCommand com =NewSqlCommand (sql,conn); A  -                     Try { -  the                         if(Updateparameter! =NULL) {  -                              -                             foreach(SqlParameter parameterinchupdateparameter) { -  + com. Parameters.Add (parameter); -  +                             }   A                         } at  - Conn. Open (); -  -                         intInfluence =com. ExecuteNonQuery (); -  -                         if(Influence >0) in                         { -  to                             return true; +                         } -                         Else the                         { *  $                             return false;Panax Notoginseng                         } -                      the}Catch(Exception Exception) { +  A                         return false; the                     } +             } -  $}

　　

In order to prevent SQL injection, using the SqlParameter class, detailed usage can refer to https://msdn.microsoft.com/zh-cn/library/ System.data.sqlclient.sqlparameter (vs.80). aspx

Ado. NET implementation of SQL Server database additions and deletions