public readonly string ColName_Field_Title = defDAL.ColName_Field_Title; /* 字段中文标题 */
/// <summary>
/// 获得字段
/// </summary>
/// <param name="field_name"></param>
/// <param name="site_id"></param>
/// <param name="channel_name"></param>
/// <param name="channel_id"></param>
/// <returns></returns>
public Model.dv_SiteChannelFieldInfo[] GetFields(string field_name, int site_id = 0, string channel_name = "", int channel_id = 0)
{
List <string> wheres = new List <string>();
wheres.Add(CreateSQL.WhereEqual(ColName_Field_Name, field_name));
if (site_id > 0)
{
wheres.Add(CreateSQL.WhereEqual(ColName_Channel_SiteID, site_id.ToString()));
}
if (!CheckData.IsStringNull(channel_name))
{
wheres.Add(CreateSQL.WhereEqual(ColName_Channel_Name, channel_name.ToString()));
}
if (channel_id > 0)
{
wheres.Add(CreateSQL.WhereEqual(ColName_ID_ChannelInfo, channel_id.ToString()));
}
string sqlwhere = ConvertTool.ToString(wheres, CreateSQL.WHERE_AND);
DataTable dt = base.GetList(0, sqlwhere, string.Empty).Tables[0];
if (CheckData.IsSizeEmpty(dt))
{
return(new Model.dv_SiteChannelFieldInfo[] { });
}
return(base.GetModelList(dt).ToArray());
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="sid">ID条件</param>
/// <returns>是否成功</returns>
public virtual bool IDDelete(string sid)
{
if (sid == AbsTable_StringID.ERROR_DEFAULT_SID_VALUE)
{
return(true); // 没有错误数据不用删除
}
return(Delete(CreateSQL.WhereEqual(ColName_SID, sid.ToString())));
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="id">ID条件</param>
/// <returns>是否成功</returns>
public virtual bool IDDelete(int id)
{
if (id <= AbsTable_IntID.ERROR_DEFAULT_INT_VALUE)
{
return(true); // 没有错误数据不用删除
}
return(Delete(CreateSQL.WhereEqual(ColName_IID, id.ToString())));
}
/// <summary>
/// 查询
/// </summary>
/// <param name="pageCount">每页展现记录数</param>
/// <param name="pageIndex">浏览页面索引</param>
/// <param name="recordCount">查询结果总记录数</param>
/// <param name="where">查询条件</param>
/// <param name="sorts">结果排序键值集合</param>
/// <returns>数据映射模型集合结果</returns>
public override M[] Select(int pageCount, int pageIndex, out int recordCount, string where, KeyBoolean[] sorts)
{
if (CheckData.IsSizeEmpty(sorts))
{
sorts = GetDefaultSortWhere();
}
string sort_order = CreateSQL.OrderBySimp(sorts);
DataSet ds = QueryRecords(pageCount, pageIndex, out recordCount, where, sort_order);
return(DataSetToModels(ds));
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="top">返回的记录数</param>
/// <param name="sql_where">查询条件</param>
/// <param name="sql_order">排序条件</param>
/// <returns>结果数据表</returns>
public DataSet QueryRecords(int top, string sql_where, string sql_order)
{
string sql_select = CreateSQL.Select(this.GetTableName(), top, sql_where, sql_order);
if (CheckData.IsStringNull(sql_select))
{
return(new DataSet());
}
DataSet ds = DbHelperSQL.Query(sql_select);
return(ds);
}
/// <summary>
/// 获取记录数量
/// </summary>
/// <param name="where">查询条件</param>
/// <returns>记录总数</returns>
public override int GetRecordCount(string where)
{
const int errorint = 0;
string sql_select = CreateSQL.Select_Count(this.GetTableName(), where);
if (CheckData.IsStringNull(sql_select))
{
return(errorint);
}
object value = DbHelperSQL.GetSingle(sql_select);
return(ConvertTool.ToInt(value, errorint));
}
/// <summary>
/// 获得IP重复数量
/// </summary>
/// <param name="ip"></param>
/// <param name="time_where"></param>
/// <param name="minute_interval"></param>
/// <returns></returns>
public int GetIPRepeatCount(string ip, DateTime time_where, int minute_interval = 15)
{
if (CheckData.IsStringNull(ip))
{
return(0);
}
List <string> wheres = new List <string>();
wheres.Add(CreateSQL.WhereEqual(ColName_ipaddress, ip));
wheres.Add(string.Format("DATEADD(MINUTE, -{0}, GETDATE()) <= {1}", minute_interval, ColName_TimeAdd));
wheres.Add(string.Format("{0} <= GETDATE()", ColName_TimeAdd));
string sqlwhere = ConvertTool.ToString(wheres, CreateSQL.WHERE_AND);
return(base.GetCount(sqlwhere));
}
/// <summary>
/// 删除
/// </summary>
/// <param name="where">查询条件</param>
/// <returns>是否成功 是:True 否:False</returns>
public override bool Delete(string where)
{
string sqldelete;
if (CheckData.IsStringNull(where))
{
//sqldelete = CreateSQL.DropTable(this.GetTableName()); // ALL Clear
sqldelete = CreateSQL.TruncateTable(this.GetTableName()); // only delete all data
}
else
{
sqldelete = CreateSQL.Delete(this.GetTableName(), where);
}
return(CheckData.IsStringNull(sqldelete) ? false : DbHelperSQL.ExecuteSql(sqldelete) > 0);
}
public void SQL_Parser_WhereModel()
{
string[] ClNs = DBS_ClNs();
string[] RVs = DBS_RVs();
DataChar.LogicChar[] Logics = DBS_Logics();
DataChar.OperChar[] Opers = DBS_Opers();
Random random = new Random();
List <FieldOrderModel> FOmlist = new List <FieldOrderModel>();
List <FieldValueModel> FVmlist = new List <FieldValueModel>();
for (int i = 0; i < ClNs.Length - 1; i++)
{
FOmlist.Add(new FieldOrderModel()
{
Name = ClNs[random.Next(0, ClNs.Length)],
IsAsc = random.Next(0, 2) == 0,
});
FVmlist.Add(new FieldValueModel(Opers[random.Next(0, Opers.Length)])
{
Name = ClNs[random.Next(0, ClNs.Length)],
Value = RVs[random.Next(0, RVs.Length)],
});
}
Print.WriteLine("解析 排序模型 测试:");
Print.WriteLine(JsonHelper.SerializeObject(FOmlist));
Print.WriteLine(CreateSQL.ParserFieldOrderModel(FOmlist.ToArray()));
Print.WriteLine("测试结束");
Print.WriteLine(string.Empty);
Print.WriteLine("解析 字段值模型 测试: ");
Print.WriteLine(JsonHelper.SerializeObject(FVmlist));
//Print.WriteLine(JsonHelper.SerializeObject(CreateSQL.ParserFieldValueModel(FVmlist.ToArray())));
Print.WriteLine("测试结束");
Print.WriteLine(string.Empty);
Print.WriteLine("解析 条件模型 测试: ");
WhereModel wm = DBS_WM(4);
Print.WriteLine(JsonHelper.SerializeObject(wm));
//Print.WriteLine(CreateSQL.ParserWhereModel(wm));
Print.WriteLine("测试结束");
}
/// <summary>
/// 分页查询数据
/// </summary>
/// <param name="pageCount">定义: 每页记录数</param>
/// <param name="pageIndex">定义: 浏览到第几页</param>
/// <param name="recordCount">得到: 总记录数</param>
/// <param name="sql_where">定义: 查询条件</param>
/// <param name="sql_order">定义: 字段排序集合, true 为正序, false 倒序</param>
/// <returns>结果数据表</returns>
public DataSet QueryRecords(int pageCount, int pageIndex, out int recordCount, string sql_where, string sql_order)
{
if (CheckData.IsStringNull(sql_order))
{
throw new Exception(@"分页查询 排序条件必须存在!");
}
recordCount = GetRecordCount(sql_where);
string sql_select = CreateSQL.Select(this.GetTableName(), 0, sql_where, string.Empty);
string sql_paging = PagingHelper.CreatePagingSql(recordCount, pageCount, pageIndex, sql_select, sql_order);
if (CheckData.IsStringNull(sql_paging))
{
return(new DataSet());
}
DataSet ds = DbHelperSQL.Query(sql_paging);
return(ds);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="kos">需要更新的键值</param>
/// <param name="where">查询条件</param>
/// <returns>是否成功 是:True 否:False</returns>
public override bool Update(KeyObject[] kos, string where)
{
if (CheckData.IsSizeEmpty(kos) || CheckData.IsStringNull(where))
{
return(false);
}
string[] expressions = ConvertTool.ListConvertType(kos, item => {
if (CheckData.IsStringNull(item.Key))
{
return(null);
}
string str_value = ConvertTool.ToString(item.Value);
return(string.Format("{0} = '{1}'", item.Key, item.Value));
}, null);
string set_str = ConvertTool.ToString(expressions, ',');
string sql_update = CreateSQL.Update(this.GetTableName(), set_str, where);
return(CheckData.IsStringNull(sql_update) ? false : DbHelperSQL.ExecuteSql(sql_update) > 0);
}
/// <summary>
/// 组合字符串
/// </summary>
protected override string CombSqlTxt(BasicsPageDataModel Obj_DaModel)
{
DataModel daModel = new DataModel();
try { daModel = (DataModel)Obj_DaModel; } catch (Exception) { }
List <string> sqls = new List <string>();
if (!CheckData.IsStringNull(daModel.Name))
{
sqls.Add(CreateSQL.WhereEqual(BLL_vismsg.ColName_name, daModel.Name));
}
daModel.Time_Start = daModel.Time_Start.Replace("'", "");
if (!CheckData.IsStringNull(daModel.Time_Start))
{
sqls.Add(CreateSQL.WhereBigThanEqual(BLL_vismsg.ColName_TimeAdd, daModel.Time_Start));
}
daModel.Time_End = daModel.Time_End.Replace("'", "");
if (!CheckData.IsStringNull(daModel.Time_End))
{
sqls.Add(CreateSQL.WhereSmallThanEqual(BLL_vismsg.ColName_TimeAdd, daModel.Time_End));
}
if (!CheckData.IsStringNull(daModel.LikeSelectValue))
{
string[] valArr = ConvertTool.ToArrayList(daModel.LikeSelectValue, ',');
string likeSelString = CreateSQL.WhereLikeAllPropertyInfo(new Model.visitor_message(), valArr);
if (!CheckData.IsStringNull(likeSelString))
{
sqls.Add(likeSelString);
}
}
string resultSQLStr = ConvertTool.ToString(sqls, CreateSQL.WHERE_AND);
if (CheckData.IsStringNull(resultSQLStr.Trim()))
{
return(string.Empty);
}
return(CreateSQL.WHERE_AND + resultSQLStr);
}
/// <summary>
/// 数据映射模型 - 转 - SQL插入语句
/// </summary>
/// <param name="model">数据映射模型</param>
/// <param name="isResultID">是否需要结果ID值</param>
/// <returns>SQL插入语句</returns>
public string SQLInsert(M model, bool isResultID)
{
List <string> fieldArr = new List <string>();
List <string> valueArr = new List <string>();
foreach (ColumnInfo item in this.Parser.GetListCanWrite())
{
KeyString ks = this.Parser.GetValue_KeyString(item, model);
if (CheckData.IsObjectNull(ks) || CheckData.IsStringNull(ks.Key))
{
continue;
}
fieldArr.Add(ks.Key);
valueArr.Add(string.Format("'{0}'", ks.Value));
}
if ((fieldArr.Count != valueArr.Count) || CheckData.IsSizeEmpty(fieldArr))
{
return(string.Empty);
}
return(CreateSQL.Insert(this.GetTableName(), fieldArr.ToArray(), valueArr.ToArray(), isResultID));
}
/// <summary>
/// Microsoft SQL Server SQL 操作符解析器
/// </summary>
public static string MSQLServer_OperChar_Parser(FieldValueModel FVm)
{
if (CheckData.IsObjectNull(FVm))
{
return(string.Empty);
}
switch (FVm.KeyChar)
{
case DataChar.OperChar.EQUAL:
return(CreateSQL.WhereEqual(FVm.Name, FVm.Value));
case DataChar.OperChar.EQUAL_NOT:
return(CreateSQL.WhereEqualNot(FVm.Name, FVm.Value));
case DataChar.OperChar.LIKE:
return(CreateSQL.WhereLike(FVm.Name, FVm.Value));
case DataChar.OperChar.IN:
return(CreateSQL.WhereIn(FVm.Name, ConvertTool.ToArrayList(FVm.Value, DataChar.ARRAYLIST_INTERVAL_CHAR)));
case DataChar.OperChar.IN_NOT:
return(CreateSQL.WhereInNot(FVm.Name, ConvertTool.ToArrayList(FVm.Value, DataChar.ARRAYLIST_INTERVAL_CHAR)));
case DataChar.OperChar.BigTHAN:
return(CreateSQL.WhereBigThan(FVm.Name, FVm.Value));
case DataChar.OperChar.BigTHAN_EQUAL:
return(CreateSQL.WhereBigThanEqual(FVm.Name, FVm.Value));
case DataChar.OperChar.SmallTHAN:
return(CreateSQL.WhereSmallThan(FVm.Name, FVm.Value));
case DataChar.OperChar.SmallTHAN_EQUAL:
return(CreateSQL.WhereSmallThanEqual(FVm.Name, FVm.Value));
default:
FVm.SetKeyChar(DataChar.OperChar.EQUAL);
return(MSQLServer_OperChar_Parser(FVm));
}
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="keyvaluedic">更新的内容和其值</param>
/// <param name="sid">ID条件</param>
/// <returns>是否成功</returns>
public virtual bool IDUpdate(KeyObject[] keyvaluedic, string sid)
{
return(Update(keyvaluedic, CreateSQL.WhereEqual(ColName_SID, sid.ToString())));
}
/// <summary>
/// 获取模型数据
/// </summary>
/// <param name="sid">ID条件</param>
/// <returns>映射数据模型</returns>
public virtual M IDGetModel(string sid)
{
return(GetModel(CreateSQL.WhereEqual(ColName_SID, sid.ToString()), null));
}
/// <summary>
/// 获取模型数据
/// </summary>
/// <param name="id">ID条件</param>
/// <returns>映射数据模型</returns>
public virtual M IDGetModel(int id)
{
return(GetModel(CreateSQL.WhereEqual(ColName_IID, id.ToString()), null));
}