/// <summary>
/// Insert Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select SQL</param>
/// <param name="pDataReader">Data Reader</param>
/// <returns>bool</returns>
public static bool InsertData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
try
{
Conn.Open();
cmd = builder.GetInsertCommand(true);
cmd.CommandTimeout = 1500;
cmd.Connection = Conn;
//builder.GetInsertCommand((true);
while (pDataReader.Read())
{
foreach (System.Data.Common.DbParameter param in cmd.Parameters)
{
//string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
string FieldName = param.SourceColumn;
if (FieldName.ToUpperInvariant() == "UNIQUEKEY")
{
param.Value = null;
}
else
{
param.Value = pDataReader[FieldName];
}
}
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
}
result = true;
return(result);
}
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>
/// Insert Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select Statement</param>
/// <param name="pDataRows">DataRows</param>
/// <returns>bool</returns>
public static bool InsertData(string pFileString, string pSelectSQL, System.Data.DataRow[] pDataRows)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
try
{
Conn.Open();
//cmd = builder.GetInsertCommand(true);
cmd = Conn.CreateCommand();
cmd.CommandTimeout = 1500;
//builder.GetInsertCommand((true);
foreach (DataRow R in pDataRows)
{
//string SQL = GetInsertSQL(pSelectSQL.Substring(pSelectSQL.LastIndexOf (" From ") + 6, pSelectSQL.Length - pSelectSQL.LastIndexOf(" From ") - 6),R);
string SQL = GetInsertSQL(R.Table.TableName, R);
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
}
result = true;
return(result);
}
public override System.Data.Common.DbCommandBuilder CreateDbCommandBuilder(System.Data.Common.DbDataAdapter adapter)
{
Type type = _Assembly.GetType("Oracle.DataAccess.Client.OracleCommandBuilder");
System.Data.Common.DbCommandBuilder builder = Activator.CreateInstance(type) as System.Data.Common.DbCommandBuilder;
if (builder != null)
{
builder.DataAdapter = adapter;
}
//if (builder == null)
// throw new ApplicationException("动态创建DbCommandBuilder失败");
return(builder);
}
public string UpdateRowToDatabase(DataTable tb, bool WithoutBinding)
{
if (WithoutBinding)
{
return("");
}
try
{
System.Data.Common.DbCommandBuilder cmdbldr = DbCurrent.GetCommandBuilder(Adapter);
Adapter.Update(tb);
return("");
}
catch (Exception ex)
{ return(ex.Message); }
}
public bool UpdateData(DataSet ds, string TableName)
{
using (MySqlConnection cnn = DataConnection)
{
MySqlCommand cmd = new MySqlCommand("Select * From " + TableName, cnn);
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
System.Data.Common.DbCommandBuilder cb = MySqlClientFactory.Instance.CreateCommandBuilder();
cb.DataAdapter = da;
cb.ConflictOption = ConflictOption.OverwriteChanges;
cb.QuotePrefix = "`";
cb.QuoteSuffix = "`";
return(da.Update(ds, TableName) > 0);
}
}
}
/// <summary>
/// Update Data
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pSelectSQL">Select Statement</param>
/// <param name="pDataReader">DataReader</param>
/// <returns>bool</returns>
public static bool UpdateData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader)
{
bool result = false;
System.Data.Common.DbConnection Conn = null;
System.Data.Common.DbDataAdapter Adapter = null;
System.Data.Common.DbCommandBuilder builder = null;
System.Data.Common.DbCommand cmd = null;
DataSet dataSet = new DataSet();
DataTable Temp = new DataTable();
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
Adapter = driver.GetDbAdapter(pSelectSQL);
Adapter.FillSchema(dataSet, SchemaType.Source);
Adapter.Fill(Temp);
builder = driver.GetDbCommandBuilder(Adapter);
}
try
{
Conn.Open();
cmd = builder.GetInsertCommand(true);
cmd.CommandTimeout = 1500;
while (pDataReader.Read())
{
foreach (System.Data.Common.DbParameter param in cmd.Parameters)
{
param.Value = pDataReader[param.SourceColumn];
}
cmd.ExecuteNonQuery();
}
}
finally
{
Conn.Close();
}
result = true;
return(result);
}
public bool UpdateData(DataSet ds, string TableName, MySqlTransaction ts)
{
// Dim da As DbDataAdapter = db.DbProviderFactory.CreateDataAdapter()
var cmd = new MySqlCommand("Select * From " + TableName, ts.Connection);
cmd.Transaction = ts;
using (var da = new MySqlDataAdapter(cmd))
{
System.Data.Common.DbCommandBuilder cb = MySqlClientFactory.Instance.CreateCommandBuilder();
cb.DataAdapter = da;
cb.ConflictOption = ConflictOption.OverwriteChanges;
cb.QuotePrefix = "`";
cb.QuoteSuffix = "`";
return(da.Update(ds, TableName) > 0);
}
}
/// <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);
}
private void Base_InitializeCommandBuilder(ref System.Data.Common.DbCommandBuilder Value)
{
Value = new System.Data.SQLite.SQLiteCommandBuilder();
}