private void ChangedDatabase() { m_adapter = new SQLiteDataAdapter("select * from serverlist", m_dbwrap.Database.Connection); SQLiteCommandBuilder cb = new SQLiteCommandBuilder(m_adapter); m_adapter.InsertCommand = cb.GetInsertCommand(); m_adapter.DeleteCommand = cb.GetDeleteCommand(); m_adapter.UpdateCommand = cb.GetUpdateCommand(); Reload(); }
/// <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; }
internal static void KeyInfoTest(DbConnection cnn) { using (DbCommand cmd = cnn.CreateCommand()) { // First test against integer primary key (optimized) keyinfo fetch cmd.CommandText = "Create table keyinfotest (id integer primary key, myuniquevalue integer unique not null, myvalue varchar(50))"; cmd.ExecuteNonQuery(); cmd.CommandText = "Select * from keyinfotest"; using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { using (DataTable tbl = reader.GetSchemaTable()) { if (tbl.Rows.Count != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned"); } } cmd.CommandText = "SELECT MyValue FROM keyinfotest"; using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { using (DataTable tbl = reader.GetSchemaTable()) { if (tbl.Rows.Count != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned"); } } cmd.CommandText = "DROP TABLE keyinfotest"; cmd.ExecuteNonQuery(); // Now test against non-integer primary key (unoptimized) subquery keyinfo fetch cmd.CommandText = "Create table keyinfotest (id char primary key, myuniquevalue integer unique not null, myvalue varchar(50))"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT MyValue FROM keyinfotest"; using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { using (DataTable tbl = reader.GetSchemaTable()) { if (tbl.Rows.Count != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned"); } } cmd.CommandText = "Select * from keyinfotest"; using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { using (DataTable tbl = reader.GetSchemaTable()) { if (tbl.Rows.Count != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned"); } } // Make sure commandbuilder can generate an update command with the correct parameter count using (DbDataAdapter adp = new SQLiteDataAdapter()) using (DbCommandBuilder builder = new SQLiteCommandBuilder()) { adp.SelectCommand = cmd; builder.DataAdapter = adp; builder.ConflictOption = ConflictOption.OverwriteChanges; using (DbCommand updatecmd = builder.GetUpdateCommand()) { if (updatecmd.Parameters.Count != 4) throw new ArgumentOutOfRangeException("Wrong number of parameters in update command!"); } } } }
//ͨ�������ݺ��� private string UpdateTable(DataTable srcTable, string tableName) { try { command.CommandText = "SELECT * FROM " + tableName; SQLiteDataAdapter oda = new SQLiteDataAdapter(command); SQLiteCommandBuilder ocb = new SQLiteCommandBuilder(oda); oda.InsertCommand = ocb.GetInsertCommand(); oda.DeleteCommand = ocb.GetDeleteCommand(); oda.UpdateCommand = ocb.GetUpdateCommand(); oda.Update(srcTable); } catch (Exception ex) { return ex.ToString(); } return "success"; }
/// <summary> /// JVUmaOutput処理 /// </summary> public static void JVUmaOutput() { DataTable umaDataTable = new DataTable(); string delDate = DateTime.Today.AddMonths(-6).ToString("yyyyMMdd"); // 出力データ取得 using (SQLiteCommand command = DbConn.CreateCommand()) { command.CommandText = "SELECT * FROM uma WHERE DelKubun='0' OR (DelKubun='1' AND DelDate>='" + delDate + "')"; using (SQLiteDataAdapter da = new SQLiteDataAdapter(command)) { da.Fill(umaDataTable); } } // 年替わり更新 if (JVRelayClass.IsNextYear == true) { foreach (DataRow umaDataRow in umaDataTable.Rows) { if (umaDataRow["UmaClass"].ToString() != GetUmaClass(umaDataRow)) { umaDataRow["UmaClass"] = GetUmaClass(umaDataRow); } } // データ更新 using (SQLiteTransaction tran = DbConn.BeginTransaction()) { using (SQLiteCommand command = DbConn.CreateCommand()) { command.Transaction = tran; command.CommandText = "SELECT * FROM uma"; using (SQLiteDataAdapter da = new SQLiteDataAdapter(command)) using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da)) { cb.SetAllValues = false; cb.ConflictOption = ConflictOption.OverwriteChanges; da.UpdateCommand = cb.GetUpdateCommand(); da.InsertCommand = cb.GetInsertCommand(); da.DeleteCommand = cb.GetDeleteCommand(); da.Update(umaDataTable); } } tran.Commit(); } } // 出力 foreach (DataRow dr in umaDataTable.Rows) { OutputUmaData(eOutput.Umanushi, dr); } }
private void applyButton_Click(object sender, EventArgs e) { SQLiteConnection mCN = ConnectionManager.connection; SQLiteDataAdapter mDA = modulesTableAdapter.Adapter; SQLiteCommandBuilder mCB = new SQLiteCommandBuilder(mDA); DataSet mDS = modulesDataSet; DataSet dsChanges = new DataSet(); if (!mDS.HasChanges()) return; dsChanges = mDS.GetChanges(DataRowState.Modified); if (dsChanges != null) { mDA.UpdateCommand = mCB.GetUpdateCommand(); mDA.Update(dsChanges, "modules"); } dsChanges = mDS.GetChanges(DataRowState.Added); if (dsChanges != null) { mDA.InsertCommand = mCB.GetInsertCommand(); mDA.Update(dsChanges, "modules"); } dsChanges = mDS.GetChanges(DataRowState.Deleted); if (dsChanges != null) { mDA.DeleteCommand = mCB.GetDeleteCommand(); mDA.Update(dsChanges, "modules"); } mDS.AcceptChanges(); UpdateModulesDropDown(); (treeView.Model as SlowTreeModel).Root.UpdateModulesFromDbRec(); treeView.Invalidate(); }
/// <summary> /// Provides information to setup the data adapter by the connection of <see cref="System.Data.IDbConnection"/>. /// </summary> /// <param name="datasource"></param> /// <param name="connection">The <see cref="System.Data.IDbConnection"/> represents an open connection to a data source.</param> /// <param name="query">The <see cref="IAdfQuery"/> whose SQL information is to be retrieved.</param> /// <returns>The requested <see cref="System.Data.IDbDataAdapter"/> information object.</returns> public virtual IDbDataAdapter SetUpAdapter(DataSources datasource, IDbConnection connection, IAdfQuery query) { var da = (SQLiteDataAdapter) GetAdapter(); if (query != null) { da.TableMappings.Add("Table", query.LeadTable()); var selectCommand = (SQLiteCommand) GetCommand(datasource, connection, query); da.SelectCommand = selectCommand; // create command builder for a new command, so we can customize the insert command var newda = new SQLiteDataAdapter(selectCommand); // Create and associate a commandbuilder which can generate insert and update queries for the DataAdapter. This is a necessary step! var commandBuilder = new SQLiteCommandBuilder(newda); da.InsertCommand = commandBuilder.GetInsertCommand(); da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord; da.UpdateCommand = commandBuilder.GetUpdateCommand(); da.DeleteCommand = commandBuilder.GetDeleteCommand(); } return da; }
/// <summary> /// Pre-condition: The parameters contain the updated dataset and the table name respectively /// Post-Condition: The updated dataset will be persisted in the database. /// Description: This method will update the database based on /// the updated records in the dataset for the specified table. /// </summary> /// <param name="DataSet_param"></param> /// <param name="strTableName_param"></param> public void saveData(DataSet pDataSet, string pStrTableName) { //Specify SELECT Statment for data adapter string strSQL = "SELECT * FROM " + pStrTableName; //Create an instance of the data adaopter //OleDbDataAdapter dbDA = new OleDbDataAdapter(strSQL, _dbConn); SQLiteDataAdapter dbDA = new SQLiteDataAdapter(strSQL, _dbConn); try { //Set up the command builder - not suitable for large databases //OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA); SQLiteCommandBuilder dbBLD = new SQLiteCommandBuilder(dbDA); dbDA.InsertCommand = dbBLD.GetInsertCommand(); dbDA.UpdateCommand = dbBLD.GetUpdateCommand(); dbDA.DeleteCommand = dbBLD.GetDeleteCommand(); //Subscribe to the OleDbRowUpdateEventHandler //dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(dbDA_RowUpdated); dbDA.RowUpdated += new EventHandler<RowUpdatedEventArgs>(dbDA_RowUpdated); //Update the database using the 'Update' method of the data adapter object if (_dbConn.State == ConnectionState.Closed) _dbConn.Open(); dbDA.Update(pDataSet, pStrTableName); //Close the connection _dbConn.Close(); //Refresh the dataset pDataSet.Tables[pStrTableName].AcceptChanges(); } catch { _dbConn.Close(); //MessageBox.Show(e.ToString()); } }
//��������������ds.Tables[0] ����sTblName�����ݿ��������Ҫ�������� public static bool SqlBulkUpdate(DataTable dt, string sTblName) { int affect = 0; SQLiteConnection conn = null; try { conn = new SQLiteConnection(connectionString); SQLiteCommand myCommand = new SQLiteCommand("select * from " + sTblName, conn); SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(myCommand); SQLiteCommandBuilder myCommandBuilder = new SQLiteCommandBuilder(myAdapter); myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(); conn.Open(); affect = myAdapter.Update(dt); conn.Close(); return true; } catch (Exception e) { if (conn != null) conn.Close(); sLastErr = e.Message; } return false; }
private void ProjectManager_Load(object sender, EventArgs e) { connection = new SQLiteConnection("Data source=" + database.fileName); command = new SQLiteCommand(connection); if (table.ToLower().Equals("activity")) command.CommandText = "SELECT ID," + fields + " FROM " + table + " WHERE ProjectID='" + projectID + "'"; else command.CommandText = "SELECT ID," + fields + " FROM " + table; dataAdapter = new SQLiteDataAdapter(command); dataSet = new DataSet(this.table); dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture; commandBuilder = new SQLiteCommandBuilder(dataAdapter); commandBuilder.ConflictOption = ConflictOption.OverwriteChanges; dataAdapter.Fill(dataSet, this.table); dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); Console.Out.WriteLine(commandBuilder.GetInsertCommand().CommandText); dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); Console.Out.WriteLine(commandBuilder.GetDeleteCommand().CommandText); dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); Console.Out.WriteLine(commandBuilder.GetUpdateCommand().CommandText); dataGridView1.DataSource = dataSet.Tables[0]; dataGridView1.Columns["ID"].Visible = false; if (table.ToLower().Equals("activity")) { dataGridView1.Columns["ProjectID"].Visible = false; } dataGridView1.Columns["Name"].HeaderText = localization.GetString("String23"); }
/// <summary> /// JVUmaReadWriting処理 /// </summary> public static void JVUmaReadWriting() { bool reading = true; int nCount = 0; object buffObj = new byte[0]; int buffsize = 110000; string timeStamp; string buffname; DataTable umaDataTable = new DataTable(); DataTable raceDataTable = new DataTable(); DataTable raceUmaDataTable = new DataTable(); // 初期化時 if (JVRelayClass.Option == (int)JVRelayClass.eJVOpenFlag.SetupSkipDialog) { using (SQLiteCommand command = DbConn.CreateCommand()) { command.CommandText = "DELETE FROM uma"; command.ExecuteNonQuery(); } } using (SQLiteCommand command = DbConn.CreateCommand()) { command.CommandText = "SELECT * FROM uma"; using (SQLiteDataAdapter da = new SQLiteDataAdapter(command)) { da.Fill(umaDataTable); } } raceDataTable.Columns.Add("RaceKey", typeof(string)); raceDataTable.Columns.Add("RaceDate", typeof(string)); raceDataTable.Columns.Add("DataKubun", typeof(string)); raceDataTable.Columns.Add("GradeCD", typeof(string)); raceDataTable.Columns.Add("SyubetuCD", typeof(string)); raceUmaDataTable.Columns.Add("RaceKey", typeof(string)); raceUmaDataTable.Columns.Add("RaceDate", typeof(string)); raceUmaDataTable.Columns.Add("KettoNum", typeof(string)); raceUmaDataTable.Columns.Add("KakuteiJyuni", typeof(string)); if (umaDataTable.PrimaryKey.Length == 0) { umaDataTable.PrimaryKey = new[] { umaDataTable.Columns["KettoNum"] }; } if (raceDataTable.PrimaryKey.Length == 0) { raceDataTable.PrimaryKey = new[] { raceDataTable.Columns["RaceKey"] }; } if (raceUmaDataTable.PrimaryKey.Length == 0) { raceUmaDataTable.PrimaryKey = new[] { raceUmaDataTable.Columns["RaceKey"], raceUmaDataTable.Columns["KettoNum"] }; } ProgressUserState.Maxinum = ReadCount; ProgressUserState.Value = 0; ProgressUserState.Text = "データ読み込み中..."; MainBackgroundWorker.ReportProgress(0, ProgressUserState); do { //--------------------- // JVLink読込み処理 //--------------------- buffObj = new byte[0]; int nRet = AxJVLink.JVGets(ref buffObj, buffsize, out buffname); timeStamp = AxJVLink.m_CurrentFileTimeStamp; byte[] buff = (byte[])buffObj; string buffStr = System.Text.Encoding.GetEncoding(932).GetString(buff); // 正常 if (0 < nRet) { string recordSpec = JVData_Struct.MidB2S(ref buff, 1, 2); buffObj = new byte[0]; buff = new byte[0]; switch (recordSpec) { case "UM": { JVData_Struct.JV_UM_UMA uma = new JVData_Struct.JV_UM_UMA(); uma.SetDataB(ref buffStr); WriteDbUmaData(eOutput.Umanushi, uma, umaDataTable); } break; case "RA": { JVData_Struct.JV_RA_RACE race = new JVData_Struct.JV_RA_RACE(); race.SetDataB(ref buffStr); WriteDbRaceData(eOutput.Umanushi, race, raceDataTable); } break; case "SE": { JVData_Struct.JV_SE_RACE_UMA raceUma = new JVData_Struct.JV_SE_RACE_UMA(); raceUma.SetDataB(ref buffStr); WriteDbRaceUmaData(eOutput.Umanushi, raceUma, raceUmaDataTable); } break; default: // 対象外recspecのファイルをスキップする。 AxJVLink.JVSkip(); nCount++; ProgressUserState.Value = nCount; ProgressUserState.Text = "データ読み込み中..."; MainBackgroundWorker.ReportProgress(0, ProgressUserState); break; } } // ファイルの切れ目 else if (-1 == nRet) { nCount++; ProgressUserState.Value = nCount; ProgressUserState.Text = "データ読み込み中..."; MainBackgroundWorker.ReportProgress(0, ProgressUserState); } // 全レコード読込み終了(EOF) else if (0 == nRet) { ProgressUserState.Value = ProgressUserState.Maxinum; ProgressUserState.Text = "データ読み込み完了"; MainBackgroundWorker.ReportProgress(0, ProgressUserState); reading = false; } // エラー else if (-1 > nRet) { // エラーファイルをスキップする。 AxJVLink.JVSkip(); nCount++; ProgressUserState.Value = nCount; ProgressUserState.Text = "データ読み込み中..."; MainBackgroundWorker.ReportProgress(0, ProgressUserState); } System.Threading.Thread.Sleep(10); } while (true == reading); // データ整備 if (raceUmaDataTable.Rows.Count > 0) { foreach (DataRow raceUmaDataRow in raceUmaDataTable.Select("", "RaceDate")) { DataRow raceDataRow = raceDataTable.Rows.Find(raceUmaDataRow["RaceKey"]); DataRow umaDataRow = umaDataTable.Rows.Find(raceUmaDataRow["KettoNum"]); if (raceDataRow != null && umaDataRow != null) { string raceDate = umaDataRow["RaceDate"].ToString(); if ("" == raceDate || 0 > raceDate.CompareTo(raceUmaDataRow["RaceDate"].ToString())) { if (raceDataRow["DataKubun"].ToString() != "9" && raceDataRow["DataKubun"].ToString() != "0") { // レースを追加 umaDataRow["BeforeUmaClass"] = umaDataRow["UmaClass"]; umaDataRow["BeforeRaceDate"] = umaDataRow["RaceDate"]; umaDataRow["BeforeRaceDataKubun"] = umaDataRow["RaceDataKubun"]; umaDataRow["BeforeRaceGradeCD"] = umaDataRow["RaceGradeCD"]; umaDataRow["BeforeRaceSyubetuCD"] = umaDataRow["RaceSyubetuCD"]; umaDataRow["BeforeRaceKakuteiJyuni"] = umaDataRow["RaceKakuteiJyuni"]; umaDataRow["RaceDate"] = raceUmaDataRow["RaceDate"]; umaDataRow["RaceDataKubun"] = raceDataRow["DataKubun"]; umaDataRow["RaceGradeCD"] = raceDataRow["GradeCD"]; umaDataRow["RaceSyubetuCD"] = raceDataRow["SyubetuCD"]; umaDataRow["RaceKakuteiJyuni"] = raceUmaDataRow["KakuteiJyuni"]; umaDataRow["UmaClass"] = GetUmaClass(umaDataRow); } } else if (0 == raceDate.CompareTo(raceUmaDataRow["RaceDate"].ToString())) { if (raceDataRow["DataKubun"].ToString() == "9" || raceDataRow["DataKubun"].ToString() == "0") { // レース中止、データ削除のため戻す umaDataRow["UmaClass"] = umaDataRow["BeforeUmaClass"]; umaDataRow["RaceDate"] = umaDataRow["BeforeRaceDate"]; umaDataRow["RaceDataKubun"] = umaDataRow["BeforeRaceDataKubun"]; umaDataRow["RaceGradeCD"] = umaDataRow["BeforeRaceGradeCD"]; umaDataRow["RaceSyubetuCD"] = umaDataRow["BeforeRaceSyubetuCD"]; umaDataRow["RaceKakuteiJyuni"] = umaDataRow["BeforeRaceKakuteiJyuni"]; umaDataRow["BeforeUmaClass"] = null; umaDataRow["BeforeRaceDate"] = null; umaDataRow["BeforeRaceDataKubun"] = null; umaDataRow["BeforeRaceGradeCD"] = null; umaDataRow["BeforeRaceSyubetuCD"] = null; umaDataRow["BeforeRaceKakuteiJyuni"] = null; } else { // レース結果の更新 if (umaDataRow["RaceDataKubun"].ToString() != raceDataRow["DataKubun"].ToString()) { umaDataRow["RaceDataKubun"] = raceDataRow["DataKubun"]; } if (umaDataRow["RaceGradeCD"].ToString() != raceDataRow["GradeCD"].ToString()) { umaDataRow["RaceGradeCD"] = raceDataRow["GradeCD"]; } if (umaDataRow["RaceSyubetuCD"].ToString() != raceDataRow["SyubetuCD"].ToString()) { umaDataRow["RaceSyubetuCD"] = raceDataRow["SyubetuCD"]; } if (umaDataRow["RaceKakuteiJyuni"].ToString() != raceUmaDataRow["KakuteiJyuni"].ToString()) { umaDataRow["RaceKakuteiJyuni"] = raceUmaDataRow["KakuteiJyuni"]; } if (umaDataRow["UmaClass"].ToString() != GetUmaClass(umaDataRow)) { umaDataRow["UmaClass"] = GetUmaClass(umaDataRow); } } } } } } // データ更新 using (SQLiteTransaction tran = DbConn.BeginTransaction()) { using (SQLiteCommand command = DbConn.CreateCommand()) { command.Transaction = tran; command.CommandText = "SELECT * FROM uma"; using (SQLiteDataAdapter da = new SQLiteDataAdapter(command)) using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da)) { cb.SetAllValues = false; cb.ConflictOption = ConflictOption.OverwriteChanges; da.UpdateCommand = cb.GetUpdateCommand(); da.InsertCommand = cb.GetInsertCommand(); da.DeleteCommand = cb.GetDeleteCommand(); da.Update(umaDataTable); } command.CommandText = "DELETE FROM uma WHERE BirthYear <= '" + DiscardBirthYear + "'"; command.ExecuteNonQuery(); command.CommandText = "UPDATE timestamp SET date ='" + LastFileTimestamp + "'"; command.ExecuteNonQuery(); } tran.Commit(); JVRelayClass.DbTimeStamp = LastFileTimestamp; } }