点NET三层架构(简易).doc

数据访问层:using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Configuration;using System.Data;using System.Data.SqlClient;namespace DAL public class SQLHelper string connStr = ConfigurationManager.ConnectionStringsconnStr.ToString(); SqlConnection conn; SqlCommand cmd; SqlDataAdapter da; DataSet ds; / / 获取连接字符串 / / static public string getConnStr() return ConfigurationManager.ConnectionStringsconnStr.ToString(); / / 打开数据库连接 / public void conn_open() conn = new SqlConnection(connStr); if (conn.State=ConnectionState.Closed) conn.Open(); / / 关闭数据库连接 / public void conn_close() if (conn.State = ConnectionState.Open) conn.Close(); / / 执行无参存储过程,返回dataset / / public DataSet exec_proc_ds(string storeName) DataSet ds = new DataSet(); cmd = new SqlCommand(storeName,conn); cmd.CommandType = CommandType.StoredProcedure; da = new SqlDataAdapter(cmd); da.Fill(ds); return ds; #region 执行Insert,Delete,Update,统计 / / 执行有参数的SQL操作 / 用于 Insert,Delete,Update / / Sql语语 / 参数表 / 返回int 值等于1为成功执行 protected int RunSql(string strSql, IDataParameter parameters) int n = -1; objConn.Open(); SqlCommand cmd = new SqlCommand(strSql, objConn); try foreach (SqlParameter parm in parameters) cmd.Parameters.Add(parm); n = (int)cmd.ExecuteNonQuery(); catch finally objConn.Close(); return n; / / 做统计用的，返回第一行第一列的值 / 配合SQL语句中的 Count(*)使用 / / / / 返回第一行第一列的值 protected int RunSqlExcuteScalar(string strSql) int n = -1; objConn.Open(); SqlCommand cmd = new SqlCommand(strSql, objConn); try n = (int)cmd.ExecuteScalar(); catch finally objConn.Close(); return n; /同上 protected int RunSqlExecuteScalar(string strSql, IDataParameter parameters) int n = -1; objConn.Open(); SqlCommand cmd = new SqlCommand(strSql, objConn); try foreach (SqlParameter parms in parameters) cmd.Parameters.Add(parms); n = (int)cmd.ExecuteScalar(); catch finally objConn.Close(); return n; #endregion #region 返回 Dataset / / 执行Select查询，不包含SqlParameter参数 / / Sql语句 / DataSet中的Table名 / 返回 DataSet protected DataSet RunSql(string strSql, string tableName) DataSet ds = new DataSet(); objConn.Open(); SqlCommand cmd = new SqlCommand(strSql, objConn); SqlDataAdapter sqlAd = new SqlDataAdapter(cmd); sqlAd.Fill(ds, tableName); objConn.Close(); return ds; public DataTable RunsqlTable(string sql) DataTable dt = new DataTable(); objConn.Open(); SqlCommand cmd = new SqlCommand(sql, objConn); SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(dt); objConn.Close(); return dt; /同上，此为有参数据 protected DataSet RunSql(string strSql, string tableName, IDataParameter parameters) DataSet ds = new DataSet(); objConn.Open(); SqlCommand cmd = new SqlCommand(strSql, objConn); foreach (SqlParameter parm in parameters) cmd.Parameters.Add(parm); SqlDataAdapter sqlAd = new SqlDataAdapter(cmd); sqlAd.Fill(ds, tableName); objConn.Close(); sqlAd.Dispose(); return ds; / / 执行Select分页查询，不包含SqlParameter参数 / / Sql语句 / DataSet中的Table名 / 每页多少条信息 / 当前页码 / protected DataSet RunSql(string strSql, string tableName, int pageSize, int currentPageIndex) DataSet ds = new DataSet(); objConn.Open(); SqlCommand cmd = new SqlCommand(strSql, objConn); SqlDataAdapter sqlAD = new SqlDataAdapter(cmd); sqlAD.Fill(ds, pageSize * (currentPageIndex - 1), pageSize, tableName); objConn.Close(); return ds; web .config 业务层通过工厂方法获得数据访问层实例，调用读取方法： public class OrderHeader private IOrderHeader orderHeader = DataAccess.CreateOrderHeader(); public List GetOrderHeaderList(int shipMethodID, int maximumRows, int startRowIndex) return orderHeader.GetOrderHeaderList(shipMethodID, maximumRows, startRowIndex); public int GetTotalNumberOfOrderHeader(int shipMethodID, int maximumRows, int startRowIndex) return orderHeader.GetTotalNumberOfOrderHeader(shipMethodID); 注意GetTotalNumberOfOrderHeader这个方法，即ObjectDataSource控件的SelectCountMethod要指定的值，该值为方法名称且不能指定参数，所以该方法的参数应与SelectCommand所指定的方法参数相一致，否则运行时会抛出异常。四、 表示层（这里指ASP.Net页面）G