public virtual void InsertUpdateDataTable(string tableSchema, string tableName, System.Data.DataTable dt)
{
string strSQL = "SELECT * FROM ";
if (tableSchema != null)
{
strSQL += this.QuoteObjectIfNecessary(tableSchema) + ".";
}
strSQL += this.QuoteObjectIfNecessary(tableName) + " WHERE (1 = 2) ";
using (System.Data.Common.DbConnection connection = this.Connection)
{
using (System.Data.Common.DbDataAdapter daInsertUpdate = this.m_factory.CreateDataAdapter())
{
using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand())
{
cmdSelect.CommandText = strSQL;
System.Data.Common.DbCommandBuilder cb = this.m_factory.CreateCommandBuilder();
cb.DataAdapter = daInsertUpdate;
daInsertUpdate.SelectCommand = cmdSelect;
daInsertUpdate.InsertCommand = cb.GetInsertCommand();
daInsertUpdate.UpdateCommand = cb.GetUpdateCommand();
daInsertUpdate.DeleteCommand = cb.GetDeleteCommand();
daInsertUpdate.Update(dt);
} // End Using cmdSelect
} // End Using daInsertUpdate
} // End Using connection
} // End Sub InsertUpdateDataTable
/// <summary>
/// Persists on the database the datatable changes.
/// </summary>
public void Update()
{
bool exception = false;
DateTime sqlTime = DateTime.Now;
try
{
if (_ActiveConnection.State == ConnectionState.Closed)
{
Helper.OpenConnection(_ActiveConnection);
}
if (_Ad.DeleteCommand != null)
{
_Ad.DeleteCommand.CommandTimeout = _Cmd.CommandTimeout;
}
if (_Ad.InsertCommand != null)
{
_Ad.InsertCommand.CommandTimeout = _Cmd.CommandTimeout;
}
if (_Ad.UpdateCommand != null)
{
_Ad.UpdateCommand.CommandTimeout = _Cmd.CommandTimeout;
}
sqlTime = DateTime.Now;
_Ad.Update(this);
}
catch (Exception up)
{
throw (up);
}
finally
{
try
{
if (exception)
{
System.Diagnostics.Trace.TraceInformation("DataTable.Update Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec. (SqlException)");
}
else
{
System.Diagnostics.Trace.TraceInformation("DataTable.Update Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec.");
}
}
catch { }
if (_ActiveConnection.State != ConnectionState.Closed)
{
_ActiveConnection.Close();
}
}
}
/// <summary>
/// Write Data Table
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select statement</param>
/// <param name="pDestinationTableName">Destination Table name</param>
/// <param name="pDatatable">Data Table</param>
/// <returns></returns>
public static bool WriteDataTable(string pFileString, string pSelectSQL, string pDestinationTableName, DataTable pDatatable)
{
bool result = false;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
DataSet dataSet = new DataSet();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Adapter = driver.GetDbAdapter(pSelectSQL);
builder = driver.GetDbCommandBuilder(Adapter);
}
//code to modify data in DataSet here
builder.GetInsertCommand();
//Without the SqlCommandBuilder this line would fail
Adapter.Update(dataSet, pDestinationTableName);
/*
* OleDbCommand insertCommand = dataAdapter.InsertCommand;
* foreach (DataRow row in origTable.Rows)
* {
* foreach (OleDbParameter param in insertCommand.Parameters)
* {
* param.Value = row[param.ParameterName];
* }
* insertCommand->ExecuteNonQuery();
* } */
result = true;
return(result);
}
public static bool UpdateDataSource(System.Data.DataTable data, System.Collections.Specialized.ListDictionary addressbook, anmar.SharpWebMail.IEmailClient client)
{
bool error = false;
if (data == null || addressbook == null || !addressbook.Contains("connectionstring") ||
!addressbook.Contains("searchstring") || !addressbook.Contains("allowupdate") || !((bool)addressbook["allowupdate"]))
{
return(false);
}
System.String connectstring = addressbook["connectionstring"].ToString();
System.String connectusername = null, connectpassword = null;
if (addressbook.Contains("connectionusername") && addressbook.Contains("connectionpassword"))
{
connectusername = addressbook["connectionusername"].ToString();
connectpassword = addressbook["connectionpassword"].ToString();
}
else if (client != null)
{
connectusername = client.UserName;
connectpassword = client.Password;
}
System.String searchfilter = addressbook["searchstring"].ToString();
if (client != null)
{
searchfilter = searchfilter.Replace("$USERNAME$", client.UserName);
}
else
{
searchfilter = searchfilter.Replace("$USERNAME$", System.String.Empty);
}
System.Data.Common.DbDataAdapter adapter = GetDataAdapter(addressbook["type"].ToString(), connectstring, connectusername, connectpassword, searchfilter);
if (adapter != null)
{
try {
if (addressbook["type"].Equals("odbc"))
{
System.Data.Odbc.OdbcCommandBuilder builder = new System.Data.Odbc.OdbcCommandBuilder(adapter as System.Data.Odbc.OdbcDataAdapter);
adapter.Update(data);
builder = null;
}
else if (addressbook["type"].Equals("oledb"))
{
System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter as System.Data.OleDb.OleDbDataAdapter);
adapter.Update(data);
builder = null;
}
} catch (System.Exception e) {
if (log.IsErrorEnabled)
{
log.Error(System.String.Concat("Error updating address book [", addressbook["name"], "] for user [", client.UserName, "]"), e);
}
error = true;
}
}
else
{
error = true;
}
adapter = null;
return(!error);
}
