1using System;
  2using System.Collections.Generic;
  3using System.Text;
  4using System.Data;
  5using System.Data.SqlClient;
  6using System.Configuration;
  7//该源码首发自www.51aspx.com(５１aｓpｘ．ｃｏｍ)
  8
  9namespace HotelDAL
 10...{
 11    /**//// <summary>
 12    /// 数据库连接类
 13    /// </summary>
 14    public abstract class DBHelper
 15    ...{
 16        public SqlConnection CreateConn()
 17        ...{
 18            SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlConn"]);
 19            return conn;
 20        }
 21    }
 22
 23    /**//// <summary>
 24    /// 数据库操作类
 25    /// </summary>
 26    public class SqlHelper : DBHelper
 27    ...{
 28        ADO.NET组件#region ADO.NET组件
 29        private SqlConnection conn;
 30        private SqlCommand cmd;
 31        private SqlDataAdapter sda;
 32        private DataSet ds;
 33        #endregion
 34
 35        /**//// <summary>
 36        /// 创建SqlCommand来执行存储过程(带参数)
 37        /// </summary>
 38        /// <param name="procName"></param>
 39        /// <param name="para"></param>
 40        /// <returns></returns>
 41        public SqlCommand CreateCmd(string procName, SqlParameter[] para)
 42        ...{
 43            try
 44            ...{
 45                conn = CreateConn(); //得到数据库连接
 46                cmd = new SqlCommand(); //设置Command对象
 47                cmd.CommandType = CommandType.StoredProcedure; //创建存储过程
 48                cmd.CommandText = procName; //调用存储过程名称
 49                cmd.Connection = conn; //创建数据库连接对象
 50                if (para != null)
 51                ...{
 52                    //添加存储过程参数
 53                    foreach (SqlParameter sp in para)
 54                    ...{
 55                        cmd.Parameters.Add(sp);
 56                    }
 57                }
 58            }
 59            catch (SqlException ex)
 60            ...{
 61                throw ex;
 62            }
 63            return cmd;
 64        }
 65
 66        /**//// <summary>
 67        /// 创建SqlCommand来执行存储过程(不带参数)
 68        /// </summary>
 69        /// <param name="procName"></param>
 70        /// <returns></returns>
 71        public SqlCommand CreateCmd(string procName)
 72        ...{
 73            try
 74            ...{
 75                conn = CreateConn(); //得到数据库连接
 76                cmd = new SqlCommand(); //设置Command对象
 77                cmd.CommandType = CommandType.StoredProcedure; //创建存储过程
 78                cmd.CommandText = procName; //调用存储过程名称
 79                cmd.Connection = conn; //创建数据库连接对象
 80            }
 81            catch (SqlException ex)
 82            ...{
 83                throw ex;
 84            }
 85            return cmd;
 86        }
 87
 88        /**//// <summary>
 89        /// 增删改操作
 90        /// </summary>
 91        /// <param name="procName">存储过程名称</param>
 92        /// <param name="para">参数</param>
 93        /// <returns></returns>
 94        public int RunSql(string procName, SqlParameter[] para)
 95        ...{
 96            int count = 0;
 97            try
 98            ...{
 99                //得到SqlCommand对象
100                cmd = CreateCmd(procName, para);
101                conn.Open();
102                count = cmd.ExecuteNonQuery();
103            }
104            catch (SqlException ex)
105            ...{
106                throw ex;
107            }
108            finally
109            ...{
110                conn.Close();
111            }
112            return count;
113        }
114
115        /**//// <summary>
116        /// 查询DataSet(带参数)
117        /// </summary>
118        /// <param name="procName">存储过程名称</param>
119        /// <param name="para">参数</param>
120        /// <returns></returns>
121        public DataSet GetDataSet(string procName, SqlParameter[] para)
122        ...{
123            try
124            ...{
125                //得到SqlCommand对象
126                cmd = CreateCmd(procName, para);
127                sda = new SqlDataAdapter();
128                sda.SelectCommand = cmd;
129                ds = new DataSet();
130                sda.Fill(ds);
131            }
132            catch (SqlException ex)
133            ...{
134                throw ex;
135            }
136            finally
137            ...{
138                conn.Close();
139            }
140            return ds;
141        }
142
143        /**//// <summary>
144        /// 查询DataSet(不带参数)
145        /// </summary>
146        /// <param name="procName">存储过程名称</param>
147        /// <returns></returns>
148        public DataSet GetDataSet(string procName)
149        ...{
150            try
151            ...{
152                //得到SqlCommand对象
153                cmd = CreateCmd(procName);
154                sda = new SqlDataAdapter();
155                sda.SelectCommand = cmd;
156                ds = new DataSet();
157                sda.Fill(ds);
158            }
159            catch (SqlException ex)
160            ...{
161                throw ex;
162            }
163            finally
164            ...{
165                conn.Close();
166            }
167            return ds;
168        }
169    }
170}
171