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