[Category ("NotWorking")] // Requires newer sqlite than is on wrench
public void UpdateResetRowErrorCorrectly ()
{
const string connectionString = "URI = file::memory:; Version = 3";
using (var dbConnection = new SqliteConnection (connectionString)) {
dbConnection.Open ();
using (var cmd = dbConnection.CreateCommand ()) {
cmd.CommandText = "CREATE TABLE data (id PRIMARY KEY, name TEXT)";
cmd.ExecuteNonQuery ();
}
var ts = dbConnection.BeginTransaction ();
var da = new SqliteDataAdapter ("SELECT * FROM data", dbConnection);
var builder = new SqliteCommandBuilder (da);
da.UpdateCommand = builder.GetUpdateCommand ();
da.UpdateCommand.Transaction = ts;
var ds1 = new DataSet ();
da.Fill (ds1, "data");
var table = ds1.Tables [0];
var row = table.NewRow ();
row ["id"] = 10;
row ["name"] = "Bart";
table.Rows.Add (row);
var ds2 = ds1.GetChanges ();
da.Update (ds2, "data");
Assert.IsFalse (ds2.HasErrors);
}
}
public void XimarinBugzillaBug853Test()
{
const string connectionString = "URI = file:./SqliteTest.db; Version = 3";//will be in System.Data directory
SqliteConnection dbConnection = new SqliteConnection(connectionString);
dbConnection.Open();
SqliteCommand ClearTableEntry=new SqliteCommand("DELETE FROM Primus;",dbConnection);
ClearTableEntry.ExecuteNonQuery();
SqliteDataAdapter sqliteDataAdapter = new SqliteDataAdapter("SELECT * FROM primus", dbConnection);
SqliteCommandBuilder builder = new SqliteCommandBuilder(sqliteDataAdapter);
sqliteDataAdapter.InsertCommand = builder.GetInsertCommand();
sqliteDataAdapter.DeleteCommand = builder.GetDeleteCommand();
DataSet dataSet = new DataSet();
sqliteDataAdapter.Fill(dataSet, "Primus");//reset
DataRow rowToBeAdded = dataSet.Tables["Primus"].NewRow();
rowToBeAdded["id"] = 123;
rowToBeAdded["name"] = "Name";//not null primary key
rowToBeAdded["value"] = 777;
dataSet.Tables["Primus"].Rows.Add(rowToBeAdded);
sqliteDataAdapter.Update (dataSet, "Primus");
//This would fail with NULL constraint violation in bug
//report. Because before the patch, it would create
//a new record with all fields being null-- if the
//exception rises, test fails
sqliteDataAdapter.Update (dataSet, "Primus");
dbConnection.Close();
dbConnection = null;
}
private static DataTable SaveDynamicEntity(tgDataRequest request)
{
bool needToDelete = request.EntitySavePacket.RowState == tgDataRowState.Deleted;
DataTable dataTable = CreateDataTable(request);
using (SqliteDataAdapter da = new SqliteDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SqliteCommand cmd = null;
switch (request.EntitySavePacket.RowState)
{
case tgDataRowState.Added:
cmd = da.InsertCommand = Shared.BuildDynamicInsertCommand(request, request.EntitySavePacket);
SetModifiedValues(request, request.EntitySavePacket, row);
break;
case tgDataRowState.Modified:
cmd = da.UpdateCommand = Shared.BuildDynamicUpdateCommand(request, request.EntitySavePacket);
SetOriginalValues(request, request.EntitySavePacket, row, false);
SetModifiedValues(request, request.EntitySavePacket, row);
row.AcceptChanges();
row.SetModified();
break;
case tgDataRowState.Deleted:
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request);
SetOriginalValues(request, request.EntitySavePacket, row, true);
row.AcceptChanges();
row.Delete();
break;
}
if (!needToDelete && request.Properties != null)
{
request.Properties["tgDataRequest"] = request;
request.Properties["esEntityData"] = request.EntitySavePacket;
dataTable.ExtendedProperties["props"] = request.Properties;
}
DataRow[] singleRow = new DataRow[1];
singleRow[0] = row;
try
{
if (!request.IgnoreComputedColumns)
{
da.RowUpdated += new EventHandler<System.Data.Common.RowUpdatedEventArgs>(OnRowUpdated);
}
tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
#region Profiling
if (sTraceHandler != null)
{
using (tgTraceArguments esTrace = new tgTraceArguments(request, cmd, request.EntitySavePacket, "SaveEntityDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
}
finally
{
tgTransactionScope.DeEnlist(cmd);
}
if (request.EntitySavePacket.RowState != tgDataRowState.Deleted && cmd.Parameters != null)
{
foreach (SqliteParameter param in cmd.Parameters)
{
switch (param.Direction)
{
case ParameterDirection.Output:
case ParameterDirection.InputOutput:
request.EntitySavePacket.CurrentValues[param.SourceColumn] = param.Value;
break;
}
}
}
cmd.Dispose();
}
return dataTable;
}
private static DataTable SaveDynamicCollection_Deletes(tgDataRequest request)
{
SqliteCommand cmd = null;
DataTable dataTable = CreateDataTable(request);
using (tgTransactionScope scope = new tgTransactionScope())
{
using (SqliteDataAdapter da = new SqliteDataAdapter())
{
da.AcceptChangesDuringUpdate = false;
da.ContinueUpdateOnError = request.ContinueUpdateOnError;
try
{
cmd = da.DeleteCommand = Shared.BuildDynamicDeleteCommand(request);
tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
DataRow[] singleRow = new DataRow[1];
// Delete each record
foreach (tgEntitySavePacket packet in request.CollectionSavePacket)
{
DataRow row = dataTable.NewRow();
dataTable.Rows.Add(row);
SetOriginalValues(request, packet, row, true);
row.AcceptChanges();
row.Delete();
singleRow[0] = row;
#region Profiling
if (sTraceHandler != null)
{
using (tgTraceArguments esTrace = new tgTraceArguments(request, cmd, packet, "SaveCollectionDynamic", System.Environment.StackTrace))
{
try
{
da.Update(singleRow);
}
catch (Exception ex)
{
esTrace.Exception = ex.Message;
throw;
}
}
}
else
#endregion Profiling
{
da.Update(singleRow);
}
if (row.HasErrors)
{
request.FireOnError(packet, row.RowError);
}
dataTable.Rows.Clear(); // ADO.NET won't let us reuse the same DataRow
}
}
finally
{
tgTransactionScope.DeEnlist(cmd);
cmd.Dispose();
}
}
scope.Complete();
}
return request.Table;
}
void InitData()
{
string cs = "URI=file:test.db";
_dataTable=new DataTable();
// createData();
string stm = "SELECT * FROM Cars";
con = new SqliteConnection(cs);
con.Open();
ds = new DataSet();
SqliteCommandBuilder cmdBuilder;
da = new SqliteDataAdapter(stm, con);
da.Fill(ds, "Cars");
cmdBuilder = new SqliteCommandBuilder(da);
// SqliteCommand insCmd = cmdBuilder.GetInsertCommand();
// INSERT INTO [Cars] ([Name], [Note]) VALUES (@param1, @param2)
dgv.DataSource = ds.Tables["Cars"];
//BindingSource bs=ds.Tables["Cars"];
//dgv.DataSource=bs;
addRow("audi","another note");
addRowSQL("audi sql","another note");
da.Update(ds.Tables[0]);
}
private void UpdateDatabase()
{
try
{
if (File.Exists("data/db/players.db"))
{
File.Move("data/db/players.db", "data/db/players_old.db");
using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\players_old.db"))
{
old.Open();
DataTable table = new DataTable("players");
using (SqliteDataAdapter adapter = new SqliteDataAdapter())
{
adapter.SelectCommand = new SqliteCommand("SELECT * FROM players", old);
adapter.Fill(table);
}
foreach (DataRow row in table.Rows)
{
row.SetAdded();
}
using (SqliteDataAdapter adapter = new SqliteDataAdapter())
{
adapter.SelectCommand = new SqliteCommand("SELECT * FROM players", connection);
adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true);
adapter.Update(table);
}
}
}
if (File.Exists("data/db/hosts.db"))
{
File.Move("data/db/hosts.db", "data/db/hosts_old.db");
using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\hosts_old.db"))
{
old.Open();
DataTable table = new DataTable("hosts");
using (SqliteDataAdapter adapter = new SqliteDataAdapter())
{
adapter.SelectCommand = new SqliteCommand("SELECT * FROM hosts", old);
adapter.Fill(table);
}
foreach (DataRow row in table.Rows)
{
row.SetAdded();
}
using (SqliteDataAdapter adapter = new SqliteDataAdapter())
{
adapter.SelectCommand = new SqliteCommand("SELECT * FROM hosts", connection);
adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true);
adapter.Update(table);
}
}
}
if (File.Exists("data/db/comments.db"))
{
File.Move("data/db/comments.db", "data/db/comments_old.db");
using (SqliteConnection old = new SqliteConnection(@"Data Source=data\db\comments_old.db"))
{
old.Open();
DataTable table = new DataTable("comments");
using (SqliteDataAdapter adapter = new SqliteDataAdapter())
{
adapter.SelectCommand = new SqliteCommand("SELECT * FROM comments", old);
adapter.Fill(table);
}
foreach (DataRow row in table.Rows)
{
row.SetAdded();
}
using (SqliteDataAdapter adapter = new SqliteDataAdapter())
{
adapter.SelectCommand = new SqliteCommand("SELECT * FROM comments", connection);
adapter.InsertCommand = new SqliteCommandBuilder(adapter).GetInsertCommand(true);
adapter.Update(table);
}
}
}
}
catch(Exception e)
{
using (StreamWriter writer = new StreamWriter("migrate_error.txt"))
{
writer.WriteLine(e.Message);
writer.WriteLine(e.StackTrace);
}
}
}
/// <summary>
/// 更新数据集中数据到数据库
/// </summary>
/// <param name="insertCommand">insert语句</param>
/// <param name="deleteCommand">delete语句</param>
/// <param name="updateCommand">update语句</param>
/// <param name="dataSet">要更新的DataSet</param>
/// <param name="tableName">数据集中要更新的table名</param>
public static void UpdateDataset (SqliteCommand insertCommand, SqliteCommand deleteCommand, SqliteCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null)
throw new ArgumentNullException ("insertCommand");
if (deleteCommand == null)
throw new ArgumentNullException ("deleteCommand");
if (updateCommand == null)
throw new ArgumentNullException ("updateCommand");
if (tableName == null || tableName.Length == 0)
throw new ArgumentNullException ("tableName");
// Create a SQLiteDataAdapter, and dispose of it after we are done
using (SqliteDataAdapter dataAdapter = new SqliteDataAdapter ()) {
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update (dataSet, tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges ();
}
}
