GetInsertCommand() публичный Метод

Gets the automatically generated NpgsqlCommand object required to perform insertions at the data source.
public GetInsertCommand ( ) : NpgsqlCommand
Результат NpgsqlCommand
Пример #1
0
        /// <summary>
        /// DataSet ---NpgsqlDataAdapter ---Database
        /// </summary>
        public NpgsqlDataAdapter GetDataAdapter(string strSQL)
        {
            // Use NpgsqlCommandBuilder and SelectCommand--->Auto generate InsertCommand UpdateCommand DeleteCommand
              try
              {
              m_gCommnd.CommandText = strSQL;
              NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(m_gCommnd);//associate
              //InsertCommand  UpdateCommand DeleteCommand SelectCommand = m_sqlCommand;

              // Initialize the InsertCommand UpdateCommand DeleteCommand of NpgsqlDataAdapter by NpgsqlCommandBuilder.
              NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(dataAdapter);
              dataAdapter.InsertCommand = cb.GetInsertCommand();
              dataAdapter.UpdateCommand = cb.GetUpdateCommand();
              dataAdapter.DeleteCommand = cb.GetDeleteCommand();
              return dataAdapter;
              }
              catch (System.Exception ex)
              {
              MessageBox.Show(ex.ToString());
              return null;
              }
        }
