C# DBHelper类的增删改查_dbhlper增删改查代码

1：DBHelper的简单实现

public class DBHelper
    {
        //创建数据库连接字符串
        private static string ConnString = "server=.;database=stuDB;uid=sa;pwd=123456;";    //.是服务器名称 ,stuDB是数据库名称,sa是数据库登录名,123456是数据库密码
        //创建数据库连接对象
        private static SqlConnection Conn = null;
        //初始化数据库连接,InitConnection方法名自定义
        private static void InitConnection()
        {
            if (Conn == null)    //如果数据库对象为空,则new一个数据库连接对象.
            {
                Conn = new SqlConnection(ConnString);    //new一个连接对象,连接字符串给它,用于连接
            }
            if (Conn.State == ConnectionState.Closed)    //如果连接对象的状态是关闭的,就打开连接
            {
                Conn.Open();    //打开连接对象
            }
            if (Conn.State == ConnectionState.Broken)    //如果连接对象的状态是断开的,就关闭重新打开连接
            {
                Conn.Close();    //关闭连接对象
                Conn.Open();    //打开连接对象
            }
        }

        //查询获取DataTable(数据表)
        public static DataTable GetDataTable(string sqlStr)
        {
            InitConnection();    //连接数据库
            DataTable table = new DataTable();    //new一个数据表
            SqlDataAdapter dap = new SqlDataAdapter(sqlStr, Conn);    //创建数据适配器,sql语句和连接对象传给它,
            dap.Fill(table);    //将数据表填充进适配器
            Conn.Close();    //关闭数据连接
            return table;    //将数据表返回
        }
        //增删改,返回bool,用于判断是否有影响行数,即判断是否执行成功
        public static bool ExecuteNonQuery(string sqlStr)
        {
            InitConnection();    //连接数据库
            SqlCommand cmd = new SqlCommand(sqlStr,Conn);    //new一个数据命令,传入sql语句,和连接对象
            int result = cmd.ExecuteNonQuery();    //int一个结果接收数据命令影响的行数
            Conn.Close();    //关闭数据连接
            return result > 0;    //返回
        }
    }
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

方法二：在web.config中配置数据库连接

1.先在Web.config 文件中加入要连接的数据库
在配置文件里面加入

    <connectionStrings  >
      <add name="logisDb" connectionString="Data Source=.;Initial Catalog=logis;Integrated Security=True"/>
    </connectionStrings>
1
2
3
4

2.编写DBHelper类

先引入命名空间：using System.Configuration;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Configuration;
namespace HP_DAL
{
    public class DBHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
       private readonly static string config = ConfigurationManager.ConnectionStrings["logisDb"].ConnectionString;
        /// <summary>
        /// 离线查询，返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public static DataTable ExecuteTable(string sql,params SqlParameter[] par)
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(sql, config))
            {
                if (par != null && par.Length > 0)
                {
                    sda.SelectCommand.Parameters.AddRange(par);
                }
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
 
        }
        /// <summary>
        /// 查询首行首列，返回object
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] par)
        {
            using (SqlConnection con = new SqlConnection(config))
            {
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    if (par != null && par.Length > 0)
                    {
                        com.Parameters.AddRange(par);
                    }
                    if (con.State!=ConnectionState.Open)
                    {
                        con.Open();
                    }
                    return com.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 在线查询，返回SqlDataReader，存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader1(string procname, params SqlParameter[] par)
        {
            SqlConnection con = new SqlConnection(config);
 
            using (SqlCommand com = new SqlCommand(procname, con))
            {
                com.CommandType = CommandType.StoredProcedure;
                if (par != null && par.Length > 0)
                {
                    com.Parameters.AddRange(par);
                }
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return com.ExecuteReader(CommandBehavior.CloseConnection);
 
            }
        }
        /// <summary>
        /// 在线查询，返回SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string procname, params SqlParameter[] par)
        {
            SqlConnection con = new SqlConnection(config);
            
                using (SqlCommand com = new SqlCommand(procname, con))
                {
             
                    if (par != null && par.Length > 0)
                    {
                        com.Parameters.AddRange(par);
                    }
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    return com.ExecuteReader(CommandBehavior.CloseConnection);
                
            }
        }
        /// <summary>
        /// 增删改方法
        /// </summary>
        /// <param name="sql"></param>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] par)
        {
            using (SqlConnection con = new SqlConnection(config))
            {
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    if (par != null && par.Length > 0)
                    {
                        com.Parameters.AddRange(par);
                    }
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    return com.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 增删改方法，存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryProc(string sql, params SqlParameter[] par)
        {
            using (SqlConnection con = new SqlConnection(config))
            {
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.CommandTimeout = 60;
                    com.CommandType = CommandType.StoredProcedure;
                    if (par != null && par.Length > 0)
                    {
                        com.Parameters.AddRange(par);
                    }
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    return com.ExecuteNonQuery();
                }
            }
        }
    }
}
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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160

参考自：
https://www.cnblogs.com/fei-H/p/10909024.html
https://blog.csdn.net/sinat_40900884/article/details/81811418