BLL.cs完整代码及使用示例

  1using System;
  2using System.Data.SqlClient;
  3using System.Configuration;
  4using System.Data;
  5
  6namespace fradmin.DBAccess
  7{
  8    /**//// <summary>
  9    /// BLL 的摘要说明。
 10    /// </summary>
 11    public abstract class BLL
 12    {
 13        public BLL()
 14        {
 15            
 16        }
 17
 18        //返回数据连接字符串
 19        public static readonly string ConnectString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
 20        
 21        //实现只返回首行首列的数据操作,四个参数，同时其一为数据连接字符串
 22        public static object ExecuteScalar(string connectString,CommandType cmdType,string cmdText,params SqlParameter[] parms)
 23        {
 24            SqlCommand  cmd=new SqlCommand();
 25            using (SqlConnection conn=new SqlConnection(connectString))
 26            {
 27                //将各数据参数添加至数据操作对象，准备执行数据操作
 28                PrepareExecute(cmd,conn,null,cmdType,cmdText,parms);
 29                //用object类型数值接收操作返回对象
 30                object val=cmd.ExecuteScalar();
 31                //清除数据操作命令参数
 32                cmd.Parameters.Clear();
 33                return val;
 34            }
 35        }
 36
 37        //执行只返回首行首列的数据操作,四个参数对象，一为数据连接对象参数
 38        public static object ExecuteScalar(SqlConnection conn,CommandType cmdType,string cmdText,params SqlParameter[] parms)
 39        {
 40            SqlCommand cmd=new SqlCommand();
 41
 42            PrepareExecute(cmd,conn,null,cmdType,cmdText,parms);
 43            object val=cmd.ExecuteScalar();
 44            cmd.Parameters.Clear();
 45            return val;
 46        }
 47
 48        //执行返回受影响行数的ExecuteNonQuary类型数据操作,其中数据连接参数为字符串
 49        public static int ExecuteNonQuery(string connectString,CommandType cmdType,string cmdText,params SqlParameter[] parms)
 50        {
 51            SqlCommand cmd=new SqlCommand();
 52            
 53            using (SqlConnection conn=new SqlConnection(connectString))
 54            {
 55                PrepareExecute(cmd,conn,null,cmdType,cmdText,parms);
 56                int val=cmd.ExecuteNonQuery();
 57                cmd.Parameters.Clear();
 58                return val;
 59            }
 60        }
 61
 62        //其中数据连接参数为SqlConnection对象
 63        public static int ExecuteNonQuery(SqlConnection conn,CommandType cmdType,string cmdText,params SqlParameter[] parms)
 64        {
 65            SqlCommand cmd=new SqlCommand();
 66            PrepareExecute(cmd,conn,null,cmdType,cmdText,parms);
 67            int val=cmd.ExecuteNonQuery();
 68            cmd.Parameters.Clear();
 69            return val;
 70        }
 71
 72        //包含事务处理的数据操作,并且返回受影响行数的数据操作
 73        public static int ExecuteNonQuery(SqlTransaction trans,CommandType cmdType,string cmdText,params SqlParameter[] parms)
 74        {
 75            SqlCommand cmd=new SqlCommand();
 76            PrepareExecute(cmd,trans.Connection,trans,cmdType,cmdText,parms);
 77            int obj=cmd.ExecuteNonQuery();
 78            cmd.Parameters.Clear();
 79            return obj;
 80        }
 81
 82        //返回类型为SqlDataReader的数据操作，返回数据行,同时数据连接参数为string
 83        public static SqlDataReader ExecuteReader(string connectString,CommandType cmdType,string cmdText,params SqlParameter[] parms)
 84        {
 85            SqlConnection conn=new SqlConnection(connectString);
 86            SqlCommand cmd=new SqlCommand();
 87
 88            try
 89            {
 90                PrepareExecute(cmd,conn,null,cmdType,cmdText,parms);
 91                SqlDataReader objdr=cmd.ExecuteReader();
 92                cmd.Parameters.Clear();
 93                return objdr;
 94            }
 95            catch
 96            {
 97                conn.Close();
 98                throw;
 99            }
100        }
101
102        //返回类型为SqlDataReader的数据操作,返回数据操作行,同时数据操作过程不带参数
103        public static SqlDataReader ExecuteReader(string connectString,CommandType cmdType,string cmdText)
104        {
105            return ExecuteReader(connectString,cmdType,cmdText,(SqlParameter[])null);
106        }
107
108        //返回类型为DataSet的数据操作，将所选取数据加至缓存，不带有事务处理，且数据操作不带参数，数据连接参数为string类型
109        public static DataSet ExecuteDataSet(string connectString,CommandType cmdType,string cmdText)
110        {
111            return ExecuteDataSet(connectString,cmdType,cmdText,(SqlParameter[])null);
112        }
113        //返回类型为DataSet的数据操作，将所选取数据加至缓存,不带有事务处理，其中数据连接操作参数为string类型
114        public static DataSet ExecuteDataSet(string connectString,CommandType cmdType,string cmdText,params SqlParameter[] parms)
115        {
116            
117            using (SqlConnection conn=new SqlConnection(connectString))
118            {
119                conn.Open();
120                return ExecuteDataSet(conn,cmdType,cmdText,parms);
121            }
122        }
123        //返回类型为DataSet的数据操作，将所选取数据加至缓存，不带事务处理,其中数据连接操作参数为SqlConnectin类型，但不带数据操作参数
124        public static DataSet ExecuteDataSet(SqlConnection conn,CommandType cmdType,string cmdText)
125        {
126            return ExecuteDataSet(conn,cmdType,cmdText,(SqlParameter[])null);
127        }
128
129        //返回类型为DataSet的数据操作，将所选取数据加至缓存，不带事务处理,其中数据连接操作参数为SqlConnectin类型
130        public static DataSet ExecuteDataSet(SqlConnection conn,CommandType cmdType,string cmdText,params SqlParameter[] parms)
131        {
132            //新建数据库操作对象，并将各参数添加准备执行数据操作
133            SqlCommand cmd=new SqlCommand();
134            PrepareExecute(cmd,conn,(SqlTransaction)null,cmdType,cmdText,parms);
135
136            //新建SqlDataAdapter对象并将操作结果填充至DataSet对象作为返回值
137            SqlDataAdapter da=new SqlDataAdapter(cmd);
138            
139            DataSet ds=new DataSet();
140            da.Fill(ds);
141
142            cmd.Parameters.Clear();
143            return ds;
144
145        }
146
147        
148        //将各数据操作属性及参数传至操作命令，准备执行数据操作,共六个参数
149        public static void PrepareExecute(SqlCommand cmd,SqlConnection conn,SqlTransaction trans,CommandType cmdType,string cmdText,SqlParameter[] cmdParms)
150        {
151            //检查数据连接对象是否打开，
152            if(conn.State!=ConnectionState.Open)
153                conn.Open();
154
155            cmd.Connection=conn;
156            cmd.CommandText=cmdText;
157
158            //判定是否存在事务处理，有则添加执行
159            if(trans!=null)
160                cmd.Transaction=trans;
161
162            //将数据操作类型传至操作对象
163            cmd.CommandType=cmdType;
164            //检查是否具有参数，有则遍历添加至SqlParameter
165            if(cmdParms !=null)
166            {
167                foreach (SqlParameter param in cmdParms)
168                    cmd.Parameters.Add(param);
169            }
170        }
171    }
172}
173

