DataBase-白红宇

DataBase
阅读量：5278 次
发布时间：2019-06-14
本文共 10734 字，大约阅读时间需要 35 分钟。
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        ///  17        /// 执行SQL语句 18        /// 
 19        /// SQL语句 20        /// 
      
       返回一个具体值
       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        ///  40        /// 执行SQL语句 41        /// 
 42        /// 要执行的SQL语句 43        /// 参数 44        /// 
       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        ///  63        /// 创建一个Sqlcommand对象，用来构建SQL语句 64        /// 
 65        /// sql语句 66        /// sql所需要的参数 67        /// 
       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        ///  94        /// 要执行SQL语句，该方法返回一个DataTable 95        /// 
 96        /// 执行SQL语句 97        /// 
       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        /// 112        /// 执行SQL语句，返回DataTable113        /// 
114        /// 要执行的SQL语句115        /// 构建SQL语句所需要的参数116        /// 
      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        /// 130        /// 执行SQL语句，返回影响的记录行数131        /// 
132        /// 要执行的SQL语句133        /// 
      134        public int RunSql(string sql)135        {136            int result = -1;137            try138            {139                Open();140                using (SqlCommand cmd = new SqlCommand(sql, con))141                {142                   result= cmd.ExecuteNonQuery();143                   con.Close();144                   return result;145                }146            }147            catch148            {149                return 0;150            }151        }152        /// 153        /// 执行SQL语句，返回影响的记录行数154        /// 
155        /// 要执行的SQL语句156        /// SQL语句所需要的参数157        /// 
      158        public int RunSql(string sql,SqlParameter[]prams)159        {160            try161            {162                int result = -1;163                SqlCommand cmd = CreateCommandSql(sql, prams);164                result = cmd.ExecuteNonQuery();165                this.Close();166                return result;167            }168            catch169            {170                return 0;171            }172 173        }174        public void Close()175        {176            if (con != null)177                con.Close();178 179        }180        /// 181        /// 执行SQL语句，返回一个SqlDataReader182        /// 
183        /// 184        /// 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        /// 197        /// 执行存储过程198        /// 
199        /// 存储过程名称200        /// 
      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        /// 210        /// 执行存储过程211        /// 
212        /// 要执行的存储过程的名称213        /// 构建存储过程所需要的参数214        /// 
      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        /// 223        /// 执行存储过程，返回SqlDataReader224        /// 
225        /// 存储过程226        /// 要返回的SqlDataReader227        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        /// 238        /// 创建一个sqlCommand对象，用来执行存储过程239        /// 
240        /// 存储过程名称241        /// 构建存储过程所需要的参数242        /// 
      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        /// 261        /// 对DateTime型数据做限制262        /// 
263        /// 
      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            else271            {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            else283            {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        /// 296        /// 对String类型数据的限制297        /// 
298        /// 
      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            else307            {308                return MakeParam(ParamName,Dbtype,size,ParameterDirection.Input,value);309            }310        }311        /// 312        /// 对int,float数据的限制313        /// 
314        /// 
      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            else322            {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 }