none
C# import paradox into Sql Server Compact Edition

    Question

  • Hello, I'm trying to figure out how to do this. I have tables from an old Paradox (*.db/.px files) database that I would like to directly import into a SQL Compact database. All the tables have identical structure. What I want to do is take the data from each table and directly import it into SQLCE 4.0.

    Microsoft Certified Professional



    • Edited by bbcompent1 Wednesday, July 24, 2013 1:27 PM
    Wednesday, July 24, 2013 12:20 PM

Answers

  • public DataTable readDBFile(string Path,string TableName)
            {
     
                DataTable dt = new DataTable();
                string sQry = null;
                try 
                {
     
                    string OleDbConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Paradox 5.x;";
                    OleDbConnection OLEDBConn = new OleDbConnection(OleDbConnStr);
                    sQry = "select * from ["+TableName+"]";
                    OleDbCommand cmdOledb = new OleDbCommand(sQry, OLEDBConn);
                    OleDbDataAdapter oledbAda = new OleDbDataAdapter(cmdOledb);
                    DataSet ds = new DataSet();
                    oledbAda.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                        dt = ds.Tables[0];
     
                }
                catch (Exception ex)
                {
                    AppUtility.WriteToActivityLog("LDM.RDBF()", ex.ToString(), false, ModuleName);
                    AppUtility.ShowError("LDM", "Error while reading .DB file", ModuleName);
                }
                finally
                {
                    sQry = null;
     
                }
                return dt;
            }

    See the below link, it does exactly what you want

    http://psycodedeveloper.wordpress.com/2013/01/25/c-application-to-import-paradox-data-to-sql-server/


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Prajesh Thursday, July 25, 2013 10:32 AM better
    • Proposed as answer by Prajesh Thursday, July 25, 2013 10:32 AM
    • Marked as answer by bbcompent1 Thursday, July 25, 2013 10:32 AM
    Thursday, July 25, 2013 10:31 AM
  • For what its worth, here is another option that I thought I would mention. There is a open source library that provides a simple cross vendor interface between all major databases, its called DbNetData. I found it over on CodePlex and looks really good and very easy to use.

    http://dbnetdata.codeplex.com


    Microsoft Certified Professional

    • Marked as answer by bbcompent1 Thursday, July 25, 2013 1:11 PM
    Thursday, July 25, 2013 1:11 PM

