//查询所有的房间类型 public List<Model.RoomType> SelectAllRoomTypes() { //构造sql语句 string sql = string.Format("select * from RoomType"); //调用通用层中的方法来执行sql语句 DataSet ds = SqlHelpe.ExecuteDataSet(sql, "RoomType"); List<Model.RoomType> list = new List<Model.RoomType>(); //循环遍历把dataset数据集转换为集合 foreach (DataRow dr in ds.Tables[0].Rows) { Model.RoomType rt = new Model.RoomType(); rt.TypeId = (int)dr["TypeId"]; rt.TypeName = (string)dr["TypeName"]; rt.TypePrice = (decimal)dr["TypePrice"]; rt.IsAddBed = (string)dr["IsAddBed"]; rt.AddBedPrice = (decimal)dr["AddBedPrice"]; rt.Remark = dr["Remark"].ToString(); //把装满数据的对象添加到集合 list.Add(rt); } return list; } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace Pro { public abstract class DBHelper { public static SqlConnection GetConn() { SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["conn"].ToString()); return conn; } } public class sqlHelper : DBHelper { #region 定义数据库访问组件 private static SqlConnection conn; private static SqlDataAdapter adapter; private static SqlCommand cmd; private static DataSet ds; #endregion private static void GetControl(String procName, SqlParameter[] para) { cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procName; cmd.Connection = conn; if (para != null) { foreach (SqlParameter p in para) { cmd.Parameters.Add(p); } } } /// <summary> /// 增删改 /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="para">参数列表</param> /// <returns>影响的行数</returns> public static int InsertOrDeleteOrUpdateOprate(String procName, SqlParameter[] para) { int result = 0; try { using (conn = GetConn()) { conn.Open(); GetControl(procName, para); result = cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return result; } /// <summary> /// 查询的方法 /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="para">参数列表</param> /// <returns>数据集对象,保存有用户的信息</returns> public static DataSet SelectOperate(String procName, SqlParameter[] para) { try { using (conn = GetConn()) { GetControl(procName, para); adapter = new SqlDataAdapter(); ds = new DataSet(); adapter.SelectCommand = cmd; adapter.Fill(ds); } } catch (Exception ex) { throw ex; } return ds; } /// <summary> /// 首行首列 /// </summary> /// <param name="procName">存储过程名称</param> /// <param name="para">参数列表</param> /// <returns>int</returns> public static int ScalarOprate(String procName, SqlParameter[] para) { int result = 0; try { using (conn = GetConn()) { conn.Open(); GetControl(procName, para); result = Convert.ToInt32(cmd.ExecuteScalar()); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return result; } #region 执行简单SQL语句 //执行select简单sql命令的通用方法,返回DataSet public static DataSet ExecuteDataSet(string sql) { //创建数据集 DataSet ds = new DataSet(); try { //创建连接 conn = GetConn(); //创建数据适配器 adapter = new SqlDataAdapter(sql, conn); //查询出 结果然后填充到数据集中 adapter.Fill(ds); } catch (Exception ex) { throw ex; } return ds; } //2.执行select简单sql命令的通用方法,返回DataSet public static DataSet ExecuteDataSet(string sql, string tableName)//tableName是表名,调用的时候取然后再传过来 { //创建数据集 DataSet ds = new DataSet(); try { //创建连接 conn = GetConn(); //创建数据适配器 adapter = new SqlDataAdapter(sql, conn); //查询出 结果然后填充到数据集中 adapter.Fill(ds, tableName); } catch (Exception ex) { throw ex; } return ds; } //3.执行insert,update,delete简单sql命令的通用方法 public static bool ExecuteNonQuery(string sql) { //1.创建连接 conn = GetConn(); //2.创建命令对象 cmd = new SqlCommand(sql, conn); try { //3.打开连接 conn.Open(); //4.执行命令 cmd.ExecuteNonQuery(); return true; } catch (Exception) { return false; } finally { //5.关闭连接 conn.Close(); } } //4.执行select简单sql语句,返回首行首列 public static object ExecuteScalar(string sql) { try { //1.创建连接 conn = GetConn(); //2.打开链接 conn.Open(); //3.创建命令对象 cmd = new SqlCommand(sql, conn); //4.执行命令 object obj = cmd.ExecuteScalar();//得到首行首列的值 return obj; } catch (Exception ex) { return null; } finally { //5.关闭连接 conn.Close(); } } #endregion } }