Ejemplo n.º 1
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);
 }
Ejemplo n.º 2
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);
 }
Ejemplo n.º 3
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;
    }
Ejemplo n.º 4
0
		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;
		}
Ejemplo n.º 5
0
		[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;
        }
Ejemplo n.º 7
0
    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]);
    }