All replies

  • You can SSIS for easy import or SQL server import export wizard

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 1:07 PM
  • But this is for a desktop application I plan to deploy to users who don't have SQL Server. That is why I'm using SQL Compact Edition. Also, my user base is normal users who are not technical. Unless you are telling me that I can include SSIS with my deployment? If so, how do I do that?

    Microsoft Certified Professional

    Wednesday, July 24, 2013 1:13 PM
  • This is once time task to import the data into SQL server compact from foxpro or regular ?

    If this is one time, you can utilize SQL server compact destination from source as visual fox pro.


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 1:20 PM
  • Do you have any examples of how this is done? Understand, I'm not exactly sure how to proceed.

    Microsoft Certified Professional

    Wednesday, July 24, 2013 1:28 PM
  • http://www.mssqltips.com/sqlservertutorial/211/sql-server-integration-services-ssis-data-flow/

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 1:37 PM
  • This certainly looks promising. I trust I can include that SSIS as a project under the solution? Also, can this process be run on-demand by a button click event?

    Microsoft Certified Professional

    Wednesday, July 24, 2013 2:30 PM
  • Are you absolutely sure BIDS is usable by SQL Compact? When I fired up my SSIS project, it gave me an error saying that BIDS has to be installed by one of these editions of SQL Server 2008 R2: Standard, Enterprise, Developer, or Evaluation. I'm using SQL Express 2008 R2.

    Microsoft Certified Professional

    Wednesday, July 24, 2013 2:38 PM
  • you dont have BIDS in SQL Compact, yes this process be run on-demand by a button click event.

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 2:44 PM
  • So if Compact can't use BIDS, I'll need an alternative.

    Microsoft Certified Professional

    Wednesday, July 24, 2013 2:47 PM
  • Not sure if you can create linked server in SQL Compact ?

    if you can use the linked server to foxpro and insert the data in proc.


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 2:57 PM
  • If you can import to SQL Server, you can the use my tools to migrate from SQL Server to SQL Compact

    Please mark as answer, if this was it. Visit my SQL Server Compact blog Windows 8 Apps Showcase

    Thursday, July 25, 2013 8:16 AM
    Moderator
  • That works ok for me but I'm trying to do this for completely non-technical users. They aren't going to want to have to fiddle around with SQL Management studio and all that just to import their data. What I'd like to do is have some method of using C# in a code-behind class that would connect to the paradox database files, grab their data and deposit the extracted data into the SQL CE. I figure I could use code behind to connect to the paradox .db files, extract those tables one by one and import the table into the SQLCE table directly.

    Microsoft Certified Professional

    Thursday, July 25, 2013 10:27 AM
  • public DataTable readDBFile(string Path,string TableName)
            {
     
                DataTable dt = new DataTable();
                string sQry = null;
                try 
                {
     
                    string OleDbConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Paradox 5.x;";
                    OleDbConnection OLEDBConn = new OleDbConnection(OleDbConnStr);
                    sQry = "select * from ["+TableName+"]";
                    OleDbCommand cmdOledb = new OleDbCommand(sQry, OLEDBConn);
                    OleDbDataAdapter oledbAda = new OleDbDataAdapter(cmdOledb);
                    DataSet ds = new DataSet();
                    oledbAda.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                        dt = ds.Tables[0];
     
                }
                catch (Exception ex)
                {
                    AppUtility.WriteToActivityLog("LDM.RDBF()", ex.ToString(), false, ModuleName);
                    AppUtility.ShowError("LDM", "Error while reading .DB file", ModuleName);
                }
                finally
                {
                    sQry = null;
     
                }
                return dt;
            }

    See the below link, it does exactly what you want

    http://psycodedeveloper.wordpress.com/2013/01/25/c-application-to-import-paradox-data-to-sql-server/


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Prajesh Thursday, July 25, 2013 10:32 AM better
    • Proposed as answer by Prajesh Thursday, July 25, 2013 10:32 AM
    • Marked as answer by bbcompent1 Thursday, July 25, 2013 10:32 AM
    Thursday, July 25, 2013 10:31 AM
  • For what its worth, here is another option that I thought I would mention. There is a open source library that provides a simple cross vendor interface between all major databases, its called DbNetData. I found it over on CodePlex and looks really good and very easy to use.

    http://dbnetdata.codeplex.com


    Microsoft Certified Professional

    • Marked as answer by bbcompent1 Thursday, July 25, 2013 1:11 PM
    Thursday, July 25, 2013 1:11 PM
  • Thats good

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 25, 2013 1:16 PM
  • I did, I even did a +1 for voting. Thanks again.

    Microsoft Certified Professional

    Wednesday, August 21, 2013 3:54 PM
  • Where does AppUtility come from? It must be some custom .net namespace or add-on DLL.

    Microsoft Certified Professional

    Wednesday, August 21, 2013 4:01 PM
  • Ok, I figured out my own logging solution so that's fine. However, now I'm running into an error. It reads:

    'C:\BG Tracker 4\Data\BasalRates.DB' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

    However, the path is indeed valid, it does exist. I was thinking maybe a problem with permissions so for overkill sake, I tested by giving Everyone full control, no dice. Here's what my code looks like:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.OleDb;
    using System.Linq;
    using System.Text;
    using Utilities;
    
    namespace BgTracker.NET
    {
        class ImportOldBG
        {
            public DataTable readDBFile(string Path, string TableName)
            {
    
                DataTable dt = new DataTable();
                string sQry = null;
                try
                {
    
                    string OleDbConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Paradox 5.x;";
                    OleDbConnection OLEDBConn = new OleDbConnection(OleDbConnStr);
                    sQry = "select * from [" + TableName + "]";
                    OleDbCommand cmdOledb = new OleDbCommand(sQry, OLEDBConn);
                    OleDbDataAdapter oledbAda = new OleDbDataAdapter(cmdOledb);
                    DataSet ds = new DataSet();
                    oledbAda.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                        dt = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    ExceptionLogger logger = new ExceptionLogger();
                    logger.AddLogger(new TextFileLogger());
                    logger.AddLogger(new WindowLogger());
                    logger.LogException(ex);
                }
                finally
                {
                    sQry = null;
    
                }
                return dt;
            }
        }
    }
    
    
    
    
    Any ideas, anyone???

    Microsoft Certified Professional

    Thursday, August 22, 2013 4:55 PM
  • Ok, I'm back, ready to take a whack at this again. Now, last I recall I was running into errors trying to import the data and I think I may be on to something. Each table being imported has a RecID variable. Is there a way in my query to exclude this column but still bring all the data in? Maybe using a temp table to snag the data from the paradox DB and then import in from the temp table? One thing that I have to be sure of is not importing records that are already in the DB based.

    Microsoft Certified Professional

    Monday, December 30, 2013 12:30 PM