private static void Test_Command_CommandBuilder()
{
(new OleDbCommand("drop table if exists t", conn)).ExecuteNonQuery();
(new OleDbCommand("create table t(id int not null default 1,name string default 'cubrid',PRIMARY KEY (id))", conn)).ExecuteNonQuery();
(new OleDbCommand("insert into t(id,name) value(1,'cubrid');", conn)).ExecuteNonQuery();
DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable();
string query = "select id,name from t";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn);
OleDbCommandBuilder commandBuilder =new OleDbCommandBuilder(dataAdapter);
dataAdapter.Fill(dataTable);
//Without the OleDbCommandBuilder this line would fail.
foreach (System.Data.DataRow row in dataTable.Rows)
{
foreach (System.Data.DataColumn col in dataTable.Columns)
{
Assert.AreEqual(row[col], 1);
break;
}
}
OleDbCommand t1 = commandBuilder.GetInsertCommand();
Assert.AreEqual(t1.CommandText, "INSERT INTO T DEFAULT VALUES");
}
/// <summary>
/// 插入数据通过Datatable
/// </summary>
/// <param name="_dt"></param>
/// <returns>影响记录条数</returns>
public override int DataTableInsert(DataTable _dt)
{
bool flag = false;
int _nResult = 0;
if (_dt == null)
return _nResult;
string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName);
OleDbCommand _Command = (OleDbCommand)CreateCommand(_sCmdText, CommandType.Text);
OleDbDataAdapter _adapter = new OleDbDataAdapter(_Command);
OleDbDataAdapter _adapter1 = new OleDbDataAdapter(_Command);
OleDbCommandBuilder _builder = new OleDbCommandBuilder(_adapter1);
_adapter.InsertCommand = _builder.GetInsertCommand();
if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count)
{
flag = true;//因为表中有自增字段，所以CommandBuild生成的InserttCommand的参数中少了自增字段
foreach (DataColumn _dc in _dt.Columns)
{
if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName))
{
_adapter.InsertCommand.CommandText =
_adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName);
_adapter.InsertCommand.CommandText =
_adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",?");
_adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, OleDbType.Decimal, _dc.MaxLength, _dc.ColumnName);
if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count)
break;
}
}
}
if (flag)
this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName));
this.BeginTransaction();
try
{
_adapter.InsertCommand.Transaction = _Command.Transaction;
_Command.CommandText = "delete from " + _dt.TableName;
_Command.ExecuteNonQuery();
_nResult = _adapter.Update(_dt);
this.CommitTransaction();
}
catch (Exception ex)
{
this.RollbackTransaction();
throw ex;
}
finally
{
if (flag)
this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName));
}
return _nResult;
}
// 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 bool WriteLine(IEnumerable<object> values)
//{
// return WriteLine((IEnumerable<string>)values);
//}
/// <summary>
/// Make sure you have a value for every column!
/// This is really intentionally not thread safe, do not share this class across threads.
/// </summary>
public bool WriteLine(IEnumerable <string> values)
{
if (!IsOpen())
{
return(false);
}
if (_insertCmd == null)
{
DbCommand cmd = _conn.CreateCommand();
cmd.CommandText = "SELECT * FROM " + _tablename;
DbDataAdapter dda = new System.Data.OleDb.OleDbDataAdapter((OleDbCommand)cmd);
System.Data.OleDb.OleDbCommandBuilder odcb = new System.Data.OleDb.OleDbCommandBuilder((OleDbDataAdapter)dda);
_insertCmd = odcb.GetInsertCommand();
}
//double temp = Double.NaN;
int i = 0;
foreach (string val in values)
{
//if (Double.TryParse(val, out temp))
//{
// //try to parse out integers, float vars (lat/lon), ids, anything numeric
// _insertCmd.Parameters[i].Value = temp;
//}
//else if (_insertCmd.Parameters[i].SourceColumn.ToLower().EndsWith("id"))
//{
// //if the value is empty, and it HAS to be a number...
// _insertCmd.Parameters[i].Value = 0;
//}
//else
//{
_insertCmd.Parameters[i].Value = val;
//}
i++;
}
int rows = _insertCmd.ExecuteNonQuery();
return(rows == 1);
}
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 void InserOrderDetail(Order order)
{
DataSet ds=convertToTable(order.OrderProductds, order.OrderID);
myCmd = new OleDbCommand("Select * From OrderDetails", myConnection);
myCmd.Transaction = transaction;
OleDbDataAdapter adapter = new OleDbDataAdapter(myCmd);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
try
{
adapter.Update(ds, "ShoppingBasketDs");
}
catch (Exception ex)
{
throw ex;
}
}
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 string CatchImages(string target, byte[][] buffer, string[] ext, bool retFileNames)
{
string msg = string.Empty;
try
{
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);
string[] fileName = { };
dt = ds.Tables[tbl];
foreach (string e in ext)
{
while (true)
{
string name = NameGen();
bool found = false;
foreach (string f in fileName)
{
if (f == name)
{
found = true;
break;
}
}
if (!found)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
if (dr["id"].ToString() == name)
{
found = true;
break;
}
}
}
if (!found)
{
int len = fileName.Length;
Array.Resize(ref fileName, len + 1);
fileName[len] = name;
break;
}
}
}
for (int i = 0; i < ext.Length; i++)
{
dr = dt.NewRow();
dr["id"] = fileName[i];
dr["ext"] = ext[i].ToLower().Trim();
dr["data"] = Convert.ToBase64String(buffer[i]);
dr["location"] = target.Trim();
dt.Rows.Add(dr);
oda.InsertCommand = ocb.GetInsertCommand();
if (oda.Update(ds, tbl) == 1)
{
ds.AcceptChanges();
msg = "Created";
}
else
{
ds.RejectChanges();
msg = "Rejected";
break;
}
}
if (msg == "Created" && retFileNames)
{
Array.Resize(ref pgImages, 0);
pgImages = fileName;
//msg = "Created";
}
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
{
}
return msg;
}
/// <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();
}
}
//public bool WriteLine(IEnumerable<object> values)
//{
// return WriteLine((IEnumerable<string>)values);
//}
/// <summary>
/// Make sure you have a value for every column!
/// This is really intentionally not thread safe, do not share this class across threads.
/// </summary>
public bool WriteLine(IEnumerable<string> values)
{
if (!IsOpen())
return false;
if (_insertCmd == null)
{
DbCommand cmd = _conn.CreateCommand();
cmd.CommandText = "SELECT * FROM " + _tablename;
DbDataAdapter dda = new System.Data.OleDb.OleDbDataAdapter((OleDbCommand)cmd);
System.Data.OleDb.OleDbCommandBuilder odcb = new System.Data.OleDb.OleDbCommandBuilder((OleDbDataAdapter)dda);
_insertCmd = odcb.GetInsertCommand();
}
//double temp = Double.NaN;
int i = 0;
foreach (string val in values)
{
//if (Double.TryParse(val, out temp))
//{
// //try to parse out integers, float vars (lat/lon), ids, anything numeric
// _insertCmd.Parameters[i].Value = temp;
//}
//else if (_insertCmd.Parameters[i].SourceColumn.ToLower().EndsWith("id"))
//{
// //if the value is empty, and it HAS to be a number...
// _insertCmd.Parameters[i].Value = 0;
//}
//else
//{
_insertCmd.Parameters[i].Value = val;
//}
i++;
}
int rows = _insertCmd.ExecuteNonQuery();
return (rows == 1);
}
public string GoogleAdd(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();
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;
mailbox = EncDec.Encrypt(mailbox, Base.hashKey);
while (drr.Read())
{
if (drr["mailbox"].ToString().Trim() == mailbox)
{
found = true;
break;
}
}
if (!found)
{
dt = ds.Tables[tbl];
dr = dt.NewRow();
dr["mailbox"] = mailbox;
dt.Rows.Add(dr);
oda.InsertCommand = ocb.GetInsertCommand();
if (oda.Update(ds, tbl) == 1)
{
ds.AcceptChanges();
msg = "Added";
}
else
{
ds.RejectChanges();
msg = "Rejected";
}
}
else
msg = "Already Exist";
cnn.Close();
drr.Close();
ds.Dispose();
dt.Dispose();
cmd.Dispose();
drr.Dispose();
ocb.Dispose();
oda.Dispose();
cnn.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 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;
}
private void subExportToExcel(string sheetToCreate, List<DataRow> selectedRows, DataTable origDataTable, string tableName)
{
char Space = ' ';
string dest = sheetToCreate;
int i = 0;
while (File.Exists(dest))
{
dest = Path.GetDirectoryName(sheetToCreate) + "\\" + Path.GetFileName(sheetToCreate) + i + Path.GetExtension(sheetToCreate);
i += 1;
}
sheetToCreate = dest;
if (tableName == null) tableName = string.Empty;
tableName = tableName.Trim().Replace(Space, '_');
if (tableName.Length == 0) tableName = origDataTable.TableName.Replace(Space, '_');
if (tableName.Length == 0) tableName = "NoTableName";
if (tableName.Length > 30) tableName = tableName.Substring(0, 30);
//Excel names are less than 31 chars
string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] (";
Dictionary<string, string> colNames = new Dictionary<string, string>();
foreach (DataColumn dc in origDataTable.Columns)
{
//Cause the query to name each of the columns to be created.
string modifiedcolName = dc.ColumnName.Replace(Space, '_').Replace('.', '#');
string origColName = dc.ColumnName;
colNames.Add(modifiedcolName, origColName);
queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,";
}
queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";
//adds the closing parentheses to the query string
if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
{
//use Excel 2007 for large sheets.
sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
}
string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
string strConn = defineExcelConnection(ext, sheetToCreate);
if (strConn.Length <= 0)
return;
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strConn);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
cn.Open();
cmd.ExecuteNonQuery();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn);
System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
//creates the INSERT INTO command
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
cmd = cb.GetInsertCommand();
//gets a hold of the INSERT INTO command.
foreach (DataRow row in selectedRows)
{
foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
param.Value = row[colNames[param.SourceColumn]];
cmd.ExecuteNonQuery(); //INSERT INTO command.
}
cn.Close();
cn.Dispose();
da.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
}
/// <summary>
/// Update one row
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pUpdateSQL">Update SQL Statement</param>
/// <param name="pCommand">Command</param>
/// <returns>bool</returns>
public static bool Update_1_Row(string pFileString, string pSelectSQL, string pKeyString, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.SqlClient.SqlConnection Conn = null;
System.Data.OleDb.OleDbConnection ConnOle = null;
System.Data.SqlClient.SqlDataAdapter Adapter = null;
System.Data.OleDb.OleDbDataAdapter AdapterOle = null;
System.Data.SqlClient.SqlCommandBuilder builderSQL = null;
System.Data.OleDb.OleDbCommandBuilder builderOLE = null;
System.Data.SqlClient.SqlCommand cmdSqL = null;
System.Data.Common.DbCommand cmdOle = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string ConnectionString = DBReadExecute.ParseConnectionString(pFileString);
StringBuilder UpdateSQL = new StringBuilder();
try
{
Type SQLServerType = Type.GetType("Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer");
if (DBReadExecute.DataSource.GetType().AssemblyQualifiedName == SQLServerType.AssemblyQualifiedName)
{
//case DBReadExecute.enumDataSouce.SQLServer:
Conn = new System.Data.SqlClient.SqlConnection(ConnectionString);
Adapter = new System.Data.SqlClient.SqlDataAdapter(pSelectSQL, Conn);
//Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName);
Adapter.FillSchema(dataSet, SchemaType.Source);
builderSQL = new System.Data.SqlClient.SqlCommandBuilder(Adapter);
Conn.Open();
cmdSqL = Conn.CreateCommand();
cmdSqL = builderSQL.GetInsertCommand();
cmdSqL.CommandTimeout = 1500;
UpdateSQL.Append("Update ");
UpdateSQL.Append(pSelectSQL.Replace("Select * From ", ""));
UpdateSQL.Append(" Set ");
foreach (System.Data.SqlClient.SqlParameter param in cmdSqL.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
try
{
StringBuilder TUpdateSQL = new StringBuilder();
if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString()))
{
TUpdateSQL.Append("[");
TUpdateSQL.Append(FieldName);
TUpdateSQL.Append("]=");
switch (pDataReader[FieldName].GetType().ToString())
{
case "System.Boolean":
if (Convert.ToBoolean(pDataReader[FieldName]) == false)
{
TUpdateSQL.Append("0");
}
else
{
TUpdateSQL.Append("1");
}
break;
case "System.Int32":
case "System.Decimal":
case "System.Double":
case "System.Single":
case "System.Byte":
TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
break;
default:
TUpdateSQL.Append("'");
TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
TUpdateSQL.Append("'");
break;
}
TUpdateSQL.Append(",");
}
UpdateSQL.Append(TUpdateSQL);
}
catch (Exception ex)
{
// do nothing
}
}
UpdateSQL.Length = UpdateSQL.Length - 1;
UpdateSQL.Append(" Where ");
UpdateSQL.Append(pKeyString);
//builderOLE = null;
cmdSqL = null;
cmdSqL = Conn.CreateCommand();
cmdSqL.CommandText = UpdateSQL.ToString();
cmdSqL.ExecuteNonQuery();
//break;
}
else
{
//case DBReadExecute.enumDataSouce.MSAccess:
//case DBReadExecute.enumDataSouce.MSAccess2007:
//case DBReadExecute.enumDataSouce.MSExcel:
//case DBReadExecute.enumDataSouce.MSExcel2007:
ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString);
AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle);
//Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName);
AdapterOle.FillSchema(dataSet, SchemaType.Source);
AdapterOle.Fill(Temp);
builderOLE = new System.Data.OleDb.OleDbCommandBuilder();
builderOLE.DataAdapter = AdapterOle;
ConnOle.Open();
cmdOle = ConnOle.CreateCommand();
cmdOle = builderOLE.GetInsertCommand();
cmdOle.CommandTimeout = 1500;
UpdateSQL.Append("Update ");
UpdateSQL.Append(pSelectSQL.Replace("Select * From ", ""));
UpdateSQL.Append(" Set ");
foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
try
{
StringBuilder TUpdateSQL = new StringBuilder();
if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString()))
{
TUpdateSQL.Append("[");
TUpdateSQL.Append(FieldName);
TUpdateSQL.Append("]=");
switch (pDataReader[FieldName].GetType().ToString())
{
case "System.Int32":
case "System.Decimal":
case "System.Boolean":
case "System.Double":
case "System.Single":
case "System.Byte":
TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
break;
default:
TUpdateSQL.Append("'");
TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
TUpdateSQL.Append("'");
break;
}
TUpdateSQL.Append(",");
}
UpdateSQL.Append(TUpdateSQL);
}
catch (Exception ex)
{
// do nothing
}
}
UpdateSQL.Length = UpdateSQL.Length - 1;
UpdateSQL.Append(" Where ");
UpdateSQL.Append(pKeyString);
builderOLE = null;
cmdOle = null;
cmdOle = ConnOle.CreateCommand();
cmdOle.CommandText = UpdateSQL.ToString();
//DBReadExecute.ExecuteSQL(pFileString, InsertSQL.ToString());
cmdOle.ExecuteNonQuery();
//break;
}
}
catch (System.Exception ex)
{
Logger.Log(DateTime.Now + ": " + ex.Message);
}
finally
{
if (Conn != null)
{
Conn.Close();
}
if (ConnOle != null)
{
ConnOle.Close();
}
}
result = true;
return result;
}
/// <summary>
/// 插入整个DataTable
/// </summary>
/// <param name="table">要插入的DataTable</param>
/// <param name="tableName">插入的表名</param>
/// <returns></returns>
public int InsertDataTable(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.InsertCommand = cmdBuilder.GetInsertCommand();
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);
}
}
protected void btnAddNewTask_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
{
DataRow row = myDataSet.Tables["myRawTasks"].NewRow();
row["BacklogID"] = Convert.ToInt32(lblAddedBacklogID.Text);
row["TaskTitle"] = titleTaskTextBox.Text;
if (complexityTaskTextBox.Text != "")
row["TaskComplexity"] = Convert.ToInt32(complexityTaskTextBox.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 (estimationHourTaskTextBox.Text != "")
row["TaskEstimationHour"] = Convert.ToInt32(estimationHourTaskTextBox.Text);
if (spentTimeTaskTextBox.Text != "")
row["TaskSpentTime"] = Convert.ToInt32(spentTimeTaskTextBox.Text);
row["TaskStartDate"] = DateTime.Today;
if (deadlineTaskTextBox.Text != "")
row["TaskDueDate"] = Convert.ToDateTime(deadlineTaskTextBox.Text);
myDataSet.Tables["myRawTasks"].Rows.Add(row);
myAdapter.SelectCommand.CommandText = "Select * From Tasks";
OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myAdapter);
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
myAdapter.Update(myDataSet, "myRawTasks");
myDataSet.Clear();
getDatabase();
getTasks();
ScriptManager.RegisterStartupScript(updatePanel, updatePanel.GetType(), "refreshBoard", "refreshBoard();", true);
addandEditTaskPopup.Hide();
}
}
protected void btnAddNewBacklog_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
{
DataRow row = myDataSet.Tables["myRawBacklogs"].NewRow();
row["ProjectID"] = Session["currentProject"].ToString();
row["SwimlaneID"] = Convert.ToInt32(swimlaneDropDownList.SelectedValue);
if (swimlaneDropDownList.SelectedValue == "5")
{
row["BacklogStatusID"] = 3;
row["BacklogCompletedDate"] = DateTime.Today;
}
else if (swimlaneDropDownList.SelectedValue == "4")
{
row["BacklogStatusID"] = 2;
row["BacklogCompletedDate"] = DBNull.Value;
}
else
{
row["BacklogStatusID"] = 1;
row["BacklogCompletedDate"] = DBNull.Value;
}
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);
row["BacklogStartDate"] = DateTime.Today;
if (deadlineTextBox.Text != "")
row["BacklogDueDate"] = Convert.ToDateTime(deadlineTextBox.Text);
row["BacklogAssigneeID"] = Convert.ToInt32(assigneeDropDownList.SelectedValue);
mySelectCommand.CommandText = "SELECT COUNT(SwimlaneID) FROM Backlogs WHERE SwimlaneID=" + swimlaneDropDownList.SelectedValue;
row["BacklogPosition"] = Convert.ToInt32(mySelectCommand.ExecuteScalar().ToString());
myDataSet.Tables["myRawBacklogs"].Rows.Add(row);
myAdapter.SelectCommand.CommandText = "Select * From Backlogs";
OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myAdapter);
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
myAdapter.Update(myDataSet, "myRawBacklogs");
myDataSet.Clear();
getDatabase();
getBacklogs();
//ScriptManager.RegisterStartupScript(updatePanel, updatePanel.GetType(), "refreshBoard", "refreshBoard();", true);
addandEditBacklogPopup.Hide();
}
}
public string NodesAdd(string node, string parent, string fullPath, int zIndex, string tbl, string legal)
{
string msg = string.Empty;
if (tLegal == legal.Trim() && isDaysLeft)
{
node = node.Trim();
parent = parent.Trim();
fullPath = fullPath.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;
while (drr.Read())
{
if (drr["fullpath"].ToString().Trim() == fullPath)
{
found = true;
break;
}
}
if (!found)
{
dt = ds.Tables[tbl];
dr = dt.NewRow();
dr["pg"] = node.Trim();
dr["parent"] = parent.Trim();
dr["fullpath"] = fullPath.Trim();
dr["zindex"] = zIndex;
dr["body"] = EncDec.Encrypt(" ", Base.hashKey);
if (parent != "root")
dr["viewcount"] = 0;
else
dr["viewcount"] = -1;
dt.Rows.Add(dr);
oda.InsertCommand = ocb.GetInsertCommand();
if (oda.Update(ds, tbl) == 1)
{
ds.AcceptChanges();
msg = "Added";
}
else
{
ds.RejectChanges();
msg = "Rejected";
}
}
else
msg = "Already Exist";
cnn.Close();
drr.Close();
ds.Dispose();
dt.Dispose();
cmd.Dispose();
drr.Dispose();
ocb.Dispose();
oda.Dispose();
cnn.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>
/// 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());
}
}
public string ContactListCatchChanges(string tbl, DataTable dtList, string legal)
{
string msg = string.Empty;
if (tLegal == legal.Trim() && IsDaysLeft())
{
try
{
string sqlStr = "SELECT * FROM " + tbl;
if (!CleanTable(tbl))
{
return "Can't Clean Table";
}
DataRow drList;
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;
oda.Fill(ds, tbl);
dt = ds.Tables[tbl];
if (dtList.Rows.Count > 0)
{
for (int i = 0; i < dtList.Rows.Count; i++)
{
dr = dt.NewRow();
drList = dtList.Rows[i];
dr["mailbox"] = EncDec.Encrypt(drList[0].ToString().Trim(), Base.hashKey);
dr["rname"] = EncDec.Encrypt(drList[1].ToString().Trim(), Base.hashKey);
dt.Rows.Add(dr);
oda.InsertCommand = ocb.GetInsertCommand();
if (oda.Update(ds, tbl) == 1)
{
ds.AcceptChanges();
msg = "Catched";
}
else
{
ds.RejectChanges();
msg = "Rejected";
}
}
}
else
msg = "Catched";
cnn.Close();
ds.Dispose();
ocb.Dispose();
oda.Dispose();
cnn.Dispose();
dt.Dispose();
dr = null;
dt = null;
ds = null;
ocb = null;
oda = null;
cnn = null;
tbl = null;
sqlStr = null;
}
catch (Exception ex)
{
msg = ex.Message;
}
finally
{
}
}
else
msg = "illegal";
return msg;
}
/// <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();
}
}
}
public override bool Insert_1_Row(string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.OleDb.OleDbConnection ConnOle = null;
System.Data.OleDb.OleDbDataAdapter AdapterOle = null;
System.Data.OleDb.OleDbCommandBuilder builderOLE = null;
System.Data.Common.DbCommand cmdOle = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
try
{
StringBuilder InsertSQL = new StringBuilder();
StringBuilder ValueSQL = new StringBuilder();
ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString.Replace(";IMEX=1",""));
AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle);
//Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName);
AdapterOle.FillSchema(dataSet, SchemaType.Source);
AdapterOle.Fill(Temp);
builderOLE = new System.Data.OleDb.OleDbCommandBuilder();
builderOLE.DataAdapter = AdapterOle;
ConnOle.Open();
cmdOle = ConnOle.CreateCommand();
cmdOle = builderOLE.GetInsertCommand();
cmdOle.CommandTimeout = 1500;
InsertSQL.Append("Insert Into ");
InsertSQL.Append(pSelectSQL.Replace("Select * From ", ""));
InsertSQL.Append(" (");
ValueSQL.Append(" values (");
List<OleDbParameter> ParameterList = new List<OleDbParameter>();
foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
InsertSQL.Append("[");
InsertSQL.Append(FieldName);
InsertSQL.Append("],");
ValueSQL.Append(param.ParameterName);
ValueSQL.Append(",");
param.Value = pDataReader[FieldName];
ParameterList.Add(param);
/*
if (pDataReader[FieldName] == DBNull.Value)
{
ValueSQL.Append("null");
}
else
{
switch (pDataReader[FieldName].GetType().ToString())
{
case "System.Int32":
case "System.Decimal":
case "System.Boolean":
case "System.Double":
case "System.Single":
case "System.Byte":
ValueSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
break;
default:
ValueSQL.Append("'");
ValueSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
ValueSQL.Append("'");
break;
}
}
ValueSQL.Append(",");*/
}
InsertSQL.Length = InsertSQL.Length - 1;
ValueSQL.Length = ValueSQL.Length - 1;
InsertSQL.Append(")");
ValueSQL.Append(")");
InsertSQL.Append(ValueSQL);
builderOLE = null;
cmdOle = null;
cmdOle = ConnOle.CreateCommand();
cmdOle.CommandText = InsertSQL.ToString();
foreach (OleDbParameter param in ParameterList)
{
DbParameter p2 = cmdOle.CreateParameter();
p2.DbType = param.DbType;
p2.Value = pDataReader[param.SourceColumn];
p2.ParameterName = param.ParameterName;
cmdOle.Parameters.Add(p2);
}
cmdOle.ExecuteNonQuery();
}
catch (System.Exception ex)
{
Logger.Log(DateTime.Now + ": " + ex.Message);
}
finally
{
if (ConnOle != null)
{
ConnOle.Close();
}
}
result = true;
return result;
}
public static bool ExportToXLSX(string sheetToCreate, List <DataRow> selectedRows, System.Data.DataTable origDataTable, string tableName)
{
bool status = false;
System.Data.OleDb.OleDbConnection cn = new OleDbConnection();
try
{
char Space = ' ';
string dest = sheetToCreate;
if (File.Exists(dest))
{
File.Delete(dest);
}
sheetToCreate = dest;
if (tableName == null)
{
tableName = string.Empty;
}
tableName = tableName.Trim().Replace(Space, '_');
if (tableName.Length == 0)
{
tableName = origDataTable.TableName.Replace(Space, '_');
}
if (tableName.Length == 0)
{
tableName = "NoTableName";
}
if (tableName.Length > 30)
{
tableName = tableName.Substring(0, 30);
}
//Excel names are less than 31 chars
string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] (";
Dictionary <string, string> colNames = new Dictionary <string, string>();
foreach (DataColumn dc in origDataTable.Columns)
{
//Cause the query to name each of the columns to be created.
string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#');
string origColName = dc.ColumnName;
colNames.Add(modifiedcolName, origColName);
queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,";
}
queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";
//adds the closing parentheses to the query string
if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
{
//use Excel 2007 for large sheets.
sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
}
string strCn = string.Empty;
string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
if (ext == ".xls")
{
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'";
}
if (ext == ".xlsx")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
}
if (ext == ".xlsb")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' ";
}
if (ext == ".xlsm")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' ";
}
cn = new System.Data.OleDb.OleDbConnection(strCn);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
cn.Open();
cmd.ExecuteNonQuery();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn);
System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
//creates the INSERT INTO command
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
cmd = cb.GetInsertCommand();
//gets a hold of the INSERT INTO command.
foreach (DataRow row in selectedRows)
{
foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
{
param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]];
}
cmd.ExecuteNonQuery(); //INSERT INTO command.
}
cn.Close();
cn.Dispose();
da.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
status = true;
}
catch (Exception ex)
{
status = false;
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
return(status);
}
public override bool Update_1_Row(string pSelectSQL, string pKeyString, DbDataReader pDataReader)
{
bool result = false;
System.Data.OleDb.OleDbConnection ConnOle = null;
System.Data.OleDb.OleDbDataAdapter AdapterOle = null;
System.Data.OleDb.OleDbCommandBuilder builderOLE = null;
System.Data.Common.DbCommand cmdOle = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
try
{
StringBuilder UpdateSQL = new StringBuilder();
ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString);
AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle);
//Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName);
AdapterOle.FillSchema(dataSet, SchemaType.Source);
AdapterOle.Fill(Temp);
builderOLE = new System.Data.OleDb.OleDbCommandBuilder();
builderOLE.DataAdapter = AdapterOle;
ConnOle.Open();
cmdOle = ConnOle.CreateCommand();
cmdOle = builderOLE.GetInsertCommand();
cmdOle.CommandTimeout = 1500;
UpdateSQL.Append("Update ");
UpdateSQL.Append(pSelectSQL.Replace("Select * From ", ""));
UpdateSQL.Append(" Set ");
foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString()))
{
UpdateSQL.Append("[");
UpdateSQL.Append(FieldName);
UpdateSQL.Append("]=");
switch (pDataReader[FieldName].GetType().ToString())
{
case "System.Int32":
case "System.Decimal":
case "System.Boolean":
case "System.Double":
case "System.Single":
case "System.Byte":
UpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
break;
default:
UpdateSQL.Append("'");
UpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
UpdateSQL.Append("'");
break;
}
UpdateSQL.Append(",");
}
}
UpdateSQL.Length = UpdateSQL.Length - 1;
UpdateSQL.Append(" Where ");
UpdateSQL.Append(pKeyString);
builderOLE = null;
cmdOle = null;
cmdOle = ConnOle.CreateCommand();
cmdOle.CommandText = UpdateSQL.ToString();
//DBReadExecute.ExecuteSQL(pFileString, InsertSQL.ToString());
cmdOle.ExecuteNonQuery();
}
catch (System.Exception ex)
{
Logger.Log(DateTime.Now + ": " + ex.Message);
}
finally
{
if (ConnOle != null)
{
ConnOle.Close();
}
}
result = true;
return result;
}
/// <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 override bool InsertBulkRows(string pSelectSQL, System.Data.Common.DbDataReader pDataReader, SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null)
{
bool result = false;
System.Data.OleDb.OleDbConnection ConnOle = null;
System.Data.OleDb.OleDbDataAdapter AdapterOle = null;
System.Data.OleDb.OleDbCommandBuilder builderOLE = null;
System.Data.Common.DbCommand cmdOle = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
try
{
StringBuilder InsertSQL;
StringBuilder ValueSQL;
ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString.Replace(";IMEX=1", ""));
AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle);
AdapterOle.FillSchema(dataSet, SchemaType.Source);
AdapterOle.Fill(Temp);
builderOLE = new System.Data.OleDb.OleDbCommandBuilder();
builderOLE.DataAdapter = AdapterOle;
ConnOle.Open();
cmdOle = ConnOle.CreateCommand();
cmdOle.CommandTimeout = 1500;
int rowCount = 0;
int skippedRows = 0;
int totalRows = 0;
int truncatedCellCount = 0;
bool numericFieldOverflow = false;
while (pDataReader.Read())
{
cmdOle = builderOLE.GetInsertCommand();
InsertSQL = new StringBuilder();
ValueSQL = new StringBuilder();
InsertSQL.Append("Insert Into ");
InsertSQL.Append(pSelectSQL.Replace("Select * From ", ""));
InsertSQL.Append(" (");
ValueSQL.Append(" values (");
int CheckLength = 0;
List<OleDbParameter> ParameterList = new List<OleDbParameter>();
foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters)
{
string FieldName = param.SourceColumn;
InsertSQL.Append("[");
InsertSQL.Append(FieldName);
InsertSQL.Append("],");
ValueSQL.Append(param.ParameterName);
ValueSQL.Append(",");
try
{
param.Value = pDataReader[FieldName];
}
catch (Exception ex)
{
param.Value = DBNull.Value;
}
ParameterList.Add(param);
}
InsertSQL.Length = InsertSQL.Length - 1;
ValueSQL.Length = ValueSQL.Length - 1;
InsertSQL.Append(")");
ValueSQL.Append(")");
InsertSQL.Append(ValueSQL);
cmdOle = null;
cmdOle = ConnOle.CreateCommand();
cmdOle.CommandText = InsertSQL.ToString();
foreach (OleDbParameter param in ParameterList)
{
DbParameter p2 = cmdOle.CreateParameter();
p2.DbType = param.DbType;
try
{
p2.Value = pDataReader[param.SourceColumn];
CheckLength = p2.Value.ToString().Length;
if (CheckLength > 255)
{
p2.Value = p2.Value.ToString().Substring(0, 255);
truncatedCellCount++;
}
}
catch (Exception ex)
{
p2.Value = DBNull.Value;
}
p2.ParameterName = param.ParameterName;
cmdOle.Parameters.Add(p2);
}
try
{
cmdOle.ExecuteNonQuery();
rowCount++;
}
catch (OleDbException ex)
{
skippedRows++;
if (ex.Message.ToLower().Contains("numeric field overflow"))
{
numericFieldOverflow = true;
}
continue;
}
if (pStatusDelegate != null)
{
totalRows = rowCount + skippedRows;
string messageString = String.Empty;
if (skippedRows == 0)
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS, rowCount.ToString(), totalRows.ToString());
}
else
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_PROGRESS_INCLUDE_SKIPPED, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString());
}
pStatusDelegate.Invoke(messageString, (double)rowCount);
}
if (pCancellationDelegate != null && pCancellationDelegate.Invoke())
{
pStatusDelegate.Invoke(string.Format(SharedStrings.DASHBOARD_EXPORT_CANCELLED, rowCount.ToString()));
break;
}
}
if (pStatusDelegate != null)
{
totalRows = rowCount + skippedRows;
string messageString = String.Empty;
if (skippedRows == 0)
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS, totalRows.ToString());
}
else if (skippedRows > 0 && !numericFieldOverflow)
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS_SOME_SKIPPED, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString());
}
else if (skippedRows > 0 && numericFieldOverflow)
{
messageString = string.Format(SharedStrings.DASHBOARD_EXPORT_SUCCESS_SOME_SKIPPED_NUMERIC_FIELD_OVERFLOW, rowCount.ToString(), totalRows.ToString(), skippedRows.ToString());
}
if (truncatedCellCount > 0)
{
messageString = messageString + string.Format("; {0} cells truncated to 255 maximum character limit.", truncatedCellCount);
}
pStatusDelegate.Invoke(messageString);
}
}
//catch (System.Exception ex)
//{
// Logger.Log(DateTime.Now + ": " + ex.Message);
//}
finally
{
if (ConnOle != null)
{
ConnOle.Close();
ConnOle.Dispose();
}
if (AdapterOle != null)
{
AdapterOle.Dispose();
}
if (builderOLE != null)
{
builderOLE.Dispose();
}
if (cmdOle != null)
{
cmdOle.Dispose();
}
}
result = true;
return result;
}
private static void insert_row(string table, OleDbConnection target_con, OleDbCommandBuilder builder, DataRow row)
{
OleDbCommand target_insert = builder.GetInsertCommand();
target_insert.Connection = target_con;
foreach (OleDbParameter param in target_insert.Parameters)
{
param.Value = row[param.SourceColumn];
}
try
{
target_insert.ExecuteNonQuery();
}
catch (OleDbException)
{
show_db_error_message(table, target_insert);
}
}
public string NewsAdd(string tbl, string title, byte[] zipContents, byte[] buffer, string ext, string legal)
{
string msg = string.Empty;
if (tLegal == legal.Trim() && isDaysLeft)
{
tbl = tbl.Trim();
title = title.Trim();
string body = Zipper.DecompressToStrng(zipContents).Trim();
ext = ext.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];
dr = dt.NewRow();
int id = dt.Rows.Count + 1;
if (buffer.Length > 0)
{
//msg = CatchImages("{" + tbl + "}/{" + id + "}", new byte[][] { buffer }, new string[] { ext }, true);
msg = CatchImages(tbl , new byte[][] { buffer }, new string[] { ext }, true);
if (msg != "Created")
return msg;
dr["pic"] = pgImages[0];
}
dr["id"] = id;
dr["header"] = EncDec.Encrypt(title.Trim(), Base.hashKey);
dr["body"] = EncDec.Encrypt(body.Trim(), Base.hashKey);
dr["archived"] = false;
string date = string.Empty;
if (tbl == "newsfa")
date = Base.GetPersianDate();
else if (tbl == "newsen")
date = Base.GetGregorianDate();
else if (tbl == "newsar")
date = Base.GetHijriDate();
dr["date"] = Base.FormatDateToRaw(date);
dt.Rows.Add(dr);
oda.InsertCommand = ocb.GetInsertCommand();
if (oda.Update(ds, tbl) == 1)
{
ds.AcceptChanges();
msg = "Added";
}
else
{
ds.RejectChanges();
msg = "Rejected";
}
cnn.Close();
drr.Close();
ds.Dispose();
dt.Dispose();
cmd.Dispose();
drr.Dispose();
ocb.Dispose();
oda.Dispose();
cnn.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;
}
// 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 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;
}
}
}