Пример #2
0
        static void Main(string[] args)
        {
            try
            {
                var options = new Options();
                if (CommandLine.Parser.Default.ParseArguments(args, options))
                {

                    string oldName = options.OldName;
                    string newName = options.NewName;
                    string newSlaveOid = "";
                    string newSlaveAddress = options.NewSlaveAddress;
                    string oldSlaveOid = "";
                    string findStr = options.FindStr;
                    string replStr = options.ReplStr;
                    Dictionary<string, string> findreplace = new Dictionary<string, string>();
                    var find = findStr.Split(',');
                    var replace = replStr.Split(',');
                    if (find.Length != replace.Length)
                    {
                        throw new Exception("Must have equal numbers of find and replace tokens");
                    }
                    for (int i = 0; i < find.Length; i++)
                    {
                        findreplace.Add(find[i], replace[i]);
                    }

                    DataSet ds = new DataSet();
                    DataTable dt = new DataTable();

                    //our database connection string
                    string connectionString = string.Format("Server=localhost;port=5432;User Id={0};Password={1};Database=SYSCFG;", options.Username, options.Password);
                    NpgsqlConnection conn = new NpgsqlConnection(connectionString);
                    PrintVerbose("Connecting to database...", options.Verbose);
                    conn.Open();
                    //find the slave with the name that we want to copy
                    string sql = "SELECT * FROM protocol_slave_dnp WHERE entity_name = '" + oldName + "'";

                    NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                    //get the slave data
                    ds.Reset();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    //copy the slave
                    PrintVerbose("Creating the new IED...", options.Verbose);
                    DataRow row = dt.NewRow();
                    row.ItemArray = dt.Rows[0].ItemArray.Clone() as object[];
                    oldSlaveOid = row["oid"] as string; //remember the oid, we'll need it later
                    row["oid"] = newSlaveOid = GenerateOid();
                    row["entity_name"] = newName;
                    row["slave_address"] = newSlaveAddress;
                    dt.Rows.Add(row);
                    NpgsqlCommandBuilder comb = new NpgsqlCommandBuilder(da);
                    NpgsqlCommand insCommand = comb.GetInsertCommand(true);
                    da.InsertCommand = insCommand;
                    //insert the slave
                    da.Update(ds);
                    PrintVerbose("Done.", options.Verbose);
                    ////////////////////////////////////////////////////////
                    //now find the protocol address groups and copy them too
                    PrintVerbose("Getting address groups", options.Verbose);
                    sql = "SELECT * FROM protocol_address_grp_dnp WHERE protocol_slave_oid = '" + oldSlaveOid + "'";
                    da = new NpgsqlDataAdapter(sql, conn);
                    ds.Reset();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    //this is a map of old oids to new ids, so we can copy all the points in the address groups later on
                    Dictionary<string, string> prAddrGrpOids = new Dictionary<string, string>();
                    List<DataRow> rowsToAdd = new List<DataRow>();
                    //get all the address groups and make a copy
                    PrintVerbose("Copying address groups...", options.Verbose);
                    foreach (DataRow r in dt.Rows)
                    {
                        prAddrGrpOids.Add(r["oid"] as string, GenerateOid());
                        row = dt.NewRow();
                        row.ItemArray = r.ItemArray.Clone() as object[];
                        row["oid"] = prAddrGrpOids[r["oid"] as string];
                        row["prot_poll_grp_oid"] = null;
                        row["protocol_slave_oid"] = newSlaveOid;
                        rowsToAdd.Add(row);
                    }
                    //then insert the copies back into the database
                    foreach (DataRow r in rowsToAdd)
                    {
                        dt.Rows.Add(r);
                    }
                    comb = new NpgsqlCommandBuilder(da);
                    insCommand = comb.GetInsertCommand(true);
                    da.InsertCommand = insCommand;
                    da.Update(ds);
                    PrintVerbose("Done.", options.Verbose);
                    ///////////////////////////////////////////////////////////////
                    //get a list of points from protocol_address_slave that match the above protocol address group
                    PrintVerbose("Getting address group points", options.Verbose);
                    sql = "SELECT * FROM protocol_address_dnp WHERE prot_addr_grp_oid IN(" + InifyKeys(prAddrGrpOids) + ")";
                    da = new NpgsqlDataAdapter(sql, conn);
                    ds.Reset();
                    da.Fill(ds);
                    dt = ds.Tables[0];

                    //this is a map of old oids to new ids, so we can copy all the points in the address groups later on
                    Dictionary<string, string> prPointAddrOids = new Dictionary<string, string>();
                    Dictionary<string, string> prPointOids = new Dictionary<string, string>();
                    rowsToAdd = new List<DataRow>();
                    //get all the points we need to copy, and copy the new address group-point combos in advance
                    PrintVerbose("Copying group-point relationships...", options.Verbose);
                    foreach (DataRow r in dt.Rows)
                    {
                        prPointAddrOids.Add(r["oid"] as string, GenerateOid());
                        prPointOids.Add(r["slave_point_oid"] as string, GenerateOid());
                        row = dt.NewRow();
                        row.ItemArray = r.ItemArray.Clone() as object[];
                        row["oid"] = GenerateOid(); //I dont think we care about this oid
                        row["prot_addr_grp_oid"] = prAddrGrpOids[r["prot_addr_grp_oid"] as string];
                        row["slave_point_oid"] = prPointOids[r["slave_point_oid"] as string];
                        rowsToAdd.Add(row);
                    }
                    //add new rows
                    foreach (DataRow r in rowsToAdd)
                    {
                        dt.Rows.Add(r);
                    }
                    comb = new NpgsqlCommandBuilder(da);
                    insCommand = comb.GetInsertCommand(true);
                    da.InsertCommand = insCommand;
                    da.Update(ds);
                    PrintVerbose("Done", options.Verbose);
                    //////////////////////////////////////////////////////////////
                    //copy all the analog input points
                    PrintVerbose("Copying AIs...", options.Verbose);
                    sql = "SELECT * FROM point_analog_input WHERE oid IN(" + InifyKeys(prPointOids) + ")";
                    da = new NpgsqlDataAdapter(sql, conn);
                    ds.Reset();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    //copy of points to add
                    rowsToAdd = new List<DataRow>();
                    //get all the address groups and make a copy
                    foreach (DataRow r in dt.Rows)
                    {
                        row = dt.NewRow();
                        row.ItemArray = r.ItemArray.Clone() as object[];
                        row["oid"] = prPointOids[r["oid"] as string];
                        //replace tokens in point names
                        foreach (var kvp in findreplace)
                        {
                            row["entity_name"] = (row["entity_name"] as string).Replace(kvp.Key, kvp.Value);
                        }
                        rowsToAdd.Add(row);
                    }
                    //then insert the copies back into the database
                    foreach (DataRow r in rowsToAdd)
                    {
                        dt.Rows.Add(r);
                    }
                    comb = new NpgsqlCommandBuilder(da);
                    insCommand = comb.GetInsertCommand(true);
                    da.InsertCommand = insCommand;
                    da.Update(ds);
                    PrintVerbose("Done", options.Verbose);
                    //////////////////////////////////////////////////////////////
                    //copy all the analog output points
                    PrintVerbose("Copying AOs...", options.Verbose);
                    sql = "SELECT * FROM point_analog_output WHERE oid IN(" + InifyKeys(prPointOids) + ")";
                    da = new NpgsqlDataAdapter(sql, conn);
                    ds.Reset();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    //copy of points to add
                    rowsToAdd = new List<DataRow>();
                    //get all the address groups and make a copy
                    foreach (DataRow r in dt.Rows)
                    {
                        row = dt.NewRow();
                        row.ItemArray = r.ItemArray.Clone() as object[];
                        row["oid"] = prPointOids[r["oid"] as string];
                        //replace tokens in point names
                        foreach (var kvp in findreplace)
                        {
                           row["entity_name"] = (row["entity_name"] as string).Replace(kvp.Key, kvp.Value);
                        } 
                        rowsToAdd.Add(row);
                    }
                    //then insert the copies back into the database
                    foreach (DataRow r in rowsToAdd)
                    {
                        dt.Rows.Add(r);
                    }
                    comb = new NpgsqlCommandBuilder(da);
                    insCommand = comb.GetInsertCommand(true);
                    da.InsertCommand = insCommand;
                    da.Update(ds);
                    PrintVerbose("Done", options.Verbose);
                    //////////////////////////////////////////////////////////////
                    //copy all the digital input points
                    PrintVerbose("Copying DIs...", options.Verbose);
                    sql = "SELECT * FROM point_digital_input WHERE oid IN(" + InifyKeys(prPointOids) + ")";
                    da = new NpgsqlDataAdapter(sql, conn);
                    ds.Reset();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    //copy of points to add
                    rowsToAdd = new List<DataRow>();
                    //get all the address groups and make a copy
                    foreach (DataRow r in dt.Rows)
                    {
                        row = dt.NewRow();
                        row.ItemArray = r.ItemArray.Clone() as object[];
                        row["oid"] = prPointOids[r["oid"] as string];
                        //replace tokens in point names
                        foreach (var kvp in findreplace)
                        {
                           row["entity_name"] = (row["entity_name"] as string).Replace(kvp.Key, kvp.Value);
                        } 
                        rowsToAdd.Add(row);
                    }
                    //then insert the copies back into the database
                    foreach (DataRow r in rowsToAdd)
                    {
                        dt.Rows.Add(r);
                    }
                    comb = new NpgsqlCommandBuilder(da);
                    insCommand = comb.GetInsertCommand(true);
                    da.InsertCommand = insCommand;
                    da.Update(ds);
                    PrintVerbose("Done", options.Verbose);

                    //////////////////////////////////////////////////////////////
                    //copy all the digital output points
                    PrintVerbose("Copying DOs...", options.Verbose);
                    sql = "SELECT * FROM point_digital_output WHERE oid IN(" + InifyKeys(prPointOids) + ")";
                    da = new NpgsqlDataAdapter(sql, conn);
                    ds.Reset();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    //copy of points to add
                    rowsToAdd = new List<DataRow>();
                    //get all the address groups and make a copy
                    foreach (DataRow r in dt.Rows)
                    {
                        row = dt.NewRow();
                        row.ItemArray = r.ItemArray.Clone() as object[];
                        row["oid"] = prPointOids[r["oid"] as string];
                        //replace tokens in point names
                        foreach (var kvp in findreplace)
                        {
                           row["entity_name"] = (row["entity_name"] as string).Replace(kvp.Key, kvp.Value);
                        }
                        rowsToAdd.Add(row);
                    }
                    //then insert the copies back into the database
                    foreach (DataRow r in rowsToAdd)
                    {
                        dt.Rows.Add(r);
                    }
                    comb = new NpgsqlCommandBuilder(da);
                    insCommand = comb.GetInsertCommand(true);
                    da.InsertCommand = insCommand;
                    da.Update(ds);
                    PrintVerbose("Done", options.Verbose);
                    PrintVerbose("Complete.", options.Verbose);
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.Write(ex.ToString());
                Console.ForegroundColor = ConsoleColor.White;
            }
        }