How to Read/Write DBF file In C# use OleDb

How to Read/Write DBF file In C# use OleDb

• Bjoern Graf

  Grap the latest OleDB Provider for FoxPro here: Microsoft OLE DB Provider for Visual FoxPro 9.0.

  Now you can use OleDB to connect to your database and insert the values:

  // TODO: Set date value. DateTime dateValue = DateTime.Now; // Initialize database connection. OleDbConnection dbConn = new OleDbConnection(@"Provider=vfpoledb.1;Data Source=C:\MyDataDirectory\;Collating Sequence=general"); try {     // Open connection.     dbConn.Open();     using(OleDbCommand command = dbConn.CreateCommand())     {         // Create paremeter for Date field.         OleDbParameter pField1 = new OleDbParameter("@Field1", OleDbType.Date);         pField1.Value = dateValue;         // Set insert query and add paremeter.         command.CommandText = "INSERT INTO MyTable(Field1) VALUES(@Field1)";         command.Parameters.Add( pField1 );         int effected = command.ExecuteNonQuery();         MessageBox.Show( this, "INSERT INTO effected " + effected + " row(s)." );     } } finally {     // Close connection.     dbConn.Close(); }

  
  
  
  

• JaredJ

  I will try it! thx!

  
  

• dave45

  You say you have an exception but doesn't know how to write the code What is the code that you are using now that gives the exception.
  
  Are you sure the field type is a DataTime field and now a Guid field or that you are inserting a non-Guid into a Guid field
  
  
  

• John in SF

  Hi pj.

  I Try it by use Hard-code to insert data.It can insert new rows into DBF files.
  but when I change it to use OleDbparameter ,It can't work now.
  I don't know what's wrong with it.

  I think may be :
  1,The Datatype Change wrong. OleDbType.Type is not fitted in DBF Type.
  like Logic(Query Out is T or F).
  2,Make Parameters wrong.
  

  
  

• rishi gupta

  Do you get an exception when inserting a DataTime value

  Can you post your relative code

  
  
  

• ditu65

  Yes ,Got a Exception .

  Say that "A Abnormal GUID"

  I want insert a Datetime object into .Somebody told me write sql like:

  insert into [5.dbf] values({^2006-02-20},'leton',23,'question',T)

  I don't know how to write this sentence to insert a line into dbf file.

  
  

• Newbie_Reza

  Try Failed.

  I Feel So Bad.

  I try to insert a new line use PJ's way.but in the end it give me a "Syntax error";

   

  for(int j = 0;j <fields.Length;j ++)
   {
        para[j ] = new OleDbParameter();
        para[j ].ParameterName  = "@para"+j ;
        byte[]  temp = System.Text.Encoding.Default.GetBytes(fields[j ].FieldName);

        para[j ].OleDbType = fields[j ].GetTypeStr();
        System.Console.WriteLine(fields[j ].FieldName);
        para[j ].Value = fields[j] .Format(tempValue[j]);

  }
       string insert = "insert into "+ Path.GetFileName(filePath)+"(";
  
       for(int j  = 0;j <fields.Length;j ++)
       {
            insert = insert +fields[j ].FieldName;
            if(j <fields.Length-1)
           {
                insert = insert + ",";
            }
           else if (j  == fields.Length-1 )
           {
               insert = insert + ") values(";
            }
       }

  
       for(int j  =0;j <fields.Length;i++)
       {
            insert = insert + "@para"+j ;
            if(j <fields.Length-1)
           {
              insert = insert + ",";
           }
           else if (j  == fields.Length-1 )
           {
              insert = insert + ")";
            }
       }
  
       cmd.CommandText = insert;
       for(int j = 0;j<para.Length;j++)
       {
            cmd.Parameters.Add(para[j]);
       }
           cmd.ExecuteNonQuery();

   

  
  

• kevM

  I know use parameters is a goog way.but my dbf tables have 50+ columns and every data type is using in table.

  If A columns with Date type requried insert null into it.How to do it

  I need to find a way to take a large dbf file out.

  
  

• Red Jazz Of Progressive Blood

  Hi Leton,

  FoxPro doesn't have a GUID data type, although there is an AutoInc data type. This makes me really curious that you got an error message about an abnormal GUID.

  
  
  

• rauhanlinnake

  insert into [5.dbf] values({^2006-02-20},'leton',23,'question',T)

  Hi Leton,

  This code will insert a FoxPro Date value but not a DateTime value.

  First, be sure to verfiy whether you've got a Date or DateTime value in the FoxPro table. Using SQL Pass-through, for a DateTime value you can use DToT (Date to Time), or write the DateTime value in the correct format:

  Insert Into MyTable Values (DToT{^2006/02/20})

  Insert Into MyTable Values ({^2006/02/20 20:33:19})

  Even so, you'll always be better off with a parameterized Insert statement as PJ. suggested. That way OLE DB takes care of "translating" the data value.

  One more thing - I see you've got a T as your last parameter. Are you trying to represent "True " If that is the case, FoxPro Logical (Boolean) values are written in text as .T. and .F. and Null is written as .NULL. .

  Insert Into [5.dbf] values ({^2006/02/20 20:33:19}, 'leton', 23, 'question', .T.)

  Oh - and I see you've got Insert Into [5.dbf]... Is "[5.dbf]" the name of the table  In SQL Pass-through a table is usually represented by it's name only, without the DBF extension. Also, Fox tables must begin with an alpha character or an underscore. Digits can only be used after the first character. Even though you may be able to use a non-standard table name, if you have the abililty to change the name of this table you should do so.

   

  
  
  

• Jared Turner

  Hi Leton,

  It's hard to know what the syntax error is referring to without knowing the value of the string you're passing.

  Can you post the string value of "insert" you finally end up with Can you take out all the string building code and hard-code that value for "insert" and try the code

  
  
  

• Nadeem16464

  I don't know how description my question now with my poor english.
  The DBF File have a  primary key

  Must I Create a GUID column for  this file

   

  If I can't find a way to solution this.I can only to write DBF File use FileStream.
  

   

   

  
  

• Mister2zx3

  Do you get an exception
  
  If so, provide us the Stack-trace and the relative code.
  
  
  
• How to Read/Write DBF file In C# use OleDb