/// <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);
}
/// <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);
}
/// <summary>
/// GetSchema
/// </summary>
/// <param name="pFileString">File String</param>
/// <param name="pTableName">Table Name</param>
/// <param name="pDataSet">DataSet</param>
public static void GetSchema(string pFileString, string pTableName, ref DataSet pDataSet)
{
System.Data.Common.DbDataAdapter Adapter = null;
string connString = ParseConnectionString(pFileString);
if (DataSource != null)
{
IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
driver.ConnectionString = connString;
Adapter = driver.GetDbAdapter("Select * From [" + pTableName + "]");
}
if (Adapter != null)
{
Adapter.FillSchema(pDataSet, SchemaType.Source, pTableName);
}
}
/// <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);
}
/// <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);
}
