public static void _ExecuteSelect(int timeout, IDbConnection conn, string cmdText, string tableName)
{
DateTime started = DateTime.Now;
IDataReader reader = null;
IDbCommand cmd = null;
bool bConnOpenedHere = HelperFunctions.EnsureConnectionOpen(conn);
IDbTransaction transaction = conn.BeginTransaction();
// DataSet retv = new DataSet();
try
{
cmd = conn.CreateCommand();
cmd.CommandTimeout = timeout;
cmd.Transaction = transaction;
cmd.CommandText = cmdText;
/*
DbType[] reqLenTypes = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength, DbType.Binary, DbType.Object, DbType.Xml };
for (int i = 0; i < paramList.Length; i++)
{
IDbDataParameter idb = cmd.CreateParameter();
cmd.Parameters.Add(HelperFunctions.BuildParameter(idb, paramList[i]));
if (reqLenTypes.Contains(idb.DbType))
{
if (idb is OdbcParameter)
{
(idb as OdbcParameter).Size = paramList[i].Size;
}
}
}
*/
DataTable dt;
reader = cmd.ExecuteReader();
string retStr = string.Empty;
if (reader.FieldCount > 0)
{
dt = new DataTable(tableName);
// retv.Tables.Add(dt = new DataTable(tableName));
// retv.Tables.Add(reader.GetSchemaTable());
DataTable schemaTable = reader.GetSchemaTable();
Helpers.DataTableExtensions.WriteToCsvFile(schemaTable, string.Format("{0}{1}.{2}",cBaseSqlDir, tableName + "_schema", cFileExt));
CsvFileWriter csvFileWriter = new CsvFileWriter(string.Format("{0}{1}.{2}", cBaseSqlDir, tableName + "_data", cFileExt));
bool columnsBuilt = false;
while (reader.Read())
{
if (columnsBuilt == false)
{
HelperFunctions.BuildColumnData(dt, reader);
columnsBuilt = true;
}
// Old Function but Memory Exhausted
// HelperFunctions.AddDataRow(dt, reader);
// New Function write every row
CsvRow row = new CsvRow();
HelperFunctions.AddDataRowCSVWriter(row, reader);
csvFileWriter.WriteRow(row);
}
retStr += string.Format("Count:{0}=Columns:{1},Rows:{2}", tableName, reader.FieldCount, csvFileWriter.Length());
// Old Function
// Helpers.DataTableExtensions.WriteToCsvFile(dt, string.Format("{0}{1}.{2}", cBaseSqlDir, tableName + "_data", cFileExt));
// MessageBox.Show(retStr);
// New Function
csvFileWriter.Close();
Console.WriteLine(retStr);
}
transaction.Commit();
/*
for (int i = 0; i < paramList.Length; i++)
{
IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i];
if (p.Direction != ParameterDirection.Input)
{
paramList[i].OutValue = p.Value;
}
}
*/
}
catch (Exception e)
{
transaction.Rollback();
HelperFunctions.LogException(e, false);
throw e;
}
finally
{
if (reader != null)
{
if (!reader.IsClosed)
reader.Close();
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (bConnOpenedHere)
{
conn.Close();
}
}
// return retv;
}
public static void _ExecuteSelect(int timeout, IDbConnection conn, string cmdText, string tableName)
{
DateTime started = DateTime.Now;
IDataReader reader = null;
IDbCommand cmd = null;
bool bConnOpenedHere = HelperFunctions.EnsureConnectionOpen(conn);
IDbTransaction transaction = conn.BeginTransaction();
// DataSet retv = new DataSet();
try
{
cmd = conn.CreateCommand();
cmd.CommandTimeout = timeout;
cmd.Transaction = transaction;
cmd.CommandText = cmdText;
/*
* DbType[] reqLenTypes = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength, DbType.Binary, DbType.Object, DbType.Xml };
* for (int i = 0; i < paramList.Length; i++)
* {
* IDbDataParameter idb = cmd.CreateParameter();
* cmd.Parameters.Add(HelperFunctions.BuildParameter(idb, paramList[i]));
* if (reqLenTypes.Contains(idb.DbType))
* {
* if (idb is OdbcParameter)
* {
* (idb as OdbcParameter).Size = paramList[i].Size;
* }
* }
* }
*
*/
DataTable dt;
reader = cmd.ExecuteReader();
string retStr = string.Empty;
if (reader.FieldCount > 0)
{
dt = new DataTable(tableName);
// retv.Tables.Add(dt = new DataTable(tableName));
// retv.Tables.Add(reader.GetSchemaTable());
DataTable schemaTable = reader.GetSchemaTable();
Helpers.DataTableExtensions.WriteToCsvFile(schemaTable, string.Format("{0}{1}.{2}", cBaseSqlDir, tableName + "_schema", cFileExt));
CsvFileWriter csvFileWriter = new CsvFileWriter(string.Format("{0}{1}.{2}", cBaseSqlDir, tableName + "_data", cFileExt));
bool columnsBuilt = false;
while (reader.Read())
{
if (columnsBuilt == false)
{
HelperFunctions.BuildColumnData(dt, reader);
columnsBuilt = true;
}
// Old Function but Memory Exhausted
// HelperFunctions.AddDataRow(dt, reader);
// New Function write every row
CsvRow row = new CsvRow();
HelperFunctions.AddDataRowCSVWriter(row, reader);
csvFileWriter.WriteRow(row);
}
retStr += string.Format("Count:{0}=Columns:{1},Rows:{2}", tableName, reader.FieldCount, csvFileWriter.Length());
// Old Function
// Helpers.DataTableExtensions.WriteToCsvFile(dt, string.Format("{0}{1}.{2}", cBaseSqlDir, tableName + "_data", cFileExt));
// MessageBox.Show(retStr);
// New Function
csvFileWriter.Close();
Console.WriteLine(retStr);
}
transaction.Commit();
/*
* for (int i = 0; i < paramList.Length; i++)
* {
* IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i];
* if (p.Direction != ParameterDirection.Input)
* {
* paramList[i].OutValue = p.Value;
* }
* }
*/
}
catch (Exception e)
{
transaction.Rollback();
HelperFunctions.LogException(e, false);
throw e;
}
finally
{
if (reader != null)
{
if (!reader.IsClosed)
{
reader.Close();
}
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (bConnOpenedHere)
{
conn.Close();
}
}
// return retv;
}