asp.net 數據庫操作層

using System;using System.Collections.Generic;using System.Linq;using System.Text;using DataHelp;using System.Data;namespace DAL{ public class OtherDefineDAL : DALBase { public static DataTable GetOtherDefine() { string sql = string.Format("select * from dbo.OtherDefineA"); return SqlDataAccess.FillTable(sql); } public static DataTable GetOtherDefine(int ID) { string sql = string.Format(@"select * from OtherDefineA where ID=@ID"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ID",ID); return SqlDataAccess.FillTable(spm); } public static DataTable GetOtherDefineB(int ID) { string sql = string.Format(@"select * from dbo.OtherDefineB as b join dbo.OtherDefineA as a on b.ItemNo=a.ID where a.ID=@ID"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ID", ID); return SqlDataAccess.FillTable(spm); } public static int InsertOtherDefineA(string ItemCode, string ItemCName, string ItemEName) { string sql = string.Format("insert into OtherDefineA values(@ItemCode,@ItemCName,@ItemEName)"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ItemCode", ItemCode); spm.Add("@ItemCName", ItemCName); spm.Add("@ItemEName", ItemEName); int objRow = SqlDataAccess.ExecuteNonQuery(spm); return objRow; } public static DataTable GetMaxID() { string sql = string.Format("select max(ID) FROM OtherDefineA"); return SqlDataAccess.FillTable(sql); } public static int InsertOtherDefineB(int ItemNo, string ListCode, string ListA, string ListB, string ListC, string ListD, string ListE) { string sql = string.Format(@"insert into OtherDefineB values(@ItemNo,@ListCode,@ListA,@ListB,@ListC,@ListD,@ListE) "); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ItemNo", ItemNo); spm.Add("@ListCode", ListCode); spm.Add("@ListA", ListA); spm.Add("@ListB", ListB); spm.Add("@ListC", ListC); spm.Add("@ListD", ListD); spm.Add("@ListE", ListE); int objRow = SqlDataAccess.ExecuteNonQuery(spm); return objRow; } public static int UpdateOtherDefineB(int ItemNo, string ListCode, string ListA, string ListB, string ListC, string ListD, string ListE) { string sql = string.Format(@"update OtherDefineB set ListA=@ListA,ListB=@ListB,ListC=@ListC,ListD=@ListD,ListE=@ListE where ItemNo=@ItemNo and ListCode=@ListCode"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ItemNo", ItemNo); spm.Add("@ListCode", ListCode); spm.Add("@ListA", ListA); spm.Add("@ListB", ListB); spm.Add("@ListC", ListC); spm.Add("@ListD", ListD); spm.Add("@ListE", ListE); int objRow = SqlDataAccess.ExecuteNonQuery(spm); return objRow; } public static int DeleteOtherDefineB(int ItemNo, string ListCode) { string sql = string.Format(@"delete from OtherDefineB where ItemNo=@ItemNo AND ListCode=@ListCode"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ItemNo", ItemNo); spm.Add("@ListCode", ListCode); int objRow = SqlDataAccess.ExecuteNonQuery(spm); return objRow; } public static DataTable GetListCode(int ItemNo, string ListCode) { string sql = string.Format(@"select * from dbo.OtherDefineB where ListCode=@ListCode and ItemNo=@ItemNo"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ListCode", ListCode); spm.Add("@ItemNo", ItemNo); return SqlDataAccess.FillTable(spm); } public static int UpdateOtherDefineA(int ID, string ItemCode, string ItemCName, string ItemEName) { string sql = string.Format(@"update OtherDefineA set ItemCode=@ItemCode,ItemCName=@ItemCName,ItemEName=@ItemEName where ID=@ID"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ID", ID); spm.Add("@ItemCode", ItemCode); spm.Add("@ItemCName", ItemCName); spm.Add("@ItemEName", ItemEName); int objRow = SqlDataAccess.ExecuteNonQuery(spm); return objRow; } public static int DeleteOtherDefineA(int id) { string sql = string.Format(@"delete from OtherDefineA where id=@id"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@id", id); int objRow = SqlDataAccess.ExecuteNonQuery(spm); return objRow; } public static int DeleteAllOtherDefineB(int ItemNo) { string sql = string.Format(@"delete from OtherDefineB where ItemNo=@ItemNo"); SQLParameterManager spm = new SQLParameterManager(sql, CommandType.Text); spm.Add("@ItemNo", ItemNo); int objRow = SqlDataAccess.ExecuteNonQuery(spm); return objRow; } }}