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]); }