|
|
using System;using System.Data;using System.Text;using System.Data.SqlClient;using OT.IDAL;using DBUtility;//Please add references
using System.Collections.Generic;namespace OT.SQLServerDAL{ /// <summary>
/// 資料訪問類:OTB_SYS_Arguments
/// </summary>
public partial class OTB_SYS_Arguments : IOTB_SYS_Arguments { public OTB_SYS_Arguments() { } #region Method
/// <summary>
/// 是否存在該記錄
/// </summary>
public bool Exists(string ArgumentClassID, string ArgumentID) { int rowsAffected; StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from OTB_SYS_Arguments"); strSql.Append(" where ArgumentClassID=@ArgumentClassID and ArgumentID=@ArgumentID "); SqlParameter[] parameters = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10), new SqlParameter("@ArgumentID", SqlDbType.VarChar,200) }; parameters[0].Value = ArgumentClassID; parameters[1].Value = ArgumentID; return DbHelperSQL.Exists(strSql.ToString(), parameters); //int result = DbHelperSQL.RunProcedure("OSP_OTB_SYS_Arguments_Exists", parameters, out rowsAffected);
//if (result == 1)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 增加一條資料
/// </summary>
public bool Add(OT.Model.OTB_SYS_Arguments model) { StringBuilder strSql = new StringBuilder(); strSql.Append(" INSERT INTO [OTB_SYS_Arguments]( "); strSql.Append(" [ArgumentClassID],[ArgumentID],[ArgumentValue],[OrderByValue],[DelStatus],[Effective],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] "); strSql.Append(" )VALUES( "); strSql.Append(" @ArgumentClassID,@ArgumentID,@ArgumentValue,@OrderByValue,@DelStatus,@Effective,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE()) "); SqlParameter[] parametersAdd = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10), new SqlParameter("@ArgumentID", SqlDbType.VarChar,200), new SqlParameter("@ArgumentValue", SqlDbType.NVarChar,200), new SqlParameter("@OrderByValue", SqlDbType.Int,4), new SqlParameter("@LevelOfArgument", SqlDbType.Int,4), new SqlParameter("@ParentArgument", SqlDbType.VarChar,200), new SqlParameter("@DelStatus", SqlDbType.Char,1), new SqlParameter("@Effective", SqlDbType.Char,1), new SqlParameter("@Memo", SqlDbType.Text), new SqlParameter("@CreateUser", SqlDbType.VarChar,50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), new SqlParameter("@ModifyDate", SqlDbType.DateTime)}; parametersAdd[0].Value = model.ArgumentClassID; parametersAdd[1].Value = model.ArgumentID; parametersAdd[2].Value = model.ArgumentValue; parametersAdd[3].Value = model.OrderByValue; parametersAdd[4].Value = model.LevelOfArgument; parametersAdd[5].Value = model.ParentArgument; parametersAdd[6].Value = model.DelStatus; parametersAdd[7].Value = model.Effective; parametersAdd[8].Value = model.Memo; parametersAdd[9].Value = model.CreateUser; parametersAdd[10].Value = model.CreateDate; parametersAdd[11].Value = model.ModifyUser; parametersAdd[12].Value = model.ModifyDate;
List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); //定義事物執行的所有SQL
StringBuilder strSql2 = new StringBuilder(); strSql2.Append(" DECLARE @strSQL NVARCHAR(1000) "); strSql2.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue) "); strSql2.Append(" BEGIN "); //由小變大，OLD<-1<=NEW
strSql2.Append(" SET @strSQL=N'UPDATE '+@TableName+' "); strSql2.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1 "); strSql2.Append(" WHERE '+@FeildName+' >= '+@OldOrderByValue + ' AND '+@FeildName+'<='+@NewOrderByValue+' ' "); strSql2.Append(" IF @Where <>'' "); strSql2.Append(" SET @strSQL =@strSQL + ' AND ' + @Where "); strSql2.Append(" print @strSQL "); strSql2.Append(" exec sp_executesql @strSQL "); strSql2.Append(" END "); strSql2.Append(" ELSE "); strSql2.Append(" BEGIN "); //由大變小，@NewOrderByValue<=+1<@OldOrderByValue
strSql2.Append(" SET @strSQL=N'UPDATE '+@TableName+' "); strSql2.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1 "); strSql2.Append(" WHERE '+@FeildName+' <= '+@OldOrderByValue + ' AND '+@FeildName+'>='+@NewOrderByValue+' ' "); strSql2.Append(" IF @Where <>'' "); strSql2.Append(" SET @strSQL =@strSQL + ' AND ' + @Where "); strSql2.Append(" print @strSQL "); strSql2.Append(" exec sp_executesql @strSQL "); strSql2.Append(" END "); SqlParameter[] parameters = { new SqlParameter("@OldOrderByValue", SqlDbType.NVarChar,10), new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10), new SqlParameter("@FeildName", SqlDbType.NVarChar,50), new SqlParameter("@TableName", SqlDbType.NVarChar,50), new SqlParameter("@Where", SqlDbType.NVarChar,500)}; parameters[0].Value = model.OldOrderByValue; parameters[1].Value = model.OrderByValue; parameters[2].Value = "OrderByValue"; parameters[3].Value = "OTB_SYS_Arguments"; parameters[4].Value = "ArgumentClassID='" + model.ArgumentClassID + "' AND ISNULL(DelStatus,'N') = 'N'"; CommandInfo o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
//o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
o_CommandInfo.CommandText = strSql2.ToString(); o_CommandInfo.Parameters = parameters; lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表新增，雖然參數名稱相同，但是經過new以後就是新的對象
//o_CommandInfo.CommandText = "OSP_OTB_SYS_Arguments_ADD";
o_CommandInfo.CommandText = strSql.ToString(); o_CommandInfo.Parameters = parametersAdd; lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0) { return true; } else { return false; } }
/// <summary>
/// 更新一條資料
/// </summary>
public bool Update(OT.Model.OTB_SYS_Arguments model) {
StringBuilder strSql = new StringBuilder(); strSql.Append(" UPDATE [OTB_SYS_Arguments] SET "); strSql.Append(" [ArgumentValue] = @ArgumentValue,[OrderByValue] = @OrderByValue,[DelStatus] = @DelStatus,[Effective] = @Effective,[Memo] = @Memo,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE() "); strSql.Append(" WHERE ArgumentClassID=@ArgumentClassID and ArgumentID=@ArgumentID "); SqlParameter[] parameters = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10), new SqlParameter("@ArgumentID", SqlDbType.VarChar,200), new SqlParameter("@ArgumentValue", SqlDbType.NVarChar,200), new SqlParameter("@OrderByValue", SqlDbType.Int,4), new SqlParameter("@LevelOfArgument", SqlDbType.Int,4), new SqlParameter("@ParentArgument", SqlDbType.VarChar,200), new SqlParameter("@DelStatus", SqlDbType.Char,1), new SqlParameter("@Effective", SqlDbType.Char,1), new SqlParameter("@Memo", SqlDbType.Text), new SqlParameter("@CreateUser", SqlDbType.VarChar,50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), new SqlParameter("@ModifyDate", SqlDbType.DateTime)}; parameters[0].Value = model.ArgumentClassID; parameters[1].Value = model.ArgumentID; parameters[2].Value = model.ArgumentValue; parameters[3].Value = model.OrderByValue; parameters[4].Value = model.LevelOfArgument; parameters[5].Value = model.ParentArgument; parameters[6].Value = model.DelStatus; parameters[7].Value = model.Effective; parameters[8].Value = model.Memo; parameters[9].Value = model.CreateUser; parameters[10].Value = model.CreateDate; parameters[11].Value = model.ModifyUser; parameters[12].Value = model.ModifyDate;
List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); //定義事物執行的所有SQL
StringBuilder strSql2 = new StringBuilder(); strSql2.Append(" DECLARE @strSQL NVARCHAR(1000) "); strSql2.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue) "); strSql2.Append(" BEGIN "); //由小變大，OLD<-1<=NEW
strSql2.Append(" SET @strSQL=N'UPDATE '+@TableName+' "); strSql2.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1 "); strSql2.Append(" WHERE '+@FeildName+' >= '+@OldOrderByValue + ' AND '+@FeildName+'<='+@NewOrderByValue+' ' "); strSql2.Append(" IF @Where <>'' "); strSql2.Append(" SET @strSQL =@strSQL + ' AND ' + @Where "); strSql2.Append(" print @strSQL "); strSql2.Append(" exec sp_executesql @strSQL "); strSql2.Append(" END "); strSql2.Append(" ELSE "); strSql2.Append(" BEGIN "); //由大變小，@NewOrderByValue<=+1<@OldOrderByValue
strSql2.Append(" SET @strSQL=N'UPDATE '+@TableName+' "); strSql2.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1 "); strSql2.Append(" WHERE '+@FeildName+' <= '+@OldOrderByValue + ' AND '+@FeildName+'>='+@NewOrderByValue+' ' "); strSql2.Append(" IF @Where <>'' "); strSql2.Append(" SET @strSQL =@strSQL + ' AND ' + @Where "); strSql2.Append(" print @strSQL "); strSql2.Append(" exec sp_executesql @strSQL "); strSql2.Append(" END "); SqlParameter[] parametersChangeOrder = { new SqlParameter("@OldOrderByValue", SqlDbType.NVarChar,10), new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10), new SqlParameter("@FeildName", SqlDbType.NVarChar,50), new SqlParameter("@TableName", SqlDbType.NVarChar,50), new SqlParameter("@Where", SqlDbType.NVarChar,500)}; parametersChangeOrder[0].Value = model.OldOrderByValue; parametersChangeOrder[1].Value = model.OrderByValue; parametersChangeOrder[2].Value = "OrderByValue"; parametersChangeOrder[3].Value = "OTB_SYS_Arguments"; parametersChangeOrder[4].Value = "ArgumentClassID='" + model.ArgumentClassID + "' AND ISNULL(DelStatus,'N') = 'N'"; CommandInfo o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
//o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
o_CommandInfo.CommandText = strSql2.ToString(); o_CommandInfo.Parameters = parametersChangeOrder; lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表新增，雖然參數名稱相同，但是經過new以後就是新的對象
//o_CommandInfo.CommandText = "OSP_OTB_SYS_Arguments_Update";
o_CommandInfo.CommandText = strSql.ToString(); o_CommandInfo.Parameters = parameters; lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0) { return true; } else { return false; } }
/// <summary>
/// 刪除一條資料
/// </summary>
public bool Delete(OT.Model.OTB_SYS_Arguments model) { int rowsAffected = 0; StringBuilder strSql = new StringBuilder(); strSql.Append(" DECLARE @OrderByValue INT "); strSql.Append(" SELECT @OrderByValue = OrderByValue "); strSql.Append(" FROM [OTB_SYS_Arguments] "); strSql.Append(" WHERE ArgumentID=@ArgumentID "); strSql.Append(" DELETE [OTB_SYS_Arguments] "); strSql.Append(" WHERE ArgumentClassID=@ArgumentClassID and ArgumentID=@ArgumentID "); SqlParameter[] parameters = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10), new SqlParameter("@ArgumentID", SqlDbType.VarChar,200) }; parameters[0].Value = model.ArgumentClassID; parameters[1].Value = model.ArgumentID;
List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); //定義事物執行的所有SQL
StringBuilder strSql2 = new StringBuilder(); strSql2.Append(" DECLARE @strSQL NVARCHAR(1000) "); strSql2.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue) "); strSql2.Append(" BEGIN "); //由小變大，OLD<-1<=NEW
strSql2.Append(" SET @strSQL=N'UPDATE '+@TableName+' "); strSql2.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1 "); strSql2.Append(" WHERE '+@FeildName+' >= '+@OldOrderByValue + ' AND '+@FeildName+'<='+@NewOrderByValue+' ' "); strSql2.Append(" IF @Where <>'' "); strSql2.Append(" SET @strSQL =@strSQL + ' AND ' + @Where "); strSql2.Append(" print @strSQL "); strSql2.Append(" exec sp_executesql @strSQL "); strSql2.Append(" END "); strSql2.Append(" ELSE "); strSql2.Append(" BEGIN "); //由大變小，@NewOrderByValue<=+1<@OldOrderByValue
strSql2.Append(" SET @strSQL=N'UPDATE '+@TableName+' "); strSql2.Append(" SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1 "); strSql2.Append(" WHERE '+@FeildName+' <= '+@OldOrderByValue + ' AND '+@FeildName+'>='+@NewOrderByValue+' ' "); strSql2.Append(" IF @Where <>'' "); strSql2.Append(" SET @strSQL =@strSQL + ' AND ' + @Where "); strSql2.Append(" print @strSQL "); strSql2.Append(" exec sp_executesql @strSQL "); strSql2.Append(" END "); SqlParameter[] parametersChangeOrder = { new SqlParameter("@OldOrderByValue", SqlDbType.NVarChar,10), new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10), new SqlParameter("@FeildName", SqlDbType.NVarChar,50), new SqlParameter("@TableName", SqlDbType.NVarChar,50), new SqlParameter("@Where", SqlDbType.NVarChar,500)}; parametersChangeOrder[0].Value = model.OldOrderByValue; parametersChangeOrder[1].Value = model.OrderByValue; parametersChangeOrder[2].Value = "OrderByValue"; parametersChangeOrder[3].Value = "OTB_SYS_Arguments"; parametersChangeOrder[4].Value = "ArgumentClassID='" + model.ArgumentClassID + "' AND ISNULL(DelStatus,'N') = 'N'"; CommandInfo o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表修改
//o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
o_CommandInfo.CommandText = strSql2.ToString(); o_CommandInfo.Parameters = parametersChangeOrder; lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
o_CommandInfo = new CommandInfo(); //定義每一個SQL，代表新增，雖然參數名稱相同，但是經過new以後就是新的對象
//o_CommandInfo.CommandText = "OSP_OTB_SYS_Arguments_Update";
o_CommandInfo.CommandText = strSql.ToString(); o_CommandInfo.Parameters = parameters; lstCommandInfo.Add(o_CommandInfo); //添加當前的SQL
//DbHelperSQL.RunProcedure("OSP_OTB_SYS_Arguments_Delete", parameters, out rowsAffected);
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 得到一個對象實體
/// </summary>
public OT.Model.OTB_SYS_Arguments GetModel(string ArgumentClassID, string ArgumentID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 ArgumentClassID,ArgumentID,ArgumentValue,MaxNumber,OrderByValue,DelStatus,Effective,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate from OTB_SYS_Arguments "); strSql.Append(" where ArgumentClassID=@ArgumentClassID and ArgumentID=@ArgumentID "); SqlParameter[] parameters = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10), new SqlParameter("@ArgumentID", SqlDbType.VarChar,200) }; parameters[0].Value = ArgumentClassID; parameters[1].Value = ArgumentID;
OT.Model.OTB_SYS_Arguments model = new OT.Model.OTB_SYS_Arguments(); //DataSet ds = DbHelperSQL.RunProcedure("OSP_OTB_SYS_Arguments_GetModel", parameters, "ds");
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["ArgumentClassID"] != null && ds.Tables[0].Rows[0]["ArgumentClassID"].ToString() != "") { model.ArgumentClassID = ds.Tables[0].Rows[0]["ArgumentClassID"].ToString(); } if (ds.Tables[0].Rows[0]["ArgumentID"] != null && ds.Tables[0].Rows[0]["ArgumentID"].ToString() != "") { model.ArgumentID = ds.Tables[0].Rows[0]["ArgumentID"].ToString(); } if (ds.Tables[0].Rows[0]["ArgumentValue"] != null && ds.Tables[0].Rows[0]["ArgumentValue"].ToString() != "") { model.ArgumentValue = ds.Tables[0].Rows[0]["ArgumentValue"].ToString(); } if (ds.Tables[0].Rows[0]["OrderByValue"] != null && ds.Tables[0].Rows[0]["OrderByValue"].ToString() != "") { model.OrderByValue = int.Parse(ds.Tables[0].Rows[0]["OrderByValue"].ToString()); } //if (ds.Tables[0].Rows[0]["LevelOfArgument"] != null && ds.Tables[0].Rows[0]["LevelOfArgument"].ToString() != "")
//{
// model.LevelOfArgument = int.Parse(ds.Tables[0].Rows[0]["LevelOfArgument"].ToString());
//}
//if (ds.Tables[0].Rows[0]["ParentArgument"] != null && ds.Tables[0].Rows[0]["ParentArgument"].ToString() != "")
//{
// model.ParentArgument = ds.Tables[0].Rows[0]["ParentArgument"].ToString();
//}
if (ds.Tables[0].Rows[0]["DelStatus"] != null && ds.Tables[0].Rows[0]["DelStatus"].ToString() != "") { model.DelStatus = ds.Tables[0].Rows[0]["DelStatus"].ToString(); } if (ds.Tables[0].Rows[0]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "") { model.Effective = ds.Tables[0].Rows[0]["Effective"].ToString(); } if (ds.Tables[0].Rows[0]["Memo"] != null && ds.Tables[0].Rows[0]["Memo"].ToString() != "") { model.Memo = ds.Tables[0].Rows[0]["Memo"].ToString(); } if (ds.Tables[0].Rows[0]["CreateUser"] != null && ds.Tables[0].Rows[0]["CreateUser"].ToString() != "") { model.CreateUser = ds.Tables[0].Rows[0]["CreateUser"].ToString(); } if (ds.Tables[0].Rows[0]["CreateDate"] != null && ds.Tables[0].Rows[0]["CreateDate"].ToString() != "") { model.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString()); } if (ds.Tables[0].Rows[0]["ModifyUser"] != null && ds.Tables[0].Rows[0]["ModifyUser"].ToString() != "") { model.ModifyUser = ds.Tables[0].Rows[0]["ModifyUser"].ToString(); } if (ds.Tables[0].Rows[0]["ModifyDate"] != null && ds.Tables[0].Rows[0]["ModifyDate"].ToString() != "") { model.ModifyDate = DateTime.Parse(ds.Tables[0].Rows[0]["ModifyDate"].ToString()); } return model; } else { return null; } }
/// <summary>
/// 得到一個對象實體
/// </summary>
public OT.Model.OTB_SYS_Arguments DataRowToModel(DataRow row) { OT.Model.OTB_SYS_Arguments model = new OT.Model.OTB_SYS_Arguments(); if (row != null) { if (row["ArgumentClassID"] != null) { model.ArgumentClassID = row["ArgumentClassID"].ToString(); } if (row["ArgumentID"] != null) { model.ArgumentID = row["ArgumentID"].ToString(); } if (row["ArgumentValue"] != null) { model.ArgumentValue = row["ArgumentValue"].ToString(); } if (row["MaxNumber"] != null && row["MaxNumber"].ToString() != "") { model.MaxNumber = int.Parse(row["MaxNumber"].ToString()); } if (row["OrderByValue"] != null && row["OrderByValue"].ToString() != "") { model.OrderByValue = int.Parse(row["OrderByValue"].ToString()); } if (row["DelStatus"] != null) { model.DelStatus = row["DelStatus"].ToString(); } if (row["Effective"] != null) { model.Effective = row["Effective"].ToString(); } if (row["Memo"] != null) { model.Memo = row["Memo"].ToString(); } if (row["CreateUser"] != null) { model.CreateUser = row["CreateUser"].ToString(); } if (row["CreateDate"] != null && row["CreateDate"].ToString() != "") { model.CreateDate = DateTime.Parse(row["CreateDate"].ToString()); } if (row["ModifyUser"] != null) { model.ModifyUser = row["ModifyUser"].ToString(); } if (row["ModifyDate"] != null && row["ModifyDate"].ToString() != "") { model.ModifyDate = DateTime.Parse(row["ModifyDate"].ToString()); } } return model; }
/// <summary>
/// 獲得資料列表
/// </summary>
public DataSet GetList(int StartRecordIndex, int EndRecordIndex, string ArgumentClassID, string ArgumentID, string ArgumentValue, string Effective, string SortExpression) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" RowId,ArgumentClassID,ArgumentClassName,ArgumentID,ArgumentValue,Effective,OrderByValue "); strSql.Append(" ,(SELECT COUNT(0) FROM OTB_SYS_Arguments WHERE ArgumentClassID=AA.ArgumentClassID) AS ClassCount "); strSql.Append(" FROM "); strSql.Append(" ( "); strSql.Append(" SELECT "); strSql.Append(" CASE @SortExpression WHEN N'ArgumentClassName' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ptm.ArgumentClassName)) "); strSql.Append(" WHEN N'ArgumentClassName DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ptm.ArgumentClassName DESC)) "); strSql.Append(" WHEN N'ArgumentID' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ArgumentID)) "); strSql.Append(" WHEN N'ArgumentID DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ArgumentID DESC)) "); strSql.Append(" WHEN N'ArgumentValue' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ArgumentValue)) "); strSql.Append(" WHEN N'ArgumentValue DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ArgumentValue DESC)) "); strSql.Append(" WHEN N'Effective' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY tm.Effective)) "); strSql.Append(" WHEN N'Effective DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY tm.Effective DESC)) "); strSql.Append(" WHEN N'OrderByValue' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY tm.OrderByValue)) "); strSql.Append(" WHEN N'OrderByValue DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY tm.OrderByValue DESC)) "); strSql.Append(" ELSE "); strSql.Append(" (ROW_NUMBER() OVER(Order BY tm.ArgumentClassID,tm.OrderByValue)) "); strSql.Append(" END AS RowId,ptm.ArgumentClassID,ptm.ArgumentClassName,ArgumentID "); strSql.Append(" ,ArgumentValue,tm.Effective,tm.OrderByValue "); strSql.Append(" FROM OTB_SYS_Argumentclass as ptm "); strSql.Append(" LEFT JOIN OTB_SYS_Arguments as tm on ptm.ArgumentClassID = tm.ArgumentClassID "); strSql.Append(" WHERE (ptm.ArgumentClassID = @ArgumentClassID OR @ArgumentClassID IS NULL OR @ArgumentClassID='') "); strSql.Append(" AND (ArgumentID LIKE @ArgumentID OR @ArgumentID IS NULL OR @ArgumentID='' OR @ArgumentID = '%%') "); strSql.Append(" AND (ArgumentValue LIKE @ArgumentValue OR @ArgumentValue IS NULL OR @ArgumentValue='' OR @ArgumentValue = '%%') "); strSql.Append(" AND (tm.Effective = @Effective OR @Effective IS NULL OR @Effective='') "); strSql.Append(" AND tm.[DelStatus] = 'N' "); strSql.Append(" AND ptm.[DelStatus] = 'N' "); strSql.Append(" ) AS AA "); strSql.Append(" WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex "); strSql.Append(" ORDER BY RowId "); SqlParameter[] parameters = { new SqlParameter("@StartRecordIndex", SqlDbType.Int) ,new SqlParameter("@EndRecordIndex", SqlDbType.Int) ,new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10) ,new SqlParameter("@ArgumentID", SqlDbType.NVarChar,20) ,new SqlParameter("@ArgumentValue", SqlDbType.NVarChar,200) ,new SqlParameter("@Effective", SqlDbType.Char,1) ,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500) }; parameters[0].Value = StartRecordIndex; parameters[1].Value = EndRecordIndex; parameters[2].Value = ArgumentClassID; parameters[3].Value = "%" + ArgumentID + "%"; parameters[4].Value = "%" + ArgumentValue + "%"; parameters[5].Value = Effective; parameters[6].Value = SortExpression; //return DbHelperSQL.RunProcedure("OSP_OTB_SYS_Arguments_GetListByIdEff", parameters, "ds");
return DbHelperSQL.Query(strSql.ToString(), parameters); }
/// <summary>
/// 獲得資料總筆數
/// </summary>
public int GetListCount(string ArgumentClassID, string ArgumentID, string ArgumentValue, string Effective) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" COUNT(0) "); strSql.Append(" FROM "); strSql.Append(" OTB_SYS_Argumentclass as ptm "); strSql.Append(" inner join OTB_SYS_Arguments as tm on ptm.ArgumentClassID = tm.ArgumentClassID "); strSql.Append(" WHERE (ptm.ArgumentClassID = @ArgumentClassID OR @ArgumentClassID IS NULL OR @ArgumentClassID='') "); strSql.Append(" AND (tm.ArgumentID LIKE @ArgumentID OR @ArgumentID IS NULL OR @ArgumentID='' OR @ArgumentID = '%%') "); strSql.Append(" AND (tm.ArgumentValue LIKE @ArgumentValue OR @ArgumentValue IS NULL OR @ArgumentValue='' OR @ArgumentValue = '%%') "); strSql.Append(" AND (tm.Effective = @Effective OR @Effective IS NULL OR @Effective='') "); strSql.Append(" AND tm.[DelStatus] = 'N' "); strSql.Append(" AND ptm.[DelStatus] = 'N' "); SqlParameter[] parameters = { new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10) ,new SqlParameter("@ArgumentID", SqlDbType.NVarChar,20) ,new SqlParameter("@ArgumentValue", SqlDbType.NVarChar,200) ,new SqlParameter("@Effective", SqlDbType.Char,1) }; parameters[0].Value = ArgumentClassID; parameters[1].Value = "%" + ArgumentID + "%"; parameters[2].Value = "%" + ArgumentValue + "%"; parameters[3].Value = Effective; //return (int)DbHelperSQL.GetSingle("OSP_OTB_SYS_Arguments_GetCount", parameters);
return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters); } #endregion Method
}}
|
