internal KeyQuery(SqliteConnection cnn, string database, string table, params string[] columns)
{
using (SqliteCommandBuilder builder = new SqliteCommandBuilder())
{
_command = cnn.CreateCommand();
for (int n = 0; n < columns.Length; n++)
{
columns[n] = builder.QuoteIdentifier(columns[n]);
}
}
_command.CommandText = String.Format("SELECT {0} FROM [{1}].[{2}] WHERE ROWID = ?", String.Join(",", columns), database, table);
_command.Parameters.AddWithValue(null, (long)0);
}
internal KeyQuery(SqliteConnection cnn, string database, string table, params string[] columns)
{
using (SqliteCommandBuilder builder = new SqliteCommandBuilder())
{
_command = cnn.CreateCommand();
for (int n = 0; n < columns.Length; n++)
{
columns[n] = builder.QuoteIdentifier(columns[n]);
}
}
_command.CommandText = String.Format("SELECT {0} FROM [{1}].[{2}] WHERE ROWID = ?", String.Join(",", columns), database, table);
_command.Parameters.AddWithValue(null, (long)0);
}
/// <summary>
/// Retrieves foreign key information from the specified set of filters
/// </summary>
/// <param name="strCatalog">An optional catalog to restrict results on</param>
/// <param name="strTable">An optional table to restrict results on</param>
/// <param name="strKeyName">An optional foreign key name to restrict results on</param>
/// <returns>A DataTable with the results of the query</returns>
private DataTable Schema_ForeignKeys(string strCatalog, string strTable, string strKeyName)
{
DataTable tbl = new DataTable("ForeignKeys");
DataRow row;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
tbl.Columns.Add("TABLE_CATALOG", typeof(string));
tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
tbl.Columns.Add("TABLE_NAME", typeof(string));
tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string));
tbl.Columns.Add("IS_DEFERRABLE", typeof(bool));
tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool));
tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string));
tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int));
tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string));
tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string));
tbl.Columns.Add("FKEY_TO_TABLE", typeof(string));
tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string));
if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
tbl.BeginLoadData();
using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this))
using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
{
while (rdTables.Read())
{
if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), true, CultureInfo.InvariantCulture) == 0)
{
try
{
using (SqliteCommandBuilder builder = new SqliteCommandBuilder())
//using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
//using (SqliteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
using (SqliteCommand cmdKey = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
using (SqliteDataReader rdKey = cmdKey.ExecuteReader())
{
while (rdKey.Read())
{
row = tbl.NewRow();
row["CONSTRAINT_CATALOG"] = strCatalog;
row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}", rdTables[2], rdKey.GetInt32(0));
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2));
row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
row["IS_DEFERRABLE"] = false;
row["INITIALLY_DEFERRED"] = false;
row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString());
row["FKEY_TO_CATALOG"] = strCatalog;
row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString());
row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString());
row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1];
if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
tbl.Rows.Add(row);
}
}
}
catch (SqliteException)
{
}
}
}
}
tbl.EndLoadData();
tbl.AcceptChanges();
return tbl;
}
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;
}
[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 override int Update (String table, ContentValues values, String whereClause, params String[] whereArgs)
{
Debug.Assert(!table.IsEmpty());
Debug.Assert(values != null);
var builder = new SqliteCommandBuilder();
builder.SetAllValues = false;
var command = GetUpdateCommand(table, values, whereClause, whereArgs);
var resultCount = -1;
try {
resultCount = (Int32)command.ExecuteNonQuery ();
} catch (Exception ex) {
Log.E(Tag, "Error updating table " + table, ex);
}
return resultCount;
}
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]);
}