C#DBHelp类实现增删改查_c# dbhelper增删改查

//首先引入命名空间
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


 public abstract class SQLDBhelp
    {
    //连接字符串设置
        protected static string sql = "Data Source=（服务器名称）;Initial Catalog =（数据源）;User ID=（登录名）;Pwd=（密码）";

        public static void Command(SqlConnection conn,SqlCommand cmd,string cmdText ,SqlParameter [] cmdparam)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();//打开数据源连接
            }
            cmd.CommandText = cmdText;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            if (cmdparam != null)
            {
                foreach (SqlParameter param in cmdparam)
                {
                    cmd.Parameters.Add(param);
                }
            }
        }


        public static int ExecuteSQL(string SQLText,params SqlParameter [] cmdparam)
        {
            using (SqlConnection conn = new SqlConnection(sql))
            {
                using (SqlCommand cmd = new SqlCommand())
                {   
                    try
                    {
                        Command(conn, cmd, SQLText, cmdparam);
                        int row =cmd.ExecuteNonQuery();//执行SQL语句,返回受影响的行数。
                        cmd.Parameters.Clear();
                        return row;
                    }
                    catch(SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    
                }
            }
        }



        public static DataTable Query(string SQLText, params SqlParameter[] cmdparam)
        {
            using (SqlConnection conn = new SqlConnection(sql))
            {
                SqlCommand cmd = new SqlCommand();
                Command(conn, cmd, SQLText, cmdparam);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    try
                    {
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                    catch(SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return dt;
                }
            }
        }

    }

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

最后引用命名空间：

using System.Data;
using System.Data.SqlClient;
1
2

增加：

 string str = "SQL语句";
            if(SQLDBhelp.ExecuteSQL(str)>0)
            {
                MessageBox.Show("数据增加成功");
            }
            else
            {
                MessageBox.Show("数据增加失败");
            }
1
2
3
4
5
6
7
8
9

删除：

 string str = "SQL语句";
            if(SQLDBhelp.ExecuteSQL(str)>0)
            {
                MessageBox.Show("数据删除成功");
            }
            else
            {
                MessageBox.Show("数据删除失败");
            }
1
2
3
4
5
6
7
8
9

修改：

//修改
 string str = "SQL语句";
            if(SQLDBhelp.ExecuteSQL(str)>0)
            {
                MessageBox.Show("数据修改成功");
            }
            else
            {
                MessageBox.Show("数据修改失败");
            }
1
2
3
4
5
6
7
8
9
10

查找：

 string str = "SQL语句";
            if(SQLDBhelp.Query(str)>0)
            {
                MessageBox.Show("数据查找成功");
            }
            else
            {
                MessageBox.Show("数据查找失败");
            }
1
2
3
4
5
6
7
8
9