public static ModelResult <DTO.Account.Constant> ConstantUpdate(DTO.Account.Constant oConstant)
{
var oResult = new ModelResult <DTO.Account.Constant>();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.CommandText = "SP_ConstantUpdate";
if (oConstant.Id > 0)
{
cmd.Parameters.AddWithValue("@Id", oConstant.Id);
}
if (oConstant.ParentId >= 0)
{
cmd.Parameters.AddWithValue("@ParentId", oConstant.ParentId);
}
if (!string.IsNullOrEmpty(oConstant.Icon))
{
cmd.Parameters.AddWithValue("@Icon", oConstant.Icon);
}
if (!string.IsNullOrEmpty(oConstant.Name))
{
cmd.Parameters.AddWithValue("@Name", oConstant.Name);
}
if (!string.IsNullOrEmpty(oConstant.Comment))
{
cmd.Parameters.AddWithValue("@Comment", oConstant.Comment);
}
conn.Open();
oConstant.Id = Convert.ToInt32(cmd.ExecuteScalar());
oResult.HasResult = true;
oResult.Results = oConstant;
}
}
}
catch (Exception ex)
{
oResult.Message = ex.Message;
oResult.HasResult = false;
}
finally
{
conn.Close();
}
return(oResult);
}
public static ModelResult <DTO.Account.Constant> DeleteConstant(DTO.Account.Constant oConstant)
{
var oResult = new ModelResult <DTO.Account.Constant>();
using (var conn = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SP_ConstantDelete";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", oConstant.Id);
conn.Open();
oConstant.Id = Convert.ToInt32(cmd.ExecuteNonQuery());
oResult.HasResult = true;
oResult.Results = oConstant;
}
return(oResult);
}
}
public static ModelResult <List <DTO.News.AppSettings> > AppSettingsGet(int key)
{
var oResult = new ModelResult <List <DTO.News.AppSettings> >();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
#region SQLCOMMAND Builder
var command = @"SELECT TBL1.*,TBL2.Name KeyName,TBL3.Name ValueName FROM AppSettings TBL1 JOIN Constant TBL2 ON TBL1.ConKey = TBL2.Id JOIN Constant TBL3 ON TBL1.ConValue = TBL3.Id WHERE 1=1 ";
if (key > 0)
{
command += " AND TBL1.ConKey = @Key ";
cmd.Parameters.AddWithValue("@Key", key);
}
#endregion
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.CommandText = command;
SqlDataReader reader = cmd.ExecuteReader();
var lstAppSettings = new List <DTO.News.AppSettings>();
if (reader.HasRows)
{
while (reader.Read())
{
var obAppSettings = new DTO.News.AppSettings();
obAppSettings.ConKey = Convert.ToInt32(reader["ConKey"]);
obAppSettings.ConValue = Convert.ToInt32(reader["ConValue"]);
var obKey = new DTO.Account.Constant();
obKey.Name = Convert.ToString(reader["KeyName"]);
obAppSettings.OKey = obKey;
var obValue = new DTO.Account.Constant();
obValue.Name = Convert.ToString(reader["ValueName"]);
obAppSettings.OValue = obValue;
lstAppSettings.Add(obAppSettings);
}
}
if (lstAppSettings.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lstAppSettings;
oResult.RowCount = lstAppSettings.Count;
}
}
}
}
finally
{
conn.Close();
}
return(oResult);
}
public static ModelResult <List <DTO.Account.Constant> > ConstantGet(DTO.Account.Constant oConstant)
{
var oResult = new ModelResult <List <DTO.Account.Constant> >();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
#region SQLCOMMAND Builder
var command = "";
command = @"Select TBL1.* ,
TBL2.Name Parm1, TBL2.Comment Parm2
FROM Constant TBL1
LEFT JOIN Constant TBL2 ON (TBL1.ParentId = TBL2.Id)
WHERE 1=1 AND TBL1.IsDeleted = 0";
if (oConstant.Id > 0)
{
command += " AND TBL1.Id = @Id";
cmd.Parameters.AddWithValue("@Id", oConstant.Id);
}
if (oConstant.ParentId >= 0)
{
command += " And TBL1.ParentId =@ParentId ";
cmd.Parameters.AddWithValue("@ParentId", oConstant.ParentId);
}
if (!string.IsNullOrEmpty(oConstant.Name))
{
command += " And TBL1.Name Like @Name ";
cmd.Parameters.AddWithValue("@Name", "%" + oConstant.Name + "%");
}
if (!oConstant.IsList)
{
command += " order by @SortCol @SortType OFFSET(@Page - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY";
command = command.Replace("@SortCol", oConstant.SortCol);
command = command.Replace("@SortType", oConstant.SortType);
command = command.Replace("@Page", oConstant.Page.ToString());
command = command.Replace("@RowsPerPage", oConstant.RowPerPage.ToString());
}
#endregion
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.CommandText = command;
SqlDataReader reader = cmd.ExecuteReader();
var lstConstant = new List <DTO.Account.Constant>();
if (reader.HasRows)
{
while (reader.Read())
{
#region Constant Parms
var obConstant = new DTO.Account.Constant();
if (reader["Name"] != DBNull.Value)
{
obConstant.Name = Convert.ToString(reader["Name"]);
}
if (reader["Comment"] != DBNull.Value)
{
obConstant.Comment = Convert.ToString(reader["Comment"]);
}
#region Parent Constant Content Parms
var obParent = new DTO.Account.Constant();
if (reader["Parm1"] != DBNull.Value)
{
obParent.Name = Convert.ToString(reader["Parm1"]);
}
obConstant.OParent = obParent;
#endregion
obConstant.Id = Convert.ToInt32(reader["Id"]);
if (reader["ParentId"] != DBNull.Value)
{
obConstant.ParentId = Convert.ToInt32(reader["ParentId"]);
}
if (reader["Icon"] != DBNull.Value)
{
obConstant.Icon = Convert.ToString(reader["Icon"]);
}
if (reader["IsDeleted"] != DBNull.Value)
{
obConstant.IsDeleted = Convert.ToBoolean(reader["IsDeleted"]);
}
#endregion
lstConstant.Add(obConstant);
}
}
int count = 0;
if (!oConstant.IsList)
{
using (SqlConnection connCount = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmdCount = new SqlCommand())
{
cmdCount.Connection = connCount;
command = @"SELECT COUNT(1) FROM Constant WHERE 1=1 AND IsDeleted = 0";
if (oConstant.Id > 0)
{
command += " AND Id = @Id";
cmdCount.Parameters.AddWithValue("@Id", oConstant.Id);
}
if (oConstant.ParentId >= 0)
{
command += " And ParentId =@ParentId ";
cmdCount.Parameters.AddWithValue("@ParentId", oConstant.ParentId);
}
cmdCount.CommandText = command;
if (connCount.State != ConnectionState.Open)
{
connCount.Open();
}
count = Convert.ToInt32(cmdCount.ExecuteScalar());
connCount.Close();
}
}
}
if (lstConstant.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lstConstant;
oResult.RowCount = count;
}
}
}
}
catch (Exception ex)
{
oResult.Message = ex.Message;
oResult.HasResult = false;
}
finally
{
conn.Close();
}
return(oResult);
}
