DbHelper类 

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 }

 

posted @ 2013-06-13 15:20  Big.Eagle  阅读(191)  评论(0)  编辑  收藏  举报