C# SQLiteDataAdapter.Update方法代码示例

    public static void UpdateTable(string query, SQLiteConnection dbConnection, DataTable table)
    {
      //object maxId = null;
      //if (query.ToLower().Contains("from traffic_object"))
      //  maxId = GetScalar("Select max(obj_id) From traffic_object", dbConnection);

      using (SQLiteTransaction transaction = dbConnection.BeginTransaction())
      {
        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, dbConnection))
        {
          using (SQLiteCommand command = dbConnection.CreateCommand())
          {
            command.Transaction = transaction;
            command.CommandText = query;
            adapter.SelectCommand = command;

            using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder())
            {
              builder.DataAdapter = adapter;
              adapter.Update(table);

              transaction.Commit();
            }
          }
        }
      }
    }

        private void button1_Click_1(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex == 0)
            {
                string numero;
                try
                {

                    numero = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                    string appPath = Path.GetDirectoryName(Application.ExecutablePath);
                    System.Data.SQLite.SQLiteConnection sqlConnection1 =
                                           new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;");

                    System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                    cmd.CommandType = System.Data.CommandType.Text;
                    //comando sql para borrar
                    cmd.CommandText = "DELETE FROM Entradas WHERE [Numero] = " + numero;

                    cmd.Connection = sqlConnection1;

                    sqlConnection1.Open();
                    cmd.ExecuteNonQuery();

                    sqlConnection1.Close();

                    MessageBox.Show("Entrada eliminada exitosamente");

                    appPath = Path.GetDirectoryName(Application.ExecutablePath);
                    string connString = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;";

                    //create the database query
                    string query = "select * from Entradas";

                    //create an OleDbDataAdapter to execute the query
                    System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);

                    //create a command builder
                    System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);

                    //create a DataTable to hold the query results
                    DataTable dTable = new DataTable();

                    //fill the DataTable
                    dAdapter.Fill(dTable);
                    BindingSource bSource = new BindingSource();
                    bSource.DataSource = dTable;
                    dataGridView1.DataSource = bSource;
                    dAdapter.Update(dTable);

                }
                catch
                {
                    MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            string dbConnectionString = @"Data Source=patient.db;Version=3;";
            SQLiteConnection sqlite_connection = new SQLiteConnection(dbConnectionString);

            try
            {
                sqlite_connection.Open();
                string query = "select * from patient_table";

                SQLiteCommand create_command = new SQLiteCommand(query, sqlite_connection);
                create_command.ExecuteNonQuery();
                //SQLiteDataReader dr = create_command.ExecuteReader();

                SQLiteDataAdapter dataAdapt = new SQLiteDataAdapter(query, sqlite_connection);
                //var ds = new DataSet();

                DataTable dt = new DataTable("patient_table");
                //dataAdapt.Fill(ds,"patient_table");
                dataAdapt.Fill(dt);
               // listView1.DataContext = ds.Tables["patient_table"].DefaultView;
                admin_monthly_accounting_datagrid.ItemsSource = dt.DefaultView ;
                dataAdapt.Update(dt);

                sqlite_connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public Form2(string userName)
        {
            InitializeComponent();
            this.btn.Click += new System.EventHandler(this.btn_Click);
            label7.Text = "Hello! "+ userName;
            fill_comboBox();
            fill_Listbox();
            timer1.Start();

            //--- load data in DataGridView
            try
            {
                string Query = "SELECT eid,name,surname,age FROM employeeinfo";
                DataBaseOperation.ConnectToDataBase(dbConnectionString, Query, ref sqliteCon, ref sqliteCmd);

                //--- add data to dataGrid
                SQLiteDataAdapter dataAdp = new SQLiteDataAdapter(sqliteCmd);
                dataTable = new DataTable("employeeinfo");    //name of database
                dataAdp.Fill(dataTable);
                dataGridView1.DataSource = dataTable.DefaultView;
                dataAdp.Update(dataTable);

                sqliteCon.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

 public void SaveDatabase() {
     DataSet changes = DataSet.GetChanges();
     System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("Data Source=" + DB_Connection + ";Version=3;");
     SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM Members", con);
     SQLiteCommandBuilder cb = new SQLiteCommandBuilder(dataAdapter);
     dataAdapter.Update(changes);
     DataSet.AcceptChanges();
     con.Close();
     GC.Collect();
     
 }

        private void button1_Click(object sender, EventArgs e)
        {
            ///create the connection string
            string appPath2 = Path.GetDirectoryName(Application.ExecutablePath);
            ///create the connection string
            string connString = @"Data Source= " + appPath2 + @"\DBUC.s3db ;Version=3;";

            //create the database query
            string query = "SELECT * FROM Usuarios";

            //create an OleDbDataAdapter to execute the query
            System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);

            //create a command builder
            System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);

            //create a DataTable to hold the query results
            DataTable dTable = new DataTable();
            //fill the DataTable
            dAdapter.Fill(dTable);
            dAdapter.Update(dTable);

            bool usuarioexistente = false;
            bool admin = false;
            for (int i = 0; i < dTable.Rows.Count; i++)
            {
                DataRow Row = dTable.Rows[i];

                if (Row["Usuario"].ToString() == textBox1.Text && Row["Contrasena"].ToString() == textBox2.Text)
                {
                    usuarioexistente = true;

                    if (Row["TipoDeUsuario"].ToString() == "Administrador")
                    {
                        admin = true;
                        AdminAvailable(true);
                        this.Close();

                    }

                    break;

                }
            }
            if (usuarioexistente == false)
            {
                MessageBox.Show("Usuario o contraseña incorrecta");
            }
            else if (usuarioexistente == true && admin == false)
            {
                MessageBox.Show("La cuenta utilizada no es de un administrador");
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count != 0)
            {
                DialogResult resultado = MessageBox.Show("Esta seguro que desea eliminar?", "Seguro?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
                if (resultado == DialogResult.Yes)
                {

                    string appPath = Path.GetDirectoryName(Application.ExecutablePath);
                    System.Data.SQLite.SQLiteConnection sqlConnection1 =
                                    new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db");

                    System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                    cmd.CommandType = System.Data.CommandType.Text;

                    cmd.CommandText = "Delete From Proveedor Where [Nombreproveedor] = '" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "'";

                    cmd.Connection = sqlConnection1;

                    sqlConnection1.Open();
                    cmd.ExecuteNonQuery();
                    sqlConnection1.Close();

                    appPath = Path.GetDirectoryName(Application.ExecutablePath);
                    //create the connection string
                    string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";

                    //create the database query
                    string query = "Select * From Proveedor";

                    //create an OleDbDataAdapter to execute the query
                    System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);

                    //create a command builder
                    System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);

                    //create a DataTable to hold the query results
                    DataTable dTable = new DataTable();

                    //fill the DataTable
                    dAdapter.Fill(dTable);
                    BindingSource bSource = new BindingSource();
                    bSource.DataSource = dTable;
                    dataGridView1.DataSource = bSource;
                    dAdapter.Update(dTable);

                }
            }
            else
            {
                MessageBox.Show("Tiene que elegir un articulo para borrarlo");
            }
        }

 public static DataTable LogIn(DataTable dtIn)
 {
     string cmdstring = "select * from userdata";
     SQLiteDataAdapter sa = new SQLiteDataAdapter(cmdstring, objConnection);
     DataTable dtOut = new DataTable();
     sa.Fill(dtOut);
     dtOut.Rows.Add(dtIn.Rows[0].ItemArray);
     SQLiteCommandBuilder objCommandBuilder = new SQLiteCommandBuilder(sa);
     sa.Update(dtOut);
     currentUser = dtIn.Rows[0];
     objConnection.Close();
     return dtIn;
 }

 public void UpdateDatatableQuery(DataTable dt, String query)
 {
     DataTable sdt = new DataTable();
     using (SQLiteCommand command = Conn.CreateCommand())
     {
         command.CommandText = query;
         SQLiteDataAdapter adp = new SQLiteDataAdapter(command);
         adp.Fill(sdt);
         SQLiteCommandBuilder cb = new SQLiteCommandBuilder(adp);
         //SQLiteCommand update = cb.GetUpdateCommand();
         //adp.UpdateCommand = update;
         adp.Update(dt);
     }
 }

 /// <summary>
 /// 修改数据表记录
 /// </summary>
 /// <returns></returns>
 public static bool UpdateTable(DataTable srcTable, string tableName) {
     bool isok = false;
     try {
         SQLiteCommand command = new SQLiteCommand();
         using (SQLiteConnection conn = GetSQLiteConnection()) {
             if (conn.State != ConnectionState.Open) conn.Open();
             command.Connection = conn;
             command.CommandText = "SELECT * FROM " + tableName;
             SQLiteDataAdapter SQLiteDA = new SQLiteDataAdapter(command);
             SQLiteCommandBuilder SQLiteCB = new SQLiteCommandBuilder(SQLiteDA);
             SQLiteDA.InsertCommand = SQLiteCB.GetInsertCommand();
             SQLiteDA.DeleteCommand = SQLiteCB.GetDeleteCommand();
             SQLiteDA.UpdateCommand = SQLiteCB.GetUpdateCommand();
             SQLiteDA.Update(srcTable);
         }
         isok = true;
     } catch { ;}
     return isok;
 }

        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 (esTraceArguments esTrace = new esTraceArguments(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;
                        }
                    }
                }
