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