匯入foxpro檔案

匯入foxpro檔案

參考了下列資料

VB.Net 使用 dBase / FoxPro 資料庫

connectionstrings.com[dbf-foxpro]

整理一下

有空再來寫匯出=.=


using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data;
using System.Windows.Forms;
using System.IO;

public class FoxProDb
{
    public static Dictionary<string,string> ConnectTypeOleDb = new Dictionary<string,string>();
    public static Dictionary<string, string> ConnectTypeOdbc = new Dictionary<string, string>();
    /// <summary>
    /// Initials the connect string OLE db.
    /// </summary>
    public static void InitialConnectStringOleDb()
    {
        ConnectTypeOleDb.Add("dBASE IV","dBASE IV");
        ConnectTypeOleDb.Add("dBASE 5.0","dBASE 5.0");
        ConnectTypeOleDb.Add("dBASE III","dBASE III");
        ConnectTypeOleDb.Add("FoxPro 2.0","FoxPro 2.0");
        ConnectTypeOleDb.Add(" FoxPro 2.5"," FoxPro 2.5");
        ConnectTypeOleDb.Add("FoxPro 2.6","FoxPro 2.6");
        ConnectTypeOleDb.Add("FoxPro 3.0","FoxPro 3.0");

    }
    /// <summary>
    /// Initials the connect string ODBC.
    /// </summary>
    public static void InitialConnectStringOdbc()
    {
        ConnectTypeOdbc.Add("dBase", "Microsoft dBase Driver (*.dbf)");
        ConnectTypeOdbc.Add("VFP", "Microsoft dBase VFP Driver (*.dbf)");
        ConnectTypeOdbc.Add("FoxPro", "Microsoft FoxPro Driver (*.dbf)");
        ConnectTypeOdbc.Add("FoxProVFP", "Microsoft FoxPro VFP Driver (*.dbf)");
        ConnectTypeOdbc.Add("VisualFoxPro", "Microsoft Visual FoxPro Driver (*.dbf)");
    }


    /// <summary>
    /// Imports the fox pro OLE db.
    /// </summary>
    /// <param name="FileName">Name of the file.</param>
    /// <param name="DbProvider">The db provider.</param>
    /// <returns></returns>
    public static DataTable ImportFoxProOleDb(string FileName,string DbProvider)
    {
        try
        {            
               
            FileInfo fi = new FileInfo(FileName);
            string connectString;
            if (DbProvider == string.Empty)
                connectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fi.DirectoryName + 
                "; Extended Properties=dBASE IV; User ID=Admin;Password=;";
            else
            {
                InitialConnectStringOleDb();
                connectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fi.DirectoryName + 
                "; Extended Properties="+ ConnectTypeOleDb[DbProvider].ToString() + 
                "; User ID=Admin;Password=;";
            }
            OleDbConnection conn1 = new OleDbConnection(connectString);
            conn1.Open();
            DataTable dstxt1 = new DataTable();
            string se1 = "select * from " + fi.Name;
            OleDbDataAdapter da1 = new OleDbDataAdapter(se1, conn1);
            da1.Fill(dstxt1);
            conn1.Close();
            return dstxt1;

        }
        catch (Exception e)
        {

            Console.Write(e.ToString());
            return null;
        }
    }
    /// <summary>
    /// Imports the fox pro ODBC.
    /// </summary>
    /// <param name="FileName">Name of the file.</param>
    /// <param name="DbProvider">The db provider.</param>
    /// <returns></returns>
    public static DataTable ImportFoxProOdbc(string FileName, string DbProvider)
    {
        try
        {
            
            FileInfo fi = new FileInfo(FileName);
            string connectString;
            if (DbProvider == string.Empty)
                connectString = "DRIVER={Microsoft dBase Driver (*.dbf)};dbq="+fi.DirectoryName;
            else
            {
                InitialConnectStringOdbc();
                connectString = "DRIVER={"+ConnectTypeOdbc[DbProvider].ToString()+"};dbq=" + 
                fi.DirectoryName;
            }
            OdbcConnection conn1 = new OdbcConnection(connectString);
            conn1.Open();
            DataTable dstxt1 = new DataTable();
            string se1 = "select * from " + fi.Name;
            OdbcDataAdapter da1 = new OdbcDataAdapter(se1, conn1);
            da1.Fill(dstxt1);
            conn1.Close();
            return dstxt1;

        }
        catch (Exception e)
        {

            Console.Write(e.ToString());
            return null;
        }
    }
    /// <summary>
    /// Gets the fox pro table schema.
    /// </summary>
    /// <param name="FileName">Name of the file.</param>
    /// <param name="DbProvider">The db provider.</param>
    /// <returns></returns>
    public static DataTable GetFoxProTableSchema(string FileName, string DbProvider)
    {
        try
        {

            FileInfo fi = new FileInfo(FileName);
            string connectString;
            if (DbProvider == string.Empty)
                connectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + 
                fi.DirectoryName + "; Extended Properties=dBASE IV; User ID=Admin;Password=;";
            else
            {
                InitialConnectStringOleDb();
                connectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + 
                fi.DirectoryName + "; Extended Properties="
                + ConnectTypeOleDb[DbProvider].ToString() + "; User ID=Admin;Password=;";
            }            
            OleDbConnection conn1 = new OleDbConnection(connectString);
            conn1.Open();
            OleDbCommand cmd = conn1.CreateCommand();
            cmd.CommandText = "select * from " + fi.Name;
            OleDbDataReader dr = cmd.ExecuteReader();
            System.Data.DataTable dj = dr.GetSchemaTable();
            conn1.Close();
            return dj;

        }
        catch (Exception e)
        {
            Console.Write(e.ToString());
            return null;
        }
    }
}