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;
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
页:
[1]