//DAL层
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Entity;
using System.Data.OleDb;
namespace DAL
{
public class ProductDAO
{
//调用D层的方法
public DataTable QueryProductByCa(CagetoryEntity enCagetory)
{
string SQL = "SELECT * FROM Product WHERE CagetoryID=@CaID";
//查询参数
OleDbParameter[] parms =
{
new OleDbParameter("@CaID", enCagetory.ID)
};
return new SQLHelper().ExecuteQuery(SQL, parms, CommandType.Text);
}
}
}
//SQL层
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Configuration.Assemblies;
namespace DAL
{
public class SQLHelper
{
private OleDbCommand cmd = null;
private OleDbConnection con = null;
private OleDbDataReader sdr = null;
#region 构造方法
/// <summary>
/// 构造函数
/// </summary>
public SQLHelper()
{ //连接字符串，从配置文件获取
string strConn = ConfigurationManager.ConnectionStrings["mydb"].ConnectionString;
con = new OleDbConnection(strConn);
}
#endregion
#region 获得数据库连接
/// <summary>
/// 获得数据库连接
/// </summary>
private OleDbConnection GetCnn()
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
return con;
}
#endregion
#region 执行带参数的查询命令
/// <summary>
/// 执行带参数的查询命令
/// </summary>
/// <param name="cmmText">Sql语句或存储过程</param>
/// <param name="para">参数集合</param>
/// <param name="cmmType">命令类型</param>
public DataTable ExecuteQuery(string cmmText, OleDbParameter[] para, CommandType cmmType)
{
DataTable dt = new DataTable();
cmd = new OleDbCommand(cmmText, GetCnn());
cmd.CommandType = cmmType;
cmd.Parameters.AddRange(para);
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
return dt;
}
}
#endregion
}
}
配置文件连接字符串