/// <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;
}
}
public void Bug1010788UpdateRowSource()
{
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (id SERIAL PRIMARY KEY, name TEXT)", conn);
var command = new NpgsqlCommand("SELECT * FROM data", conn);
Assert.AreEqual(UpdateRowSource.Both, command.UpdatedRowSource);
var cmdBuilder = new NpgsqlCommandBuilder();
var da = new NpgsqlDataAdapter(command);
cmdBuilder.DataAdapter = da;
Assert.IsNotNull(da.SelectCommand);
Assert.IsNotNull(cmdBuilder.DataAdapter);
NpgsqlCommand updateCommand = cmdBuilder.GetUpdateCommand();
Assert.AreEqual(UpdateRowSource.None, updateCommand.UpdatedRowSource);
}
}
public void Bug1010788UpdateRowSource()
{
if (((int)BackendProtocolVersion) < 3)
Assert.Ignore("Don't have the right metadata with protocol version 2");
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var command = new NpgsqlCommand("select * from data", conn);
Assert.AreEqual(UpdateRowSource.Both, command.UpdatedRowSource);
var cmdBuilder = new NpgsqlCommandBuilder();
var da = new NpgsqlDataAdapter(command);
cmdBuilder.DataAdapter = da;
Assert.IsNotNull(da.SelectCommand);
Assert.IsNotNull(cmdBuilder.DataAdapter);
NpgsqlCommand updateCommand = cmdBuilder.GetUpdateCommand();
Assert.AreEqual(UpdateRowSource.None, updateCommand.UpdatedRowSource);
}
}
public void Bug1010788UpdateRowSource()
{
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var command = new NpgsqlCommand("select * from data", conn);
Assert.AreEqual(UpdateRowSource.Both, command.UpdatedRowSource);
var cmdBuilder = new NpgsqlCommandBuilder();
var da = new NpgsqlDataAdapter(command);
cmdBuilder.DataAdapter = da;
Assert.IsNotNull(da.SelectCommand);
Assert.IsNotNull(cmdBuilder.DataAdapter);
NpgsqlCommand updateCommand = cmdBuilder.GetUpdateCommand();
Assert.AreEqual(UpdateRowSource.None, updateCommand.UpdatedRowSource);
}
}
public void GetUpdateCommand()
{
using (var da = new NpgsqlDataAdapter("SELECT field_pk, field_int4 FROM data", Conn))
{
using (var cb = new NpgsqlCommandBuilder(da))
{
var updateCommand = cb.GetUpdateCommand(true);
da.UpdateCommand = updateCommand;
var ds = new DataSet();
da.Fill(ds);
var table = ds.Tables[0];
var row = table.Rows.Add();
row["field_pk"] = 1;
row["field_int4"] = 1;
da.Update(ds);
row["field_int4"] = 2;
da.Update(ds);
row.Delete();
da.Update(ds);
}
}
}
protected void OnExecuteActionActivated(object sender, System.EventArgs e)
{
string connectionString = "Server=localhost;Database=dbprueba2;User Id=dbprueba;Password=sistemas;";
IDbConnection dbConnection = new NpgsqlConnection(connectionString);
IDbCommand selectCommand = dbConnection.CreateCommand();
selectCommand.CommandText = "select * from articulos";
IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder((NpgsqlDataAdapter)dbDataAdapter);
dbConnection.Open();
DataSet dataSet = new DataSet();
dbDataAdapter.Fill(dataSet);
Console.WriteLine("Table.Count={0}\n", dataSet.Tables.Count);
foreach (DataTable dataTable in dataSet.Tables)
show (dataTable);
DataRow dataRow = dataSet.Tables[0].Rows[0];
dataRow["nombre"] = DateTime.Now.ToString();
Console.WriteLine ("\nTabla con los cambios:");
show (dataSet.Tables[0]);
IDbCommand comando = commandBuilder.GetUpdateCommand (dataSet.Tables[0].Rows[0]);
//COSAS DE HOY DIA 18
comando.ExecuteNonQuery ();
}
