/// <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)); }