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);
        }
Example #2
0
        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);
        }