// update by the dataset from the Form public void Update(string dbName, string tabName) { OleDbConnection con = getCon(dbName); string sql = "select * From " + tabName; sda = new OleDbDataAdapter(sql, con); OleDbCommandBuilder builder = new OleDbCommandBuilder(sda); sda.InsertCommand = builder.GetInsertCommand(); sda.DeleteCommand = builder.GetDeleteCommand(); sda.UpdateCommand = builder.GetUpdateCommand(); this.ds = new DataSet(); sda.Fill(this.ds, tabName); }
private void mineralsBindingNavigatorSaveItem_Click(object sender, EventArgs e) { DataSet dataSet = new DataSet(); string queryString = "SELECT * FROM minerals"; string connectionString = @"Data Source=remote.failgaming.com,1433\athena;Initial Catalog=ore_stock;User ID=test;Password=FailZ0rz69"; using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = new OleDbCommand(queryString, connection); OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); adapter.Fill(dataSet); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.Update(dataSet); } this.Validate(); this.mineralsBindingSource.EndEdit(); this.tableAdapterManager.UpdateAll(this.ore_stockDataSet1); }
public void Insert(DataTable dt, String tableName) { using (connection = new OleDbConnection(connString)) { connection.Open(); String query = "SELECT * FROM " + tableName; using(OleDbCommand command = new OleDbCommand(query, connection)) { using (OleDbDataAdapter adapter = new OleDbDataAdapter(command)) { using (OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adapter)) { adapter.DeleteCommand = commandBuilder.GetDeleteCommand(); adapter.InsertCommand = commandBuilder.GetInsertCommand(); adapter.UpdateCommand = commandBuilder.GetUpdateCommand(); } } } } }
public void ExtractTableParameters(string TableName, System.Data.IDbDataAdapter adapter, out DatabaseCache InsertCache, out DatabaseCache DeleteCache, out DatabaseCache UpdateCache, out DatabaseCache IsExistCache, out System.Data.DataTable dt) { adapter.SelectCommand.CommandText = "select top 1 * from " + TableName; DataSet ds = new DataSet(); dt = adapter.FillSchema(ds, SchemaType.Source)[0]; dt.TableName = TableName; OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter as OleDbDataAdapter); builder.ConflictOption = ConflictOption.OverwriteChanges; //builder.SetAllValues = false; OleDbCommand InsertCmd = builder.GetInsertCommand(true); builder.ConflictOption = ConflictOption.OverwriteChanges; InsertCache = new DatabaseCache(InsertCmd.CommandText, InsertCmd.Parameters); InsertCache.CurrentTable = dt; foreach (DataColumn c in dt.Columns) { if (c.AutoIncrement) { InsertCache.IsHaveAutoIncrement = true; InsertCache.SQL += ";Select @@IDENTITY;"; break; } } OleDbCommand UpdateCmd = builder.GetUpdateCommand(true); UpdateCache = new DatabaseCache(UpdateCmd.CommandText, UpdateCmd.Parameters); UpdateCache.CurrentTable = dt; OleDbCommand DeleteCmd = builder.GetDeleteCommand(true); DeleteCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters); DeleteCache.CurrentTable = dt; IsExistCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters); IsExistCache.CurrentTable = dt; IsExistCache.SQL = IsExistCache.SQL.Replace("DELETE FROM [" + TableName + "]", "Select count(1) from [" + TableName + "] with(nolock) "); }
public static bool OpenConnexion() { _bddConnection = new OleDbConnection(ConnexionWords); _bddCommand = new OleDbCommand { Connection = _bddConnection, CommandText = "SELECT * from Clients", CommandType = CommandType.Text }; _bddAdapter = new OleDbDataAdapter(_bddCommand); OleDbCommandBuilder bCB = new OleDbCommandBuilder(_bddAdapter); _bddDataTable = new DataTable("Clients"); if (_bddConnection == null) { } else if (_bddConnection.State == ConnectionState.Open) { new ClientListException("La base de donnée est déja ouverte !"); return false; } if (!File.Exists(Settings.Default.BDDPath)) { new ClientListException("La base de donnée n'existe pas !"); return false; } _bddAdapter.UpdateCommand = bCB.GetUpdateCommand(); _bddAdapter.DeleteCommand = bCB.GetDeleteCommand(); _bddAdapter.InsertCommand = bCB.GetInsertCommand(); _bddConnection.Open(); _bddAdapter.Fill(_bddDataTable); return true; }
public void SaveDataSetThroughAdapter(System.Data.DataSet dsSetRef, Boolean blnRequiredTransaction, String ExcludeTableName, String strConName) { Boolean blnBeginTrans = false; OleDbDataAdapter objOleDBAdpater; OdbcDataAdapter objOdbcDBAdpater; SqlDataAdapter objSqlDBAdpater; OdbcCommandBuilder objOdbcDBCommandBuilder; OleDbCommandBuilder objOleDBCommandBuilder; SqlCommandBuilder objSqlDBCommandBuilder; IDbCommand IMainCommand; DataTable dtInsert; DataTable dtUpdate; DataTable dtDelete; Boolean TableExist; String strQuery; try { if (dsSetRef == null) { throw new Exception("DataSet not Initialized"); } String[] TableName; char[] delimeter; String seperator; seperator = ","; delimeter = seperator.ToCharArray(); TableName = ExcludeTableName.Split(delimeter); if (blnRequiredTransaction.IsFalse()) { if (strConName.Length > 0) { OpenConnection(strConName); } else { OpenConnection(String.Empty); } } if (disconnection.IsNotNull()) { if (blnRequiredTransaction.IsFalse()) { transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted); blnBeginTrans = true; } else { if (transaction == null) { throw new Exception("Transaction is not initialized"); } else { blnBeginTrans = true; } } if (ProviderType == Util.ConnectionLibrary.SQlClient) { IMainCommand = new SqlCommand(); } else if (ProviderType == Util.ConnectionLibrary.Oledb) { IMainCommand = new OleDbCommand(); } else if (ProviderType == Util.ConnectionLibrary.ODBC) { IMainCommand = new OdbcCommand(); } else { IMainCommand = null; } IMainCommand.Connection = disconnection; IMainCommand.Transaction = transaction; } else { throw new Exception("Connection is not initialized"); } IMainCommand.CommandTimeout = CommandTimeOutValue; foreach (DataTable dtRef in dsSetRef.Tables) { TableExist = false; foreach (String tablename in TableName) { if (dtRef.TableName.ToUpper() == tablename.ToUpper()) { TableExist = true; break; } } if (TableExist) continue; if ((Boolean)dtRef.ExtendedProperties[JoinedQuery]) { strQuery = dtRef.ExtendedProperties[UpdateQuery].ToString(); } else { strQuery = dtRef.ExtendedProperties[Query].ToString(); } if ((strQuery.Trim()).Length == 0) { throw new Exception("Query is blank"); } IMainCommand.CommandText = strQuery; dtInsert = dtRef.GetChanges(DataRowState.Added); dtUpdate = dtRef.GetChanges(DataRowState.Modified); dtDelete = dtRef.GetChanges(DataRowState.Deleted); if (ProviderType == Util.ConnectionLibrary.SQlClient) { objSqlDBAdpater = new SqlDataAdapter((SqlCommand)IMainCommand); objSqlDBCommandBuilder = new SqlCommandBuilder(objSqlDBAdpater); if (dtDelete.IsNotNull()) { objSqlDBCommandBuilder.GetDeleteCommand(); objSqlDBAdpater.Update(dtDelete); dtDelete.Dispose(); dtDelete = null; } if (dtInsert.IsNotNull()) { objSqlDBCommandBuilder.GetInsertCommand(); objSqlDBAdpater.Update(dtInsert); dtInsert.Dispose(); dtInsert = null; } if (dtUpdate.IsNotNull()) { objSqlDBCommandBuilder.GetUpdateCommand(); objSqlDBAdpater.Update(dtUpdate); dtUpdate.Dispose(); dtUpdate = null; } } else if (ProviderType == Util.ConnectionLibrary.Oledb) { objOleDBAdpater = new OleDbDataAdapter((OleDbCommand)IMainCommand); objOleDBCommandBuilder = new OleDbCommandBuilder(objOleDBAdpater); if (dtInsert.IsNotNull()) { objOleDBCommandBuilder.GetInsertCommand(); objOleDBAdpater.Update(dtInsert); dtInsert.Dispose(); dtInsert = null; } if (dtUpdate.IsNotNull()) { objOleDBCommandBuilder.GetUpdateCommand(); objOleDBAdpater.Update(dtUpdate); dtUpdate.Dispose(); dtUpdate = null; } if (dtDelete.IsNotNull()) { objOleDBCommandBuilder.GetDeleteCommand(); objOleDBAdpater.Update(dtDelete); dtDelete.Dispose(); dtDelete = null; } } else if (ProviderType == Util.ConnectionLibrary.ODBC) { objOdbcDBAdpater = new OdbcDataAdapter((OdbcCommand)IMainCommand); objOdbcDBCommandBuilder = new OdbcCommandBuilder(objOdbcDBAdpater); if (dtInsert.IsNotNull()) { objOdbcDBCommandBuilder.GetInsertCommand(); objOdbcDBAdpater.Update(dtInsert); dtInsert.Dispose(); dtInsert = null; } if (dtUpdate.IsNotNull()) { objOdbcDBCommandBuilder.GetUpdateCommand(); objOdbcDBAdpater.Update(dtUpdate); dtUpdate.Dispose(); dtUpdate = null; } if (dtDelete.IsNotNull()) { objOdbcDBCommandBuilder.GetDeleteCommand(); objOdbcDBAdpater.Update(dtDelete); dtDelete.Dispose(); dtDelete = null; } } else { objSqlDBAdpater = null; objOleDBAdpater = null; objOdbcDBAdpater = null; objSqlDBCommandBuilder = null; objOleDBCommandBuilder = null; objOdbcDBCommandBuilder = null; } } if (blnRequiredTransaction.IsFalse()) { if (blnBeginTrans) { transaction.Commit(); blnBeginTrans = false; } disconnection.Close(); disconnection.Dispose(); disconnection = null; } } catch (System.Data.OleDb.OleDbException exOleDb) { if (blnBeginTrans && blnRequiredTransaction.IsFalse()) { transaction.Rollback(); if (disconnection.IsNotNull()) { if (disconnection.State == System.Data.ConnectionState.Open) { disconnection.Close(); } disconnection.Dispose(); disconnection = null; } } throw (exOleDb); } catch (System.Data.DBConcurrencyException exDBCE) { if (blnBeginTrans && blnRequiredTransaction.IsFalse()) { transaction.Rollback(); if (disconnection.IsNotNull()) { if (disconnection.State == System.Data.ConnectionState.Open) { disconnection.Close(); } disconnection.Dispose(); disconnection = null; } } throw (exDBCE); } catch (System.Exception ex) { if (blnBeginTrans && blnRequiredTransaction.IsFalse()) { transaction.Rollback(); if (disconnection.IsNotNull()) { if (disconnection.State == System.Data.ConnectionState.Open) { disconnection.Close(); } disconnection.Dispose(); disconnection = null; } } throw (ex); } finally { if (ProviderType == Util.ConnectionLibrary.SQlClient) { IMainCommand = null; objSqlDBAdpater = null; objSqlDBCommandBuilder = null; } else if (ProviderType == Util.ConnectionLibrary.Oledb) { IMainCommand = null; objOleDBAdpater = null; objOleDBCommandBuilder = null; } else if (ProviderType == Util.ConnectionLibrary.ODBC) { IMainCommand = null; objOdbcDBAdpater = null; objOdbcDBCommandBuilder = null; } } }
public string MasterSetDaysLeft(string count, string legal) { string msg = string.Empty; if (tLegal == legal.Trim()) { string tbl = "admin"; string sqlStr = "SELECT * FROM " + tbl; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); OleDbCommand cmd = new OleDbCommand(sqlStr, cnn); cnn.Open(); OleDbDataReader drr = cmd.ExecuteReader(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); dt = ds.Tables["admin"]; dr = dt.Rows[0]; dr.BeginEdit(); dr["daysleft"] = EncDec.Encrypt(count.Trim(), Base.hashKey); dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "Days Count Was Set!"; } else { ds.RejectChanges(); msg = "Rejected"; } drr.Close(); cnn.Close(); cmd.Dispose(); drr.Dispose(); ds.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt.Dispose(); cmd = null; drr = null; ds = null; ocb = null; oda = null; dr = null; dt = null; cnn = null; } catch (Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } else msg = errInvalidLegal; return msg; }
// Sauvegarde tous les changements effectué dans le dataset public void SaveDataSet(string tableName, DataSet dataSet) { if (dataSet.HasChanges() == false) return; switch (connType) { case ConnectionType.DATABASE_MSSQL: { try { var conn = new SqlConnection(connString); var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn); var builder = new SqlCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; DataSet changes = dataSet.GetChanges(); adapter.Update(changes, tableName); PrintDatasetErrors(changes); dataSet.AcceptChanges(); } conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table " + tableName, ex); } break; } case ConnectionType.DATABASE_ODBC: { try { var conn = new OdbcConnection(connString); var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn); var builder = new OdbcCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); DataSet changes; lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; changes = dataSet.GetChanges(); adapter.Update(changes, tableName); dataSet.AcceptChanges(); } PrintDatasetErrors(changes); conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table ", ex); } break; } case ConnectionType.DATABASE_MYSQL: { return; } case ConnectionType.DATABASE_OLEDB: { try { var conn = new OleDbConnection(connString); var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn); var builder = new OleDbCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); DataSet changes; lock (dataSet) // lock dataset to prevent changes to it { adapter.ContinueUpdateOnError = true; changes = dataSet.GetChanges(); adapter.Update(changes, tableName); dataSet.AcceptChanges(); } PrintDatasetErrors(changes); conn.Close(); } catch (Exception ex) { throw new DatabaseException("Can not save table", ex); } break; } } }
public static int Save(DataTable tb,string tbName) { if (连接数据库() == true) { tb.TableName = "dbo." + tbName; OleDbDataAdapter da = new OleDbDataAdapter(string.Format("select * from {0}", "dbo." + tbName), 数据库操作对象); da.MissingSchemaAction = MissingSchemaAction.Ignore; OleDbCommandBuilder CommandBuilder = new OleDbCommandBuilder(da); da.InsertCommand = CommandBuilder.GetInsertCommand(); da.DeleteCommand = CommandBuilder.GetDeleteCommand(); da.UpdateCommand = CommandBuilder.GetUpdateCommand(); return da.Update(tb); } return -1; }
/// <summary> /// Pre-condition: The parameters will be in the order of dataset and string. /// Post-condition: The update 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="pDataSet">The dataset that contains the updated records.</param> /// <param name="pStrTableName">The table name that will be updated.</param> public void saveData(DataSet pDataSet, string pStrTableName) { //specify select statement for our data adapter string strSQL = "SELECT * FROM " + pStrTableName; // create an instance of the data adapter OleDbDataAdapter dbDA = new OleDbDataAdapter(strSQL, _dbConn); try { // setup the command builder - not suitable for large databases OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA); dbDA.InsertCommand = dbBLD.GetInsertCommand(); dbDA.UpdateCommand = dbBLD.GetUpdateCommand(); dbDA.DeleteCommand = dbBLD.GetDeleteCommand(); // subscribe to the OleDbRowUpdateEventHandler dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(dbDA_RowUpdated); // update the database using the Update method of the data adapter if (_dbConn.State == ConnectionState.Closed) _dbConn.Open(); // update the database dbDA.Update(pDataSet, pStrTableName); // close the connection _dbConn.Close(); // refresh the dataset pDataSet.Tables[pStrTableName].AcceptChanges(); } catch (Exception e) { _dbConn.Close(); MessageBox.Show("An error occurred. Contact your system administrator."); //FileLogger fw = new FileLogger("ErrorLog", "Text"); //fw.write(e.ToString()); } }
// Define the parameters for the UPDATE command in different ways private static void AddParameters(OleDbCommandBuilder cb) { try { cb.GetUpdateCommand().Parameters.Add("@return", OleDbType.Char, 1, "G_RETRN"); } catch (Exception e) { Console.WriteLine(e.Message); } }
private void Button_SaveDB_Click(object sender, EventArgs e) { OleDbDataAdapter dAdapter = new OleDbDataAdapter(GlobalVariables.Start_Query.ToString(), SQL_Connect); //Required for CommandBuilder OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter); try { dAdapter.UpdateCommand = cBuilder.GetUpdateCommand(); dAdapter.Update(Table_Result); //Try to Update DataTable with changes made to GridView and sync. to Access DB MessageBox.Show("Data Saved Successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception) { MessageBox.Show("An Error Occoured due to one or more characters entered!", "FATAL ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public string PreferencesSet(string tag, string val, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && isDaysLeft) { tag = tag.Trim(); val = val.Trim(); string tbl = "preferences"; string sqlStr = "SELECT * FROM " + tbl; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); OleDbCommand cmd = new OleDbCommand(sqlStr, cnn); cnn.Open(); OleDbDataReader drr = cmd.ExecuteReader(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); dt = ds.Tables[tbl]; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if (dr["tag"].ToString().Trim() == tag) { dr.BeginEdit(); dr["val"] = val; dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "OK"; } else { ds.RejectChanges(); msg = "Rejected"; } break; } } drr.Close(); cnn.Close(); cmd.Dispose(); drr.Dispose(); ds.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt.Dispose(); cmd = null; drr = null; ds = null; ocb = null; oda = null; dr = null; dt = null; cnn = null; } catch(Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } return msg; }
public string GoogleEdit(string mailbox, string newMailbox, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && isDaysLeft) { mailbox = mailbox.Trim(); newMailbox = newMailbox.Trim(); string tbl = "google"; string sqlStr = "SELECT * FROM " + tbl; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); OleDbCommand cmd = new OleDbCommand(sqlStr, cnn); OleDbDataReader drr = cmd.ExecuteReader(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); bool found = false; bool duplicate = false; mailbox = EncDec.Encrypt(mailbox, Base.hashKey); newMailbox = EncDec.Encrypt(newMailbox, Base.hashKey); while (drr.Read()) { if (drr["mailbox"].ToString().Trim() == mailbox) found = true; else if (drr["mailbox"].ToString().Trim() == newMailbox) duplicate = true; } if (found && !duplicate) { dt = ds.Tables[tbl]; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if (dr["mailbox"].ToString().Trim() == mailbox) { dr.BeginEdit(); dr["mailbox"] = newMailbox; dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { msg = ReNewPageImages(tbl + "\\" + mailbox, tbl + "\\" + newMailbox); if (msg != "ReNewed") return msg; ds.AcceptChanges(); msg = "Updated"; } else { ds.RejectChanges(); msg = "Rejected"; } break; } } } else if (duplicate) msg = "Duplicate Error"; else msg = "Not Found"; cnn.Close(); drr.Close(); ds.Dispose(); cmd.Dispose(); drr.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt.Dispose(); ds = null; ocb = null; oda = null; dr = null; dt = null; cmd = null; drr = null; cnn = null; } catch (Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } else msg = errInvalidLegal; return msg; }
public string CalendarEdit(string month, string day, string title, string newTitle, string body, string tbl, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && isDaysLeft) { month = month.Trim(); day = day.Trim(); title = title.Trim(); newTitle = newTitle.Trim(); body = body.Trim(); tbl = tbl.Trim(); string sqlStr = "SELECT * FROM " + tbl; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); OleDbCommand cmd = new OleDbCommand(sqlStr, cnn); OleDbDataReader drr = cmd.ExecuteReader(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); bool found = false; bool duplicate = false; bool isTitleChanged = title == newTitle ? false : true; while (drr.Read()) { if (EncDec.Decrypt(drr["title"].ToString(), Base.hashKey).Trim() == title) found = true; else if (EncDec.Decrypt(drr["title"].ToString().Trim(), Base.hashKey) == newTitle && isTitleChanged) duplicate = true; if (found && !isTitleChanged) { duplicate = false; break; } } if (found && !duplicate) { dt = ds.Tables[tbl]; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if (EncDec.Decrypt(dr["title"].ToString(), Base.hashKey).Trim() == title) { dr.BeginEdit(); dr["month"] = month; dr["day"] = day; if (isTitleChanged) dr["title"] = EncDec.Encrypt(newTitle, Base.hashKey); dr["body"] = EncDec.Encrypt(body, Base.hashKey); dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "Updated"; } else { ds.RejectChanges(); msg = "Rejected"; } break; } } } else if (duplicate) msg = "Duplicate Error"; else msg = "Not Found"; cnn.Close(); drr.Close(); ds.Dispose(); cmd.Dispose(); drr.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt.Dispose(); ds = null; ocb = null; oda = null; dr = null; dt = null; cmd = null; drr = null; cnn = null; } catch (Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } else msg = errInvalidLegal; return msg; }
public string AdminPwSet(string pw, string npw, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && isDaysLeft) { string tbl = "admin"; string sqlStr = "SELECT * FROM " + tbl; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); OleDbCommand cmd = new OleDbCommand(sqlStr, cnn); cnn.Open(); OleDbDataReader drr = cmd.ExecuteReader(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); while (drr.Read()) { string tPw = EncDec.Decrypt(drr["pw"].ToString(), Base.hashKey); if (tPw == pw.Trim()) msg = "OK"; else msg = "invalid"; break; } if (msg == "OK") { dt = ds.Tables[tbl]; dr = dt.Rows[0]; dr.BeginEdit(); dr["pw"] = EncDec.Encrypt(npw.Trim(), Base.hashKey); dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); } else { ds.RejectChanges(); msg = "Rejected"; } } drr.Close(); cnn.Close(); cmd.Dispose(); drr.Dispose(); ds.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt.Dispose(); cmd = null; drr = null; ds = null; ocb = null; oda = null; dr = null; dt = null; cnn = null; } catch (Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } else msg = errInvalidLegal; return msg; }
public string NewsErase(string tbl, int wh, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && IsDaysLeft()) { tbl = tbl.Trim(); string sqlStr = "SELECT * FROM " + tbl; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); bool found = false; bool isDeleted = false; dt = ds.Tables[tbl]; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if (isDeleted) { dr.BeginEdit(); dr["id"] = i + 1; dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "Erased"; } else { ds.RejectChanges(); msg = "Rejected"; break; } } else if (Convert.ToInt32(dr["id"]) == wh) { found = true; if (dr["pic"].ToString().Trim() != string.Empty) { msg = RemoveImages(new string[] { dr["pic"].ToString().Trim() }); if (msg != "Removed") return msg; } dr.Delete(); oda.DeleteCommand = ocb.GetDeleteCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "Erased"; } else { ds.RejectChanges(); msg = "Rejected"; break; } dt = ds.Tables[tbl]; isDeleted = true; --i; } } if (!found) msg = "Not Found"; cnn.Close(); ds.Dispose(); dt.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); ds = null; ocb = null; oda = null; dr = null; dt = null; cnn = null; } catch (Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } else msg = errInvalidLegal; return msg; }
public string NewsSetArchive(string tbl, int wh, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && isDaysLeft) { tbl = tbl.Trim(); string sqlStr = "SELECT * FROM " + tbl; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); OleDbCommand cmd = new OleDbCommand(sqlStr, cnn); OleDbDataReader drr = cmd.ExecuteReader(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); dt = ds.Tables[tbl]; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if (Convert.ToInt32(dr["id"]) == wh) { dr.BeginEdit(); dr["archived"] = !Convert.ToBoolean(dr["archived"]); dr.EndEdit(); break; } } oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "Updated"; } else { ds.RejectChanges(); msg = "Rejected"; } cnn.Close(); drr.Close(); ds.Dispose(); cmd.Dispose(); drr.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt.Dispose(); ds = null; ocb = null; oda = null; dr = null; dt = null; cmd = null; drr = null; cnn = null; } catch (Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } else msg = errInvalidLegal; return msg; }
/// <summary> /// Procedure for saving all data collected from frmEditWindow /// </summary> private void Save() { // // Saving collected data to dataGridView1 // dataGridView1.Rows[_activeRow].Cells[0].Value = _id; dataGridView1.Rows[_activeRow].Cells[1].Value = _name; dataGridView1.Rows[_activeRow].Cells[2].Value = _description; dataGridView1.Rows[_activeRow].Cells[3].Value = _link; int[] res = _checkbox_array; Array.Sort(res); // // The CATEGORIES cell should take ints of categories comma separated // dataGridView1.Rows[_activeRow].Cells[4].Value = string.Join(",", res); // // Absolutely MUST command before updating dda. No programmatical updates would take place in dataGridView without this method! // bs.EndEdit(); try { dda = new OleDbDataAdapter("SELECT * FROM [data]", connectionString); cb = new OleDbCommandBuilder(dda); cb.GetUpdateCommand(); // // A little trick to get UpdateCommand. No updates without these methods could be possible! // dda.UpdateCommand = cb.GetUpdateCommand(); dda.Update(dtab); dtab = new DataTable(); dda.Fill(dtab); } catch (OleDbException exc) { MessageBox.Show(exc.Message, "OledbException Error"); } catch (Exception e) { MessageBox.Show(e.Message); } // // We don't forget to show the result of update // ShowData(); }
/// <summary> /// Pre Condition: The paremeters will be in order of passing the dataset and the string table name /// Post Condition: The update dataset will be persisted in the data base /// Description: This method will update the database based on the update /// </summary> /// <param name="pDataSet"> The dataset that contains the updated records</param> /// <param name="pStrTableName"> The table name will be updated in the database </param> public void SaveData(DataSet pDataSet, string pStrTableName) { string strQuery = "SELECT * FROM " + pStrTableName; OleDbDataAdapter dbDA = new OleDbDataAdapter(strQuery, _dbConn); try { // setup the command builders OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA); dbDA.InsertCommand = dbBLD.GetInsertCommand(); dbDA.UpdateCommand = dbBLD.GetUpdateCommand(); dbDA.DeleteCommand = dbBLD.GetDeleteCommand(); // subsrcibe to the OleDBRowUpdateEventHandler dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(onRowUpdated); _dbConn.Open(); dbDA.Update(pDataSet, pStrTableName); pDataSet.Tables[pStrTableName].AcceptChanges(); _dbConn.Close(); pStrTableName = null; } catch (Exception e) { MessageBox.Show(e.ToString()); } finally { _dbConn.Close(); } }
protected void btnUpdateBacklog_Click(object sender, EventArgs e) { if (titleTextBox.Text == "") { lblBacklogNotice.Text = "Backlog Title is required!"; addandEditBacklogPopup.Show(); } else if (complexityTextBox.Text != "" && !IsNumeric(complexityTextBox.Text)) { lblBacklogNotice.Text = "Backlog Complexity must be a number!"; addandEditBacklogPopup.Show(); } else if (deadlineTextBox.Text != "" && !IsDate(deadlineTextBox.Text)) { lblBacklogNotice.Text = "Backlog Due date must be a date!"; addandEditBacklogPopup.Show(); } else { string id = addandEditBacklogLegend.InnerText.Remove(0, 17); DataRow row = myDataSet.Tables["myRawBacklogs"].Select("BacklogID = " + id)[0]; row["SwimlaneID"] = Convert.ToInt32(swimlaneDropDownList.SelectedValue); row["BacklogTitle"] = titleTextBox.Text; row["BacklogDescription"] = descriptionTextBox.Text; row["BacklogColor"] = colorDropDownList.SelectedValue.Split(',')[1].ToString(); row["BacklogColorHeader"] = colorDropDownList.SelectedValue.Split(',')[0].ToString(); if (complexityTextBox.Text != "") row["BacklogComplexity"] = Convert.ToInt32(complexityTextBox.Text); else row["BacklogComplexity"] = DBNull.Value; if (deadlineTextBox.Text != "") row["BacklogDueDate"] = Convert.ToDateTime(deadlineTextBox.Text); else row["BacklogDueDate"] = DBNull.Value; row["BacklogAssigneeID"] = Convert.ToInt32(assigneeDropDownList.SelectedValue); myAdapter.SelectCommand.CommandText = "Select * From Backlogs"; OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myAdapter); myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(); myAdapter.Update(myDataSet, "myRawBacklogs"); myDataSet.Clear(); getDatabase(); ScriptManager.RegisterStartupScript(updatePanel, updatePanel.GetType(), "refreshBoard", "refreshBoard();", true); addandEditBacklogPopup.Hide(); } }
public string ServerPageSet(string fullPath, byte[] zipContents, byte[][] buffer, string[] ext, string[] ph, string tbl, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && isDaysLeft) { string sqlStr = "SELECT * FROM " + tbl; string contents = Zipper.DecompressToStrng(zipContents).Trim(); try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommand cmd = new OleDbCommand(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); OleDbDataReader drr = cmd.ExecuteReader(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; oda.Fill(ds, tbl); int id = -1; while (drr.Read()) { ++id; if (drr["fullpath"].ToString().Trim() == fullPath.Trim()) { dt = ds.Tables[tbl]; dr = dt.Rows[id]; string oldContetns = string.Empty; string fileName = string.Empty; string src = "src=\""; string show = "showpics.aspx?t=&id="; oldContetns = EncDec.Decrypt(drr["body"].ToString().Trim(), Base.hashKey); int pos1 = -1; int pos2 = 0; string[] removed = { }; while (true) { pos1 = oldContetns.IndexOf(src, pos2) + src.Length; if (pos1 != src.Length - 1) { pos2 = oldContetns.IndexOf("\"", pos1); fileName = oldContetns.Substring(pos1, pos2 - pos1); if (fileName.IndexOf(show) != -1) { fileName = fileName.Substring(show.Length); if (contents.IndexOf(fileName) == -1) { bool duplicated = false; foreach (string f in removed) { if (f == fileName) duplicated = true; } if (!duplicated) { int len = removed.Length; Array.Resize(ref removed, len + 1); removed[len] = fileName; duplicated = false; } } } } else break; } if (removed.Length > 0) { msg = RemoveImages(removed); if (msg != "Removed") return msg; } if (ph.Length > 0) { msg = CatchImages(fullPath, buffer, ext, true); if (msg != "Created") return msg; for (int i = 0; i < ph.Length; i++) { contents = contents.Replace(ph[i], show + pgImages[i]); } } //flvObject Section pos1 = -1; pos2 = 0; Array.Resize(ref removed, 0); if (oldContetns.ToLower().Contains("<div")) { int p1 = -1; int p2 = -1; fileName = string.Empty; while (true) { pos1 = oldContetns.ToLower().IndexOf("<div", pos2); if (pos1 == -1) break; pos2 = oldContetns.ToLower().IndexOf("</div>", pos1) + "</div>".Length; if (pos2 == -1) break; string swf = oldContetns.Substring(pos1, pos2 - pos1); if (swf.Contains(".flv")) { p1 = swf.IndexOf("id=\"") + "id=\"".Length; p2 = swf.IndexOf("\"", p1); fileName = swf.Substring(p1, p2 - p1); if (!contents.Contains(fileName)) { bool duplicated = false; foreach (string f in removed) { if (f == fileName) duplicated = true; } if (!duplicated) { int len = removed.Length; Array.Resize(ref removed, len + 1); removed[len] = fileName; duplicated = false; } } } } } if (removed.Length > 0) { msg = RemoveFLV(removed); if (msg != "Removed") return msg; } //end flvObject Section dr.BeginEdit(); dr["body"] = EncDec.Encrypt(contents.Trim(), Base.hashKey); dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { msg = "Saved"; ds.AcceptChanges(); } else { ds.RejectChanges(); msg = "Rejected"; } break; } } drr.Close(); cnn.Close(); cmd.Dispose(); drr.Dispose(); ds.Dispose(); oda.Dispose(); cnn.Dispose(); cmd = null; drr = null; ds = null; oda = null; cnn = null; } catch (Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } else msg = errInvalidLegal; return msg; }
protected void btnUpdateTask_Click(object sender, EventArgs e) { if (titleTaskTextBox.Text == "") { lblTaskNotice.Text = "Task Title is required!"; addandEditTaskPopup.Show(); } else if (complexityTaskTextBox.Text != "" && !IsNumeric(complexityTaskTextBox.Text)) { lblTaskNotice.Text = "Task Complexity must be a number!"; addandEditTaskPopup.Show(); } else if (estimationHourTaskTextBox.Text != "" && !IsNumeric(estimationHourTaskTextBox.Text)) { lblTaskNotice.Text = "Estimation hour must be a number!"; addandEditTaskPopup.Show(); } else if (spentTimeTaskTextBox.Text != "" && !IsNumeric(spentTimeTaskTextBox.Text)) { lblTaskNotice.Text = "Spent time must be a number!"; addandEditTaskPopup.Show(); } else if (deadlineTaskTextBox.Text != "" && !IsDate(deadlineTaskTextBox.Text)) { lblTaskNotice.Text = "Task Due date must be a date!"; addandEditTaskPopup.Show(); } else { string id = addandEditTaskLegend.InnerText.Remove(0, 14); DataRow row = myDataSet.Tables["myRawTasks"].Select("TaskID = " + id)[0]; row["TaskTitle"] = titleTaskTextBox.Text; row["TaskAssigneeID"] = Convert.ToInt32(assigneeTaskDropDownList.SelectedValue); row["TaskStatusID"] = Convert.ToInt32(statusTaskDropDownList.SelectedValue); if (statusTaskDropDownList.SelectedValue == "3") row["TaskCompletedDate"] = DateTime.Today; else row["TaskCompletedDate"] = DBNull.Value; if (complexityTaskTextBox.Text != "") row["TaskComplexity"] = Convert.ToInt32(complexityTaskTextBox.Text); else row["TaskComplexity"] = DBNull.Value; if (estimationHourTaskTextBox.Text != "") row["TaskEstimationHour"] = Convert.ToInt32(estimationHourTaskTextBox.Text); else row["TaskEstimationHour"] = DBNull.Value; if (spentTimeTaskTextBox.Text != "") row["TaskSpentTime"] = Convert.ToInt32(spentTimeTaskTextBox.Text); else row["TaskSpentTime"] = DBNull.Value; if (deadlineTaskTextBox.Text != "") row["TaskDueDate"] = Convert.ToDateTime(deadlineTaskTextBox.Text); else row["TaskDueDate"] = DBNull.Value; myAdapter.SelectCommand.CommandText = "Select * From Tasks"; OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myAdapter); myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(); myAdapter.Update(myDataSet, "myRawTasks"); myDataSet.Clear(); getDatabase(); ScriptManager.RegisterStartupScript(updatePanel, updatePanel.GetType(), "refreshBoard", "refreshBoard();", true); addandEditTaskPopup.Hide(); } }
private void NodesReSort(string parentPath, string tbl) { string sqlStr = "SELECT * FROM " + tbl + " ORDER BY fullpath ASC"; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); dt = ds.Tables[tbl]; int zIndex = -1; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if (dr["fullpath"].ToString().Trim() == string.Concat(parentPath, "\\", dr["pg"].ToString().Trim())) { dr.BeginEdit(); dr["zindex"] = ++zIndex; dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); } else { ds.RejectChanges(); } } } cnn.Close(); ds.Dispose(); dt.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); ds = null; ocb = null; oda = null; dr = null; dt = null; cnn = null; } catch { } finally { tbl = null; sqlStr = null; } }
private void FillDataTable(string tableName, bool quitSyncProcess) { using (OleDbDataAdapter adapter = new OleDbDataAdapter()) { // Open the connection. if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); } // A table mapping names the DataTable. adapter.TableMappings.Add("Table", tableName); // Create a SqlCommand to retrieve Suppliers data. string sql = String.Format("SELECT * FROM {0};", tableName); adapter.SelectCommand = new OleDbCommand(sql, _dbConnection); OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); // 填充数据 adapter.Fill(_dataSet); // 数据交换 if (!quitSyncProcess) { DataExchange(tableName); // 数据更新 adapter.UpdateCommand = builder.GetUpdateCommand(); try { adapter.Update(_dataSet); } catch { } } } }
private void SaveProxy() { string tbl = "proxy"; string sqlStr = "SELECT * FROM " + tbl; try { OleDbConnection cnn = new OleDbConnection(Base.cnnStrLocal); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); dt = ds.Tables[tbl]; dr = dt.Rows[0]; dr.BeginEdit(); dr["useie"] = Base.proxyUseDefault; if (Base.proxyUseDefault) { dr["addr"] = "{IE}"; dr["port"] = "{IE}"; } else { dr["addr"] = Base.proxyAddr; dr["port"] = Base.proxyPort; } dr.EndEdit(); oda.UpdateCommand = ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); MessageBox.Show("تنظیمات پراکسی اعمال شد", Base.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1); } else { ds.RejectChanges(); } cnn.Close(); ds.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt.Dispose(); ds = null; ocb = null; oda = null; dr = null; dt = null; cnn = null; } catch (Exception ex) { MessageBox.Show(Base.errPrefix + ex.Message, Base.msgTitle, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { tbl = null; sqlStr = null; } }
/// <summary> /// 界面加载数据OLEDB /// </summary> /// <param name="ConnStr">连接字符串</param> /// <param name="strSQL">SQL语句</param> /// <param name="strTableName">DataTable表名</param> public static SD.DataTable GetOLEDBData(string ConnStr, string strSQL, string strTableName, bool isTable) { SD.DataTable dt = new SD.DataTable(strTableName); try { using (OleDbsqlconn = new SDOL.OleDbConnection(ConnStr)) { //sqlconn.Open(); using (OleDbsqlcmd = new SDOL.OleDbCommand(strSQL, OleDbsqlconn)) { //if (sqlcmd == null) //{ // using (dt = new SD.DataTable(strTableName)) // { // return dt; // } //} using (OleDbsqladp = new SDOL.OleDbDataAdapter(OleDbsqlcmd)) { if (isTable) { using (OleDbsqlcmdbd = new SDOL.OleDbCommandBuilder(OleDbsqladp)) { OleDbsqlcmdbd.ConflictOption = SD.ConflictOption.CompareAllSearchableValues; OleDbsqladp.InsertCommand = OleDbsqlcmdbd.GetInsertCommand(); OleDbsqladp.UpdateCommand = OleDbsqlcmdbd.GetUpdateCommand(); OleDbsqladp.DeleteCommand = OleDbsqlcmdbd.GetDeleteCommand(); OleDbsqladp.Fill(dt); return(dt); } } else { OleDbsqladp.Fill(dt); return(dt); } } } } } catch (Exception ex) { throw ex; } finally { if (sqlconn != null) { if (sqlconn.State != SD.ConnectionState.Closed) { sqlconn.Close(); } sqlconn.Dispose(); } } }
/// <summary> /// 更新整个DataTable /// </summary> /// <param name="table">要更新的DataTable</param> /// <param name="tableName">更新的表名</param> /// <returns></returns> public int UpdateDataTable(DataTable table, string tableName) { try { Open(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from " + tableName; OleDbDataAdapter da = new OleDbDataAdapter(cmd); OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da); da.UpdateCommand = cmdBuilder.GetUpdateCommand(); int val = da.Update(table); da.Dispose(); return val; } catch (OleDbException e) { this.Close(); throw e; } }
private void saveChanges_DoWork(object sender, DoWorkEventArgs e) { try { string selectStatement = "SELECT * From [members];"; OleDbConnection conn = new OleDbConnection(thisWindow.secureConnectionString); conn.Open(); OleDbDataAdapter OleDbDa = new OleDbDataAdapter(); OleDbDa.SelectCommand = new OleDbCommand(selectStatement, conn); OleDbCommandBuilder cb = new OleDbCommandBuilder(OleDbDa); cb.QuotePrefix = "["; cb.QuoteSuffix = "]"; OleDbDa.InsertCommand = cb.GetInsertCommand(); OleDbDa.DeleteCommand = cb.GetDeleteCommand(); OleDbDa.UpdateCommand = cb.GetUpdateCommand(); OleDbDa.Update(dataSet, "mainTable"); conn.Close(); } catch (Exception ex) { MessageBox.Show("An error occured while trying to save. Message: " + ex.Message); } }
// Функция добавления объектов в таблицу и сохранения в базу данных public static void AddToTableAndSave(DataSet ds, OleDbDataAdapter adapter, string tableName) { OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.InsertCommand = builder.GetInsertCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); using (builder) { adapter.Update(ds, tableName); } }
private string ReNewPageImages(string fullPath, string newFullPath) { string msg = "ReNewed"; try { while (true) { string tbl = "pics"; string sqlStr = "SELECT * FROM " + tbl; OleDbConnection cnn = new OleDbConnection(Base.cnnStrPics); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); dt = ds.Tables[tbl]; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if ((dr["location"].ToString().Trim()).Contains(fullPath)) { dr.BeginEdit(); dr["location"] = dr["location"].ToString().Trim().Replace(fullPath, newFullPath); dr.EndEdit(); oda.UpdateCommand= ocb.GetUpdateCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "ReNewed"; } else { ds.RejectChanges(); msg = "Rejected"; break; } } } cnn.Close(); ds.Dispose(); dt.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); ds = null; ocb = null; oda = null; dr = null; dt = null; cnn = null; break; } } catch (Exception ex) { msg = ex.Message; } finally { } return msg; }