Fill a datatable from a txt

Question

I have a datareader writing directly into the text file,

int count = dataReader.FieldCount;
                while (dataReader.Read())
                {
                    for (int i = 0; i < count; i++)
                    {
                        file.WriteLine(dataReader.GetValue(i));
                    }
                }
            } while (dataReader.NextResult());

This is the fruit of this code,

1--------------------------------------------Esse cara é a primeira coluna, Codigo Fabricante

FABRICANTE GENERICO----------Esse cara é a segunda coluna, Fabricante

BLOCO GENERICO-------------------Esse cara é a terceira coluna, Bloco

EMAIL GENERICO---------------------Esse cara é a quarta coluna, Email

OUTRO CODIGO DE FAB2

OUTRO FABR. GEN2

OUTRO BLO. GEN2

OUTRO EMAIL GEN2

OUTRO CODIGO DE FAB3

OUTRO FABR. GEN3

OUTRO BLO. GEN3

OUTRO EMAIL GEN3

... But in the form that I got the data, I can’t structure it into a datatable, because it didn’t get the column name, but I know what it is and how much. Could I make an insertion in the datatable with the static columns? Type,

Pseudocode;

ENQUANTO ((LINHA = DATATABLE.PROXIMALINHA()) OU != NULO)
        {
    COLUNA 0 = Codigo...; 
    COLUNA 1 = Fabri...;
    COLUNA 2 = Bloco...;
    COLUNA 3 = Email...;
    SE(HOUVERLINHA){
        PARA(I=0;ENQUANTO I <= I+4; I++){
        DATATABLE.COLUNA(I).LINHA = LELINHAARQUIVO();       
        }   
        I+=4;           
    }

Or else better form?

Answer 1

if you want to popular a DataTable, should not be using a DataReader, but rather a DataAdapter.:

var adapter = new SqlDataAdapter(minhaConsulta, customerConnection);
var dataSet = new DataSet();  
adapter.Fill(dataSet, "Tabela");
var dataTable = customerOrders.Tables["Tabela"];

EDIT

Pivotgrid - Devexpress

As to the PivotGrid of DevExpress, I can’t help you much, but according to them, the ideal scenario for you is to use Database Server Mode, either using a EntityServerModeDataSource or LinqServerModeDataSource as DataSource. On the Devexpress website is this guide on Entity Framework 4.0+ Server Mode

Sql Server Paging

As for paging, you have two options, use the new OFFSET FETCH, which is available from the SQL Server 2012, or use the ROW_NUMBER, this is available from the SQL Server 2005

OFFSET FETCH

DECLARE @page as int
DECLARE @pageSize as int

SET @page = 10
SET @pageSize = 20

SELECT TabelaId, Coluna1, Coluna2, ..., ColunaN
FROM Tabela 
ORDER BY TabelaId -- Ou Qual quer outro critério de ordenação 
OFFSET (@page * @pageSize) ROWS FETCH NEXT @pageSize ROWS ONLY

In the example above, the OFFSET will ignore the first 200 records, while the FETCH will read only the next 20 records. This way will return only the records from number 201 to 220.

ROW_NUMBER

DECLARE @page as int
DECLARE @pageSize as int

SET @page = 10
SET @pageSize = 20

WITH CTE_Page AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY TabelaId) AS Ordem, -- Ou Qual quer outro critério de ordenação 
        *
    FROM Tabela 
)

SELECT TabelaId, Coluna1, Coluna2, ..., ColunaN
FROM CTE_Page 
WHERE Ordem BETWEEN (@page * @pageSize + 1) AND (@page * @pageSize + @pageSize)

The ROW_NUMBER will create a number of records sequentially based on the sort criteria. The WHERE will filter the records to return only those of order between 201 and 220.

File reading

In this case it is interesting to create a file with fixed position, so if possible to search a record by a specific line.

In the example below I am using the package FlatFile.FixedLength.Attributes.

Myclass

using FlatFile.FixedLength;
using FlatFile.FixedLength.Attributes;

