// 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");
}
