Id like to help. However, id stick with the plain process with OLEDB for VPF.
CREATE TABLE DBF
This could be your create table script for .DBF
CREATE TABLE "C:\test.dbf" ([ID] Numeric(18,0), [Name] Char(100) NULL, [Details] Char(200) NULL, [Status] Logical NULL, [CreateDate] DATETIME NULL)
Then, you can call this one from an OLE DB script like as used below.
string vpfScript = "CREATE TABLE \"C:\test.dbf\" ([ID] Numeric(18,0), [Name] Char(100) NULL, [Details] Char(200) NULL, [Status] Logical NULL, [CreateDate] DATETIME NULL)";
string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
OleDbConnection connection = new OleDbConnection(connectionString);
using (OleDbCommand scriptCommand = connection.CreateCommand())
{
connection.Open();
scriptCommand.CommandType = CommandType.StoredProcedure;
scriptCommand.CommandText = "ExecScript";
scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = vfpScript;
scriptCommand.ExecuteNonQuery();
}
IMPORT ROW IN TABLE
To import rows in a DBF table, its not as far as the usual SQL scripts we do in other DB's.
string vpfScript = "INSERT INTO \"C:\test.dbf\" ([ID], [Name], [Details], [Status], [CreateDate]) VALUES (1,'test john','test details',.t.,{^2015-09-15)";
string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
OleDbConnection connection = new OleDbConnection(connectionString);
using (OleDbCommand scriptCommand = connection.CreateCommand())
{
connection.Open();
scriptCommand.CommandType = CommandType.StoredProcedure;
scriptCommand.CommandText = "ExecScript";
scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = vfpScript;
scriptCommand.ExecuteNonQuery();
}
You can just now change the values what fits your need, just like the usage below.
DataTable dt = new DataTable(); // assuming this is already populated from your SQL Database.
StringBuilder buildInsert = new StringBuilder();
string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
OleDbConnection connection = new OleDbConnection(connectionString);
using (OleDbCommand scriptCommand = connection.CreateCommand())
{
connection.Open();
foreach(DataRow dr in dt.Rows)
{
string id = dr["ID"].ToString();
string name = dr["Name"].ToString();
string details = dr["Details"].ToString();
string status = Convert.ToBoolean(dr["Status"]) ? ".t." : ".f.";
string createDate = "{^" + Convert.ToDateTime(dr["CreateDate"]).ToString("yyyy-MM-dd") + "}";
builderInsert.Append("INSERT INTO \"C:\test.dbf\" ([ID], [Name], [Details], [Status], [CreateDate]) VALUES (" + id + ",\"" + name + "\",\"" + details + "\"," + status + "," + createDate + ")" + Environment.NewLine);
scriptCommand.CommandType = CommandType.StoredProcedure;
scriptCommand.CommandText = "ExecScript";
scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = builderInsert;
scriptCommand.ExecuteNonQuery();
builderInsert = "";
}
}
Please let me know if you have other concerns.
Don't forget to install this one in your machine.
VFP OLE DB
mysql
,postgresql
,sql-server
,oracle
ordb2
- or something else entirely. – Penholder