public bool ClearGeneralActionLog()
{
bool flag = false;
if (!this.IsReady)
{
base.CurDBEngine = new DatabaseEngine(this.DBType, this.Conn);
if (this.IsReady = base.CurDBEngine.Connect())
{
this.CurSQLFactory = new SQLFactory(this.DBType);
}
}
if (this.IsReady)
{
DatabaseParameters keys = new DatabaseParameters();
this.CurSQLFactory.DeleteCommand(keys, this.DataStructure.Tables.LogActions.ActualTableName);
try
{
base.CurDBEngine.ExecuteQuery(this.CurSQLFactory.SQL);
flag = true;
}
catch (Exception exception)
{
flag = false;
this.ErrMsg = exception.Message;
}
}
return(flag);
}
public bool LogError(string LogID, string Content, string LoggerID)
{
bool flag = false;
if (!this.IsReady)
{
base.CurDBEngine = new DatabaseEngine(this.DBType, this.Conn);
if (this.IsReady = base.CurDBEngine.Connect())
{
this.CurSQLFactory = new SQLFactory(this.DBType);
}
}
if (this.IsReady)
{
DatabaseParameters keys = new DatabaseParameters();
keys.Add(new DatabaseParameter(this.DataStructure.Tables.LogError.LogID.ActualFieldName, LogID));
keys.Add(new DatabaseParameter(this.DataStructure.Tables.LogError.LogDateTime.ActualFieldName, DateTime.Now));
keys.Add(new DatabaseParameter(this.DataStructure.Tables.LogError.Description.ActualFieldName, Content.Replace("'", "''"), true));
keys.Add(new DatabaseParameter(this.DataStructure.Tables.LogError.LogBy.ActualFieldName, LoggerID));
this.CurSQLFactory.InsertCommand(keys, this.DataStructure.Tables.LogError.ActualTableName);
string sQL = this.CurSQLFactory.SQL;
try
{
base.CurDBEngine.ExecuteQuery(sQL);
flag = true;
}
catch (Exception exception)
{
flag = false;
this.ErrMsg = exception.Message;
}
}
return(flag);
}
public override void Dispose()
{
clsLog oLog = new clsLog();
oLog.WriteToDebugLogFile("Starting Function", "Dispose");
if (this.is_detroyable)
{
oLog.WriteToDebugLogFile("Before Destroy", "Dispose");
if (this.IsReady)
{
oLog.WriteToDebugLogFile("Before Disconnect", "Dispose");
if (CurDBEngine != null)
{
this.CurDBEngine.Disconnect();
oLog.WriteToDebugLogFile("After Disconnect", "Dispose");
}
else
{
oLog.WriteToDebugLogFile("DB Engine is Null", "Dispose");
}
}
this.CurDBEngine = null;
this.CurSQLFactory = null;
oLog.WriteToDebugLogFile("Ending Function", "Dispose");
}
}
public ActionResult SearchDay(string beginTime, string endTime)
{
try
{
if (string.IsNullOrEmpty(beginTime) || string.IsNullOrEmpty(endTime))
{
beginTime = DateTime.Now.ToString("yyyy-MM-dd");
endTime = DateTime.Now.ToString("yyyy-MM-dd");
}
List <SqlParameter> spList = new List <SqlParameter>
{
new SqlParameter("Model", 1),
new SqlParameter("BeginTime", beginTime),
new SqlParameter("EndTime", endTime),
new SqlParameter("Account", user.Account)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_StatisticsWP", CommandType.StoredProcedure, spList.ToArray());
var parkData = JsonConvert.DeserializeObject(JsonConvert.SerializeObject(ds.Tables[0]));
var obj = JsonConvert.SerializeObject(new { Flag = 1, Part1 = parkData });
return(Content(obj));
}
catch (Exception ex)
{
return(Content(JsonConvert.SerializeObject(Result.Exception(exmsg: ex.StackTrace))));
}
}
/* Fornece uma interface para criar famílias de objetos relacionados ou dependentes sem especificar
* suas classes concretas.
*
* Este padrão pode ser utilizado quando um sistema de software precisa ser independente de como classes concretas
* são criadas, compostas ou representadas. A fabrica fica responsavel por encapsular a criação dos objetos, o usuario
* conhece apenas a interface da criacao.
*
* Participantes:
* Abstract Factory - declara uma interface para opearacoes que criam objetos-produtos abstratos
* Concrete Factory - implementa as operacoes que criam objetos-produtos concretos.
* Abstract Product - declara uma interface para um tipo de objeto-produto
* Concrete Product - define um objeto-produto a ser criado pela correspondente fabrica concreta; implementa a interface de Abstract Product
* Cliente - usa somente as interfaces declaradas pelas classes Abstract Factory e Abstract Product
*/
public void AbstractFactory()
{
DBAbstractFactory dbfSQL = new SQLFactory();
DBAbstractFactory dbfOracle = new OracleFactory();
var conn = dbfSQL.createConnection();
if (conn.Open())
{
Response.Write("Conexão aberta com sucesso usando AbstractFactory");
}
var cmd = dbfSQL.createCommand();
if (cmd.Execute())
{
Response.Write("<br>Comando executado com sucesso usando AbstractFactory");
}
var cmdOracle = dbfOracle.createCommand();
if (cmdOracle.Execute())
{
Response.Write("<br>Comando da Oracle executado com sucesso!");
}
}
private static void InitializeSQLConnection()
{
_conn = new SQLiteAsyncConnection(DependencyService.Get <IFileHelper>().GetLocalFilePath(Constants.TestDB));
foreach (var t in SQLFactory.Types)
{
SQLFactory.BuildForeignKeyDictionary(t);
}
CreateAllTables();
}
public LogManager(TypeOfDatabase DBType, string Conn) : base(DBType, Conn)
{
this.DataStructure = new Swordfish_v2_Core.CoreManagers.DataStructure();
this.IsReady = false;
this.Conn = "";
this.CurSQLFactory = null;
this.DBType = TypeOfDatabase.MSSQL;
this.ErrMsg = "";
this.DBType = DBType;
this.Conn = Conn;
}
public LogManager(SessionConfig PrivateConfig) : base(PrivateConfig)
{
this.DataStructure = new Swordfish_v2_Core.CoreManagers.DataStructure();
this.IsReady = false;
this.Conn = "";
this.CurSQLFactory = null;
this.DBType = TypeOfDatabase.MSSQL;
this.ErrMsg = "";
this.DBType = PrivateConfig.DatabaseType;
this.Conn = PrivateConfig.DatabaseConnectionString;
}
public SwordfishManagerBase(SessionConfig PrivateConfig)
{
base.PrivateConfig = PrivateConfig;
this.CurSQLFactory = new SQLFactory(base.PrivateConfig.DatabaseType);
if (PrivateConfig.CurDBEngine != null)
{
this.CurDBEngine = PrivateConfig.CurDBEngine;
this.is_detroyable = false;
this.is_ready = true;
}
}
public override void Dispose()
{
if (this.is_detroyable)
{
if (this.IsReady)
{
this.CurDBEngine.Disconnect();
}
this.CurDBEngine = null;
this.CurSQLFactory = null;
}
}
public Plugin(PluginModel pModel, bool threadStart)
{
Pro = new Protocol();
this.pModel = pModel;
this.status = 1;
this.isSlow = 0;
this.statistics = 0L;
this.sqlfc = new OracleHelper(pModel.DesIP, pModel.DesPort, pModel.DesCatalog, pModel.DesUser, pModel.DesPwd);
if (threadStart)
{
this.dealDataThread = new Thread(new ThreadStart(this.Run));
this.dealDataThread.Start();
}
}
public ActionResult GetOrderData(string stime, string etime)
{
if (!string.IsNullOrEmpty(etime))
{
etime = etime + " 23:59:59";
}
var spList = new List <SqlParameter> {
new SqlParameter("BeginTime", stime),
new SqlParameter("EndTime", etime)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_OrderStatistics", CommandType.StoredProcedure, spList.ToArray());
var obj = JsonConvert.SerializeObject(ds.Tables[0]);
return(Content(obj));
}
public ActionResult GetResourceData(string park)
{
var beginTime = DateTime.Now.ToString("yyyy-MM-dd");
var endTime = DateTime.Now.ToString("yyyy-MM-dd");
List <SqlParameter> spList = new List <SqlParameter>
{
new SqlParameter("Park", park),
new SqlParameter("BeginTime", beginTime),
new SqlParameter("EndTime", endTime),
new SqlParameter("Account", user.Account)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_StatisticsAll", CommandType.StoredProcedure, spList.ToArray());
var obj = JsonConvert.SerializeObject(ds.Tables[0]);
return(Content(obj));
}
public static List <T_MenuFunc> GetFunc(string account, string menuPath)
{
try
{
var spList = new List <SqlParameter> {
new SqlParameter("Account", account),
new SqlParameter("MenuPath", menuPath)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_GetFunc", CommandType.StoredProcedure, spList.ToArray());
return(JsonConvert.DeserializeObject <List <T_MenuFunc> >(JsonConvert.SerializeObject(ds.Tables[0])));
}
catch (Exception)
{
throw;
}
}
public static IDAO <T> getFormat()
{
var confManager = new ConfigManager();
switch (confManager.GetActualFormat())
{
case TipoFichero.TXT:
return(new DAOTxt <T>());
case TipoFichero.JSON:
return(new DAOJson <T>());
case TipoFichero.XML:
return(new DAOXml <T>());
case TipoFichero.SQL:
return((IDAO <T>) SQLFactory <T> .getFormat());
default:
return(null);
}
}
public LogsCollection GetActionLogByReffID(string InternalID)
{
LogsCollection logss = null;
if (!this.IsReady)
{
base.CurDBEngine = new DatabaseEngine(this.DBType, this.Conn);
if (this.IsReady = base.CurDBEngine.Connect())
{
this.CurSQLFactory = new SQLFactory(this.DBType);
}
}
if (this.IsReady)
{
DatabaseParameters parameters = new DatabaseParameters();
parameters.Add(new DatabaseParameter(this.DataStructure.Tables.LogActions.ReffID.ActualFieldName, InternalID));
this.CurSQLFactory.SelectCommand(parameters, this.DataStructure.Tables.LogActions.ActualTableName);
DataTable table = base.CurDBEngine.SelectQuery(this.CurSQLFactory.SQL);
if (table != null)
{
logss = new LogsCollection();
DataRow[] rowArray = table.Select("", this.DataStructure.Tables.LogActions.LogDateTime.ActualFieldName);
foreach (DataRow row in rowArray)
{
ActionLogItem item = new ActionLogItem {
ReffID = row[this.DataStructure.Tables.LogActions.ReffID.ActualFieldName].ToString(),
LogDate = Convert.ToDateTime(row[this.DataStructure.Tables.LogActions.LogDateTime.ActualFieldName]),
LoggedBy = new ApplicationUser(row[this.DataStructure.Tables.LogActions.LogBy.ActualFieldName].ToString()),
ActionDescription = row[this.DataStructure.Tables.LogActions.Description.ActualFieldName].ToString()
};
logss.Add(item);
}
return(logss);
}
this.ErrMsg = "[LogManager.GetActionLogByReffID] : Failed at this.CurDBEngine.SelectQuery('" + this.CurSQLFactory.SQL + "') : " + base.CurDBEngine.ErrorMessage;
}
return(logss);
}
public FileResult ImportToExcel(string park, string stime, string etime, int model)
{
try
{
HSSFWorkbook book = new HSSFWorkbook();
string fileName = string.Empty;
if (model == 1)
{
//汇总数据导出
fileName = "工位出租率汇总报表-" + DateTime.Now.ToString("yyyyMMddHHmmsss");
if (string.IsNullOrEmpty(stime) || string.IsNullOrEmpty(etime))
{
stime = DateTime.Now.ToString("yyyy-MM-dd");
etime = DateTime.Now.ToString("yyyy-MM-dd");
}
List <SqlParameter> spList = new List <SqlParameter>
{
new SqlParameter("Model", 1),
new SqlParameter("BeginTime", stime),
new SqlParameter("EndTime", etime),
new SqlParameter("Account", user.Account)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_StatisticsWP", CommandType.StoredProcedure, spList.ToArray());
//工作簿
DataRow[] rows = string.IsNullOrEmpty(park) ? ds.Tables[0].Select() : ds.Tables[0].Select(string.Format("ParkID='{0}'", park));
ISheet sheet = book.CreateSheet("出租率汇总数据");
//条件列
IRow condition = sheet.CreateRow(0);
condition.CreateCell(0).SetCellValue("园区：");
condition.CreateCell(1).SetCellValue(string.IsNullOrEmpty(park) ? "全部" : rows[0]["ParkName"].ToString());
condition.CreateCell(2).SetCellValue("开始时间：");
condition.CreateCell(3).SetCellValue(stime);
condition.CreateCell(4).SetCellValue("结束时间：");
condition.CreateCell(5).SetCellValue(etime);
//第一行：标题行
IRow title = sheet.CreateRow(1);
title.CreateCell(0).SetCellValue("园区");
title.CreateCell(1).SetCellValue("工位总数");
title.CreateCell(2).SetCellValue("出租个数");
title.CreateCell(3).SetCellValue("内部使用个数");
title.CreateCell(4).SetCellValue("空置个数");
title.CreateCell(5).SetCellValue("出租率");
title.CreateCell(6).SetCellValue("内部使用率");
title.CreateCell(7).SetCellValue("空置率");
var rowIndex = 2;
//内容
foreach (var item in rows)
{
IRow row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(item["ParkName"].ToString());
row.CreateCell(1).SetCellValue(item["Total"].ToString());
row.CreateCell(2).SetCellValue(item["Rent"].ToString());
row.CreateCell(3).SetCellValue(item["Self"].ToString());
row.CreateCell(4).SetCellValue(item["Free"].ToString());
row.CreateCell(5).SetCellValue(item["RentRate"].ToString() + "%");
row.CreateCell(6).SetCellValue(item["SelfRate"].ToString() + "%");
row.CreateCell(7).SetCellValue(item["FreeRate"].ToString() + "%");
rowIndex++;
}
}
else
{
//趋势图数据导出
fileName = "工位出租率趋势报表-" + DateTime.Now.ToString("yyyyMMddHHmmsss");
if (string.IsNullOrEmpty(stime) || string.IsNullOrEmpty(etime))
{
stime = DateTime.Now.ToString("yyyy-MM") + "-01";
etime = DateTime.Now.ToString("yyyy-MM") + "-01";
}
else
{
stime = stime + "-01";
etime = etime + "-01";
}
List <SqlParameter> spList = new List <SqlParameter>
{
new SqlParameter("Model", 2),
new SqlParameter("BeginTime", stime),
new SqlParameter("EndTime", etime),
new SqlParameter("Account", user.Account)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_StatisticsWP", CommandType.StoredProcedure, spList.ToArray());
var result = JsonConvert.DeserializeObject <List <StatisticsMonthView> >(JsonConvert.SerializeObject(ds.Tables[0]));
//if (!string.IsNullOrEmpty(park)) result = result.Where(a => a.ParkID == park).ToList();
//工作簿
ISheet monthSheet = book.CreateSheet("出租率趋势数据");
//第一行：标题行
var column = 1;
IRow monthTitle = monthSheet.CreateRow(0);
monthTitle.CreateCell(0).SetCellValue("园区");
var month = result.GroupBy(a => a.MonthTime).OrderBy(a => a.Key).Select(a => a.Key).ToList();
foreach (var item in month)
{
monthTitle.CreateCell(column).SetCellValue(item);
column++;
}
//内容
var row = 1;
var monthPark = result.GroupBy(a => new { a.ParkID, a.ParkName }).Select(a => new { a.Key.ParkID, a.Key.ParkName }).ToList();
foreach (var item in monthPark)
{
IRow dataRow = monthSheet.CreateRow(row);
dataRow.CreateCell(0).SetCellValue(item.ParkName);
column = 1;
var monthData = result.Where(a => a.ParkID == item.ParkID).OrderBy(a => a.MonthTime).ToList();
foreach (var data in monthData)
{
dataRow.CreateCell(column).SetCellValue(data.RentRate + "%");
column++;
}
row++;
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", fileName + ".xls"));
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// 查询指定表中的所有符合条件的数据
/// 接受两个约束参数
/// </summary>
/// <param name="Tname">表名</param>
/// <param name="dl">约束条件1</param>
/// <param name="d2">约束条件2</param>
/// <param name="content1">约束条件1的值</param>
/// <param name="content2">约束条件2的值</param>
/// <returns></returns>
public static DataTable GetDTBySQL(string Tname, string dl, string d2, string content1, string content2)
{
string strSQL = SQLFactory.BuildSQLSelectString(Tname, dl, d2, content1, content2);
return(ConnHelper.GetDataTable(strSQL));
}
/// <summary>
/// 删除表内的数据，有一个约束条件
/// </summary>
/// <param name="v1">表名</param>
/// <param name="v2">约束字段</param>
/// <param name="v3">约束值</param>
public static bool DeleteRows(string v1, string v2, string v3)
{
string strSQL = SQLFactory.BuildSQLDeleteString(v1, v2, v3);
return(ConnHelper.ExecuteNoneQueryOperation(strSQL));
}
/// <summary>
/// 查询一张表的所有数据
/// </summary>
/// <param name="Tname">查询的表名</param>
/// <returns></returns>
public static DataTable GetDTBySQL(string Tname)
{
string strSQL = SQLFactory.BuildSQLSelectString(Tname);
return(ConnHelper.GetDataTable(strSQL));
}
public ActionResult SearchMonth(string beginTime, string endTime)
{
try
{
if (string.IsNullOrEmpty(beginTime) || string.IsNullOrEmpty(endTime))
{
beginTime = DateTime.Now.ToString("yyyy-MM") + "-01";
endTime = DateTime.Now.ToString("yyyy-MM") + "-01";
}
else
{
beginTime = beginTime + "-01";
endTime = endTime + "-01";
}
List <SqlParameter> spList = new List <SqlParameter>
{
new SqlParameter("Model", 2),
new SqlParameter("BeginTime", beginTime),
new SqlParameter("EndTime", endTime),
new SqlParameter("Account", user.Account)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_StatisticsWP", CommandType.StoredProcedure, spList.ToArray());
var result = JsonConvert.DeserializeObject <List <StatisticsMonthView> >(JsonConvert.SerializeObject(ds.Tables[0]));
/*
* 园区数据组装:为图表服务
*/
var titleList = result.GroupBy(a => new { a.ParkID, a.ParkName })
.OrderBy(a => a.Key.ParkID)
.Select(a => new { a.Key.ParkID, a.Key.ParkName })
.ToList();
var monthList = result.GroupBy(a => a.MonthTime)
.OrderBy(a => a.Key)
.Select(a => a.Key)
.ToList();
var seriesData = new List <object>();
foreach (var item in titleList)
{
seriesData.Add(new
{
name = item.ParkName,
data = result.Where(a => a.ParkID == item.ParkID)
.OrderBy(a => a.MonthTime)
.Select(a => a.RentRate)
.ToList()
});
}
var graph = new
{
legend = titleList.Select(a => a.ParkName).ToList(),
xAxis = monthList,
series = seriesData
};
/*
* 月份数据组装:为表格服务
*/
var rowsData = new List <object>();
foreach (var item in monthList)
{
rowsData.Add(new
{
name = item,
data = result.Where(a => a.MonthTime == item)
.OrderBy(a => a.ID)
.Select(a => a.RentRate)
.ToList()
});
}
var table = new
{
title = titleList.Select(a => a.ParkName).ToList(),
rows = rowsData
};
var obj = JsonConvert.SerializeObject(new
{
Flag = 1,
graph = graph,
table = table
});
return(Content(obj));
}
catch (Exception ex)
{
return(Content(JsonConvert.SerializeObject(Result.Exception(exmsg: ex.StackTrace))));
}
}
/// <summary>
/// 更新数据表
/// 更新一个字段，有一个条件
/// </summary>
/// <param name="v1">表名</param>
/// <param name="v2">更新的字段</param>
/// <param name="v3">更新后的值</param>
/// <param name="v4">约束名</param>
/// <param name="v5">约束值</param>
/// <returns></returns>
public static bool UpdateRows(string v1, string v2, string v3, string v4, string v5)
{
string strSQL = SQLFactory.BuildSQLUpdate(v1, v2, v3, v4, v5);
return(ConnHelper.ExecuteNoneQueryOperation(strSQL));
}
public FileResult ImportToExcel(string park, string stime, string etime)
{
try
{
HSSFWorkbook book = new HSSFWorkbook();
if (string.IsNullOrEmpty(stime) || string.IsNullOrEmpty(etime))
{
stime = DateTime.Now.ToString("yyyy-MM-dd");
etime = DateTime.Now.ToString("yyyy-MM-dd");
}
List <SqlParameter> spList = new List <SqlParameter>
{
new SqlParameter("Park", park),
new SqlParameter("BeginTime", stime),
new SqlParameter("EndTime", etime),
new SqlParameter("Account", user.Account)
};
DataSet ds = SQLFactory.Create().GetDataSet("Pro_StatisticsAll", CommandType.StoredProcedure, spList.ToArray());
ISheet sheet = book.CreateSheet("资源出租率汇总数据");
//条件列
IRow condition = sheet.CreateRow(0);
condition.CreateCell(0).SetCellValue("园区：");
condition.CreateCell(1).SetCellValue(string.IsNullOrEmpty(park) ? "全部" : dc.Set <T_Park>().Where(a => a.ID == park).FirstOrDefault().Name);
condition.CreateCell(2).SetCellValue("开始时间：");
condition.CreateCell(3).SetCellValue(stime);
condition.CreateCell(4).SetCellValue("结束时间：");
condition.CreateCell(5).SetCellValue(etime);
//第一行：标题行
IRow title = sheet.CreateRow(1);
title.CreateCell(0).SetCellValue("资源类型");
title.CreateCell(1).SetCellValue("总数");
title.CreateCell(2).SetCellValue("客户租赁");
title.CreateCell(3).SetCellValue("内部使用");
title.CreateCell(4).SetCellValue("空置");
title.CreateCell(5).SetCellValue("客户出租率");
title.CreateCell(6).SetCellValue("内部使用率");
title.CreateCell(7).SetCellValue("空置率");
var rowIndex = 2;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
IRow row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(GetResourceName(ds.Tables[0].Rows[i]["Kind"].ToString()));
row.CreateCell(1).SetCellValue(ds.Tables[0].Rows[i]["Total"].ToString());
row.CreateCell(2).SetCellValue(ds.Tables[0].Rows[i]["Rent"].ToString());
row.CreateCell(3).SetCellValue(ds.Tables[0].Rows[i]["Self"].ToString());
row.CreateCell(4).SetCellValue(ds.Tables[0].Rows[i]["Free"].ToString());
row.CreateCell(5).SetCellValue(ds.Tables[0].Rows[i]["RentRate"].ToString() + "%");
row.CreateCell(6).SetCellValue(ds.Tables[0].Rows[i]["SelfRate"].ToString() + "%");
row.CreateCell(7).SetCellValue(ds.Tables[0].Rows[i]["FreeRate"].ToString() + "%");
rowIndex++;
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", string.Format("资源出租率汇总报表-{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmsss"))));
}
catch (Exception)
{
return(null);
}
}
/// <summary>
/// ！猜测代码
/// 获取指定表中的指定字段中的所有数据
/// 查询的字段拥有一个约束条件【字段约束】
/// 返回一个list，包含每一行的数据
/// </summary>
/// <param name="strTable">表名</param>
/// <param name="name1">字段</param>
/// <param name="name2">约束字段名</param>
/// <param name="data">约束字段的值</param>
/// <returns></returns>
public static List <string> GetDistinctString(string strTable, string name1, string name2, string data)
{
string strSQL = SQLFactory.BuildSQLSelectString(strTable, name1, name2, data);
return(ConnHelper.GetDistinceColoum(strSQL, name1));
}
public static bool DeleteTab(string TName)
{
string strSQL = SQLFactory.BuildSQLDeleteString(TName);
return(ConnHelper.ExecuteNoneQueryOperation(strSQL));
}
public override void LoadParameters(StringBuilder sb, List <DbParameter> pars, JoinType joinType)
{
String fieldName = FieldName;
String parameterName = FieldName.Replace("(", "").Replace(")", "").
Replace("&", "").Replace("|", "").Replace("+", "").
Replace("-", "").Replace("*", "").Replace("/", "");
//检测参数是否有重复项
if (pars.Exists(p => p.ParameterName == parameterName))
{
parameterName += Guid.NewGuid().ToString("N");
}
if (sb.Length > 3 && !sb.ToString().EndsWith("("))//避免一上来就是AND/OR连接符；避免左括号直接跟AND/OR连接符
{
sb.Append(" " + joinType.ToString() + " ");
}
switch (ConditionType)
{
case SearchType.Accurate:
if (FieldValue == null)
{
sb.Append(fieldName + " IS NULL");
}
else
{
sb.Append(fieldName + "=@" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, FieldValue));
}
break;
case SearchType.Fuzzy:
sb.Append(fieldName + " LIKE @" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, "%" + FieldValue + "%"));
break;
case SearchType.LeftFuzzy:
sb.Append(fieldName + " LIKE '%'" + CommonVar.JoinTag + "@" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, FieldValue));
break;
case SearchType.RightFuzzy:
sb.Append(fieldName + " LIKE @" + parameterName + CommonVar.JoinTag + "'%'");
pars.Add(SQLFactory.CreateParameter(parameterName, FieldValue));
break;
case SearchType.UnContains:
sb.Append(fieldName + " NOT LIKE @" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, "%" + FieldValue + "%"));
break;
case SearchType.Unequal:
if (FieldValue == null)
{
sb.Append(fieldName + " IS NOT NULL");
}
else
{
sb.Append(fieldName + " <> @" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, FieldValue));
}
break;
case SearchType.ValuesIn:
sb.Append("(");
String[] fiels = FieldValue.ToString().Split(',');
for (int i = 0; i < fiels.Length; i++)
{
if (i == (fiels.Length - 1))
{
sb.Append(fieldName + " = @" + (parameterName + i));
}
else
{
sb.Append(fieldName + " = @" + (parameterName + i) + " OR ");
}
pars.Add(SQLFactory.CreateParameter((parameterName + i), fiels[i]));
}
sb.Append(")");
break;
case SearchType.ValuesNotIn:
sb.Append("(");
fiels = FieldValue.ToString().Split(',');
for (int i = 0; i < fiels.Length; i++)
{
if (i == (fiels.Length - 1))
{
sb.Append(FieldName + " <> @" + (FieldName + i));
}
else
{
sb.Append(FieldName + " <> @" + (FieldName + i) + " AND ");
}
pars.Add(SQLFactory.CreateParameter((parameterName + i), fiels[i]));
}
sb.Append(")");
break;
case SearchType.Greater:
sb.Append(fieldName + " > @" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, FieldValue));
break;
case SearchType.Lower:
sb.Append(fieldName + " < @" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, FieldValue));
break;
case SearchType.GreaterEqual:
sb.Append(fieldName + " >= @" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, FieldValue));
break;
case SearchType.LowerEqual:
sb.Append(fieldName + " <= @" + parameterName);
pars.Add(SQLFactory.CreateParameter(parameterName, FieldValue));
break;
default:
break;
}
}
