Home Database Mysql Tutorial 将DBF文件导入Sqlserver数据库_MySQL

将DBF文件导入Sqlserver数据库_MySQL

May 27, 2016 pm 07:11 PM

项目中的问题:用户选择N个dbf文件导入sql2005数据库,由于每年dbf表结构都在变化,所以在sql2005中根本就不存在,需要每年根据dbf的结构自动建表。

 

解决方法(摘抄自网络):     

 

--方法一:

 

         select * into 要生成的SQL表名 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2;DATABASE=c:/','select * from dbf表名.dbf')

 

--方法二:

 

         select * into 要生成的SQL表名 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase III;HDR=NO;IMEX=2;DATABASE=c:/','select * from dbf表名.dbf')

 

--方法三:

 

         select * into 要生成的SQL表名 from openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/','select * from dbf表名.DBF')

 

--用前两种方法导入SQL SERVER后,源表再用VFP打开就不提示“不能存取文件”,说明语句执行后就把源表关闭了。不过也有不尽人意的地方,就是用前两种方法导入后,源表中的字符型字段导入后SQL表字段对应变成NVARCHAR了。

 

--第三种方法有一个缺点:把DBF表导入SQL Server中后,马上用VISUAL FOXPRO打开DBF表,会提示“不能存取文件”,即这个表还被SQL打开着呢。可是过了1分钟左右,再打开DBF表就可以了,说明经过一段时间后查询分析器才把这个表关闭。

 

可以直接将dbf文件导入sqlserver数据库,也可以先将dbf文件导入dataset,再将dataset的数据导入数据库。dbf文件导入dataset的具体实现方法如下:

 

需要引入system.data.Odbc包


 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";

            //对于连接串,注意版本问题

            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;
        }
Copy after login


下面的程序通过Timer定时器,设置定时将本地dbf文件直接存入数据库表,该dbf文件会定时被覆盖掉。


 class Connect
    {
        //定义连接字符串,连接对象,命令对象
        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;//设置一分钟
            timer.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)//设置每个小时的第五分钟执行
            c.Insert();
        }
        private void Insert()
        {
            //string filepath = "C;//";
            //将dbf文件导入指定数据库的表
            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();
        }
    }
Copy after login


 

1.无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "msdasql" 的数据源对象”;

 

原因是安装sql2005数据库的系统中没有 VFPODBC驱动,到http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx下载并安装

 

 2.SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问;

 

原因是因为“功能的外围应用配置器”中没有“启用openrowset和opendatasource支持”,只需要打开“功能的外围应用配置器”设置一下就可以。

以上就是将DBF文件导入Sqlserver数据库_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!


Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to open phpmyadmin How to open phpmyadmin Apr 10, 2025 pm 10:51 PM

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

MySQL: An Introduction to the World's Most Popular Database MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

How to use single threaded redis How to use single threaded redis Apr 10, 2025 pm 07:12 PM

Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.

MySQL's Place: Databases and Programming MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Why Use MySQL? Benefits and Advantages Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to connect to the database of apache How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

How to start mysql by docker How to start mysql by docker Apr 15, 2025 pm 12:09 PM

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

Centos install mysql Centos install mysql Apr 14, 2025 pm 08:09 PM

Installing MySQL on CentOS involves the following steps: Adding the appropriate MySQL yum source. Execute the yum install mysql-server command to install the MySQL server. Use the mysql_secure_installation command to make security settings, such as setting the root user password. Customize the MySQL configuration file as needed. Tune MySQL parameters and optimize databases for performance.

See all articles