ADO.NET实现对SQL Server数据库的增删改查
了解了上一篇的ADO.NET简介，我们就可以来对数据库进行增删改查等基本操作了！下面是每种操作的具体实现。
先在自定义类的头部定义好数据库连接对象和连接字符串：
1 string connectionString = "Data Source=SC-201607131829;Initial Catalog=Animal;Integrated Security=True"; 2 3 SqlConnection conn;
1.数据库的查询操作，返回一个DataTable
1 public DataTable doSelect() 2 { 3 4 string sql = "select * from detial"; 5 6 using (conn = new SqlConnection(connectionString)) 7 { 8 9 conn.Open(); 10 11 SqlDataAdapter da = new SqlDataAdapter(sql, conn); 12 13 DataSet ds = new DataSet(); 14 15 da.Fill(ds); //填充DataSet 16 17 return ds.Tables[0]; 18 19 } 20 }
2.数据库插入操作，返回布尔值
1 public bool doInsert(string name, string skin, string weight) 2 { 3 4 string sql = "insert into detial(name,skin,weight)values(@name,@skin,@weight)"; 5 6 SqlParameter[] newAnimal = { 7 new SqlParameter("name",name), 8 new SqlParameter("skin",skin), 9 new SqlParameter("weight",skin) 10 }; 11 12 using (conn = new SqlConnection(connectionString)) 13 { 14 SqlCommand com = new SqlCommand(sql, conn); 15 try 16 { 17 if (newAnimal != null) 18 { 19 foreach (SqlParameter parameter in newAnimal) 20 { 21 com.Parameters.Add(parameter); 22 23 } 24 } 25 conn.Open(); 26 27 int influence = com.ExecuteNonQuery(); 28 29 if (influence > 0) 30 { 31 32 return true; 33 } 34 else 35 { 36 37 return false; 38 } 39 } 40 catch (Exception exception) 41 { 42 return false; 43 } 44 } 45 }
3.数据库删除操作，返回布尔值
1 public bool doDelete(string name) 2 { 3 4 string sql = "delete from detial where name = @name"; 5 6 SqlParameter[] deleteParameter = { new SqlParameter("name", name) }; 7 8 using (conn = new SqlConnection(connectionString)) 9 { 10 11 SqlCommand com = new SqlCommand(sql, conn); 12 13 try 14 { 15 16 if (deleteParameter != null) 17 { 18 foreach (SqlParameter parameter in deleteParameter) 19 { 20 com.Parameters.Add(parameter); 21 } 22 23 } 24 25 conn.Open(); 26 27 int influence = com.ExecuteNonQuery(); 28 29 if (influence > 0) 30 { 31 32 return true; 33 } 34 else 35 { 36 37 return false; 38 } 39 } 40 catch (Exception exception) 41 { 42 return false; 43 } 44 } 45 }
4.数据库更新操作，返回布尔值
1 public bool doUpdate(string name , string skin) { 2 3 string sql = "update detial set skin = @skin where name = @name"; 4 SqlParameter[] updateParameter = { 5 new SqlParameter("name",name), 6 new SqlParameter("skin",skin) 7 }; 8 9 using (conn = new SqlConnection(connectionString)) { 10 11 SqlCommand com = new SqlCommand(sql,conn); 12 13 try { 14 15 if (updateParameter != null) { 16 17 foreach(SqlParameter parameter in updateParameter){ 18 19 com.Parameters.Add(parameter); 20 21 } 22 } 23 24 conn.Open(); 25 26 int influence = com.ExecuteNonQuery(); 27 28 if (influence > 0) 29 { 30 31 return true; 32 } 33 else 34 { 35 36 return false; 37 } 38 39 }catch(Exception exception){ 40 41 return false; 42 } 43 } 44 45 }
其中为了防止sql注入，用到了SqlParameter类，详细用法可以参照https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlparameter(VS.80).aspx
