|
public GetInsertCommand ( ) : |
||
| Результат | ||
/// <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;
}
}
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;
}
}