使用示例：AdminDB.cs

  1using System;
  2using System.Data;
  3using System.Data.SqlClient;
  4using System.Configuration;
  5using fradmin.DBAccess;
  6
  7namespace fradmin
  8{
  9    /**//// <summary>
 10    /// AdminDB 的摘要说明。
 11    /// </summary>
 12    public class AdminDB
 13    {
 14        public AdminDB()
 15        {
 16            //
 17            // TODO: 在此处添加构造函数逻辑
 18            //
 19        }
 20        //验证管理员登录，返回为管理员编号AdminID
 21        public int AdminLogin(string AdminName,string AdminPwd)
 22        {
 23            SqlParameter[] para={    new SqlParameter("@AdminName",AdminName),
 24                                    new SqlParameter("@AdminPwd",AdminPwd)
 25                                 };
 26            return Convert.ToInt32(DBAccess.BLL.ExecuteScalar(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"AdminLogin",para));
 27        }
 28
 29        //通过管理员编号AdminID获得相应管理员角色,将该方法设置为静态，直接引用
 30        public static int GetRoleID(int adminID)
 31        {
 32            SqlParameter[] para={    new SqlParameter("@AdminID",adminID)};
 33            return Convert.ToInt32(DBAccess.BLL.ExecuteScalar(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"GetRoleID",para));
 34        }
 35
 36        //添加新管理员,可以为普通或超级管理员
 37        public int AddNewAdmin(string adminid,string adminpwd,int roleid)
 38        {
 39            object DB_NULL=Convert.DBNull;
 40            SqlParameter[] para={    new SqlParameter("@AdminName",adminid),
 41                                    new SqlParameter("@AdminPwd",adminpwd),
 42                                    new SqlParameter("@RoleId",roleid),
 43                                    new SqlParameter("@result",SqlDbType.Int,4,ParameterDirection.Output,true,0,0,"",DataRowVersion.Default,DB_NULL)
 44                                };
 45            try
 46            {
 47                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"AddNewAdmin",para);
 48            }
 49            catch
 50            {
 51                throw;
 52            }
 53
 54            //返回操作结果
 55            return Convert.ToInt32(para[3].Value);
 56        }
 57
 58        //取出所有用户,返回类型为DataSet，将数据添填充致缓存
 59        public DataSet GetUserList()
 60        {
 61            return DBAccess.BLL.ExecuteDataSet(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"GetUserList");
 62        }
 63
 64        //由于订单处理余额问题，管理员手动修改用户帐户余额
 65        public void UpdateUserIntegral(int userid,double integral)
 66        {
 67            SqlParameter[] param={    new SqlParameter("@UserID",userid),
 68                                    new SqlParameter("@Integral",integral)
 69                                 };
 70
 71            try
 72            {
 73                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"UpdateUserIntegral",param);
 74            }
 75            catch
 76            {
 77                throw;
 78            }
 79
 80        }
 81
 82        //添加管理员日志,返回为void，参数为管理员编号和日志内容
 83        public static void InsertAction(int adminID,string actionDetail)
 84        {
 85            SqlParameter[] param={    new SqlParameter("@AdminID",adminID),
 86                                    new SqlParameter("@ActionDetail",actionDetail)
 87                                 };
 88
 89            try
 90            {
 91                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"InsertAction",param);
 92            }
 93            catch
 94            {
 95                throw;
 96            }
 97        }
 98
 99        //索引商品列表，返回类型为DataSet，将数据填充至缓存
