CODEWITHSUNDEEP
c#mysqldatabasevisual-foxpro
CBreeze
CBreeze

Reputation: 2965

FoxPro DBF Table Into MySQL Table C#

I'm attempting to select the columns I want from a FoxPro DBF table and insert them into a MySQL table, preferably (if possible) creating the SQL Table on the fly from the columns I've chosen.

I've had a look and I unserstand this is probably going to be built around the SQL Statement "SELECT INTO FROM" but I'm unsure how I would do this, especially programatically within C# and especially scenes as I'm going from DBF to MySQL, not MySQL to MySQL.

Can someone advise on how I would do this within C#? I already have an OleDB Connection setup as such;

public void runDbfCmd()
{
    string constr = ConfigurationManager.ConnectionStrings["dbfString"].ConnectionString;

    using (OleDbConnection dbfCon = new OleDbConnection(constr))
    {
        try
        {
            dbfCon.Open();
            OleDbCommand dbfCmd = new OleDbCommand("SELECT INTO STATEMENT HERE");
            dbfCmd.ExecuteNonQuery();
        }
        catch (OleDbException ex)
        {
            throw ex;
        }
        finally
        {
            dbfCon.Close();
        }
    }
}

As you can see I'm stuck on how to build the command and whether or not this will work how I would like it to.

Upvotes: 0

Views: 1043

Answers (2)

DRapp
DRapp

Reputation: 48139

What you are looking to do can not just be done from C# from a VFP database and directly into SQL in one statement as you are attempting. Reason being. You can't have one connection pointing to two different database types... VFP and MySQL respectively.

What you would need to do is...

Open Connection to VFP,
select * from YourTable and fill into a C# data table... 
Look into OleDbDataAdapter.Fill()

Once the data has been pulled from VFP locally, you can then open a connection to your MySQL Database, create the table, and then for each row in the localized VFP table, insert into the MySQL.

Alternatively, once local in C#, you could possibly dump to a standard format that MySQL can do a bulk insert into.

But again, you can't go directly from one to the other.

Upvotes: 1

Stefan Wuebbe
Stefan Wuebbe

Reputation: 2149

If you want to do it on the FoxPro side by sending SQL statements there, the FoxPro statement Select ... From ... Into ... as you proposed would probably not do what you want because it would create a FoxPro "Into" destination, i.e. a DBF table or a "cursor" or an array.

Whereas on the FoxPro side, SQL Insert Into ... Select ... From ... would be able to do what you want if the "into" destination is a Visual FoxPro "CursorAdapter" object, or a "Remote View" (residing in a FoxPro "database" DBC), which both could be connected to a MySQL database via ODBC, and the former also via OleDB. If you do not have either of them already prepared on the FoxPro side, you would need a VFP development environment to create them because not all FoxPro commands and functions are supported by its OleDB driver, see also "Supported Visual FoxPro Commands and Functions in OLE DB Provider" https://msdn.microsoft.com/en-us/library/80x51c04%28v=vs.80%29.aspx

Assumed you do not have VFP, perhaps it would be easier to do most things on the C# side?, like sending "Select ... From ..." statements to the FoxPro DB, retrieving and intermediately storing the result on the C# side, and then send "Insert ... Into ..." statements to the MySQL DB, and also "Create Table ..." statements if you will

Upvotes: 1

Related Questions