//.........这里部分代码省略.........

        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 (esTraceArguments esTrace = new esTraceArguments(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;
        }

//.........这里部分代码省略.........
                                    {
                                        if (batch.Any())
                                        {
                                            var conditions = batch.Select(b => string.Format("([zoom_level]={0} and [tile_row]={1} and [tile_column]={2})", b.Tile.Level, b.Tile.Row, b.Tile.Column));
                                            var whereClause = string.Join("or", conditions);
                                            var mapDeleteStatement = string.Format("delete from map where {0}", whereClause);
                                            var imageDeleteStatement = string.Format("delete from images where [tile_id] in (select [tile_id] from map where {0})", whereClause);

                                            imageDeleteCommand.CommandText = imageDeleteStatement;
                                            imageDeleteCommand.ExecuteNonQuery();
                                            mapDeleteCommand.CommandText = mapDeleteStatement;
                                            mapDeleteCommand.ExecuteNonQuery();
                                        }

                                        imagesAdapter.Fill(imagesTable);
                                        mapAdapter.Fill(mapTable);
                                        //Dictionary to eliminate duplicate images within batch
                                        Dictionary<string, int> added = new Dictionary<string, int>();
                                        //looping thru keys is safe to do here because
                                        //the Keys property of concurrentDictionary provides a snapshot of the keys
                                        //while enumerating
                                        //the TryGet & TryRemove inside the loop checks for items that were removed by another thread
                                        List<int> tileIdsInCurrentBatch = new List<int>();
                                        foreach (var tileimg in batch)
                                        {
                                            string hash = Convert.ToBase64String(new System.Security.Cryptography.MD5CryptoServiceProvider().ComputeHash(tileimg.Image));
                                            int tileid = -1;
                                            if (!added.ContainsKey(hash))
                                            {
                                                mapCommand.CommandText = "SELECT [tile_id] FROM [images] WHERE [tile_md5hash] = @hash";
                                                mapCommand.Parameters.Add(new SQLiteParameter("hash", hash));
                                                object tileObj = mapCommand.ExecuteScalar();
                                                if (tileObj != null && int.TryParse(tileObj.ToString(), out tileid))
                                                    added.Add(hash, tileid);
                                                else
                                                {
                                                    tileid = currentTileId++;
                                                    added.Add(hash, tileid);
                                                    DataRow idr = imagesTable.NewRow();
                                                    idr["tile_md5hash"] = hash;
                                                    idr["tile_data"] = tileimg.Image;
                                                    idr["tile_id"] = added[hash];
                                                    imagesTable.Rows.Add(idr);
                                                }
                                            }

                                            tileIdsInCurrentBatch.Add(added[hash]);

                                            DataRow mdr = mapTable.NewRow();
                                            mdr["zoom_level"] = tileimg.Tile.Level;
                                            mdr["tile_column"] = tileimg.Tile.Column;
                                            mdr["tile_row"] = tileimg.Tile.Row;
                                            mdr["tile_id"] = added[hash];
                                            mapTable.Rows.Add(mdr);
                                        }//for loop thru images

                                        tileIdsInCurrentBatch.Clear();

                                        imagesAdapter.Update(imagesTable);
                                        mapAdapter.Update(mapTable);
                                        transaction.Commit();

                                        if (verbose)
                                            Console.WriteLine(String.Format("Saving an image batch of {0}.", batch.Length));
                                    }//using for datatable
                                }//using for insert command
                            }//using for command builder
                        }//using for select command
                    }
                }//using for connection
            };

            List<TileImage> tilebatch = new List<TileImage>();
            foreach (var tileImage in images.GetConsumingEnumerable())
            {
                tilebatch.Add(tileImage);
                if (tilebatch.Count < 50)
                    continue;
                processBatch(tilebatch.ToArray());
                tilebatch.Clear();
            }

            if (verbose)
                Console.WriteLine("Saving remaining images that didn't fit into a batch.");

            processBatch(tilebatch.ToArray());
            tilebatch.Clear();

            if (verbose)
                Console.WriteLine("Creating Index on table [map] and Creating View [tiles].");
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                connection.Execute("CREATE UNIQUE INDEX IF NOT EXISTS map_index on map (zoom_level, tile_column, tile_row)");
                connection.Execute("CREATE VIEW IF NOT EXISTS tiles as SELECT map.zoom_level as zoom_level, map.tile_column as tile_column, map.tile_row as tile_row, images.tile_data as tile_data FROM map JOIN images on images.tile_id = map.tile_id");
                connection.Close();
            }

            Console.WriteLine("All Done !!!");
        }

        void TabVod(string vodutel)
        {
            //MessageBox.Show(vodutel);
            string Query = "SELECT id_avto 'Гос.номер', date 'Дата ТО', id_work 'Вид ТО', pokazaniya 'Показания КМ', nextto 'Следующий ТО', id_person 'Провел ТО', operation 'Операция, материалы' FROM otchetto WHERE id_person LIKE '%" + vodutel + "%' ORDER BY id_avto";
            string Query1 = "SELECT id_avto 'Гос.номер', date 'Дата ремонта', id_work 'Вид ремонта', pokazaniya 'Показания КМ', id_person 'Провел ремонт', operation 'Операция, материалы' FROM otchetrem WHERE id_person LIKE'%" + vodutel + "%' ORDER BY id_avto";

            SQLiteCommand cmdDataBase = new SQLiteCommand(Query, DB.Con);
            SQLiteCommand cmdDataBase2 = new SQLiteCommand(Query1, DB.Con);

            SQLiteDataAdapter sda = new SQLiteDataAdapter();
            SQLiteDataAdapter sda2 = new SQLiteDataAdapter();

            sda.SelectCommand = cmdDataBase;
            sda2.SelectCommand = cmdDataBase2;

            DataTable dbdataset = new DataTable();
            DataTable dbdataset2 = new DataTable();

            sda.Fill(dbdataset);
            sda2.Fill(dbdataset2);

            BindingSource bSource = new BindingSource();
            BindingSource bSource2 = new BindingSource();

            bSource.DataSource = dbdataset;
            bSource2.DataSource = dbdataset2;

            dataGridView2.DataSource = bSource;
            dataGridView3.DataSource = bSource2;

            sda.Update(dbdataset);
            sda.Update(dbdataset2);
        }

        void TabTO(string nomer)
        {
            string Query = "SELECT date 'Дата ТО', id_work 'Вид ТО', pokazaniya 'Показания КМ', nextto 'Следующий ТО', id_person 'Провел ТО', operation 'Операция, материалы' FROM otchetto WHERE id_avto='" + nomer + "' ORDER BY date";
            SQLiteCommand cmdDataBase = new SQLiteCommand(Query, DB.Con);
            SQLiteDataAdapter sda = new SQLiteDataAdapter();
            sda.SelectCommand = cmdDataBase;
            DataTable dbdataset = new DataTable();
            sda.Fill(dbdataset);
            BindingSource bSource = new BindingSource();

            bSource.DataSource = dbdataset;
            dataGridView1.DataSource = bSource;
            sda.Update(dbdataset);
        }