[FixedLengthFile]
public class MyClass
{
    [FixedLengthField(1, 5, PaddingChar = ' ', Padding = Padding.Right)]
    public int Indice { get; set; }
    [FixedLengthField(1, 50, PaddingChar = ' ', Padding = Padding.Left)]
    public Guid Campo01 { get; set; }
    [FixedLengthField(2, 50, PaddingChar = ' ', Padding = Padding.Left)]
    public Guid Campo02 { get; set; }
    [FixedLengthField(3, 50, PaddingChar = ' ', Padding = Padding.Left)]
    public Guid Campo03 { get; set; }
    [FixedLengthField(4, 50, PaddingChar = ' ', Padding = Padding.Left)]
    public Guid Campo04 { get; set; }
    [FixedLengthField(5, 50, PaddingChar = ' ', Padding = Padding.Left)]
    public Guid Campo05 { get; set; }
}

Writing an Archive

var factory = new FixedLengthFileEngineFactory();
var flatFile = factory.GetEngine<MyClass>();
using (var stream = new FileStream(@"D:\Temp\Registros.txt", FileMode.Create))
{
    for (var i = 1; i <= 500; i++) {
        var registro = new MyClass();
        registro.Indice = i;
        registro.Campo01 = Guid.NewGuid();
        registro.Campo02 = Guid.NewGuid();
        registro.Campo03 = Guid.NewGuid();
        registro.Campo04 = Guid.NewGuid();
        registro.Campo05 = Guid.NewGuid();
        flatFile.Write<MyClass>(stream, new List<MyClass> { registro });
    }
}

In the example above I am trying to simulate the behavior of DataReader, you will write record by record in the file.

Reading from the Archive

var factory = new FixedLengthFileEngineFactory();
var flatFile = factory.GetEngine<MyClass>();

var lineSize = 257;
var page = 10;
var pageSize = 20;
using (var stream = new FileStream(@"D:\Temp\Registros.txt", FileMode.Open))
{
    var binary = new byte[pageSize * lineSize];
    var inicio = lineSize * (page * pageSize);
    stream.Seek((long)inicio, SeekOrigin.Begin);
    stream.Read(binary, 0, binary.Length);
    using (var memory = new MemoryStream(binary))
    {
        var registros = flatFile.Read<MyClass>(memory).ToList();
    }
}

In the above example, only records from 201 to 220 were read.

Answer 2

See if that helps you:

var reader = cmd.ExecuteReader();
var columns = new List<string>();

for(int i = 0; i < reader.FieldCount; i++)
{
   columns.Add(reader.GetName(i));
}

another example

var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();

Answer 3

The solution was that way.

public DataTable ConverteArquivoTextoParaDataTable(string ArquivoTxt)
    {
        dataTable = new DataTable();

        dataTable.Columns.Add("Coluna1", typeof(String));          
        dataTable.Columns.Add("Coluna2", typeof(String));
        dataTable.Columns.Add("Coluna3", typeof(String));
        dataTable.Columns.Add("Coluna4", typeof(String));


        string[] totalLinhas = File.ReadAllLines(ArquivoTxt);
        int colunaIndex = 0;
        DataRow dr = dataTable.NewRow();
        foreach (string qtdLinhas in totalLinhas )
        {
            dr[colunaIndex] = qtdLinhas ;
            if (colunaIndex == 3)
            {
                dataTable.Rows.Add(dr);
                dr = dataTable.NewRow();
                colunaIndex = 0;
                Invoke(new MethodInvoker(delegate {
                GRIDGENERICO.DataSource = dataTable;
                GRIDGENERICO.DataMember = 
                dataTable.TableName.ToString();
                GRIDGENERICO.Refresh();

                }));
            }
            else
            {
                colunaIndex++;
            }
        }

I inserted the speakers in my hand. I read the number of lines in the text file. I considered using indexes. And invoke is thread-safe

Answer 4

What is your version of SQL Server? Depending we can use ROWNUMBER or OFFSET FETCH. And as for Pivotgrid, you can use Virtual Scroll