Read .DBF file and convert to .BTR file using C#.NET 2.0

[sadhramani]

I have certain .DBF files (dBASE files, which I am able to open it in Excel), which has to be converted to .BTR file using C#.NET 2.0

I have Visual Studio 2005 environment. Also I have Pervasive v10.10 installed. I would require some sample code, written in .NET which reads .DBF files and converts those to .BTR files

 

[mirtheil]

There are no specific samples that would show exactly what you want. What have you done so far? Are you having a specific problem?

Basically, you will want to read the DBF files using ODBC, OLEDB,or whatever method you want. Then, you'd use either Btrieve API, ODBC, OLEDB, Managed Provider (Pervasive.Data.SqlClient) to write to the BTR files.

Btrieve/Pervasive doesn't store field metadata in the data file. If all you have is a BTR file, you would need the record layout so that you can use the Btrieve API or create a set of DDFs (Data Dictionary Files) to describe the table and record layouts.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sadhramani]

Then, you'd use either Btrieve API, ODBC, OLEDB, Managed Provider (Pervasive.Data.SqlClient) to write to the BTR files.

Can you pls elaborate on this point? I had already downloaded the Managed Provider (Pervasive.Data.SqlClient.dll) and added as a reference to my .NET application.

What are the classes available to manipulate the .DBF file and to create a .BTR file?

 

[mirtheil]

I don't know what classes are available for DBF files.

By creating a .BTR file, do you mean that you don't have an existing file? If you are creating a brand new file that will not be used by another application, you can use the Managed Provider to issue a CREATE TABLE statement to create the data file and the table entry in a PSQL database.

Here's a very simple C# application that shows how to use the Managed Provider:

using System;
using System.Data;
using Pervasive.Data.SqlClient;
using System.IO;


namespace SimpleAdoNetConsole
{
	class Class1
	{
        [STAThread]
      	static void Main(string[] args)
		{
                #region ADO.NET
                try
                {
                    Console.WriteLine("Using ADO.NET.");
                    PsqlConnection conn = new PsqlConnection("ServerDSN=demodata;ServerName=localhost;");
                    conn.Open();
                    Console.WriteLine("ServerName: " + conn.ServerName.ToString());
                    Console.WriteLine("ServerDSN: " + conn.ServerDSN.ToString());
                    //Create a new table
                    string SQLstr = "create table t1 (f1 char(10))";

                    PsqlCommand DBCmd = new PsqlCommand(SQLstr, conn);
                    int iRes = DBCmd.ExecuteNonQuery();
                    
                    // insert a record
                    SQLstr = "insert into t1 (f1) values ('test value')";
                    DBCmd.CommandText = SQLstr;
                    iRes = DBCmd.ExecuteNonQuery();
                    
                    //Read the data in the table
                    SQLstr = "Select * from t1";
                    DBCmd.CommandText = SQLstr;
                    PsqlDataReader myDataReader;
                    myDataReader = DBCmd.ExecuteReader();
                    Console.WriteLine("FieldCount: " + myDataReader.FieldCount.ToString());
                    while (myDataReader.Read())
                    {
                        for (int i = 0; i < myDataReader.FieldCount; i++)
                        {
                           Console.WriteLine("Field " + i.ToString() + ": " + myDataReader[i].ToString());
                        }
                    }
                    myDataReader.Close();
                    conn.Close();
                    Console.WriteLine("Press Enter to continue");
                    Console.ReadLine();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.ReadLine();
                }
                #endregion

		}
        
	}
}

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sadhramani]

Thanks for the sample code.

The basic requirement is like this:
1) There are certain old C programs, which should be replaced with C#.NET programs.
2) The C programs reads the .DBF file, converts it to new .BTR file. Yes, I don't have a BTR file. But I have the structure for the .DBF files.

So I would a sample code which will read the .DBF file and convert it to a new .BTR file?

 

[sadhramani]

Hi Mirtheil,

The sample program that you gave works fine. It basically generates T1.MKD file in the Pervasive directory.

