using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace HotelManager.DAL
{
public class DBHelpSQL
{
static string connctionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
//public static SqlConnection GetConnection()
//{
// SqlConnection conn = new SqlConnection(connctionString);
// return conn;
//}
/// <summary>
/// 执行一个sql语句,返回受影响的行数
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="parmaeters">sql语句中的参数值 如果sql语句中没有参数则传入null</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] parmaeters)
{
using (SqlConnection conn = new SqlConnection(connctionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (parmaeters != null)
{
cmd.Parameters.AddRange(parmaeters);
}
conn.Open();
int ir = cmd.ExecuteNonQuery();
return ir;
}
}
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parmaeters)
{
SqlConnection conn = new SqlConnection(connctionString);
SqlCommand cmd = new SqlCommand(sql, conn);
if (parmaeters != null)
{
cmd.Parameters.AddRange(parmaeters);
}
conn.Open();
//SqlDataReader对象需要与数据库保持链接才能取到查询后保存在数据库中的数据
//ExecuteReader的CommandBehavior.CloseConnection参数表示当SqlDataReader
//对象关闭时，conn对象关闭与数据库的链接
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
数据库访问层：每一个模型层的类，都在数据库访问层对应一个类，该类的命名一般为模型层中的类名+Services,此类中仅包含对该表的CRUD的方法。
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using HotelManager.Models; 7 using System.Data.SqlClient; 8 using System.Data; 9 10 namespace HotelManager.DAL 11 { 12 public class RoomTypeServices 13 { 14 public int AddRoomType(RoomType addRoomType) 15 { 16 string sql = @"INSERT INTO [RoomType] 17 ([TypeName] 18 ,[Price] 19 ,[AddBed] 20 ,[BedPrice] 21 ,[Remark]) 22 VALUES 23 (@typename 24 ,@price 25 ,@addbed 26 ,@bedprice 27 ,@remark)"; 28 29 //添加参数的方式 30 SqlParameter[] parms = 31 { 32 new SqlParameter("@typename",SqlDbType.NVarChar,50), 33 new SqlParameter("@price",SqlDbType.Money,8), 34 new SqlParameter("@addbed",SqlDbType.Bit,1), 35 new SqlParameter("@bedprice",SqlDbType.Money,8), 36 new SqlParameter("@remark",SqlDbType.NVarChar,200) 37 38 }; 39 40 parms[0].Value = addRoomType.TypeName; 41 parms[1].Value = addRoomType.Price; 42 parms[2].Value = addRoomType.AddBed; 43 parms[3].Value = addRoomType.BedPrice; 44 if (addRoomType.Remark.Length == 0) //********实体中空值数据的处理******** 45 { 46 47 parms[4].Value = DBNull.Value; 48 } 49 else 50 { 51 parms[4].Value = addRoomType.Remark; 52 } 53 54 return DBHelpSQL.ExecuteNonQuery(sql, parms); 55 56 57 } 58 /// <summary> 59 /// 通过类型名称得到一个类型实体,如果不存在类型,则返回null 60 /// </summary> 61 /// <param name="typeName">实型类型的名称</param> 62 /// <returns></returns> 63 public RoomType GetRoomTypeByTypeName(string typeName) 64 { 65 string sql = "select * from RoomType where TypeName=@typename"; 66 SqlParameter[] parms = {new SqlParameter("@typename",typeName) }; 67 SqlDataReader dr = DBHelpSQL.ExecuteReader(sql,parms); 68 RoomType oneRoomType = null; 69 if (dr.Read()) 70 { 71 oneRoomType = new RoomType(); 72 oneRoomType.TypeId = Convert.ToInt32(dr["TypeId"]); 73 oneRoomType.TypeName = dr["TypeName"].ToString(); 74 oneRoomType.Price = Convert.ToDecimal(dr["Price"]); 75 oneRoomType.AddBed = Convert.ToBoolean(dr["AddBed"]); 76 oneRoomType.BedPrice = Convert.ToDecimal(dr["BedPrice"]); 77 78 //数据库中可空字段的处理 79 oneRoomType.Remark = dr.IsDBNull(dr.GetOrdinal("Remark")) ? "" : dr["Remark"].ToString(); 80 } 81 82 dr.Close(); 83 return oneRoomType; 84 85 } 86 87 88 } 89 }