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