Friday, November 9, 2012

How to Read DBF file in C# and display in a gridview

 

we can use two connections to deal with dbf files.
one is Odbc Connection and the other one is OleDb Connection

Read .Dbf file using ODBC Connection

Steps to read dbf file :
1)Create Obdc Connection object.
2)Specify connection string as follows:

obdcconn.ConnectionString = "Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" + FilePath+ ";Exclusive=No; NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";

3)Open Connection
4)write a command (query) to read data from dbf file.
5)store it in DataTable using ExecuteReader.
6)Bind DataTable to grid.

Apsx Code :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ReadDBFFile.aspx.cs" Inherits="Forum_Topics_ReadDBFFile" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblResult" runat="server" ForeColor="Red"></asp:Label>
        <br /><br />
        <asp:GridView ID="gv1" runat="server"></asp:GridView>
    </div>
    </form>
</body>
</html>

Code Behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Win32;
using System.Data.Odbc;

public partial class Forum_Topics_ReadDBFFile : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
            ReadDBFUsingOdbc();
    }

public void ReadDBFUsingOdbc()
   {

       string FilePath= "C:\\Users\\Administrator\\Desktop\\";
       string DBF_FileName = "SCTFIN.dbf";
       OdbcConnection obdcconn = new System.Data.Odbc.OdbcConnection();      
       obdcconn.ConnectionString = "Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" + FilePath+ ";Exclusive=No; NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
       obdcconn.Open();
       OdbcCommand oCmd = obdcconn.CreateCommand();
       oCmd.CommandText = "SELECT * FROM " + FilePath + DBF_FileName;

         /*Load data to table*/

       DataTable dt1 = new DataTable();
       dt1.Load(oCmd.ExecuteReader());
       obdcconn.Close();

         /*Bind data to grid*/

       gv1.DataSource = dt1;
       gv1.DataBind();


       lblResult.Text = "Congratulations, your .dbf file has been transferred to Grid.";

   }

}

Note : To open Odbc Connection  , Odbc driver should be there in your system.

Output :

ReadDBFFile_1

Read .Dbf file using OleDB Connection
Steps to read dbf file :
1)Create OleDB Connection object.
2)Specify connection string as follows:

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath+ ";Extended Properties=dBASE IV;");

                                   OR

OleDbConnection conn = new OleDbConnection(@"Provider=vfpoledb;Data Source=" + FilePath+ ";Collating Sequence=machine;");

3)Open Connection
4)write a command (query) to read data from dbf file.
5)store it in datatable using Executereader.
6)Bind datatable to grid.

Note : With OleDB Connection and can use either “ Microsoft.Jet.OLEDB.4.0” provider or “vfpoledb” provider.

vfpoledb Provider : The Visual FoxPro OLE DB Provider (VfpOleDB.dll) exposes OLE DB interfaces that you can use to access Visual FoxPro databases and tables from other programming languages and applications.

This provider can be downloaded from http://www.microsoft.com/downloads/details.ASPx?familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en 

To check if   vfpoledb provider is registered in your system , use the following function . If registered , it returns true , else false.

public static bool IsInstalled()
    {
        return Registry.ClassesRoot.OpenSubKey("TypeLib\\{50BAEECA-ED25-11D2-B97B-000000000000}") != null;
    }

Apsx Code : Same as above

Code Behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Win32;
using System.Data.Odbc;

public partial class Forum_Topics_ReadDBFFile : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        ReadDBFUsingOleDB();
    }

    public void ReadDBFUsingOleDB()
    {

        string FilePath= "C:\\Users\\Administrator\\Desktop\\";
        string DBF_FileName = "SCTFIN.dbf";
        //define the connections to the .dbf file
        //OleDbConnection conn = new OleDbConnection(@"Provider=vfpoledb;Data Source=" + FilePath+ ";Collating Sequence=machine;");
        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath+ ";Extended Properties=dBASE IV;");

        OleDbCommand command = new OleDbCommand("select * from " + DBF_FileName, conn);
        conn.Open();
      

//open the connection and read in all the data from .dbf file into a datatable
        DataTable dt = new DataTable();
        dt.Load(command.ExecuteReader());
        gv1.DataSource = dt;
        gv1.DataBind();
        conn.Close();  //close connection to the .dbf file
       

lblResult.Text = "Congratulations, your .dbf file has been transferred to Grid.";
    }
}

Output : Same as above

Posted by at
Labels:

