public int ExportTable(string FilePathAndName, bool AppendToFile = false)
{
int _row_count = 0;
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
cmd.CommandText = string.Format("SELECT * FROM [{0}].[{1}]", SchemaName, TableName);
using (CsvWriter = new CsvFileWriter(FilePathAndName, AppendToFile, Encoding.UTF8))
{
SqlDatabaseDataReader dataReader = cmd.ExecuteReader();
List <string> ColumnNames = new List <string>();
// Write header i.e. column names
for (int i = 0; i < dataReader.VisibleFieldCount; i++)
{
if (dataReader.GetFieldType(i) != Type.GetType("byte[]")) // BLOB will not be written
{
ColumnNames.Add(dataReader.GetName(i)); //maintain columns in the same order as the header line.
CsvWriter.AddField(dataReader.GetName(i));
}
}
CsvWriter.SaveAndCommitLine();
// Write data i.e. rows.
while (dataReader.Read())
{
foreach (string ColumnName in ColumnNames)
{
CsvWriter.AddField(dataReader.GetString(dataReader.GetOrdinal(ColumnName))); //dataReader.GetOrdinal(ColumnName) provides the position.
}
CsvWriter.SaveAndCommitLine();
_row_count++; //Increase row count to track number of rows written.
}
}
}
return(_row_count);
}
public long ExportTable(string filePathAndName, bool appendToFile = false)
{
SQLDatabaseConnection.Open();
long rowCount = 0;
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
cmd.CommandText = $@"SELECT * FROM [{SchemaName}].[{TableName}]";
using (DelimitedWriter = new DelimitedFileWriter(filePathAndName, appendToFile, Encoding.UTF8))
{
// set variables
DelimitedWriter.Delimiter = Delimiter;
DelimitedWriter.QuoteWrap = ReplicationFormData.QuoteWrap;
DelimitedWriter.NullValue = ReplicationFormData.NullValue;
// write custom header to file if not empty
if (!string.IsNullOrWhiteSpace(ReplicationFormData.CustomHeader))
{
DelimitedWriter.WriteLineToFile(ReplicationFormData.CustomHeader);
}
SqlDatabaseDataReader dataReader = cmd.ExecuteReader();
List <string> columnNames = new List <string>();
// Write header i.e. column names
for (int i = 0; i < dataReader.VisibleFieldCount; i++)
{
var name = dataReader.GetName(i);
if (dataReader.GetFieldType(i) != Type.GetType("byte[]") &&
name != Constants.ReplicationRecordId &&
name != Constants.ReplicationVersionIds &&
name != Constants.ReplicationVersionRecordId) // BLOB will not be written
{
columnNames.Add(name); //maintain columns in the same order as the header line.
DelimitedWriter.AddField(name);
}
}
DelimitedWriter.SaveAndCommitLine();
// Write data i.e. rows.
while (dataReader.Read())
{
foreach (string columnName in columnNames)
{
DelimitedWriter.AddField(
dataReader.GetString(
dataReader.GetOrdinal(
columnName))); //dataReader.GetOrdinal(ColumnName) provides the position.
}
DelimitedWriter.SaveAndCommitLine();
rowCount++; //Increase row count to track number of rows written.
}
}
}
return(rowCount);
}