void WriteSendHistory(AppEmailData email, Guid emailId, SqlCmdHelper.SqlRuleMapResult mapSql)
{
AppEmailReceiver send = new AppEmailReceiver()
{
PrimaryMsgId = emailId,
CreateTime = DateTime.Now,
IsDelete = false,
SendResult = false,
SendTo = email.To,
IsMailer = false
};
send.SendTime = email.SendTime.HasValue ? email.SendTime.Value : DateTime.Now;
send.DayInt = int.Parse(send.CreateTime.ToString("yyyyMMdd"));
send.Id = Guid.NewGuid();
SqlCmdHelper help = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
help.InsertSqlParam(send.GetInsertSql(), send, mapSql);
if (email.Mailer != null)
{ //抄送人
send.IsMailer = true;
for (int i = 0; i < email.Mailer.Count; i++)
{
send.Id = Guid.NewGuid();
send.SendTo = email.Mailer[i];
help.InsertSqlParam(send.GetInsertSql(), send, mapSql);
}
}
}
public List <Menu> QueryMenus()
{
Menu menu = new Menu();
string cmd = SqlCmdHelper.GenerateSampleSelectSql <Menu>();// menu.QueryMenus();
return(CommonRepository.QueryModelList <Menu>(cmd, null, SqlConnString, 0, int.MaxValue));
}
public bool Add(CategoryItems entity)
{
//拼接SQL语句
string cmd = entity.GetInsertSql();
//读取属性名
Dictionary <string, object> properties = entity.GetAllPorpertiesNameAndValues();
List <SqlParameter> ps = new List <SqlParameter>();
foreach (KeyValuePair <string, object> item in properties)
{
string paramName = "@" + item.Key;
string field = "{" + item.Key + "}";
if (cmd.Contains(field))
{
cmd = cmd.Replace(field, paramName);
//获取参数的数据类型
SqlParameter p = new SqlParameter(paramName, item.Value == null?DBNull.Value:item.Value);
ps.Add(p);
}
}
SqlCmdHelper helper = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(helper.ExcuteNoQuery(cmd, ps.ToArray()) > 0);
}
public static List <T> QueryModelList <T>(string sql, SqlParameter[] param, string sqlConnString, int beginRow, int endRow) where T : class
{
DataSet ds = new SqlCmdHelper()
{
Timeout = 60
}.QueryDataSet(sql, sqlConnString, param, beginRow, endRow, typeof(T).Name);
return(DataHelp.DataReflection.DataSetConvert <T>(ds));
}
public bool Add(ActiveData entity)
{
SqlCmdHelper cmd = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(cmd.ExcuteInsert(entity) > 0);
}
public List <ReserveEmailAccount> QueryEmailAccounts()
{
string sql = new ReserveEmailAccount().GetSelectAllSql();
SqlCmdHelper help = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(CommonRepository.QueryModelList <ReserveEmailAccount>(sql, null, SqlConnString, 0, int.MaxValue));
}
public bool Edit(CategoryItems entity)
{
string sql = entity.GetUpdateSql();
SqlCmdHelper helper = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(helper.GenerateNoQuerySqlAndExcute(sql, entity) > 0);
}
public bool Add(Model.Menu entity)
{
Menu m = new Menu()
{
IsEnable = true
};
string insert = SqlCmdHelper.GenerateInsertSql <Menu>();// m.InserSql();
return(CommonRepository.ExtInsert <Menu>(insert, SqlConnString, entity));
}
public int AddReturnId(EnumData data)
{
string sql = SqlCmdHelper.GenerateInsertSql <EnumData>();
object obj = SqlCmdHelper.ExtInsertReturnGenerateId(sql, SqlConnString, data);
if (obj == null)
{
return(-1);
}
return(int.Parse(obj.ToString()));//此处 (int)obj 进行强制类型转换会出现异常，因为数据库中查询出的数据没有定义数据类型
}
/// <summary>
/// 添加
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Add(Organze entity)
{
string sqlFormat = SqlCmdHelper.GenerateInsertSql <Organze>();
SqlCmdHelper help = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
SqlCmdHelper.SqlRuleMapResult rule = new SqlCmdHelper.SqlRuleMapResult();
help.InsertSqlParam(sqlFormat, entity, rule);
return(help.ExcuteNoQuery(string.Join(";", rule.WaitExcuteSql), rule.SqlParams.ToArray()) > 0);
}
public List <CategoryItems> QueryNodesByIndex(string keySpell)
{
CategoryItems item = new CategoryItems()
{
IndexSpell = "%" + keySpell + "%"
};
DataSet ds = new SqlCmdHelper()
{
SqlConnString = SqlConnString
}.GenerateQuerySqlAndExcute(item.GetQueryByIndexSpell(), item);
return(DataHelp.DataReflection.DataSetConvert <CategoryItems>(ds));
}
public static List <R> QueryModels <R, T>(string sql, T entity, string sqlConnString, Dictionary <ParameterDirection, string[]> paramDirection = null) where T : class where R : class
{
SqlCmdHelper help = new SqlCmdHelper()
{
SqlConnString = sqlConnString
};
DataSet ds = help.GenerateQuerySqlAndExcute <T>(sql, entity, paramDirection);
if (ds == null)
{
}
return(DataHelp.DataReflection.ConvertModels <R>(ds));
}
public List <EnumData> QueryEnumMember(string enumName, bool isContainerDelete)
{
Dictionary <string, string> columns = SqlCmdHelper.GenerateColumnMapPropertyDict <EnumData>();
string[] col = columns.Keys.ToArray();
EnumData ed = new EnumData()
{
Code = enumName
};
string sql = ed.QueryEnumMembersSqlFormat(col, isContainerDelete);
return(CommonRepository.QueryModels <EnumData, EnumData>(sql, ed, SqlConnString));
}
public bool ChangeParentMenu(int id, int parentId)
{
Menu m = new Menu()
{
Id = id, ParentId = parentId
};
string sql = m.ChangeParentMenuSql();
SqlCmdHelper cmd = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(cmd.GenerateNoQuerySqlAndExcute(sql, m) > 0);
}
public bool ChangeSpell(int id, string spell)
{
CategoryItems item = new CategoryItems()
{
Id = id, IndexSpell = spell
};
string sql = item.GetChangeSpellWord();
SqlCmdHelper helper = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(helper.GenerateNoQuerySqlAndExcute(sql, item) > 0);
}
/// <summary>
/// 设置是否启用菜单
/// </summary>
/// <param name="id"></param>
/// <param name="operate"></param>
/// <returns></returns>
public bool ChangeMenuStatue(int id, bool operate)
{
Menu menu = new Menu()
{
Id = id,
IsEnable = operate
};
string sql = menu.ChangeStatueSql();
SqlCmdHelper cmd = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(cmd.GenerateNoQuerySqlAndExcute(sql, menu) > 0);
}
/// <summary>
/// 设置更改菜单类型
/// </summary>
/// <param name="id"></param>
/// <param name="type"></param>
/// <returns></returns>
public bool ChangeMenuType(int id, int type)
{
Menu menu = new Menu()
{
Id = id,
MenuType = (short)type
};
string sql = menu.ChangeMenuTypeSql();
SqlCmdHelper cmd = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(cmd.GenerateNoQuerySqlAndExcute(sql, menu) > 0);
}
public bool SaveSignInInfo(UserAccount user)
{//保存数据
if (string.IsNullOrEmpty(user.Encry))
{
user.Encry = "-1";
}
user.Id = Guid.NewGuid();
user.CreateTime = DateTime.Now;
SqlCmdHelper help = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
string sql = SqlCmdHelper.GenerateInsertSql <UserAccount>();
return(help.GenerateNoQuerySqlAndExcute(sql, user) > 0);
}
public static int ExecuteCount(string sql, SqlParameter[] param, string sqlConnString)
{
SqlDataReader read = new SqlCmdHelper().ExcuteQuery(sql, sqlConnString, param);
if (read.HasRows)
{
read.Read();
object obj = read[0];
int count = -1;
if (obj != null && int.TryParse(obj.ToString(), out count))
{
return(count);
}
return(-1);
}
read.Close();
return(-1);
}
public static int RunProcedureNoQuery <T>(string procdureCmd, string connString, T entity,
string outPutName, SqlDbType outPutType, out object procedureOut) where T : class
{
SqlExcuteParam pms = BuilderSqlParamter(procdureCmd, entity);
pms.Param.Add(new SqlParameter()
{
ParameterName = outPutName, SqlDbType = outPutType, Direction = ParameterDirection.Output
});
SqlCmdHelper helper = new SqlCmdHelper()
{
SqlConnString = connString
};
int result = helper.RunProcedureNoQuery(pms.Cmd, pms.Param.ToArray());
procedureOut = pms.Param[pms.Param.Count - 1].Value;
return(result);
}
/// <summary>
/// 批量添加
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">单挑新增的SQL脚本</param>
/// <param name="connString">数据库连接串</param>
/// <param name="entitys"></param>
/// <returns>批量执行非查询的数目</returns>
public static int ExtBatchInsert <T>(string sql, string connString, List <T> entitys) where T : class
{
if (entitys.Count == 0)
{
}
string[] properties = entitys[0].GetAllProperties();
List <string> pis = new List <string>();
foreach (string item in properties)
{
string field = "{" + item + "}";
if (sql.Contains(field))
{
pis.Add(item);
}
}
List <SqlParameter> ps = new List <SqlParameter>();
List <string> batchSql = new List <string>();
for (int i = 0; i < entitys.Count; i++)
{
T item = entitys[i];
string cmd = sql;
foreach (var pi in pis)
{
string paramName = "@" + pi + i;
string field = "{" + pi + "}";
cmd = cmd.Replace(field, paramName);
bool exists = false;
object obj = item.GetPropertyValue(pi, out exists);
//获取参数的数据类型
SqlParameter p = new SqlParameter(paramName, obj == null ? DBNull.Value : obj);
ps.Add(p);
}
batchSql.Add(cmd);
}
SqlCmdHelper helper = new SqlCmdHelper()
{
SqlConnString = connString
};
return(helper.ExcuteNoQuery(string.Join(";", batchSql), ps.ToArray()));
}
public CategoryItems Get(object key)
{
int id = Convert.ToInt32(key);
CategoryItems item = new CategoryItems()
{
Id = id
};
string sql = item.GetFirstOneSql();
DataSet ds = new SqlCmdHelper()
{
SqlConnString = SqlConnString
}.GenerateQuerySqlAndExcute(sql, item);
List <CategoryItems> data = DataHelp.DataReflection.DataSetConvert <CategoryItems>(ds);
if (data == null || data.Count == 0)
{
return(null);
}
return(data[0]);
}
public List <Organze> QueryOrganzes(RequestParam param)
{
string sql = SqlCmdHelper.GenerateSampleSelectSql <Organze>() + " where 1=1";
List <SqlParameter> sqlParam = new List <SqlParameter>();
if (!string.IsNullOrEmpty(param.QueryKey))
{
sql += "and Code like @key+'%' or Name like @key+'%'";
sqlParam.Add(new SqlParameter()
{
ParameterName = "@key", Value = param.QueryKey
});
}
SqlCmdHelper help = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(CommonRepository.QueryModelList <Organze>(sql, sqlParam.ToArray(), SqlConnString, param.RowBeginIndex, param.RowEndIndex));
}
public int Count(RequestParam param)
{
string sql = SqlCmdHelper.GenerateCountSql <Organze>();
List <SqlParameter> sqlParam = new List <SqlParameter>();
if (!string.IsNullOrEmpty(param.QueryKey))
{
sql += "and Code like @key+'%' or Name like @key+'%'";
sqlParam.Add(new SqlParameter()
{
ParameterName = "@key", Value = param.QueryKey
});
}
SqlCmdHelper help = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
return(CommonRepository.ExecuteCount(sql, sqlParam.ToArray(), SqlConnString));
}
public static void BulkSave <T>(string table, string connString, List <T> rows) where T : class
{
T def = System.Activator.CreateInstance <T>();
string[] pnames = def.GetAllProperties();
DataTable dt = new DataTable();
string specialType = typeof(bool).Name;
foreach (var item in pnames)
{
//获取属性的数据类型
Type pt = def.GetPropertyType(item);
dt.Columns.Add(new DataColumn(item, pt.Name == specialType?typeof(int):pt));
}
foreach (T item in rows)
{
DataRow row = dt.NewRow();
foreach (var column in pnames)
{
bool hasProperty = false;
Type pt = item.GetPropertyType(column);
object value = item.GetPropertyValue(column, out hasProperty);
if (pt.Name == specialType)
{
row[column] = ((bool)value ? 1 : 0);
}
else
{
row[column] = value;
}
}
dt.Rows.Add(row);
}
SqlCmdHelper helper = new SqlCmdHelper()
{
SqlConnString = connString
};
helper.BulkSave(dt, table);
}
public void Add(T entity, string xmlFile, string sqlNodeKeyName, string sqlNodeValueName, string nodeSqlKey)
{
//读取xml中节点数据
string sql = GetXml(xmlFile, sqlNodeKeyName, sqlNodeValueName, nodeSqlKey + "/" + typeof(T).Name);
if (string.IsNullOrEmpty(sql))
{ //没有找到节点
}
//是否存在指定格式的属性字段
string[] properties = entity.GetEntityProperties();//获取的指定属性
List <SqlParameter> ps = new List <SqlParameter>();
foreach (string item in properties)
{
if (!sql.Contains("{" + item + "}"))
{
continue;
}
string value = entity.GetPropertyValue(item);//需要判断该字段的数据类型在进行赋值
//数字或者bit类型才不需要增加 '' 组装值
Type t = entity.GetPropertyType(item);
value = string.IsNullOrEmpty(value) ? null : value;
string pname = "@" + item;
if (t.Name != typeof(int).Name && t.Name != typeof(bool).Name)
{
sql = sql.Replace("'{" + item + "}'", pname);
}
else
{
sql = sql.Replace("{" + item + "}", pname);
}
ps.Add(new SqlParameter(pname, value));
}
SqlCmdHelper helper = new SqlCmdHelper();
helper.SqlConnString = SqlConnectionString;
int row = helper.ExcuteNoQuery(sql, ps.ToArray());
}
public bool WriteLog(ELogType type, string log, string title, bool isNormalLog)
{
DateTime appTime = Domain.GlobalModel.AppRunData.RunTime;//程序启动时的时间
LogData data = new LogData()
{
CreateTime = DateTime.Now,
Category = (short)type.GetHashCode(),
Id = Guid.NewGuid(),
IsError = !isNormalLog,
Note = log,
Title = title
};
data.DayInt = int.Parse(data.CreateTime.ToString(CommonFormat.DateIntFormat));
SqlCmdHelper cmd = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
string sql = SqlCmdHelper.GenerateInsertSql <LogData>();
return(CommonRepository.ExtInsert(sql, SqlConnString, data));
// throw new NotImplementedException();
}
/// <summary>
/// 查询存储过程，获取数据结构
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="storedProcedure"></param>
/// <param name="sqlConnString"></param>
/// <param name="param"></param>
/// <returns></returns>
public static List <T> QueryProcedure <T>(string storedProcedure, string sqlConnString, SqlParameter[] param) where T : class
{
DataSet ds = new SqlCmdHelper().ExcuteQuerySP(storedProcedure, sqlConnString, null, null, typeof(T).Name, param);
return(DataHelp.DataReflection.DataSetConvert <T>(ds));
}
public bool SaveWaitSendEmailData(AppEmailData email)
{
DateTime now = DateTime.Now;
SqlCmdHelper.SqlRuleMapResult mapSql = new SqlCmdHelper.SqlRuleMapResult();
//数据存储到数据表
AppEmail ae = new AppEmail()
{
Body = email.Body,
Subject = email.Subject,
CreateTime = now,
IsDelete = false,
ParentId = new Guid(),
SendBy = email.From,
BodyType = (short)email.BodyType.GetHashCode()
};
if (email.EmailId.Equals(new Guid()))
{
ae.Id = Guid.NewGuid();
}
else
{
ae.Id = email.EmailId;
}
SqlCmdHelper help = new SqlCmdHelper()
{
SqlConnString = SqlConnString
};
help.InsertSqlParam(ae.GetInsertSql(), ae, mapSql);
if (!email.SendTime.HasValue)
{//这条邮件数据是此刻进行发送
email.SendTime = now;
}
//进行定时计划存储
AppEmailPlan plan = new AppEmailPlan()
{
CreateTime = now,
Id = Guid.NewGuid(),
PrimaryMsgId = ae.Id,
SendNumber = 0,
SendTime = email.SendTime.Value
};
if (plan.SendTime <= now)
{//已发送
plan.SendNumber = 1;
}
help.InsertSqlParam(plan.GetInsertSql(), plan, mapSql);
AppEmailReceiverPlan emailTo = new AppEmailReceiverPlan()
{
CreateTime = now,
Id = Guid.NewGuid(),
IsMailer = false,
PrimaryMsgId = ae.Id,
SendTo = email.To
};
help.InsertSqlParam(emailTo.GetInsertSql(), emailTo, mapSql);
List <AppEmailReceiverPlan> emailToColl = new List <AppEmailReceiverPlan>();
emailToColl.Add(emailTo);
if (email.Mailer != null)
{
foreach (var item in email.Mailer)
{//抄送人
AppEmailReceiverPlan emailers = new AppEmailReceiverPlan()
{
IsMailer = true,
SendTo = item,
PrimaryMsgId = ae.Id,
Id = Guid.NewGuid(),
CreateTime = now
};
help.InsertSqlParam(emailers.GetInsertSql(), emailers, mapSql);
//emailToColl.Add(emailers);
}
}
if (!string.IsNullOrEmpty(string.Join("", mapSql.NoMapRule)))
{
//日志输出没有匹配的规则
return(false);
}
string sql = string.Join(";", mapSql.WaitExcuteSql);
return(help.ExcuteNoQuery(sql, mapSql.SqlParams.ToArray()) == mapSql.WaitExcuteSql.Count);
}
/// <summary>
/// 不含有密码
/// </summary>
/// <param name="param"></param>
/// <param name="canNoWhereQuery">当使用无条件查找时忽略查找条件中非行位置条件</param>
/// <returns></returns>
public List <UserAccount> QueryUses(RequestParam param, bool canNoWhereQuery)
{
string select = SqlCmdHelper.GenerateSampleSelectSql <UserAccount>();
bool valid = false;
List <string> where = new List <string>();
List <SqlParameter> ps = new List <SqlParameter>();
#region 用户名只允许右匹配
if (!string.IsNullOrEmpty(param.QueryKey))
{//不允许前置匹配
string[] arr = param.QueryKey.Split('%');
if (!string.IsNullOrEmpty(arr[0]))
{
where.Add("UserName like @QueryKey ");
ps.Add(new SqlParameter()
{
ParameterName = "@QueryKey", Value = param.QueryKey + "%"
});
}
else
{
valid = true;
}
}
if (valid)
{
return(null);
}
#endregion
#region 创建时间检索
if (!string.IsNullOrEmpty(param.BeginTime) && !string.IsNullOrEmpty(param.EndTime))
{
where.Add("between CreateTime @BeginTime and @EndTime");
ps.Add(new SqlParameter()
{
ParameterName = "@BeginTime", Value = param.BeginTime
});
ps.Add(new SqlParameter()
{
ParameterName = "@EndTime", Value = param.EndTime
});
}
else if (!string.IsNullOrEmpty(param.BeginTime))
{
where.Add("CreateTime>= @BeginTime ");
ps.Add(new SqlParameter()
{
ParameterName = "@BeginTime", Value = param.BeginTime
});
}
else if (!string.IsNullOrEmpty(param.EndTime))
{
where.Add("CreateTime<= @EndTime ");
ps.Add(new SqlParameter()
{
ParameterName = "@EndTime", Value = param.EndTime + "%"
});
}
#endregion
#region 昵称
if (!string.IsNullOrEmpty(param.Name))
{
where.Add("Nick like @Name");
ps.Add(new SqlParameter()
{
ParameterName = "@Name", Value = "%" + param.Name + "%"
});
}
#endregion
if (!canNoWhereQuery && where.Count == 0)
{//没有提供检索条件
return(null);
}
else
{
where = new List <string>();
//限制查询数目
if (param.RowEndIndex - param.RowBeginIndex > 30)
{
param.RowEndIndex = param.RowBeginIndex + 30;
}
}
string sql = select + (where.Count > 0 ? " where " + string.Join(" and ", where) : string.Empty) + " order by CreateTime desc";
SqlCmdHelper cmd = new SqlCmdHelper();
DataSet ds = cmd.QueryDataSet(sql, SqlConnString, ps.ToArray(), param.RowBeginIndex, param.RowEndIndex, typeof(UserAccount).Name);//限定数量
return(DataHelp.DataReflection.DataSetConvert <UserAccount>(ds));
}