14 comments:

  1. AtulJuly 9, 2013 at 5:19 PM

    I tried to run this code using MicroSoft.Jet.Oledb and got the error :
    'External table is not in the expected format." at the ocmd.executereader()... Any Suggestion

    Atul K Srivastava

    ReplyDelete
  2. Sailaja ReddyJuly 10, 2013 at 2:16 PM

    Hi Atul,

    DBase file (DBF file) could be of some different version.
    So while dealing with dbf files , better use "vfpoledb Provider" , as dbf files are from visual fox pro databases.

    Else try changing the version of DBF if possible , i am not sure of this.

    plz see the following link which addresses the same issue:

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/f461a6c6-3c03-46e0-8c9b-bb04a77ed2ce/microsoftodbc-dbase-driverexternal-table-is-not-in-the-expected-format-problem

    ReplyDelete
  3. UnknownNovember 6, 2013 at 6:47 PM

    This comment has been removed by the author.

    ReplyDelete
  4. UnknownNovember 6, 2013 at 6:48 PM

    Nice Man! good example.
    Was the only one that worked properly!

    ReplyDelete
  5. AnonymousMay 14, 2014 at 9:43 AM

    binding gridview to database and showing data on it

    ReplyDelete
  6. AnonymousMarch 6, 2015 at 6:37 AM

    saved my day, thank you!

    ReplyDelete
  7. AnonymousOctober 20, 2015 at 10:58 PM

    Thank you, it is a biggest support!

    ReplyDelete
  8. UnknownNovember 4, 2015 at 2:50 PM

    try this with oledb it is working 100%
    -------------------------------------------------------------
    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;

    ///
    /// Summary description for dbf
    ///
    public class dbf
    {
    private OleDbConnection conn;
    private OleDbCommand cmd;
    private OleDbDataReader dr;
    private string sqlStr = "";
    private DataSet myDataSet;
    private OleDbDataAdapter myAdapter;
    private int r_val;
    public dbf()
    {
    //
    // TODO: Add constructor logic here
    //
    }
    public void oledb_conn()
    {
    conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\mockreal\map;Extended Properties=DBASE III;");
    }
    public DataSet Execute_dataset(string sqlStr)
    {
    oledb_conn();
    conn.Open();
    //sqlStr = "update G.dbf set CRIME=10 where PARCEL_NO='20'";
    //Make a DataSet object
    myDataSet = new DataSet();
    //Using the OleDbDataAdapter execute the query
    //cmd = new OleDbCommand(sqlStr,conn);
    //int r_val = cmd.ExecuteNonQuery();
    myAdapter = new OleDbDataAdapter(sqlStr, conn);
    //Build the Update and Delete SQL Statements
    //OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(myAdapter);
    try
    {
    //Fill the DataSet with the Table 'bookstock'
    myAdapter.Fill(myDataSet);
    conn.Close();
    }
    catch (Exception oEx)
    {
    throw (oEx);
    }
    return myDataSet;
    }
    public int Execute_command(string sqlStr)
    {
    oledb_conn();
    conn.Open();
    try
    {
    cmd = new OleDbCommand(sqlStr,conn);
    r_val = cmd.ExecuteNonQuery();
    }
    catch (Exception oEx)
    {
    throw (oEx);
    }
    return r_val;
    }
    }

    ReplyDelete
  9. travellingJuly 18, 2016 at 10:37 PM

    How to bind DataGridView with DataSource? Because with the above code it does not allow to bind the gridview. Please Explain.

    Thanks!!

    ReplyDelete
  10. vignesjoseMarch 8, 2017 at 12:56 PM

    This is the useful post for me. I learn a lot of new information from your post. keep sharing. thank you for share us.
    Selenium Training in Chennai
    Selenium Course in Chennai

    ReplyDelete
  11. UnknownJuly 12, 2017 at 11:43 AM

    Got more useful details about reading dbf file.



    Visual FoxPro to .Net

    VB.Net Migration

    Convert ASP to ASP.Net

    ReplyDelete
  12. i Digital AcademySeptember 4, 2019 at 1:39 PM

    I’ve found extensive lists before, but none this informative. Thanks for sharing!
    Thanks & Regards,
    Digital Marketing Course

    ReplyDelete
  13. merlinMay 15, 2020 at 1:15 PM

    Thanks for sharing this insightful information.Worth reading.


    Selenium Training in chennai | Selenium Training in annanagar | Selenium Training in omr | Selenium Training in porur | Selenium Training in tambaram | Selenium Training in velachery


    ReplyDelete
  14. Daily GamesFebruary 20, 2022 at 9:12 PM

    Thanks For Post which have lot of knowledge and informataion thanks.... tinyMediaManager Crack




    blender crack





    Ultimate Suite for Excel Crack




    DBF Converter Crack





    SoftPerfect Network Scanner Crack




    DVDFab Passkey Crack





    The Bat Professional Crack




    AutoRun Pro Enterprise Crack




    jAlbum Crack





    PCDJ DEX Crack

    ReplyDelete

Subscribe to: Post Comments (Atom)