// 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); }
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; }
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); } }
private void GenerateReport(DataTable dtReports) { Base.CleanTable(Base.tblReports, Base.cnnStrReports); try { DataRow drReports; string sqlStr = "SELECT * FROM " + Base.tblReports; OleDbConnection cnn = new OleDbConnection(Base.cnnStrReports); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; oda.Fill(ds, Base.tblReports); dt = ds.Tables[Base.tblReports]; for (int i = 0; i < dtReports.Rows.Count; i++) { dr = dt.NewRow(); drReports = dtReports.Rows[i]; dr[0] = drReports[0]; dr[1] = drReports[1]; dt.Rows.Add(dr); } oda.DeleteCommand = ocb.GetDeleteCommand(); if (oda.Update(ds, Base.tblReports) == 1) ds.AcceptChanges(); else ds.RejectChanges(); sqlStr = null; cnn.Close(); dt.Dispose(); ds.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dr = null; dt = null; ds = null; ocb = null; oda = null; cnn = null; this.Activate(); rptViewCount.Focus(); rptViewCount.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.FullPage; this.PageRanksTableAdapter.Fill(this.reportsDataSet.PageRanks); this.rptViewCount.RefreshReport(); Base.CleanTable(Base.tblReports, Base.cnnStrReports); } catch (Exception ex) { MessageBox.Show(errReport + ex.Message, errReportHeader, MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign); Base.Loading(this, false); return; } finally { } }
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 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(); } }
private string CleanPageImages(string fullPath) { string msg = "Cleaned"; 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.Delete(); oda.DeleteCommand = ocb.GetDeleteCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "Cleaned"; i--; } 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; }
private string RemoveImages(string[] ids) { string msg = string.Empty; 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]; foreach (string id in ids) { bool found = false; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if (dr["id"].ToString().Trim() == id.Trim()) { found = true; dr.Delete(); } } if (found) { oda.DeleteCommand = ocb.GetDeleteCommand(); if (oda.Update(ds, tbl) == 1) { ds.AcceptChanges(); msg = "Removed"; } else { ds.RejectChanges(); msg = "Rejected"; break; } } else { msg = "Image Not Found And Cannot Be Removed..."; 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; }
public string NodesErase(string fullPath, string parentPath, string tbl, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && isDaysLeft) { fullPath = fullPath.Trim(); parentPath = parentPath.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(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); bool found = false; dt = ds.Tables[tbl]; for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if ((dr["fullpath"].ToString().Trim() + "\\").Contains(fullPath + "\\")) { found = true; dr.Delete(); oda.DeleteCommand = ocb.GetDeleteCommand(); if (oda.Update(ds, tbl) == 1) { msg = CleanPageImages(fullPath); if (msg != "Cleaned") return msg; ds.AcceptChanges(); msg = "Erased"; i--; } else { ds.RejectChanges(); msg = "Rejected"; break; } } } 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; NodesReSort(parentPath, tbl); } catch (Exception ex) { msg = ex.Message; } finally { tbl = null; sqlStr = null; } } else msg = errInvalidLegal; return msg; }
/// <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); try { //Set up 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 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()); } }
/// <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(); } } }
DbDataAdapter IDataProviderDatabase.CreateAdapter(IDbConnection connection, Microsoft.Matrix.Packages.DBAdmin.DBEngine.Table table, bool readOnly) { OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + table.Name.Replace("]", "]]") + "]", (OleDbConnection) connection); if (!readOnly) { OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); builder.QuotePrefix = "["; builder.QuoteSuffix = "]"; OleDbCommand insertCommand = builder.GetInsertCommand(); OleDbCommand deleteCommand = builder.GetDeleteCommand(); OleDbCommand updateCommand = builder.GetUpdateCommand(); foreach (Microsoft.Matrix.Packages.DBAdmin.DBEngine.Column column in table.Columns) { if (column.IsIdentity) { insertCommand.CommandText = insertCommand.CommandText + ";SELECT @retId=@@IDENTITY"; insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters; OleDbParameter parameter = new OleDbParameter(); parameter.ParameterName = "@retId"; parameter.DbType = column.DbType; parameter.Size = column.Size; parameter.Direction = ParameterDirection.Output; parameter.IsNullable = column.AllowNulls; parameter.Precision = (byte) column.NumericPrecision; parameter.Scale = (byte) column.NumericScale; parameter.SourceColumn = column.Name; parameter.SourceVersion = DataRowVersion.Current; parameter.Value = DBNull.Value; insertCommand.Parameters.Add(parameter); break; } } adapter.DeleteCommand = deleteCommand; adapter.UpdateCommand = updateCommand; adapter.InsertCommand = insertCommand; } return adapter; }
public void UpdateTableData(string tableName, DataTable datatable) { string query = $"Select* From {tableName}"; OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = $@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source= {path}"; DataTable compareTable = RetrieveTableData(tableName).Tables[0]; try { foreach (DataRow row in compareTable.Rows) { if (row["List_Name"].ToString().Equals( datatable.Rows[0]["List_Name"].ToString() )){ compareTable.Rows[compareTable.Rows.IndexOf(row)].Delete(); } } compareTable.Rows.Add(datatable.Rows[0].ItemArray); } catch { compareTable = datatable; } OleDbCommand command = new OleDbCommand(query, conn); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command); OleDbCommandBuilder oledbCommandBuilder = new OleDbCommandBuilder(dataAdapter); dataAdapter.DeleteCommand = oledbCommandBuilder.GetDeleteCommand(); dataAdapter.InsertCommand = oledbCommandBuilder.GetInsertCommand(); dataAdapter.UpdateCommand = oledbCommandBuilder.GetUpdateCommand(); dataAdapter.Update(compareTable); conn.Close(); }
public static void CleanTable(string tbl, string cnnStr) { try { string sqlStr = "SELECT * FROM " + tbl; OleDbConnection cnn = new OleDbConnection(cnnStr); OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn); OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda); cnn.Open(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); oda.Fill(ds, tbl); dt = ds.Tables[tbl]; foreach (DataRow dr in dt.Rows) dr.Delete(); oda.DeleteCommand = ocb.GetDeleteCommand(); if (oda.Update(ds, tbl) == 1) ds.AcceptChanges(); else ds.RejectChanges(); cnn.Close(); dt.Dispose(); ds.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt = null; ds = null; ocb = null; oda = null; cnn = null; sqlStr = null; } catch { } finally { } }
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 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 GoogleErase(string mailbox, string legal) { string msg = string.Empty; if (tLegal == legal.Trim() && isDaysLeft) { mailbox = mailbox.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(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataRow dr; ocb.QuotePrefix = "["; ocb.QuoteSuffix = "]"; oda.Fill(ds, tbl); bool found = false; dt = ds.Tables[tbl]; mailbox = EncDec.Encrypt(mailbox, Base.hashKey); for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; if (dr["mailbox"].ToString().Trim() == mailbox) { found = true; dr.Delete(); oda.DeleteCommand = ocb.GetDeleteCommand(); if (oda.Update(ds, tbl) == 1) { msg = CleanPageImages(tbl + "\\" + mailbox); if (msg != "Cleaned") return msg; ds.AcceptChanges(); msg = "Erased"; } else { ds.RejectChanges(); msg = "Rejected"; } break; } } 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; }
private bool CleanTable(string tbl) { bool success = true; try { string sqlStr = "SELECT * FROM " + tbl; 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(); oda.Fill(ds, tbl); dt = ds.Tables[tbl]; foreach (DataRow dr in dt.Rows) dr.Delete(); oda.DeleteCommand = ocb.GetDeleteCommand(); if (oda.Update(ds, tbl) == 1) ds.AcceptChanges(); else ds.RejectChanges(); cnn.Close(); dt.Dispose(); ds.Dispose(); ocb.Dispose(); oda.Dispose(); cnn.Dispose(); dt = null; ds = null; ocb = null; oda = null; cnn = null; sqlStr = null; } catch { success = false; } finally { } return success; }
/// <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()); } }
// Функция добавления объектов в таблицу и сохранения в базу данных 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); } }
// 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 void updateDbfromDS(DataSet ds) { OleDbCommandBuilder builder = new OleDbCommandBuilder(adapterKinds); adapterKinds.DeleteCommand = builder.GetDeleteCommand(); adapterKinds.UpdateCommand = builder.GetUpdateCommand(); adapterKinds.InsertCommand = builder.GetInsertCommand(); adapterKinds.Update(ds, "KindsTBL"); }