SQL分布式查询

/*
测试文件内容:
e:/Module/test.xls
job_desc  job_id  max_lvl  min_lvl
记录一  1.0  11.0  11.0
记录二  2.0  22.0  22.0
记录三  3.0  33.0  33.0
记录四  4.0  44.0  44.0
e:/Module/temp.mdb文件jobs 表
job_desc job_id  max_lvl  min_lvl
记录一  1  11  11
记录二  2  22  22
记录三  3  33  33
记录四  4  44  44
e:/Module/test.csv
Fname Lname Salary Tax
smith william 1000 275
scott tiger 2000 275
John Jumangi 2500 345
sam Rooban 3524 600
peter norton 1234 320
kathy Lee 8300 1200
e:/Module/test.txt
Fname Lname Salary Tax
smith william 1000 275
scott tiger 2000 275
John Jumangi 2500 345
sam Rooban 3524 600
peter norton 1234 320
e:/Module/union1.txt
Fname Lname Salary Tax
smith william 1000 275
scott tiger 2000 275
John Jumangi 2500 345
sam Rooban 3524 600
peter norton 1234 320
e:/Module/union1.csv
Fname Lname Salary Tax
smith nana 1000 275
scott jany 2000 275
John leilei 2500 345
sam Rooban 3524 600
peter norton 1234 320

*/

--Read Excel Sheet using OpenDataSource
--使用OpenDataSource读取Excel表格
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="e:/Module/test.xls";
    User ID=Admin;Password=;Extended properties=Excel 5.0')...Sheet1$
--Read Excel Sheet using OpenRowSet
--使用OpenRowSet读取Excel表格
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;DATABASE=e:/Module/test.xls', 'Select * from [Sheet1$]')
--Read SQL Server table using OpenDataSource
--使用OpenDataSource 跨服务器查询
--经测试，Data Source 只接受机器名。而不接受IP地址
SELECT * FROM  OPENDATASOURCE('SQLOLEDB','Data Source=accp;User ID=sa;Password=').pubs.dbo.jobs
--Read SQL Server table using OPENROWSET
--使用OPENROWSET 跨服务器查询
SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=accp;UID=sa;PWD=',pubs.dbo.jobs)
--Read MS-Access table using OpenDataSource
SELECT * FROM  OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="e:/Module/temp.mdb";User ID=Admin;Password=')...jobs
--Read MS-Access table using OpenRowSet
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','e:/Module/temp.mdb';'admin';'', jobs)
--查询csv文件
--Read CSV using OpenRowSet
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=e:/Module','select top 6 * from test.csv')
--Read Text using OpenRowSet
--将上面用到的csv文件拷一份重命名为test.txt即可
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=e:/Module','select top 5 * from
test.txt')

--联合csv和txt 文件的查询
select * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=e:/Module;',
'select top 5 * from union1.txt')
 union
select * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=e:/Module;',
'select top 5 * from union1.csv')

--Not found List
--过滤查询
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=e:/Module;','select top 5 * from union1.txt')
where Lname not in
(select Lname from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=e:/Module;','select top 5 * from
union1.csv') )

--建立视图查询.txt文本文件内容
create view [MyText] as
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=e:Module;','select top 5 * from
test.txt')

Go
select * from [MyText]
go
select * from [MyText] where Fname like 'S%'
go
--建立视图查询.xls文件内容
Create view [MyExcel] as
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="e:Module/test.xls";
    User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
go
select * from [MyExcel] where job_desc like '%三%'

 

//HDR有2个值

//HDR=NO无字段列

//HDR=Yes第一行作为字段列

 

//IMEX有3个值:
//0:此Excel只可读取

//1:此Excel只可写入
//2:此Excel同时支持读取及写入。

string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID=Admin;Password=;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"", "C:\\test.xls");
            int num = 0;
           
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                string sql = "insert into [" + sheetName + "$A:H](说话人,英文,中文,起始时间,播放长度,本地路径,原路径) values("
                       + "'"+row["lesson_title"].ToString().Replace ("'","''") + "',"
                       + "'"+ row["lesson_eng"].ToString().Replace ("'","''") + "',"
                       + "'"+ row["lesson_chn"].ToString().Replace ("'","''") + "',"
                       + "'',"
                       + "'"+ row["lesson_voice_len"].ToString().Replace ("'","''") + "',"
                       + "'"+ row["lesson_short"].ToString().Replace ("'","''") + "',"
                       + "'"+ row["lesson_voice"].ToString().Replace ("'","''") + "');";
                //在Excel的Sheet1的A3到H3处插入数据
                //cmd.CommandText = "insert into [Sheet1$A3:H3] (F1,F2,F3,F4,F5,F6,F7,F8) values('" + a200.Date + "','"
                //    + a200.PreviousClosePrice + "','" + a200.OpenPrice + "','" + a200.High + "','" + a200.Low + "','" + a200.Close + "','" +
                //    a200.Change + "','" + a200.ChangeRate + "')";

                System.Data.OleDb.OleDbCommand command  = new OleDbCommand();
                command.Connection = connection;
                command.Connection.Open();
                command.CommandText = sql;
                Console.WriteLine (sql);
                try
                {
                    num  = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
              
            }