面向接口开发的一个架构(二)
讲述一个基于接口开发的架构模式,数据访问层
数据访问层
一、IDAL 定义数据访问层接口
IDABase.cs
IDABase
1namespace IDAL
2{
3 public interface IDABase
4 {
5 DataSet Get();
6 DataSet Get(int id);
7 int Add(DataSet ds);
8 int Delete(int id);
9 int Update(DataSet ds);
10 }
11}
1namespace IDAL
2{
3 public interface IDABase
4 {
5 DataSet Get();
6 DataSet Get(int id);
7 int Add(DataSet ds);
8 int Delete(int id);
9 int Update(DataSet ds);
10 }
11}
二、DataAccess 数据访问实现
BaseDA.cs
BaseDA
1namespace DataAccess
2{
3 public class BaseDA : IDABase
4 {
5 IDABase Members#region IDABase Members
6
7 public virtual DataSet Get()
8 {
9 throw new NotImplementedException();
10 }
11
12 public virtual DataSet Get(int id)
13 {
14 throw new NotImplementedException();
15 }
16
17 public virtual int Add(System.Data.DataSet ds)
18 {
19 throw new NotImplementedException();
20 }
21
22 public virtual int Delete(int id)
23 {
24 throw new NotImplementedException();
25 }
26
27 public virtual int Update(System.Data.DataSet ds)
28 {
29 throw new NotImplementedException();
30 }
31
32 #endregion
33
34 protected SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString);
35
36 protected SqlParameter[] AssignParam(SqlParameter[] parms, DataSet ds)
37 {
38 if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
39 {
40 for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
41 {
42 parms[i].Value = ds.Tables[0].Rows[0][i];
43 }
44 }
45 return parms;
46 }
47 protected DataSet GetData(string sqlText)
48 {
49 DataSet ds = new DataSet();
50 SqlDataAdapter da = new SqlDataAdapter(sqlText, conn);
51 da.Fill(ds);
52 return ds;
53 }
54 }
55}
1namespace DataAccess
2{
3 public class BaseDA : IDABase
4 {
5 IDABase Members#region IDABase Members
6
7 public virtual DataSet Get()
8 {
9 throw new NotImplementedException();
10 }
11
12 public virtual DataSet Get(int id)
13 {
14 throw new NotImplementedException();
15 }
16
17 public virtual int Add(System.Data.DataSet ds)
18 {
19 throw new NotImplementedException();
20 }
21
22 public virtual int Delete(int id)
23 {
24 throw new NotImplementedException();
25 }
26
27 public virtual int Update(System.Data.DataSet ds)
28 {
29 throw new NotImplementedException();
30 }
31
32 #endregion
33
34 protected SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString);
35
36 protected SqlParameter[] AssignParam(SqlParameter[] parms, DataSet ds)
37 {
38 if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
39 {
40 for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
41 {
42 parms[i].Value = ds.Tables[0].Rows[0][i];
43 }
44 }
45 return parms;
46 }
47 protected DataSet GetData(string sqlText)
48 {
49 DataSet ds = new DataSet();
50 SqlDataAdapter da = new SqlDataAdapter(sqlText, conn);
51 da.Fill(ds);
52 return ds;
53 }
54 }
55}
LogDA.cs
LogDA
1namespace DataAccess
2{
3 public class LogDA : BaseDA
4 {
5 private const string SQL_GET_LOGID = "select top 1 id from Log order by id desc";
6 private const string SQL_INSERT_LOG = "insert into Log values(@ID,@Name,@Describe,@CreateDate)";
7 private const string SQL_GET_LOG = "select * from Log";
8 private const string SQL_GET_LOG_ID = "select * from Log where ID=";
9
10 public override int Add(DataSet ds)
11 {
12 int id = 0;
13 SqlParameter[] parms = GetLogParamerters();
14 parms = AssignParam(parms, ds);
15 conn.Open();
16 object objID = SqlHelper.ExecuteScalar(conn, CommandType.Text, SQL_GET_LOGID, null);
17 if (objID != null)
18 {
19 id = Convert.ToInt32(objID);
20 }
21 id++;
22 parms[0].Value = id;
23 SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_LOG, parms);
24 conn.Close();
25 return id;
26 }
27
28 public override DataSet Get()
29 {
30 return base.GetData(SQL_GET_LOG);
31 }
32
33 public override DataSet Get(int id)
34 {
35 string sqlText = SQL_GET_LOG_ID + id;
36 return base.GetData(sqlText);
37 }
38
39 private SqlParameter[] GetLogParamerters()
40 {
41 SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_LOG);
42
43 if (parms == null)
44 {
45 parms = new SqlParameter[]
46 {
47 new SqlParameter("@ID",SqlDbType.Int,4),
48 new SqlParameter("@Name",SqlDbType.VarChar,1024),
49 new SqlParameter("@Describe",SqlDbType.VarChar,4000),
50 new SqlParameter("@CreateDate",SqlDbType.DateTime)
51 };
52
53 SqlHelper.CacheParameters(SQL_INSERT_LOG, parms);
54 }
55
56 return parms;
57 }
58 }
59}
1namespace DataAccess
2{
3 public class LogDA : BaseDA
4 {
5 private const string SQL_GET_LOGID = "select top 1 id from Log order by id desc";
6 private const string SQL_INSERT_LOG = "insert into Log values(@ID,@Name,@Describe,@CreateDate)";
7 private const string SQL_GET_LOG = "select * from Log";
8 private const string SQL_GET_LOG_ID = "select * from Log where ID=";
9
10 public override int Add(DataSet ds)
11 {
12 int id = 0;
13 SqlParameter[] parms = GetLogParamerters();
14 parms = AssignParam(parms, ds);
15 conn.Open();
16 object objID = SqlHelper.ExecuteScalar(conn, CommandType.Text, SQL_GET_LOGID, null);
17 if (objID != null)
18 {
19 id = Convert.ToInt32(objID);
20 }
21 id++;
22 parms[0].Value = id;
23 SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_LOG, parms);
24 conn.Close();
25 return id;
26 }
27
28 public override DataSet Get()
29 {
30 return base.GetData(SQL_GET_LOG);
31 }
32
33 public override DataSet Get(int id)
34 {
35 string sqlText = SQL_GET_LOG_ID + id;
36 return base.GetData(sqlText);
37 }
38
39 private SqlParameter[] GetLogParamerters()
40 {
41 SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_LOG);
42
43 if (parms == null)
44 {
45 parms = new SqlParameter[]
46 {
47 new SqlParameter("@ID",SqlDbType.Int,4),
48 new SqlParameter("@Name",SqlDbType.VarChar,1024),
49 new SqlParameter("@Describe",SqlDbType.VarChar,4000),
50 new SqlParameter("@CreateDate",SqlDbType.DateTime)
51 };
52
53 SqlHelper.CacheParameters(SQL_INSERT_LOG, parms);
54 }
55
56 return parms;
57 }
58 }
59}
SQLHelper是放在Utility层中的通用工具类,跟Petshop中的SQLHelper是一个文件。只是添加了ConnectionString属性