howto convert datatable or datagrid to .dbf flie in pls urgent RRS feed

  • Question

  • hi to all,

    i want to export dataset or datagrid to .dbf file pls urgent

    • Moved by Andrea Montanari Friday, April 30, 2010 11:18 PM moving to ADO.Net forum (From:SQL Server Express)
    Friday, April 30, 2010 3:13 PM


  • Microsoft has produced quite a few components capable of talking to a .dbf file, but there are multiple products that write to .dbf files, and multiple versions of those products.  Do you know which one you're targeting?  Also, is your code running on a 64-bit or 32-bit version of Windows?  There's a new Access OLE DB provider that supports 64 bit that's currently in beta.  Short of that, all other components I know of that can talk to a .dbf file are 32-bit only.  If you're working in 32-bit mode, the Visual FoxPro OLE DB provider is probably your best bet.

    If you need to create a .dbf file, that should be fairly straightforward using a CREATE TABLE query.  The exact syntax may depend on the type of .dbf file you're looking to create.

    Once you're able to access your .dbf file, you'll want to generate a parameterized INSERT INTO query and execute it for each row.  If the data resides only in a grid, that's likely your best option.  If your data resides in a DataTable, you can simplify things using a DataAdapter.  The DataAdapter can submit pending changes stored in a DataTable to a database.  You can either supply your own logic in code (which requires more code on your part, but code that will execute much, much faster) or through the use of a CommandBuilder, that queries the database for schema information (table name, column names and data types, key information, etc.) necessary to generate that updating logic for you.

    Here's some sample code that uses the VFP OLE DB provider, a DataTable and a DataAdapter with updating logic specified in code.  I hope this information proves helpful.

    //DataTable of rows to send to .dbf file
    //Since we want the DataAdapter to treat these rows as pending inserts
    //   the RowState of all such rows needs to be marked as Added.
    //   If yours aren't, then call the SetAdded method on each DataRow.
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("OtherField", typeof(string));
    table.Rows.Add(1, "First row");
    table.Rows.Add(2, "Second row");
    table.Rows.Add(3, "Third row");
    //Using a ConnectionStringBuilder to create the connection string
    OleDbConnectionStringBuilder builder = 
        new OleDbConnectionStringBuilder();
    builder.Provider = "VFPOLEDB";
    builder.DataSource = directory;
    OleDbConnection connection = 
        new OleDbConnection(builder.ConnectionString);
    //Clear or create the table as necessary
    OleDbCommand command = connection.CreateCommand();
    if (System.IO.File.Exists(directory + @"\DbfTest.dbf"))
        command.CommandText = "DELETE FROM DbfTest";
        command.CommandText = 
            "CREATE TABLE DbfTest (ID int, OtherField varchar(50), " +
                                   "PRIMARY KEY (ID))";
    //Create the DataAdapter
    command.CommandText = "SELECT ID, OtherField FROM DbfTest";
    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
    //Create the insert logic
    OleDbCommand insertCommand = connection.CreateCommand();
    adapter.InsertCommand = insertCommand;
    insertCommand.CommandText = 
        "INSERT INTO DbfTest (ID, OtherField) VALUES (?, ?)";
    insertCommand.Parameters.Add("pID", OleDbType.Integer, 4, "ID");
    insertCommand.Parameters.Add("pOtherField", OleDbType.VarChar, 
                                 50, "OtherField");
    insertCommand.UpdatedRowSource = UpdateRowSource.None;
    //Or use a CommandBuilder
    //OleDbCommandBuilder commandBuilder = 
    //    new OleDbCommandBuilder(adapter);
    //Submit the pending updates

    David Sceppa
    Monday, May 3, 2010 5:26 PM