Example #1
0
        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();
        }
Example #2
0
        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();
        }