private void DeleteObjects(string Table, TableInfo.Params ParametersIn)
{
DbCommand cmd;
DbDataReader rdr;
string sql;
if(!QSMain.ProjectTables.ContainsKey(Table))
{
ErrorString = "Нет описания для таблицы " + Table;
logger.Error(ErrorString);
ErrorHappens = true;
return;
}
QSMain.CheckConnectionAlive();
if(QSMain.ProjectTables[Table].DeleteItems.Count > 0)
{
foreach(KeyValuePair<string, TableInfo.DeleteDependenceItem> pair in QSMain.ProjectTables[Table].DeleteItems)
{
if(QSMain.ProjectTables[pair.Key].DeleteItems.Count > 0 || QSMain.ProjectTables[pair.Key].ClearItems.Count > 0)
{
sql = "SELECT * FROM " +pair.Key + " " + pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
rdr = cmd.ExecuteReader();
List<TableInfo.Params> ReadedData = new List<TableInfo.Params>();
string IntFieldName = QSMain.ProjectTables[pair.Key].PrimaryKey.ParamInt;
string StrFieldName = QSMain.ProjectTables[pair.Key].PrimaryKey.ParamStr;
while(rdr.Read())
{
TableInfo.Params OutParam = new TableInfo.Params();
if(IntFieldName != "")
OutParam.ParamInt = rdr.GetInt32(rdr.GetOrdinal(IntFieldName));
if(StrFieldName != "")
OutParam.ParamStr = rdr.GetString(rdr.GetOrdinal(StrFieldName));
ReadedData.Add(OutParam);
}
rdr.Close ();
foreach(TableInfo.Params row in ReadedData)
{
DeleteObjects (pair.Key, row);
}
}
sql = "DELETE FROM " + pair.Key + " " + pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
cmd.ExecuteNonQuery();
}
}
if(QSMain.ProjectTables[Table].ClearItems.Count > 0)
{
foreach(KeyValuePair<string, TableInfo.ClearDependenceItem> pair in QSMain.ProjectTables[Table].ClearItems)
{
sql = "UPDATE " + pair.Key + " SET ";
bool first = true;
foreach (string FieldName in pair.Value.ClearFields)
{
if(!first)
sql += ", ";
sql += FieldName + " = NULL ";
first = false;
}
sql += pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
cmd.ExecuteNonQuery ();
}
}
sql = "DELETE FROM " + Table + " WHERE ";
bool FirstKey = true;
if(QSMain.ProjectTables[Table].PrimaryKey.ParamInt != "")
{
sql += QSMain.ProjectTables[Table].PrimaryKey.ParamInt + " = @IntParam ";
FirstKey = false;
}
if(QSMain.ProjectTables[Table].PrimaryKey.ParamStr != "")
{
if(!FirstKey)
sql += "AND ";
sql += QSMain.ProjectTables[Table].PrimaryKey.ParamStr + " = @StrParam ";
FirstKey = false;
}
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
TableInfo.PrimaryKeys TempParams = new TableInfo.PrimaryKeys(
QSMain.ProjectTables[Table].PrimaryKey.ParamInt != "" ? "@IntParam" : "",
QSMain.ProjectTables[Table].PrimaryKey.ParamStr != "" ? "@StrParam" : ""
);
AddParameters(cmd, TempParams, ParametersIn);
cmd.ExecuteNonQuery();
}
private void DeleteObjects(string Table, TableInfo.Params ParametersIn)
{
DbCommand cmd;
DbDataReader rdr;
string sql;
if (!QSMain.ProjectTables.ContainsKey(Table))
{
ErrorString = "Нет описания для таблицы " + Table;
logger.Error(ErrorString);
ErrorHappens = true;
return;
}
QSMain.CheckConnectionAlive();
if (QSMain.ProjectTables[Table].DeleteItems.Count > 0)
{
foreach (KeyValuePair <string, TableInfo.DeleteDependenceItem> pair in QSMain.ProjectTables[Table].DeleteItems)
{
if (QSMain.ProjectTables[pair.Key].DeleteItems.Count > 0 || QSMain.ProjectTables[pair.Key].ClearItems.Count > 0)
{
sql = "SELECT * FROM " + pair.Key + " " + pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
rdr = cmd.ExecuteReader();
List <TableInfo.Params> ReadedData = new List <TableInfo.Params>();
string IntFieldName = QSMain.ProjectTables[pair.Key].PrimaryKey.ParamInt;
string StrFieldName = QSMain.ProjectTables[pair.Key].PrimaryKey.ParamStr;
while (rdr.Read())
{
TableInfo.Params OutParam = new TableInfo.Params();
if (IntFieldName != "")
{
OutParam.ParamInt = rdr.GetInt32(rdr.GetOrdinal(IntFieldName));
}
if (StrFieldName != "")
{
OutParam.ParamStr = rdr.GetString(rdr.GetOrdinal(StrFieldName));
}
ReadedData.Add(OutParam);
}
rdr.Close();
foreach (TableInfo.Params row in ReadedData)
{
DeleteObjects(pair.Key, row);
}
}
sql = "DELETE FROM " + pair.Key + " " + pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
cmd.ExecuteNonQuery();
}
}
if (QSMain.ProjectTables[Table].ClearItems.Count > 0)
{
foreach (KeyValuePair <string, TableInfo.ClearDependenceItem> pair in QSMain.ProjectTables[Table].ClearItems)
{
sql = "UPDATE " + pair.Key + " SET ";
bool first = true;
foreach (string FieldName in pair.Value.ClearFields)
{
if (!first)
{
sql += ", ";
}
sql += FieldName + " = NULL ";
first = false;
}
sql += pair.Value.sqlwhere;
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
AddParameters(cmd, pair.Value.SqlParam, ParametersIn);
cmd.ExecuteNonQuery();
}
}
sql = "DELETE FROM " + Table + " WHERE ";
bool FirstKey = true;
if (QSMain.ProjectTables[Table].PrimaryKey.ParamInt != "")
{
sql += QSMain.ProjectTables[Table].PrimaryKey.ParamInt + " = @IntParam ";
FirstKey = false;
}
if (QSMain.ProjectTables[Table].PrimaryKey.ParamStr != "")
{
if (!FirstKey)
{
sql += "AND ";
}
sql += QSMain.ProjectTables[Table].PrimaryKey.ParamStr + " = @StrParam ";
FirstKey = false;
}
cmd = QSMain.ConnectionDB.CreateCommand();
cmd.CommandText = sql;
TableInfo.PrimaryKeys TempParams = new TableInfo.PrimaryKeys(
QSMain.ProjectTables[Table].PrimaryKey.ParamInt != "" ? "@IntParam" : "",
QSMain.ProjectTables[Table].PrimaryKey.ParamStr != "" ? "@StrParam" : ""
);
AddParameters(cmd, TempParams, ParametersIn);
cmd.ExecuteNonQuery();
}