Import the DBF file to Sqlserver database _ MySQL

Project problems: the user selects N dbf files to import to the sql2005 database. because the dbf table structure is changing every year, it does not exist in sql2005 at all. it is necessary to automatically create a table based on the dbf structure every year. Solution (excerpted from the network): -- Method 1: select * I... project problems: the user selects N dbf files to import to the sql2005 database. because the dbf table structure is changing every year, it does not exist in sql2005 at all. it is necessary to automatically create a table based on the dbf structure every year.

Solution (extracted from the network ):

-- Method 1:

Select * into the SQL table name to be generated from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase IV; HDR = NO; IMEX = 2; DATABASE = c:/', 'select * from dbf table name. dbf ')

-- Method 2:

Select * into the SQL table name to be generated from OPENROWSET ('Microsoft. JET. OLEDB.4.0 ', 'dbase III; HDR = NO; IMEX = 2; DATABASE = c:/', 'select * from dbf table name. dbf ')

-- Method 3:

Select * into the SQL table name to be generated from openrowset ('msdasql ', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = c :/', 'select * from dbf table name. dbf ')

-- After the SQL SERVER is imported using the first two methods, if the source table is opened with VFP, the system does not prompt "file cannot be accessed". after the statement is executed, the source table is disabled. However, it is not satisfactory that the SQL table fields in the source table are converted to NVARCHAR after being imported using the first two methods.

-- The third method has one drawback: after importing the DBF table into SQL Server, you can use VISUAL FOXPRO to open the DBF table immediately and the system will prompt "file access unavailable ", that is, the table is still opened by SQL. However, after about one minute, you can open the DBF table, indicating that the analyzer will shut down the table after a period of time.

You can directly import the dbf file to the sqlserver database, or import the dbf file to the dataset file before importing the dataset data to the database. The implementation of importing a dbf file to dataset is as follows:

System. data. Odbc package needs to be introduced

Public DataSet importDbfToDataSet (string FilePath, string tabname) {string strConnection = @ "Dsn = Visual FoxPro Tables; sourcedb =" + FilePath. substring (0, FilePath. lastIndexOf ("//") + "; sourcetype = DBF; exclusive = No; backgroundfetch = Yes; collate = Machine"; // for connection strings, pay attention to version issues: string strSelect = "SELECT * FROM" + tabname; OdbcConnection thisConnection = new OdbcConnection (strConnection); thisConnection. open (); OdbcDataAdapter thisAdapter = new OdbcDataAdapter (strSelect, thisConnection); DataSet thisDataSet = new DataSet (); try {thisAdapter. fill (thisDataSet);} catch (Exception e) {throw e;} finally {thisConnection. close () ;}return thisDataSet ;}

The following program uses the Timer to set and regularly store the local dbf file to the database table. this dbf file will be overwritten on a regular basis.

Class Connect {// define the connection String, connection object, command object private String connectionstr; private SqlConnection connection; private SqlCommand command; private DataSet dataset; public Connect () {connectionstr = "Server = 192.168.88.59; Initial Catalog = Test; User ID = sa; Password = sasa;"; connection = new SqlConnection (connectionstr); dataset = new DataSet (); command = connection. createCommand (); connection. open ();} public void CreateTimer () {Timer timer = new Timer (); timer. enabled = true; timer. interval = 60*1000; // Set timer for one minute. elapsed + = new ElapsedEventHandler (timer_Elapsed);} void timer_Elapsed (object sender, ElapsedEventArgs e) {Connect c = new Connect (); int minute = e. signalTime. minute; int iminute = 5; if (minute = iminute) // set the fifth Minute of every hour to execute c. insert ();} private void Insert () {// string filepath = "C ;//"; // import the dbf file to the table string creattb = "select * into tablename from OPENROWSET ('msdasql ', 'driver = Microsoft visual foxpro driver; sourcedb = c ://; sourceType = dbf', 'select * from rsz1031.dbf') "; SqlCommand mycommand = new SqlCommand (creattb, connection); mycommand. executeNonQuery ();}}

1. unable to initialize the data source object of the ole db access interface "msdasql" linked to the server "(null ";

The reason is that the system where the sql2005 database is installed does not have the VFPODBC driver, which is downloaded and installed at http://msdn.microsoft.com/en-us/vfoxpro/bb213233.aspx.

2. the SQL Server blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component;

The reason is that "openrowset and opendatasource support is not enabled in the" peripheral application configurator of the function ". you only need to enable the" peripheral application configurator of the function.

The preceding figure shows how to import the DBF file to _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!