DataBase

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Data;
  6 using System.Data.SqlClient;
  7 namespace build
  8 {
  9    public class Database
 10     {
 11        public SqlConnection getConnection()
 12        {
 13            return new SqlConnection("server=.;uid=home;pwd=;database=build");
 14        }
 15        SqlConnection con = null;
 16        /// <summary>
 17        /// 执行SQL语句
 18        /// </summary>
 19        /// <param name="sql">SQL语句</param>
 20        /// <returns>返回一个具体值</returns>
 21        public object QueryScalar(string sql)
 22        {
 23            Open();//打开数据连接
 24            object result = null;
 25            try
 26            {
 27                using(SqlCommand cmd=new SqlCommand(sql,con))
 28                {
 29                    result = cmd.ExecuteScalar();
 30                    return result;
 31                }
 32            }
 33            catch
 34            {
 35                return null;
 36            }
 37 
 38        }
 39        /// <summary>
 40        /// 执行SQL语句
 41        /// </summary>
 42        /// <param name="sql">要执行的SQL语句</param>
 43        /// <param name="prams">参数</param>
 44        /// <returns></returns>
 45        public object QueryScalar(string sql,SqlParameter[]prams)
 46        {
 47            Open();
 48            object result = null;
 49            try
 50            {
 51                using (SqlCommand cmd = CreateCommandSql(sql, prams))
 52                {
 53                    result = cmd.ExecuteScalar();
 54                    return result;
 55                }
 56            }
 57            catch
 58            {
 59                return null;
 60            }
 61        }
 62        /// <summary>
 63        /// 创建一个Sqlcommand对象，用来构建SQL语句
 64        /// </summary>
 65        /// <param name="sql">sql语句</param>
 66        /// <param name="prams">sql所需要的参数</param>
 67        /// <returns></returns>
 68        public SqlCommand CreateCommandSql(string sql, SqlParameter[] prams)
 69        {
 70            Open();
 71            SqlCommand cmd = new SqlCommand(sql,con);
 72            if (prams != null)
 73            {
 74                foreach (SqlParameter parameter in prams)
 75                {
 76                    cmd.Parameters.Add(parameter);
 77                }
 78            }
 79            return cmd;
 80        }
 81        private void Open()
 82        {
 83            if (con == null)
 84            {
 85                con = new SqlConnection("server=.;uid=home;pwd=;database=build");
 86 
 87            }
 88            if (con.State == ConnectionState.Closed)
 89            {
 90                con.Open();
 91            }
 92        }
 93        /// <summary>
 94        /// 要执行SQL语句，该方法返回一个DataTable
 95        /// </summary>
 96        /// <param name="sql">执行SQL语句</param>
 97        /// <returns></returns>
 98        public DataTable Query(string sql)
 99        {
100            Open();
101            using (SqlDataAdapter sqlda = new SqlDataAdapter(sql, con))
102            {
103                using (DataTable dt = new DataTable())
104                {
105                    sqlda.Fill(dt);
106                    return dt;
107                }
108            }
109 
110        }
111        /// <summary>
112        /// 执行SQL语句，返回DataTable
113        /// </summary>
114        /// <param name="sql">要执行的SQL语句</param>
115        /// <param name="prams">构建SQL语句所需要的参数</param>
116        /// <returns></returns>
117        public DataTable Query(string sql,SqlParameter[]prams)
118        {
119            SqlCommand cmd = CreateCommandSql(sql,prams);
120            using (SqlDataAdapter sqldata = new SqlDataAdapter(cmd))
121            {
122                using (DataTable dt = new DataTable())
123                {
124                    sqldata.Fill(dt);
125                    return dt;
126                }
127            }
128        }
129        /// <summary>
130        /// 执行SQL语句，返回影响的记录行数
131        /// </summary>
132        /// <param name="sql">要执行的SQL语句</param>
133        /// <returns></returns>
134        public int RunSql(string sql)
135        {
136            int result = -1;
137            try
138            {
139                Open();
140                using (SqlCommand cmd = new SqlCommand(sql, con))
141                {
142                   result= cmd.ExecuteNonQuery();
143                   con.Close();
144                   return result;
145                }
146            }
147            catch
148            {
149                return 0;
150            }
151        }
152        /// <summary>
153        /// 执行SQL语句，返回影响的记录行数
154        /// </summary>
155        /// <param name="sql">要执行的SQL语句</param>
156        /// <param name="prams">SQL语句所需要的参数</param>
157        /// <returns></returns>
158        public int RunSql(string sql,SqlParameter[]prams)
159        {
160            try
161            {
162                int result = -1;
163                SqlCommand cmd = CreateCommandSql(sql, prams);
164                result = cmd.ExecuteNonQuery();
165                this.Close();
166                return result;
167            }
168            catch
169            {
170                return 0;
171            }
172 
173        }
174        public void Close()
175        {
176            if (con != null)
177                con.Close();
178 
179        }
180        /// <summary>
181        /// 执行SQL语句，返回一个SqlDataReader
182        /// </summary>
183        /// <param name="sql"></param>
184        /// <param name="dataReader"></param>
185        public void RunSql(string sql,out SqlDataReader dataReader)
186        {
187            SqlCommand cmd = CreateCommandSql(sql,null);
188            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
189 
190        }
191        public void RunSql(string sql,SqlParameter[]prams,out SqlDataReader dataReader)
192        {
193            SqlCommand cmd = CreateCommandSql(sql,prams);
194            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
195        }
196        /// <summary>
197        /// 执行存储过程
198        /// </summary>
199        /// <param name="ProcName">存储过程名称</param>
200        /// <returns></returns>
201        public int RunProc(string ProcName)
202        {
203            SqlCommand cmd = CreateCommand(ProcName,null);
204            cmd.ExecuteNonQuery();
205            this.Close();
206            return (int)cmd.Parameters["ReturnValue"].Value; 
207 
208        }
209        /// <summary>
210        /// 执行存储过程
211        /// </summary>
212        /// <param name="ProcName">要执行的存储过程的名称</param>
213        /// <param name="prams">构建存储过程所需要的参数</param>
214        /// <returns></returns>
215        public int RunProc(string ProcName,SqlParameter[]prams)
216        {
217            SqlCommand cmd = CreateCommand(ProcName,prams);
218            cmd.ExecuteNonQuery();
219            this.Close();
220            return (int)cmd.Parameters["ReturnValue"].Value;
221        }
222        /// <summary>
223        /// 执行存储过程，返回SqlDataReader
224        /// </summary>
225        /// <param name="ProcName">存储过程</param>
226        /// <param name="dataReader">要返回的SqlDataReader</param>
227        public void RunProc(string ProcName,out SqlDataReader dataReader)
228        {
229            SqlCommand cmd = CreateCommand(ProcName,null);
230            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
231        }
232        public void RunProc(string ProcName,SqlParameter[]prams,out SqlDataReader dataReader)
233        {
234            SqlCommand cmd = CreateCommand(ProcName,prams);
235            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
236        }
237        /// <summary>
238        /// 创建一个sqlCommand对象，用来执行存储过程
239        /// </summary>
240        /// <param name="ProcName">存储过程名称</param>
241        /// <param name="prams">构建存储过程所需要的参数</param>
242        /// <returns></returns>
243        private SqlCommand CreateCommand(string ProcName,SqlParameter[]prams)
244        {
245            Open();
246            SqlCommand cmd = new SqlCommand(ProcName,con);
247            cmd.CommandType = CommandType.StoredProcedure;
248            if (prams != null)
249            {
250                foreach (SqlParameter parameter in prams)
251                {
252                    cmd.Parameters.Add(parameter);
253                }
254 
255            }
256            cmd.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
257            return cmd;
258 
259        }
260        /// <summary>
261        /// 对DateTime型数据做限制
262        /// </summary>
263        /// <returns></returns>
264        public SqlParameter MakeInParamDate(string ParamName,SqlDbType DbType,int size,DateTime value)
265        {
266            if (value.ToShortDateString() == "0001-1-1")
267            {
268                return MakeParam(ParamName, DbType, size, ParameterDirection.Input, System.DBNull.Value);
269            }
270            else
271            {
272                return MakeParam(ParamName,DbType,size,ParameterDirection.Input,value);
273            }
274        }
275        public SqlParameter MakeParam(string ParamName,SqlDbType DbType,int size,ParameterDirection Direction,object value)
276        {
277            SqlParameter Param;
278            if (size > 0)
279            {
280                Param = new SqlParameter(ParamName, DbType, size);
281            }
282            else
283            {
284                Param = new SqlParameter(ParamName,DbType);
285            }
286            Param.Direction = Direction;
287            if (!(Direction == ParameterDirection.Output && value == null))
288            {
289                Param.Value = value;
290 
291 
292            }
293            return Param;
294        }
295        /// <summary>
296        /// 对String类型数据的限制
297        /// </summary>
298        /// <returns></returns>
299        public SqlParameter MakeInParamStr(string ParamName,SqlDbType Dbtype,int size,string  value)
300        {
301            if (value == null)
302            {
303                return MakeParam(ParamName, Dbtype, size, ParameterDirection.Input, System.DBNull.Value);
304 
305            }
306            else
307            {
308                return MakeParam(ParamName,Dbtype,size,ParameterDirection.Input,value);
309            }
310        }
311        /// <summary>
312        /// 对int,float数据的限制
313        /// </summary>
314        /// <returns></returns>
315        public SqlParameter MakeInParamIntF(string ParamName,SqlDbType DbType,int size,object value)
316        {
317            if (float.Parse(value.ToString()) == 0)
318            {
319                return MakeParam(ParamName, DbType, size, ParameterDirection.Input, System.DBNull.Value);
320            }
321            else
322            {
323                return MakeParam(ParamName,DbType,size,ParameterDirection.Input,value);
324            }
325        }
326        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, object value)
327        {
328            return MakeParam(ParamName, DbType, 0, ParameterDirection.Input, value);
329        }
330        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int size, object value)
331        {
332            return MakeParam(ParamName, DbType, size, ParameterDirection.Input, value);
333        }
334        public SqlParameter MakeOutParam(string ParamName,SqlDbType DbType,int size)
335        {
336            return MakeParam(ParamName, DbType, size, ParameterDirection.Output, null);
337        }
338        public SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int size)
339        {
340            return MakeParam(ParamName,DbType,size,ParameterDirection.ReturnValue,null);
341        }
342 
343     }
344 }
View Code