But I wanted to create T1.BTR file, basically a Btrieve file.

 

[mirtheil]

An MKD and a BTR file have the same format. There is no conversion needed. Pervasive/Btrieve does not require a specific file extension.
If you really need the file to be .BTR, look at the "USING" clause on the CREATE TABLE statement.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sadhramani]

Thank you so much for your reply.....

I am trying to use the "USING" clause on the CREATE TABLE statement in C#.NET code.

string SQLstr = "SELECT * INTO GLDDBF USING 'E:\\dBase\\GLD0DETL.DBF'";

Here are my questions:

1) I wanted to create a new table with the same structure like 'GLD0DETL.DBF'. The reason behind this is, the .DBF file might change in the future. Everytime this program runs, a new GLD0DETL.MKD should be generated with the structure what GLD0DETL.DBF has.

2) My next step would be to import the data from the .DBF file to .MKD file.

Pls help....

 

[sadhramani]

If there is any approach for the previous post, you can let me know.

Or else, I had currently created a DataTable using C#.NET, which has the structure of .DBF file.

Using Pervasive SQL, I need to create a .MKD file with the help of the above created DataTable.

Pls help...

 

[sadhramani]

conn = new OdbcConnection(@"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=E:\dBase\GLD0DETL.DBF;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;");
conn.Open();
sqlStr = "Select * from E:\\dBase\\GLD0DETL.DBF";
DataTable dt = new DataTable();
cmd = conn.CreateCommand();
cmd.CommandText = sqlStr;
dt.Load(cmd.ExecuteReader());
foreach (DataColumn column in dt.Columns)
{
Console.WriteLine("{0} = {1} = {2}", column, column.DataType,column.MaxLength);
}
conn.Close();

Using the above piece of code I am able to retrieve the column name, datatype for the column and the length of the column.

Using this information, I had to create a .MKD file using C#.NET as front end and Pervasive SQL as the back end.

 

[sadhramani]

conn = new OdbcConnection(@"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=E:\dBase\GLD0DETL.DBF;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;");
conn.Open();
sqlStr = "Select * from E:\\dBase\\GLD0DETL.DBF";
DataTable dt = new DataTable();
int count = 0;
cmd = conn.CreateCommand();
cmd.CommandText = sqlStr;
dt.Load(cmd.ExecuteReader());
StringBuilder sSqry = new StringBuilder();
sSqry.Append("CREATE TABLE GLDDBF(");
foreach (DataColumn column in dt.Columns)
{
count = count + 1;
if (count != dt.Columns.Count)
{
sSqry.Append(column + " " + Convert.ToString(column.DataType).Replace("System.String", "varchar") + "(" + (column.MaxLength) / 2 + "),");
}
else if (count == dt.Columns.Count)
{
sSqry.Append(column + " " + Convert.ToString(column.DataType).Replace("System.String", "varchar") + "(" + (column.MaxLength) / 2 + ")");
}
Console.WriteLine("{0} = {1} = {2}", column, Convert.ToString(column.DataType).Replace("System.String", "varchar"), (column.MaxLength) / 2);
}
sSqry.Append(")");
conn.Close();
PsqlConnection conn1 = new PsqlConnection("ServerDSN=DEMODATA;ServerName=hk387bmplusist;");
Console.WriteLine(sSqry);
conn1.Open();
PsqlCommand DBCmd = new PsqlCommand(Convert.ToString(sSqry), conn1);
int iRes = DBCmd.ExecuteNonQuery();

Pls find the complete code. Atlast I am able to create a .MKD file from a .DBF file.

The next step is I need to import the data from .DBF file to .MKD file.

Pls help me out on this.....

 

[mirtheil]

The short answer is that you'll need to read the records in the DBF file with a SELECT statement and a OdbcDataReader then write the record to the PSQL database using an INSERT statement and a PsqlCommand ExecuteNonQuery. The sample I gave earlier shows how to issue an Insert statement.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sadhramani]

Thank you so much Mirtheil for all the help that you had done.....
I am able to insert records....