DBHelper 使用的是存储过程

最新推荐文章于 2023-11-03 19:08:56 发布
weixin_33697898
最新推荐文章于 2023-11-03 19:08:56 发布
阅读量120
点赞数
原文链接：http://www.cnblogs.com/tangzhengyue/archive/2012/03/03/2378453.html
版权
  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System.Data.SqlClient;
  6 using System.Data;
  7 
  8 /// <summary>
  9 ///DBHelper 的摘要说明
 10 /// </summary>
 11 public class DBHelper
 12 {
 13     private static DBHelper _instance;
 14     protected DBHelper()
 15     {
 16         //
 17 //TODO: 在此处添加构造函数逻辑
 18 //
 19     }
 20     public static DBHelper getInstance()
 21     {
 22         if (_instance == null)
 23         {
 24             _instance = new DBHelper();
 25         }
 26         return _instance;
 27     }
 28     //返回一个连接
 29     public static SqlConnection ReturnConn()
 30     {
 31         string strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
 32         SqlConnection Conn = new SqlConnection(strConn);
 33         if (Conn.State.Equals(ConnectionState.Closed))
 34         {
 35             Conn.Open();
 36         }
 37         return Conn;
 38     }
 39     //带参数的CreateCmd命令
 40     public static SqlCommand CreatCmd(string procName, SqlParameter[] prams)
 41     {
 42         SqlConnection Conn = ReturnConn();
 43         SqlCommand Cmd = new SqlCommand();
 44         Cmd.CommandType = CommandType.StoredProcedure;
 45         Cmd.Connection = Conn;
 46         Cmd.CommandText = procName;
 47         if (prams != null)
 48         {
 49             foreach (SqlParameter parameter in prams)
 50             {
 51                 if (parameter != null)
 52                 {
 53                     Cmd.Parameters.Add(parameter);
 54                 }
 55             }
 56         }
 57         return Cmd;
 58     }
 59     //重载带参数带连接的CreateCmd命令
 60     public static SqlCommand CreatCmd(string procName, SqlParameter[] prams, SqlConnection Conn)
 61     {
 62         SqlConnection SqlConn = Conn;
 63         if (SqlConn.State.Equals(ConnectionState.Closed))
 64         {
 65             SqlConn.Open();
 66         }
 67         SqlCommand Cmd = new SqlCommand();
 68         Cmd.CommandType = CommandType.StoredProcedure;
 69         Cmd.Connection = SqlConn;
 70         Cmd.CommandText = procName;
 71         if (prams != null)
 72         {
 73             foreach (SqlParameter parameter in prams)
 74             {
 75                 if (parameter != null)
 76                 {
 77                     Cmd.Parameters.Add(parameter);
 78                 }
 79             }
 80         }
 81         return Cmd;
 82     }
 83     //重载不带参数带连接的CreateCmd命令
 84     public static SqlCommand CreatCmd(string procName, SqlConnection Conn)
 85     {
 86         SqlConnection SqlConn = Conn;
 87         if (SqlConn.State.Equals(ConnectionState.Closed))
 88         {
 89             SqlConn.Open();
 90         }
 91         SqlCommand Cmd = new SqlCommand();
 92         Cmd.CommandType = CommandType.StoredProcedure;
 93         Cmd.Connection = SqlConn;
 94         Cmd.CommandText = procName;
 95         return Cmd;
 96     }
 97     //重载不带参数不带连接的CreateCmd命令
 98     public static SqlCommand CreatCmd(string procName)
 99     {
100         SqlConnection SqlConn = ReturnConn();
101         SqlCommand Cmd = new SqlCommand();
102         Cmd.CommandType = CommandType.StoredProcedure;
103         Cmd.Connection = SqlConn;
104         Cmd.CommandText = procName;
105         return Cmd;
106     }
107     //带参数的RunProcGetReader
108     public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams)
109     {
110         SqlCommand Cmd = CreatCmd(procName, prams);
111         SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
112         return Dr;
113     }
114     //重载带参数和连接的RunProcGetReader
115     public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams, SqlConnection Conn)
116     {
117         SqlCommand Cmd = CreatCmd(procName, prams, Conn);
118         SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
119         return Dr;
120     }
121     //重载带连接的RunProcGetReader
122     public static SqlDataReader RunProcGetReader(string procName, SqlConnection Conn)
123     {
124         SqlCommand Cmd = CreatCmd(procName, Conn);
125         SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
126         return Dr;
127     }
128     //重载不带参数和连接的RunProcGetReader
129     public static SqlDataReader RunProcGetReader(string procName)
130     {
131         SqlCommand Cmd = CreatCmd(procName);
132         SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
133         return Dr;
134     }
135     //返回执行命令后的表
136     public static DataTable RunProcGetTable(string procName, SqlParameter[] prams, SqlConnection Conn)
137     {
138         SqlCommand Cmd = CreatCmd(procName, prams, Conn);
139         SqlDataAdapter Dtr = new SqlDataAdapter();
140         DataSet Ds = new DataSet();
141         Dtr.SelectCommand = Cmd;
142         Dtr.Fill(Ds);
143         DataTable Dt = Ds.Tables[0];
144         Conn.Close();
145         return Dt;
146 
147     }
148     //重载返回执行命令后的表
149     public static DataTable RunProcGetTable(string procName, SqlParameter[] prams)
150     {
151         SqlConnection Conn = ReturnConn();
152         SqlCommand Cmd = CreatCmd(procName, prams, Conn);
153         SqlDataAdapter Dtr = new SqlDataAdapter();
154         DataSet Ds = new DataSet();
155         Dtr.SelectCommand = Cmd;
156         Dtr.Fill(Ds);
157         DataTable Dt = Ds.Tables[0];
158         Conn.Close();
159         return Dt;
160 
161     }
162     //重载返回执行命令后的表
163     public static DataTable RunProcGetTable(string procName)
164     {
165         SqlConnection Conn = ReturnConn();
166         SqlCommand Cmd = CreatCmd(procName, Conn);
167         SqlDataAdapter Dtr = new SqlDataAdapter();
168         DataSet Ds = new DataSet();
169         Dtr.SelectCommand = Cmd;
170         Dtr.Fill(Ds);
171         DataTable Dt = Ds.Tables[0];
172         Conn.Close();
173         return Dt;
174 
175     }
176     //返回执行SQL语句后受影响的行数
177     public static int RunExecute(string procName)
178     {
179         SqlConnection Conn = ReturnConn();
180         SqlCommand Cmd = CreatCmd(procName, Conn);
181         int intResult = Cmd.ExecuteNonQuery();
182         Conn.Close();
183         return intResult;
184 
185     }
186     //返回执行SQL语句后受影响的行数，带参数重载
187     public static int RunExecute(string procName, SqlParameter[] prams)
188     {
189         SqlConnection Conn = ReturnConn();
190         SqlCommand Cmd = CreatCmd(procName, prams, Conn);
191         int intResult = Cmd.ExecuteNonQuery();
192         Conn.Close();
193         return intResult;
194     }
195     //执行并返回第一行第一列
196     public static int RunExecuteScalar(string procName)
197     {
198         SqlConnection Conn = ReturnConn();
199         SqlCommand Cmd = CreatCmd(procName, Conn);
200         int intResult = Convert.ToInt32(Cmd.ExecuteScalar());
201         Conn.Close();
202         return intResult;
203     }
204     //执行并返回第一行第一列,带参数重载
205     public static int RunExecuteScalar(string procName, SqlParameter[] prams)
206     {
207         SqlConnection Conn = ReturnConn();
208         SqlCommand Cmd = CreatCmd(procName, prams, Conn);
209         int intResult = Convert.ToInt32(Cmd.ExecuteScalar());
210         Conn.Close();
211         return intResult;
212     }
213 }