数据库三层模式

tb_ware.cs  数据库表字段属性类：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// tb_brand 表字段对应属性类
/// </summary>
public class tb_brand
{
private int _b_Id;              //品牌编号
private DateTime _b_Regdate;    //添加日期
private Boolean _b_State;       //品牌状态
private string _b_Letter;       //品牌字母标识
private string _b_Sort;         //品牌类别
private string _b_Bname;        //品牌名称
private string _b_Brief;        //品牌简介
private string _b_Logo;         //品牌LOGO
private string _b_Hall;         //品牌所在地（厅）
private string _b_Network;      //品牌网址
public int b_Id
{
get { return _b_Id; }
set { _b_Id = value; }
}
public DateTime b_Regdate
{
get { return _b_Regdate; }
set { _b_Regdate = value; }
}
public Boolean b_State
{
get { return _b_State; }
set { _b_State = value; }
}
public string b_Letter
{
get { return _b_Letter; }
set { _b_Letter = value; }
}
public string b_Sort
{
get { return _b_Sort; }
set { _b_Sort = value; }
}
public string b_Bname
{
get { return _b_Bname; }
set { _b_Bname = value; }
}
public string b_Brief
{
get { return _b_Brief; }
set { _b_Brief = value; }
}
public string b_Logo
{
get { return _b_Logo; }
set { _b_Logo = value; }
}
public string b_Hall
{
get { return _b_Hall; }
set { _b_Hall = value; }
}
public string b_Network
{
get { return _b_Network; }
set { _b_Network = value; }
}
}
集合类：cs_ware.cs   ：
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// cs_brand 的摘要说明
/// </summary>
public class cs_brand : ArrayList
{
public cs_brand() : base()
{
}
public cs_brand(ICollection c) : base(c)
{
}
}
db_ware.cs  操作类：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// db_brand 类为对数据库中表db_brand的操作
/// </summary>
public class db_brand
{
private SqlConnection _Conn;             //SQL数据库连接
private String _strDB = "tb_brand";      //SQL数据库表
//连接数据库
public db_brand()
{
_Conn = new SqlConnection(ConfigurationManager.AppSettings["strConn"]);
}
//打开数据库
public void Open()
{
if (_Conn.State == ConnectionState.Closed)
{
_Conn.Open();
}
}
//关闭数据库
public void Close()
{
if (_Conn.State == ConnectionState.Open)
{
_Conn.Close();
_Conn.Dispose();
}
}
//向tb_brand.cs类的属性填充数据
public tb_brand setData(SqlDataReader sdr)
{
tb_brand setD = new tb_brand();
setD.b_Id = Convert.ToInt32(sdr["b_Id"]);
setD.b_Regdate = Convert.ToDateTime(sdr["b_Regdate"]);
setD.b_State = Convert.ToBoolean(sdr["b_State"]);
setD.b_Letter = Convert.ToString(sdr["b_Letter"]);
setD.b_Sort = Convert.ToString(sdr["b_Srot"]);
setD.b_Bname = Convert.ToString(sdr["b_Bname"]);
setD.b_Brief = Convert.ToString(sdr["b_Brief"]);
setD.b_Logo = Convert.ToString(sdr["b_Logo"]);
setD.b_Hall = Convert.ToString(sdr["b_Hall"]);
setD.b_Network = Convert.ToString(sdr["b_Network"]);
return setD;
}
//按传入SQL执行指定任务，返回类型为属性，用于查询
public tb_brand returnTB(string tbSql)
{
tb_brand tbReturn =new tb_brand();
SqlCommand cmd = new SqlCommand(tbSql, _Conn);
if (cmd.ExecuteScalar() != null)
{
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
tbReturn = setData(dr);
dr.Close();
dr.Dispose();
return tbReturn;
}
else
{
return tbReturn = null;
}
}
//按传入SQL执行指定任务，返回类型为集合，用于查询
public cs_brand returnCS(string csSql)
{
cs_brand csReturn = new cs_brand();
SqlCommand cmd = new SqlCommand(csSql, _Conn);
if (cmd.ExecuteScalar() != null)
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tb_brand setTB = setData(dr);
csReturn.Add(setTB);
}
dr.Close();
dr.Dispose();
return csReturn;
}
else
{
return csReturn = null;
}
}
//查看品牌状态是否为可用
public Boolean state_Brand(int state_Id)
{
string strSql = "select b_State from " + _strDB + " where b_Id='" + state_Id + "'";
SqlCommand cmd = new SqlCommand(strSql, _Conn);
if (cmd.ExecuteScalar() != null)
{
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
Boolean states = Convert.ToBoolean(dr["b_State"]);
dr.Close();
dr.Dispose();
return states;
}
else
{
return true;
}
}
//查询所有品牌信息
public cs_brand find_allBrand()
{
cs_brand brandC = new cs_brand();
string strSql = "select b_Id,b_Regdate,b_State,b_Letter,b_Sort,b_Bname,b_Brief,b_Logo,b_Hall,b_Network from " + _strDB;
SqlCommand cmd = new SqlCommand(strSql, _Conn);
if (cmd.ExecuteScalar() != null)
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tb_brand setBrand = setData(dr);
brandC.Add(setBrand);
}
dr.Close();
dr.Dispose();
return brandC;
}
else
{
return brandC = null;
}
}
//查询指定品牌信息
public tb_brand find_Brand(int find_id)
{
tb_brand findC = new tb_brand();
string strSql = "select b_Id,b_Regdate,b_State,b_Letter,b_Sort,b_Bname,b_Brief,b_Logo,b_Hall,b_Network from " + _strDB + " where b_Id='" + find_id + "'";
SqlCommand cmd = new SqlCommand(strSql, _Conn);
if (cmd.ExecuteScalar() != null)
{
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
findC = setData(dr);
dr.Close();
dr.Dispose();
return findC;
}
else
{
return findC = null;
}
}
//添加新品牌数据
public void add_Brand(tb_brand addbrand)
{
string strSql = "insert into " + _strDB + "(b_Letter,b_Sort,b_Bname,b_Brief,b_Logo,b_Hall,b_Network)" +
" values(@b_Letter,@b_Sort,@b_Bname,@b_Brief,@b_Logo,@b_Hall,@b_Network)";
SqlCommand cmd = new SqlCommand(strSql, _Conn);
cmd.Parameters.Add("@b_Letter", SqlDbType.NVarChar).Value = addbrand.b_Letter;
cmd.Parameters.Add("@b_Sort", SqlDbType.NVarChar).Value = addbrand.b_Sort;
cmd.Parameters.Add("@b_Bname", SqlDbType.NVarChar).Value = addbrand.b_Bname;
cmd.Parameters.Add("@b_Brief", SqlDbType.NVarChar).Value = addbrand.b_Brief;
cmd.Parameters.Add("@b_Logo", SqlDbType.NVarChar).Value = addbrand.b_Logo;
cmd.Parameters.Add("@b_Hall", SqlDbType.NVarChar).Value = addbrand.b_Hall;
cmd.Parameters.Add("@b_Network", SqlDbType.NVarChar).Value = addbrand.b_Network;
cmd.ExecuteNonQuery();
}
//修改品牌状态
public Boolean modify_State(tb_brand modifystate)
{
string strSql = "update " + _strDB + " set b_State=@b_State where b_Id='" + modifystate.b_Id + "'";
SqlCommand cmd = new SqlCommand(strSql, _Conn);
cmd.Parameters.Add("@b_State", SqlDbType.Bit).Value = modifystate.b_State;
if (cmd.ExecuteScalar() != null)
{
cmd.ExecuteNonQuery();
return true;
}
else
{
return false;
}
}
//修改品牌LOGO
public Boolean modify_Logo(tb_brand modifylogo)
{
string strSql = "update " + _strDB + " set b_Logo=@b_Logo where b_Id='" + modifylogo.b_Id + "'";
SqlCommand cmd = new SqlCommand(strSql, _Conn);
cmd.Parameters.Add("@b_Logo", SqlDbType.NVarChar).Value = modifylogo.b_Logo;
if (cmd.ExecuteScalar() != null)
{
cmd.ExecuteNonQuery();
return true;
}
else
{
return false;
}
}
//修改品牌数据
public Boolean modify_Brand(tb_brand modifybrand)
{
string strSql = "update " + _strDB + " set b_Letter=@b_Letter,b_Sort=@b_Sort," +
"b_Bname=@b_Bname,b_Brief=@b_Brief," +
"b_Hall=@b_Hall,b_Network=@b_Network" +
" where b_Id='" + modifybrand.b_Id + "'";
SqlCommand cmd = new SqlCommand(strSql, _Conn);
cmd.Parameters.Add("@b_Letter", SqlDbType.NVarChar).Value = modifybrand.b_Letter;
cmd.Parameters.Add("@b_Sort", SqlDbType.NVarChar).Value = modifybrand.b_Sort;
cmd.Parameters.Add("@b_Bname", SqlDbType.NVarChar).Value = modifybrand.b_Bname;
cmd.Parameters.Add("@b_Brief", SqlDbType.NVarChar).Value = modifybrand.b_Brief;
cmd.Parameters.Add("@b_Hall", SqlDbType.NVarChar).Value = modifybrand.b_Hall;
cmd.Parameters.Add("@b_Network", SqlDbType.NVarChar).Value = modifybrand.b_Network;
if (cmd.ExecuteScalar() != null)
{
cmd.ExecuteNonQuery();
return true;
}
else
{
return false;
}
}
//删除品牌数据
public Boolean del_Brand(int del_id)
{
string strSql = "delete from " + _strDB + " where b_Id='" + del_id + "'";
SqlCommand cmd = new SqlCommand(strSql, _Conn);
if (cmd.ExecuteScalar() != null)
{
cmd.ExecuteNonQuery();
return true;
}
else
{
return false;
}
}
}

转载于:https://www.cnblogs.com/like0112/archive/2008/10/30/1323264.html