/// <summary>
/// Open
/// </summary>
/// <param name="tableOrViewName"></param>
/// <param name="fieldCollent"></param>
/// <param name="condition"></param>
/// <param name="orderBy"></param>
/// <param name="limitStart"></param>
/// <param name="limitCount"></param>
/// <returns></returns>
protected override DataTable __Open(string tableOrViewName, FieldCollect fieldCollent, string condition, string orderBy, int limitStart, int limitCount)
{
tableOrViewName = ReplaceDefinitionForObjectName(tableOrViewName);
string commandString = "select ";
if ((fieldCollent == null) || (fieldCollent.Count < 1))
{
commandString += "* ";
}
else
{
for (int i = 0; i < fieldCollent.Count; i++)
{
string FieldName = fieldCollent[i].Trim(new char[] { ' ', ' ', '\t', '\r', '\n', '\v', '\f' });
if (FieldName != "*")
{
FieldName = ReplaceDefinitionForObjectName(FieldName);
}
commandString += (FieldName + ((i == (fieldCollent.Count - 1)) ? " " : ", "));
}
}
commandString += "from " + tableOrViewName + " ";
condition = condition.Trim();
condition = string.IsNullOrEmpty(condition) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(condition));
if (!string.IsNullOrEmpty(condition))
{
commandString += "where " + condition + " ";
}
orderBy = orderBy.Trim();
orderBy = string.IsNullOrEmpty(orderBy) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(orderBy));
if (!string.IsNullOrEmpty(orderBy))
{
commandString += "order by " + orderBy + " ";
}
if ((limitStart >= 0) && (limitCount > 0))
{
commandString += "limit " + limitStart.ToString() + ", " + limitCount.ToString();
}
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(commandString, (MySqlConnection)connection);
if (transcation != null)
{
da.SelectCommand.Transaction = (MySqlTransaction)transcation;
}
da.Fill(dt);
return(dt);
}
private DataTable __OpenWithoutLimit(string TableOrViewName, FieldCollect fieldCollent, string Condition, string OrderBy)
{
string CommandString = "select ";
if ((fieldCollent == null) || (fieldCollent.Count < 1))
{
CommandString += "* ";
}
else
{
for (int i = 0; i < fieldCollent.Count; i++)
{
string FieldName = fieldCollent[i].Trim(new char[] { ' ', ' ', '\t', '\r', '\n', '\v', '\f' });
if (FieldName != "*")
{
FieldName = ReplaceDefinitionForObjectName(FieldName);
}
CommandString += (FieldName + ((i == (fieldCollent.Count - 1)) ? " " : ", "));
}
}
CommandString += "from " + TableOrViewName + " ";
if (!String.IsNullOrEmpty(Condition))
{
CommandString += "where " + Condition + " ";
}
if (!String.IsNullOrEmpty(OrderBy))
{
CommandString += "order by " + OrderBy;
}
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(CommandString, (SqlConnection)connection);
if (transcation != null)
{
da.SelectCommand.Transaction = (SqlTransaction)transcation;
}
da.Fill(dt);
return(dt);
}
private DataTable __OpenWithLimit(string TableOrViewName, FieldCollect fieldCollent, string Condition, string OrderBy, int LimitStart, int LimitCount)
{
string CommandString = "select * from (select top " + (LimitStart + LimitCount).ToString() + " ";
if ((fieldCollent == null) || (fieldCollent.Count < 1))
{
CommandString += "*, ";
}
else
{
for (int i = 0; i < fieldCollent.Count; i++)
{
string FieldName = fieldCollent[i].Trim(new char[] { ' ', ' ', '\t', '\r', '\n', '\v', '\f' });
if (FieldName != "*")
{
FieldName = ReplaceDefinitionForObjectName(FieldName);
}
CommandString += (FieldName + ", ");
}
}
CommandString += "Row_Number() over (order by " + OrderBy + ") as __SE_ROW ";
CommandString += "from " + TableOrViewName + " ";
if (!String.IsNullOrEmpty(Condition))
{
CommandString += "where " + Condition + " ";
}
CommandString += "order by " + OrderBy + ") as a where __SE_ROW > " + LimitStart.ToString() + " order by __SE_ROW";
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(CommandString, (SqlConnection)connection);
if (transcation != null)
{
da.SelectCommand.Transaction = (SqlTransaction)transcation;
}
da.Fill(dt);
return(dt);
}
/// <summary>
/// Open
/// </summary>
/// <param name="TableOrViewName"></param>
/// <param name="fieldCollent"></param>
/// <param name="Condition"></param>
/// <param name="OrderBy"></param>
/// <param name="LimitStart"></param>
/// <param name="LimitCount"></param>
/// <returns></returns>
protected override DataTable __Open(string TableOrViewName, FieldCollect fieldCollent, string Condition, string OrderBy, int LimitStart, int LimitCount)
{
TableOrViewName = ReplaceDefinitionForObjectName(TableOrViewName);
Condition = Condition.Trim();
OrderBy = OrderBy.Trim();
Condition = String.IsNullOrEmpty(Condition) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(Condition));
OrderBy = String.IsNullOrEmpty(OrderBy) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(OrderBy));
if (String.IsNullOrEmpty(OrderBy))
{
LimitStart = 0;
LimitCount = 0;
}
if ((LimitStart >= 0) && (LimitCount > 0))
{
return(__OpenWithLimit(TableOrViewName, fieldCollent, Condition, OrderBy, LimitStart, LimitCount));
}
return(__OpenWithoutLimit(TableOrViewName, fieldCollent, Condition, OrderBy));
}
/// <summary>
/// Open
/// </summary>
/// <param name="tableOrViewName"></param>
/// <param name="fieldCollent"></param>
/// <param name="condition"></param>
/// <param name="orderBy"></param>
/// <param name="limitStart"></param>
/// <param name="limitCount"></param>
/// <returns></returns>
protected override DataTable __Open(string tableOrViewName, FieldCollect fieldCollent, string condition, string orderBy, int limitStart, int limitCount)
{
tableOrViewName = ReplaceDefinitionForObjectName(tableOrViewName);
condition = condition.Trim();
orderBy = orderBy.Trim();
condition = string.IsNullOrEmpty(condition) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(condition));
orderBy = string.IsNullOrEmpty(orderBy) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(orderBy));
if (string.IsNullOrEmpty(orderBy))
{
limitStart = 0;
limitCount = 0;
}
if ((limitStart >= 0) && (limitCount > 0))
{
return(__OpenWithLimit(tableOrViewName, fieldCollent, condition, orderBy, limitStart, limitCount));
}
return(__OpenWithoutLimit(tableOrViewName, fieldCollent, condition, orderBy));
}
/// <summary>
/// Insert
/// </summary>
/// <param name="TableName"></param>
/// <param name="fieldCollent"></param>
/// <param name="fieldValueCollect"></param>
/// <returns></returns>
protected override long __Insert(string TableName, FieldCollect fieldCollent, FieldValueCollect fieldValueCollect)
{
if (DatabaseReadOnlyState)
{
return(-9999);
}
if ((fieldCollent == null) || (fieldCollent.Count < 1))
{
throw new Exception("the fieldCollent can't is null.");
}
if ((fieldValueCollect == null) || (fieldValueCollect.Count < 1))
{
throw new Exception("the fieldValueCollect can't is null.");
}
if (fieldCollent.Count != fieldValueCollect.Count)
{
throw new Exception("fieldCollent and fieldValueCollect's Number of inconsistencies.");
}
TableName = ReplaceDefinitionForObjectName(TableName);
string CommandString = "insert into " + TableName + " (";
for (int i = 0; i < fieldCollent.Count; i++)
{
string FieldName = ReplaceDefinitionForObjectName(fieldCollent[i]);
CommandString += (FieldName + ((i == (fieldCollent.Count - 1)) ? ") values (" : ", "));
}
int ParameterCount = 0;
for (int i = 0; i < fieldValueCollect.Count; i++)
{
CommandString += (i > 0) ? ", " : "";
object FieldValue = fieldValueCollect[i];
if (FieldValue is FieldValueCalculate)
{
throw new Exception("Insert method does not support FieldValueCalculate.");
}
if (FieldValue is FieldValueCalculate)
{
CommandString += ((FieldValueCalculate)FieldValue).ToString();
}
else
{
CommandString += "@p" + ParameterCount.ToString();
ParameterCount++;
}
}
CommandString += "); select ifnull(last_insert_rowid(), -99999999);";
SQLiteCommand Cmd = new SQLiteCommand(CommandString, (SQLiteConnection)connection);
ParameterCount = 0;
for (int i = 0; i < fieldValueCollect.Count; i++)
{
object FieldValue = fieldValueCollect[i];
if (FieldValue is FieldValueCalculate)
{
continue;
}
SQLiteParameter p = new SQLiteParameter("@p" + ParameterCount.ToString(), (FieldValue == null ? System.DBNull.Value : (String.IsNullOrEmpty(FieldValue.ToString()) ? System.DBNull.Value : FieldValue)));
Cmd.Parameters.Add(p);
ParameterCount++;
}
if (transcation != null)
{
Cmd.Transaction = (SQLiteTransaction)transcation;
}
WriteCommandToLogger(3, CommandString + "\tParameterCount: " + ParameterCount.ToString());
object objResult = Cmd.ExecuteScalar();
long Result = System.Convert.ToInt64(objResult);
if (Result == -99999999)
{
return(0);
}
return(Result);
}
/// <summary>
/// Update
/// </summary>
/// <param name="tableName"></param>
/// <param name="fieldCollent"></param>
/// <param name="fieldValueCollect"></param>
/// <param name="condition"></param>
/// <returns></returns>
protected override long __Update(string tableName, FieldCollect fieldCollent, FieldValueCollect fieldValueCollect, string condition)
{
if ((fieldCollent == null) || (fieldCollent.Count < 1))
{
throw new Exception("the fieldCollent can't is null.");
}
if ((fieldValueCollect == null) || (fieldValueCollect.Count < 1))
{
throw new Exception("the fieldValueCollect can't is null.");
}
if (fieldCollent.Count != fieldValueCollect.Count)
{
throw new Exception("fieldCollent and fieldValueCollect's Number of inconsistencies.");
}
tableName = ReplaceDefinitionForObjectName(tableName);
string commandString = "update " + tableName + " set ";
int parameterCount = 0;
for (int i = 0; i < fieldCollent.Count; i++)
{
string FieldName = ReplaceDefinitionForObjectName(fieldCollent[i]);
object FieldValue = fieldValueCollect[i];
commandString += ((i > 0) ? ", " : "") + FieldName + " = ";
if (FieldValue is FieldValueCalculate)
{
commandString += ((FieldValueCalculate)FieldValue).ToString();
}
else
{
commandString += "?p" + parameterCount.ToString();
parameterCount++;
}
}
condition = condition.Trim();
condition = string.IsNullOrEmpty(condition) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(condition));
if (!string.IsNullOrEmpty(condition))
{
commandString += " where " + condition;
}
commandString += "; select ifnull(ROW_COUNT(), -99999999)";
MySqlCommand cmd = new MySqlCommand(commandString, (MySqlConnection)connection);
parameterCount = 0;
for (int i = 0; i < fieldValueCollect.Count; i++)
{
object FieldValue = fieldValueCollect[i];
if (FieldValue is FieldValueCalculate)
{
continue;
}
MySqlParameter p = new MySqlParameter("?p" + parameterCount.ToString(), (FieldValue == null ? System.DBNull.Value : (string.IsNullOrEmpty(FieldValue.ToString()) ? System.DBNull.Value : FieldValue)));
cmd.Parameters.Add(p);
parameterCount++;
}
if (transcation != null)
{
cmd.Transaction = (MySqlTransaction)transcation;
}
object objResult = cmd.ExecuteScalar();
long result = System.Convert.ToInt64(objResult);
if (result == -99999999)
{
return(0);
}
return(result);
}
/// <summary>
/// Insert
/// </summary>
/// <param name="tableName"></param>
/// <param name="fieldCollent"></param>
/// <param name="fieldValueCollect"></param>
/// <returns></returns>
protected override long __Insert(string tableName, FieldCollect fieldCollent, FieldValueCollect fieldValueCollect)
{
if ((fieldCollent == null) || (fieldCollent.Count < 1))
{
throw new Exception("the fieldCollent can't is null.");
}
if ((fieldValueCollect == null) || (fieldValueCollect.Count < 1))
{
throw new Exception("the fieldValueCollect can't is null.");
}
if (fieldCollent.Count != fieldValueCollect.Count)
{
throw new Exception("fieldCollent and fieldValueCollect's Number of inconsistencies.");
}
tableName = ReplaceDefinitionForObjectName(tableName);
string commandString = "insert into " + tableName + " (";
for (int i = 0; i < fieldCollent.Count; i++)
{
string FieldName = ReplaceDefinitionForObjectName(fieldCollent[i]);
commandString += (FieldName + ((i == (fieldCollent.Count - 1)) ? ") values (" : ", "));
}
int parameterCount = 0;
for (int i = 0; i < fieldValueCollect.Count; i++)
{
commandString += (i > 0) ? ", " : "";
object FieldValue = fieldValueCollect[i];
if (FieldValue is FieldValueCalculate)
{
throw new Exception("Insert method does not support FieldValueCalculate.");
}
if (FieldValue is FieldValueCalculate)
{
commandString += ((FieldValueCalculate)FieldValue).ToString();
}
else
{
commandString += "?p" + parameterCount.ToString();
parameterCount++;
}
}
commandString += "); select ifnull(LAST_INSERT_ID(), -99999999);";
MySqlCommand cmd = new MySqlCommand(commandString, (MySqlConnection)connection);
parameterCount = 0;
for (int i = 0; i < fieldValueCollect.Count; i++)
{
object FieldValue = fieldValueCollect[i];
if (FieldValue is FieldValueCalculate)
{
continue;
}
MySqlParameter p = new MySqlParameter("?p" + parameterCount.ToString(), (FieldValue == null ? System.DBNull.Value : (string.IsNullOrEmpty(FieldValue.ToString()) ? System.DBNull.Value : FieldValue)));
cmd.Parameters.Add(p);
parameterCount++;
}
if (transcation != null)
{
cmd.Transaction = (MySqlTransaction)transcation;
}
object objResult = cmd.ExecuteScalar();
long result = System.Convert.ToInt64(objResult);
if (result == -99999999)
{
return(0);
}
return(result);
}
/// <summary>
/// 更新记录
/// </summary>
/// <param name="TableName"></param>
/// <param name="fieldCollent"></param>
/// <param name="fieldValueCollect"></param>
/// <param name="Condition"></param>
/// <returns></returns>
public long Update(string TableName, FieldCollect fieldCollent, FieldValueCollect fieldValueCollect, string Condition)
{
return(this.__Update(TableName, fieldCollent, fieldValueCollect, Condition));
}
/// <summary>
/// 更新记录
/// </summary>
/// <param name="TableName"></param>
/// <param name="fieldCollent"></param>
/// <param name="fieldValueCollect"></param>
/// <param name="Condition"></param>
/// <returns></returns>
protected virtual long __Update(string TableName, FieldCollect fieldCollent, FieldValueCollect fieldValueCollect, string Condition)
{
return(0);
}
/// <summary>
/// 插入记录
/// </summary>
/// <param name="TableName"></param>
/// <param name="fieldCollent"></param>
/// <param name="fieldValueCollect"></param>
/// <returns></returns>
protected virtual long __Insert(string TableName, FieldCollect fieldCollent, FieldValueCollect fieldValueCollect)
{
return(0);
}
/// <summary>
/// 打开表或视图
/// </summary>
/// <param name="TableOrViewName"></param>
/// <param name="fieldCollent"></param>
/// <param name="Condition"></param>
/// <param name="OrderBy"></param>
/// <param name="LimitStart"></param>
/// <param name="LimitCount"></param>
/// <returns></returns>
public DataTable Open(string TableOrViewName, FieldCollect fieldCollent, string Condition, string OrderBy, int LimitStart, int LimitCount)
{
return(this.__Open(TableOrViewName, fieldCollent, Condition, OrderBy, LimitStart, LimitCount));
}
/// <summary>
/// 打开表或视图
/// </summary>
/// <param name="TableOrViewName"></param>
/// <param name="fieldCollent"></param>
/// <param name="Condition"></param>
/// <param name="OrderBy"></param>
/// <param name="LimitStart"></param>
/// <param name="LimitCount"></param>
/// <returns></returns>
protected virtual DataTable __Open(string TableOrViewName, FieldCollect fieldCollent, string Condition, string OrderBy, int LimitStart, int LimitCount)
{
return(null);
}
/// <summary>
/// 打开表或视图
/// </summary>
/// <param name="tableOrViewName"></param>
/// <param name="fieldCollent"></param>
/// <param name="condition"></param>
/// <param name="orderBy"></param>
/// <param name="limitStart"></param>
/// <param name="limitCount"></param>
/// <returns></returns>
public DataTable Open(string tableOrViewName, FieldCollect fieldCollent, string condition, string orderBy, int limitStart, int limitCount)
{
return(this.__Open(tableOrViewName, fieldCollent, condition, orderBy, limitStart, limitCount));
}
/// <summary>
/// Update
/// </summary>
/// <param name="TableName"></param>
/// <param name="fieldCollent"></param>
/// <param name="fieldValueCollect"></param>
/// <param name="Condition"></param>
/// <returns></returns>
protected override long __Update(string TableName, FieldCollect fieldCollent, FieldValueCollect fieldValueCollect, string Condition)
{
if (DatabaseReadOnlyState)
{
return(-9999);
}
if ((fieldCollent == null) || (fieldCollent.Count < 1))
{
throw new Exception("the fieldCollent can't is null.");
}
if ((fieldValueCollect == null) || (fieldValueCollect.Count < 1))
{
throw new Exception("the fieldValueCollect can't is null.");
}
if (fieldCollent.Count != fieldValueCollect.Count)
{
throw new Exception("fieldCollent and fieldValueCollect's Number of inconsistencies.");
}
TableName = ReplaceDefinitionForObjectName(TableName);
string CommandString = "update " + TableName + " set ";
int ParameterCount = 0;
for (int i = 0; i < fieldCollent.Count; i++)
{
string FieldName = ReplaceDefinitionForObjectName(fieldCollent[i]);
object FieldValue = fieldValueCollect[i];
CommandString += ((i > 0) ? ", " : "") + FieldName + " = ";
if (FieldValue is FieldValueCalculate)
{
CommandString += ((FieldValueCalculate)FieldValue).ToString();
}
else
{
CommandString += "@p" + ParameterCount.ToString();
ParameterCount++;
}
}
Condition = Condition.Trim();
Condition = String.IsNullOrEmpty(Condition) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(Condition));
if (!String.IsNullOrEmpty(Condition))
{
CommandString += " where " + Condition;
}
CommandString += "; select ifnull(changes(), -99999999);";
SQLiteCommand Cmd = new SQLiteCommand(CommandString, (SQLiteConnection)connection);
ParameterCount = 0;
for (int i = 0; i < fieldValueCollect.Count; i++)
{
object FieldValue = fieldValueCollect[i];
if (FieldValue is FieldValueCalculate)
{
continue;
}
SQLiteParameter p = new SQLiteParameter("@p" + ParameterCount.ToString(), (FieldValue == null ? System.DBNull.Value : (String.IsNullOrEmpty(FieldValue.ToString()) ? System.DBNull.Value : FieldValue)));
Cmd.Parameters.Add(p);
ParameterCount++;
}
if (transcation != null)
{
Cmd.Transaction = (SQLiteTransaction)transcation;
}
WriteCommandToLogger(4, CommandString + "\tParameterCount: " + ParameterCount.ToString());
object objResult = Cmd.ExecuteScalar();
long Result = System.Convert.ToInt64(objResult);
if (Result == -99999999)
{
return(0);
}
return(Result);
}
/// <summary>
/// Open
/// </summary>
/// <param name="TableOrViewName"></param>
/// <param name="fieldCollent"></param>
/// <param name="Condition"></param>
/// <param name="OrderBy"></param>
/// <param name="LimitStart"></param>
/// <param name="LimitCount"></param>
/// <returns></returns>
protected override DataTable __Open(string TableOrViewName, FieldCollect fieldCollent, string Condition, string OrderBy, int LimitStart, int LimitCount)
{
TableOrViewName = ReplaceDefinitionForObjectName(TableOrViewName);
string CommandString = "select ";
if ((fieldCollent == null) || (fieldCollent.Count < 1))
{
CommandString += "* ";
}
else
{
for (int i = 0; i < fieldCollent.Count; i++)
{
string FieldName = fieldCollent[i].Trim(new char[] { ' ', ' ', '\t', '\r', '\n', '\v', '\f' });
if (FieldName != "*")
{
FieldName = ReplaceDefinitionForObjectName(FieldName);
}
CommandString += (FieldName + ((i == (fieldCollent.Count - 1)) ? " " : ", "));
}
}
CommandString += "from " + TableOrViewName + " ";
Condition = Condition.Trim();
Condition = String.IsNullOrEmpty(Condition) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(Condition));
if (!String.IsNullOrEmpty(Condition))
{
CommandString += "where " + Condition + " ";
}
OrderBy = OrderBy.Trim();
OrderBy = String.IsNullOrEmpty(OrderBy) ? "" : FilteSqlInfusionForCondition(ReplaceDefinitionForStatement(OrderBy));
if (!String.IsNullOrEmpty(OrderBy))
{
CommandString += "order by " + OrderBy + " ";
}
if ((LimitStart >= 0) && (LimitCount > 0))
{
CommandString += "limit " + LimitStart.ToString() + ", " + LimitCount.ToString();
}
WriteCommandToLogger(1, CommandString);
DataTable dt = new DataTable();
SQLiteDataAdapter da = new SQLiteDataAdapter(CommandString, (SQLiteConnection)connection);
if (transcation != null)
{
da.SelectCommand.Transaction = (SQLiteTransaction)transcation;
}
da.Fill(dt);
return(dt);
}
/// <summary>
/// 插入记录
/// </summary>
/// <param name="TableName"></param>
/// <param name="fieldCollent"></param>
/// <param name="fieldValueCollect"></param>
/// <returns></returns>
public long Insert(string TableName, FieldCollect fieldCollent, FieldValueCollect fieldValueCollect)
{
return(this.__Insert(TableName, fieldCollent, fieldValueCollect));
}
