public int Update(SysEnumInfo model)
{
if (IsExist(model.EnumCode, model.ParentId, model.Id))
{
return(110);
}
StringBuilder sb = new StringBuilder(250);
sb.Append(@"update Sys_Enum set EnumCode = @EnumCode,EnumName = @EnumName,EnumValue = @EnumValue,ParentId = @ParentId,Sort = @Sort,Remark = @Remark
where Id = @Id
" );
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@EnumCode", SqlDbType.NVarChar, 50),
new SqlParameter("@EnumName", SqlDbType.NVarChar, 50),
new SqlParameter("@EnumValue", SqlDbType.NVarChar, 256),
new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Sort", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.NVarChar, 256)
};
parms[0].Value = model.Id;
parms[1].Value = model.EnumCode;
parms[2].Value = model.EnumName;
parms[3].Value = model.EnumValue;
parms[4].Value = model.ParentId;
parms[5].Value = model.Sort;
parms[6].Value = model.Remark;
return(SqlHelper.ExecuteNonQuery(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), parms));
}
public SysEnumInfo GetModel(string enumCode)
{
SysEnumInfo model = null;
string cmdText = @"select top 1 Id,EnumCode,EnumName,EnumValue,ParentId,Sort,Remark from [Sys_Enum] where EnumCode = @EnumCode ";
SqlParameter parm = new SqlParameter("@EnumCode", SqlDbType.VarChar, 50);
parm.Value = enumCode;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new SysEnumInfo();
model.Id = reader["Id"];
model.EnumCode = reader.GetString(1);
model.EnumName = reader.GetString(2);
model.EnumValue = reader.GetString(3);
model.ParentId = reader[4];
model.Sort = reader.GetInt32(5);
model.Remark = reader.GetString(6).Trim();
}
}
}
return(model);
}
public List <SysEnumInfo> GetList()
{
string cmdText = @"select Id,EnumCode,EnumName,EnumValue,ParentId,Sort,Remark
from [Sys_Enum] order by Sort ";
List <SysEnumInfo> list = new List <SysEnumInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GzxySiteDbConnString, CommandType.Text, cmdText))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SysEnumInfo model = new SysEnumInfo();
model.Id = reader["Id"];
model.EnumCode = reader.GetString(1);
model.EnumName = reader.GetString(2);
model.EnumValue = reader.GetString(3);
model.ParentId = reader[4];
model.Sort = reader.GetInt32(5);
model.Remark = reader.GetString(6).Trim();
list.Add(model);
}
}
}
return(list);
}
public int Insert(SysEnumInfo model)
{
if (IsExist(model.EnumCode, model.ParentId, null))
{
return(110);
}
StringBuilder sb = new StringBuilder(250);
sb.Append(@"insert into Sys_Enum (EnumCode,EnumName,EnumValue,ParentId,Sort,Remark)
values
(@EnumCode,@EnumName,@EnumValue,@ParentId,@Sort,@Remark)
" );
SqlParameter[] parms =
{
new SqlParameter("@EnumCode", SqlDbType.NVarChar, 50),
new SqlParameter("@EnumName", SqlDbType.NVarChar, 50),
new SqlParameter("@EnumValue", SqlDbType.NVarChar, 256),
new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Sort", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.NVarChar, 256)
};
parms[0].Value = model.EnumCode;
parms[1].Value = model.EnumName;
parms[2].Value = model.EnumValue;
parms[3].Value = model.ParentId;
parms[4].Value = model.Sort;
parms[5].Value = model.Remark;
return(SqlHelper.ExecuteNonQuery(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), parms));
}
public SysEnumInfo GetModel(object Id)
{
SysEnumInfo model = null;
StringBuilder sb = new StringBuilder(300);
sb.Append(@"select top 1 Id,EnumCode,EnumName,EnumValue,ParentId,Sort,Remark
from Sys_Enum
where Id = @Id " );
SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(Id.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GzxySiteDbConnString, CommandType.Text, sb.ToString(), parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new SysEnumInfo();
model.Id = reader.GetGuid(0);
model.EnumCode = reader.GetString(1);
model.EnumName = reader.GetString(2);
model.EnumValue = reader.GetString(3);
model.ParentId = reader.GetGuid(4);
model.Sort = reader.GetInt32(5);
model.Remark = reader.GetString(6);
}
}
}
return(model);
}
public int Update(SysEnumInfo model)
{
if (model == null)
{
return(-1);
}
if (IsExist(model.EnumName, model.ParentId, model.Id))
{
return(110);
}
string cmdText = @"update [Sys_Enum] set EnumCode = @EnumCode,EnumName = @EnumName,EnumValue = @EnumValue,ParentId = @ParentId,Sort = @Sort,Remark = @Remark
where Id = @Id";
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@EnumCode", SqlDbType.VarChar, 50),
new SqlParameter("@EnumName", SqlDbType.NVarChar, 256),
new SqlParameter("@EnumValue", SqlDbType.NVarChar, 256),
new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Sort", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.NVarChar, 256)
};
parms[0].Value = Guid.Parse(model.Id.ToString());
parms[1].Value = model.EnumCode;
parms[2].Value = model.EnumName;
parms[3].Value = model.EnumValue;
parms[4].Value = Guid.Parse(model.ParentId.ToString());
parms[5].Value = model.Sort;
parms[6].Value = model.Remark;
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
public string SaveSysEnum(SysEnumInfo sysEnumModel)
{
if (string.IsNullOrWhiteSpace(sysEnumModel.EnumName))
{
return(MessageContent.Submit_Params_InvalidError);
}
if (string.IsNullOrWhiteSpace(sysEnumModel.EnumCode))
{
return(MessageContent.Submit_Params_InvalidError);
}
if (string.IsNullOrWhiteSpace(sysEnumModel.EnumValue))
{
return(MessageContent.Submit_Params_InvalidError);
}
Guid gId = Guid.Empty;
Guid.TryParse(sysEnumModel.Id.ToString(), out gId);
sysEnumModel.Id = gId;
Guid parentId = Guid.Empty;
Guid.TryParse(sysEnumModel.ParentId.ToString(), out parentId);
sysEnumModel.ParentId = parentId;
SysEnum bll = new SysEnum();
int effect = -1;
if (!gId.Equals(Guid.Empty))
{
effect = bll.Update(sysEnumModel);
}
else
{
effect = bll.Insert(sysEnumModel);
}
if (effect == 110)
{
return(MessageContent.Submit_Exist);
}
if (effect > 0)
{
return("1");
}
else
{
return(MessageContent.Submit_Error);
}
}
/// <summary>
/// 获取数据分页列表，并返回所有记录数
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <param name="sqlWhere"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public List <SysEnumInfo> GetList(int pageIndex, int pageSize, out int totalCount, string sqlWhere, params SqlParameter[] cmdParms)
{
//获取数据集总数
string cmdText = "select count(*) from [Sys_Enum] t1 join dbo.Sys_Enum t2 on t1.ParentId = t2.Id ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
totalCount = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms);
//返回分页数据
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
cmdText = @"select * from(select row_number() over(order by t1.Sort) as RowNumber,t1.Id,t1.EnumCode,t1.EnumName,t1.EnumValue,t1.ParentId,t1.Sort,t1.Remark
from [Sys_Enum] t1 join dbo.Sys_Enum t2 on t1.ParentId = t2.Id ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <SysEnumInfo> list = new List <SysEnumInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SysEnumInfo model = new SysEnumInfo();
model.Id = reader["Id"];
model.EnumCode = reader.GetString(2);
model.EnumName = reader.GetString(3);
model.EnumValue = reader.GetString(4);
model.ParentId = reader[5];
model.Sort = reader.GetInt32(6);
model.Remark = reader.GetString(7).Trim();
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="sqlWhere"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public List <SysEnumInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select t1.Id,t1.EnumCode,t1.EnumName,t1.EnumValue,t1.ParentId,t1.Sort,t1.Remark
from [Sys_Enum] t1 join [Sys_Enum] t2 on t1.ParentId = t2.Id ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
cmdText += " order by Sort ";
List <SysEnumInfo> list = new List <SysEnumInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SysEnumInfo model = new SysEnumInfo();
model.Id = reader["Id"];
model.EnumCode = reader.GetString(1);
model.EnumName = reader.GetString(2);
model.EnumValue = reader.GetString(3);
model.ParentId = reader[4];
model.Sort = reader.GetInt32(5);
model.Remark = reader.GetString(6).Trim();
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(SysEnumInfo model)
{
if (model == null)
{
return(-1);
}
//判断当前记录是否存在，如果存在则返回;
if (IsExist(model.EnumName, model.ParentId, null))
{
return(110);
}
string cmdText = @"insert into [Sys_Enum] (EnumCode,EnumName,EnumValue,ParentId,Sort,Remark)
values (@EnumCode,@EnumName,@EnumValue,@ParentId,@Sort,@Remark)";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@EnumCode", SqlDbType.VarChar, 50),
new SqlParameter("@EnumName", SqlDbType.NVarChar, 256),
new SqlParameter("@EnumValue", SqlDbType.NVarChar, 256),
new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Sort", SqlDbType.Int),
new SqlParameter("@Remark", SqlDbType.NVarChar, 256)
};
parms[0].Value = model.EnumCode;
parms[1].Value = model.EnumName;
parms[2].Value = model.EnumValue;
parms[3].Value = Guid.Parse(model.ParentId.ToString());
parms[4].Value = model.Sort;
parms[5].Value = model.Remark;
//执行数据库操作
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(SysEnumInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(SysEnumInfo model)
{
return(dal.Insert(model));
}
public string SaveSysEnum(SysEnumInfo model)
{
if (string.IsNullOrWhiteSpace(model.EnumName))
{
return(MC.Submit_Params_InvalidError);
}
if (string.IsNullOrWhiteSpace(model.EnumCode))
{
return(MC.Submit_Params_InvalidError);
}
if (string.IsNullOrWhiteSpace(model.EnumValue))
{
return(MC.Submit_Params_InvalidError);
}
Guid gId = Guid.Empty;
Guid.TryParse(model.Id.ToString(), out gId);
model.Id = gId;
Guid parentId = Guid.Empty;
Guid.TryParse(model.ParentId.ToString(), out parentId);
model.ParentId = parentId;
SysEnum bll = new SysEnum();
int effect = -1;
if (!gId.Equals(Guid.Empty))
{
effect = bll.Update(model);
}
else
{
using (TransactionScope scope = new TransactionScope())
{
using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Suppress))
{
if (bll.IsExist(model.EnumCode, model.ParentId, model.Id))
{
effect = 110;
}
else
{
effect = bll.Insert(model);
}
scope2.Complete();
}
scope.Complete();
}
}
if (effect == 110)
{
return(MC.Submit_Exist);
}
if (effect > 0)
{
return("1");
}
else
{
return(MC.Submit_Error);
}
}