100        public DataSet GetGoodList()
101        {
102            return DBAccess.BLL.ExecuteDataSet(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"GetGoodList");
103        }
104
105        //修改商品单价，参数为商品编号以及新单价
106        public void UpdateGoodPrice(int goodID,decimal newPrice)
107        {
108            SqlParameter[] param={    new SqlParameter("@GoodID",goodID),
109                                    new SqlParameter("@Price",newPrice)
110                                 };
111            try
112            {
113                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"UpdateGoodPrice",param);
114            }
115            catch
116            {
117                throw;
118            }
119        }
120
121        //删除选定商品，参数为商品编号
122        public void DeleteGood(int goodID)
123        {
124            SqlParameter[] param={    new SqlParameter("@GoodID",goodID) };
125            
126            try
127            {
128                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"DeleteGood",param);
129            }
130            catch
131            {
132                throw;
133            }
134        }
135
136        //添加新商品,
137        public void AddNewGood(string goodName,int categoryID,decimal price,string goodImage,
138            string goodDescription,string autherName,string press,string translator,string ISBN,DateTime pressTime)
139        {
140            SqlParameter[] param={    new SqlParameter("@GoodName",goodName),
141                                    new SqlParameter("@CategoryID",categoryID),
142                                    new SqlParameter("@Price",price),
143                                    new SqlParameter("@GoodImage",goodImage),    
144                                    new SqlParameter("@GoodDescription",goodDescription),
145                                    new SqlParameter("@AutherName",autherName),
146                                    new SqlParameter("@Press",press),
147                                    new SqlParameter("@Translator",translator),
148                                    new SqlParameter("@ISBN",ISBN),
149                                    new SqlParameter("@PressTime",pressTime)
150                                 };
151
152            try
153            {
154                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"AddNewGood",param);
155            }
156            catch
157            {
158                throw;
159            }
160        }
161
162        //获取商品类别,返回类型SqlDataReader,为便于使用将其置为静态方法
163        public static SqlDataReader GetCategories()
164        {
165            return DBAccess.BLL.ExecuteReader(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"GetCategories");
166        }
167
168        //获取管理员角色类型，返回类型为SqlDataReader，设置为static类型
169        public static  SqlDataReader GetAdminRoleList()
170        {
171            return DBAccess.BLL.ExecuteReader(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"GetAdminRoleList");
172        }
173
174        //获取管理员列表,若roleID=-1则返回所有管理员，否则返回对应角色管理员信息
175        public SqlDataReader GetAdminList(int roleid)
176        {
177            SqlParameter[] param={    new SqlParameter("@roleid",roleid)};
178            return DBAccess.BLL.ExecuteReader(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"GetAdminList",param);
179        }
180
181        //通过管理员ID获取管理员日志，按时间倒序排列
182        public DataSet GetActionList(int adminID)
183        {
184            SqlParameter[] param={new SqlParameter("@AdminID",adminID)};
185
186            return DBAccess.BLL.ExecuteDataSet(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"GetActionList",param);
187        }
188
189        //根据管理员ID，删除相应管理员
190        public void DeleteAdmin(int adminID)
191        {
192            SqlParameter[] param={
193                                     new SqlParameter("@AdminID",adminID)
194                                 };
195            try
196            {
197                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"DeleteAdmin",param);
198            }
199            catch
200            {
201                throw;
202            }
203        }
204
205        //添加商品类别，参数为categoryname,返回类型为void
206        public void AddNewCategory(string categoryName)
207        {
208            SqlParameter[] param={
209                                     new SqlParameter("@CategoryName",categoryName)
210                                 };
211            try
212            {
213                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"AddNewCategory",param);
214            }
215            catch
216            {
217                throw;
218            }
219        }
220        
221        //删除商品类别，参数为类别ID,返回类型为void
222        public void DeleteCategory(int categoryID)
223        {
224            SqlParameter[] param={
225                                     new SqlParameter("@CategoryID",categoryID)
226                                 };
227            try
228            {
229                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"DeleteCategory",param);
230            }
231            catch
232            {
233                throw;
234            }
235        }
236
237        //修改密码，返回类型为int ,1为成功更改，-1为更改失败
238        public int ChangePwd(int adminID,string oldPwd,string newPwd)
239        {
240            //设定初始值为空，注意为object类型
241            object p_DBNull=Convert.DBNull;
242            SqlParameter[] param={
243                                    new SqlParameter("@AdminID",adminID),
244                                    new SqlParameter("@OldPwd",oldPwd),
245                                    new SqlParameter("@NewPwd",newPwd),
246                                    new SqlParameter("@Result",SqlDbType.Int,4,ParameterDirection.Output,true,0,0,"",DataRowVersion.Default,p_DBNull)
247                                 };
248
249            try
250            {
251                DBAccess.BLL.ExecuteNonQuery(DBAccess.BLL.ConnectString,CommandType.StoredProcedure,"ChangeAdminPwd",param);
252            }
253            catch
254            {
255                throw;
256            }
257
258            //返回插入标志数值
259            return Convert.ToInt32(param[3].Value);
260        }
261    }
262}
263