ADO.NET实现对SQL Server数据库的增删改查示例

时间:2021-11-26 17:28:24

了解了上一篇的ADO.NET简介,我们就可以来对数据库进行增删改查等基本操作了!下面是每种操作的具体实现。

先在自定义类的头部定义好数据库连接对象和连接字符串:

?
1
2
string connectionString = "Data Source=SC-201607131829;Initial Catalog=Animal;Integrated Security=True";
 SqlConnection conn;

1.数据库的查询操作,返回一个DataTable

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public DataTable doSelect()
   {
 
     string sql = "select * from detial";
 
     using (conn = new SqlConnection(connectionString))
     {
 
       conn.Open();
 
       SqlDataAdapter da = new SqlDataAdapter(sql, conn);
 
       DataSet ds = new DataSet();
 
       da.Fill(ds);  //填充DataSet
 
       return ds.Tables[0];
 
     }
   }

2.数据库插入操作,返回布尔值

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
public bool doInsert(string name, string skin, string weight)
    {
 
      string sql = "insert into detial(name,skin,weight)values(@name,@skin,@weight)";
 
      SqlParameter[] newAnimal = {
         new SqlParameter("name",name),
         new SqlParameter("skin",skin),
         new SqlParameter("weight",skin)
      };
 
      using (conn = new SqlConnection(connectionString))
      {
        SqlCommand com = new SqlCommand(sql, conn);
        try
        {
          if (newAnimal != null)
          {
            foreach (SqlParameter parameter in newAnimal)
            {
              com.Parameters.Add(parameter);
 
            }
          }
          conn.Open();
 
          int influence = com.ExecuteNonQuery();
 
          if (influence > 0)
          {
 
            return true;
          }
          else
          {
 
            return false;
          }
        }
        catch (Exception exception)
        {
          return false;
        }
      }
    }

3.数据库删除操作,返回布尔值

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
public bool doDelete(string name)
    {
 
      string sql = "delete from detial where name = @name";
 
      SqlParameter[] deleteParameter = { new SqlParameter("name", name) };
 
      using (conn = new SqlConnection(connectionString))
      {
 
        SqlCommand com = new SqlCommand(sql, conn);
        
        try
        {
 
          if (deleteParameter != null)
          {
            foreach (SqlParameter parameter in deleteParameter)
            {
              com.Parameters.Add(parameter);
            }
            
          }
 
          conn.Open();
 
          int influence = com.ExecuteNonQuery();
 
          if (influence > 0)
          {
 
            return true;
          }
          else
          {
 
            return false;
          }
        }
        catch (Exception exception)
        {
          return false;
        }
      }
    }

4.数据库更新操作,返回布尔值

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
public bool doUpdate(string name , string skin) {
 
      string sql = "update detial set skin = @skin where name = @name";
      SqlParameter[] updateParameter = {
                    new SqlParameter("name",name),
                    new SqlParameter("skin",skin)
      };
 
      using (conn = new SqlConnection(connectionString)) {
 
        SqlCommand com = new SqlCommand(sql,conn);
 
          try {
 
            if (updateParameter != null) {
              
              foreach(SqlParameter parameter in updateParameter){
 
                com.Parameters.Add(parameter);
 
              }
            }
 
            conn.Open();
 
            int influence = com.ExecuteNonQuery();
 
            if (influence > 0)
            {
 
              return true;
            }
            else
            {
 
              return false;
            }
          
          }catch(Exception exception){
 
            return false;
          }
      }
 
    }

其中为了防止sql注入,用到了SqlParameter类。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:http://www.cnblogs.com/SunshineAgain/p/5721632.html