同步方法和异步方法的区别
同步方法调用在程序继续执行之前需要等待同步方法执行完毕返回结果
异步方法则在被调用之后立即返回以便程序在被调用方法完成其任务的同时执行其它操作
异步编程概览
.NET Framework 允许您异步调用任何方法。定义与您需要调用的方法具有相同签名的委托;公共语言运行库将自动为该委托定义具有适当签名
的 BeginInvoke 和 EndInvoke 方法。
BeginInvoke 方法用于启动异步调用。它与您需要异步执行的方法具有相同的参数,只不过还有两个额外的参数(将在稍后描述)。
BeginInvoke 立即返回,不等待异步调用完成。
BeginInvoke 返回 IasyncResult,可用于监视调用进度。
EndInvoke 方法用于检索异步调用结果。调用 BeginInvoke 后可随时调用 EndInvoke 方法;如果异步调用未完成,EndInvoke 将一直阻塞到
异步调用完成。EndInvoke 的参数包括您需要异步执行的方法的 out 和 ref 参数(在 Visual Basic 中为 <Out> ByRef 和 ByRef)以及由
BeginInvoke 返回的 IAsyncResult。
四种使用 BeginInvoke 和 EndInvoke 进行异步调用的常用方法。调用了 BeginInvoke 后,可以:
1.进行某些操作,然后调用 EndInvoke 一直阻塞到调用完成。
2.使用 IAsyncResult.AsyncWaitHandle 获取 WaitHandle,使用它的 WaitOne 方法将执行一直阻塞到发出 WaitHandle 信号,然后调用
EndInvoke。这里主要是主程序等待异步方法,等待异步方法的结果。
3.轮询由 BeginInvoke 返回的 IAsyncResult,IAsyncResult.IsCompeted确定异步调用何时完成,然后调用 EndInvoke。此处理个人认为与
相同。
4.将用于回调方法的委托传递给 BeginInvoke。该方法在异步调用完成后在 ThreadPool 线程上执行,它可以调用 EndInvoke。这是在强制装
换回调函数里面IAsyncResult.AsyncState(BeginInvoke方法的最后一个参数)成委托,然后用委托执行EndInvoke。
警告 始终在异步调用完成后调用 EndInvoke。
以上有不理解的稍后可以再理解。
例子
1)先来个简单的没有回调函数的异步方法例子
请再运行程序的时候,仔细看注释,对理解很有帮助。还有,若将注释的中的两个方法都同步,你会发现异步运行的速度优越性。
using System;
namespace ConsoleApplication1
{
class Class1
{
//声明委托
public delegate void AsyncEventHandler();
//异步方法
void Event1()
{
Console.WriteLine("Event1 Start");
System.Threading.Thread.Sleep(4000);
Console.WriteLine("Event1 End");
}
// 同步方法
void Event2()
{
Console.WriteLine("Event2 Start");
int i=1;
while(i<1000)
{
i=i+1;
Console.WriteLine("Event2 "+i.ToString());
}
Console.WriteLine("Event2 End");
}
[STAThread]
static void Main(string[] args)
{
long start=0;
long end=0;
Class1 c = new Class1();
Console.WriteLine("ready");
start=DateTime.Now.Ticks;
//实例委托
AsyncEventHandler asy = new AsyncEventHandler(c.Event1);
//异步调用开始,没有回调函数和AsyncState,都为null
IAsyncResult ia = asy.BeginInvoke(null, null);
//同步开始,
c.Event2();
//异步结束,若没有结束,一直阻塞到调用完成,在此返回该函数的return,若有返回值。
asy.EndInvoke(ia);
//都同步的情况。
//c.Event1();
//c.Event2();
end =DateTime.Now.Ticks;
Console.WriteLine("时间刻度差="+ Convert.ToString(end-start) );
Console.ReadLine();
}
}
}



2)下面看有回调函数的WebRequest和WebResponse的异步操作。
using System;
using System.Net;
using System.Threading;
using System.Text;
using System.IO;

// RequestState 类用于通过
// 异步调用传递数据
public class RequestState
{
const int BUFFER_SIZE = 1024;
public StringBuilder RequestData;
public byte[] BufferRead;
public HttpWebRequest Request;
public Stream ResponseStream;
// 创建适当编码类型的解码器
public Decoder StreamDecode = Encoding.UTF8.GetDecoder();
public RequestState()
{
BufferRead = new byte[BUFFER_SIZE];
RequestData = new StringBuilder("");
Request = null;
ResponseStream = null;
}
}
// ClientGetAsync 发出异步请求
class ClientGetAsync
{
public static ManualResetEvent allDone = new ManualResetEvent(false);
const int BUFFER_SIZE = 1024;
public static void Main(string[] args)
{
if (args.Length < 1)
{
showusage();
return;
}
// 从命令行获取 URI
Uri HttpSite = new Uri(args[0]);
// 创建请求对象
HttpWebRequest wreq = (HttpWebRequest)WebRequest.Create(HttpSite);
// 创建状态对象
RequestState rs = new RequestState();
// 将请求添加到状态,以便它可以被来回传递
rs.Request = wreq;
// 发出异步请求
IAsyncResult r = (IAsyncResult)wreq.BeginGetResponse(new AsyncCallback(RespCallback), rs);
// 将 ManualResetEvent 设置为 Wait,
// 以便在调用回调前,应用程序不退出
allDone.WaitOne();
}
public static void showusage()
{
Console.WriteLine("尝试获取 (GET) 一个 URL");
Console.WriteLine("\r\n用法::");
Console.WriteLine("ClientGetAsync URL");
Console.WriteLine("示例::");
Console.WriteLine("ClientGetAsync http://www.microsoft.com/net/");
}
private static void RespCallback(IAsyncResult ar)
{
// 从异步结果获取 RequestState 对象
RequestState rs = (RequestState)ar.AsyncState;
// 从 RequestState 获取 HttpWebRequest
HttpWebRequest req = rs.Request;
// 调用 EndGetResponse 生成 HttpWebResponse 对象
// 该对象来自上面发出的请求
HttpWebResponse resp = (HttpWebResponse)req.EndGetResponse(ar);
// 既然我们拥有了响应,就该从
// 响应流开始读取数据了
Stream ResponseStream = resp.GetResponseStream();
// 该读取操作也使用异步完成,所以我们
// 将要以 RequestState 存储流
rs.ResponseStream = ResponseStream;
// 请注意,rs.BufferRead 被传入到 BeginRead。
// 这是数据将被读入的位置。
IAsyncResult iarRead = ResponseStream.BeginRead(rs.BufferRead, 0, BUFFER_SIZE, new AsyncCallback(ReadCallBack), rs);
}

private static void ReadCallBack(IAsyncResult asyncResult)
{
// 从 asyncresult 获取 RequestState 对象
RequestState rs = (RequestState)asyncResult.AsyncState;
// 取出在 RespCallback 中设置的 ResponseStream
Stream responseStream = rs.ResponseStream;
// 此时 rs.BufferRead 中应该有一些数据。
// 读取操作将告诉我们那里是否有数据
int read = responseStream.EndRead(asyncResult);
if (read > 0)
{
// 准备 Char 数组缓冲区,用于向 Unicode 转换
Char[] charBuffer = new Char[BUFFER_SIZE];
// 将字节流转换为 Char 数组,然后转换为字符串
// len 显示多少字符被转换为 Unicode
int len = rs.StreamDecode.GetChars(rs.BufferRead, 0, read, charBuffer, 0);
String str = new String(charBuffer, 0, len);
// 将最近读取的数据追加到 RequestData stringbuilder 对象中,
// 该对象包含在 RequestState 中
rs.RequestData.Append(str);

// 现在发出另一个异步调用,读取更多的数据
// 请注意,将不断调用此过程,直到
// responseStream.EndRead 返回 -1
IAsyncResult ar = responseStream.BeginRead(rs.BufferRead, 0, BUFFER_SIZE, new AsyncCallback(ReadCallBack), rs);
}
else
{
if (rs.RequestData.Length > 1)
{
// 所有数据都已被读取,因此将其显示到控制台
string strContent;
strContent = rs.RequestData.ToString();
Console.WriteLine(strContent);
}
// 关闭响应流
responseStream.Close();
// 设置 ManualResetEvent,以便主线程可以退出
allDone.Set();
}
return;
}
}


在这里有回调函数,且异步回调中又有异步操作。
首先是异步获得ResponseStream,然后异步读取数据。
这个程序非常经典。从中可以学到很多东西的。我们来共同探讨。
总结
上面说过,.net framework 可以异步调用任何方法。所以异步用处广泛。
在.net framework 类库中也有很多异步调用的方法。一般都是已Begin开头End结尾构成一对,异步委托方法,外加两个回调函数和AsyncState参数,组成异步操作的宏观体现。所以要做异步编程,不要忘了委托delegate、Begin,End,AsyncCallBack委托,AsyncState实例(在回调函数中通过IAsyncResult.AsyncState来强制转换),IAsycResult(监控异步),就足以理解异步真谛了。
1.耗时的操作使用线程,提高应用程序响应
2.并行操作时使用线程,如C/S架构的服务器端并发线程响应用户的请求。
3.多CPU系统中,使用线程提高CPU利用率
4.改善程序结构。一个既长又复杂的进程可以考虑分为多个线程,成为几个独立或半独
立的运行部分,这样的程序会利于理解和修改。
使用多线程的理由之一是和进程相比,它是一种非常花销小,切换快,更"节俭"的多任务操作方式。在Linux系统下,启动一个新的进程必须分配给它独立的地址空间,建立众多的数据表来维护它的代码段、堆栈段和数据段,这是一种"昂贵"的多任务工作方式。而运行于一个进程中的多个线程,它们彼此之间使用相同的地址空间,共享大部分数据,启动一个线程所花费的空间远远小于启动一个进程所花费的空间,而且,线程间彼此切换所需的时间也远远小于进程间切换所需要的时间。
使用多线程的理由之二是线程间方便的通信机制。对不同进程来说,它们具有独立的数据空间,要进行数据的传递只能通过通信的方式进行,这种方式不仅费时,而且很不方便。线程则不然,由于同一进程下的线程之间共享数据空间,所以一个线程的数据可以直接为其它线程所用,这不仅快捷,而且方便。当然,数据的共享也带来其他一些问题,有的变量不能同时被两个线程所修改,有的子程序中声明为static的数据更有可能给多线程程序带来灾难性的打击,这些正是编写多线程程序时最需要注意的地方。
除了以上所说的优点外,不和进程比较,多线程程序作为一种多任务、并发的工作方式,当然有以下的优点:
1) 提高应用程序响应。这对图形界面的程序尤其有意义,当一个操作耗时很长时,整个系统都会等待这个操作,此时程序不会响应键盘、鼠标、菜单的操作,而使用多线程技术,将耗时长的操作(time consuming)置于一个新的线程,可以避免这种尴尬的情况。
2) 使多CPU系统更加有效。操作系统会保证当线程数不大于CPU数目时,不同的线程运行于不同的CPU上。
3) 改善程序结构。一个既长又复杂的进程可以考虑分为多个线程,成为几个独立或半独立的运行部分,这样的程序会利于理解和修改。
当前流行的Windows操作系统,它能同时运行几个程序(独立运行的程序又称之为进程),对于同一个程序,它又可以分成若干个独立的执行流,我们称之为线程,线程提供了多任务处理的能力。用进程和线程的观点来研究软件是当今普遍采用的方法,进程和线程的概念的出现,对提高软件的并行性有着重要的意义。现在的应用软件无一不是多线程多任务处理,单线城的软件是不可想象的。因此掌握多线程多任务设计方法对每个程序员都是必需要掌握的。本文针对多线程技术在应用中经常遇到的问题,如线程间的通信、同步等,对它们分别进行探讨。
一、 理解线程
要讲解线程,不得不说一下进程,进程是应用程序的执行实例,每个进程是由私有的虚拟地址空间、代码、数据和其它系统资源组成。进程在运行时创建的资源随着进程的终止而死亡。线程的基本思想很简单,它是一个独立的执行流,是进程内部的一个独立的执行单元,相当于一个子程序,它对应Visual C++中的CwinThread类的对象。单独一个执行程序运行时,缺省的运行包含的一个主线程,主线程以函数地址的形式,如main或WinMain函数,提供程序的启动点,当主线程终止时,进程也随之终止,但根据需要,应用程序又可以分解成许多独立执行的线程,每个线程并行的运行在同一进程中。
一个进程中的所有线程都在该进程的虚拟地址空间中,使用该进程的全局变量和系统资源。操作系统给每个线程分配不同的CPU时间片,在某一个时刻,CPU只执行一个时间片内的线程,多个时间片中的相应线程在CPU内轮流执行,由于每个时间片时间很短,所以对用户来说,仿佛各个线程在计算机中是并行处理的。操作系统是根据线程的优先级来安排CPU的时间,优先级高的线程优先运行,优先级低的线程则继续等待。
线程被分为两种:用户界面线程和工作线程(又称为后台线程)。用户界面线程通常用来处理用户的输入并响应各种事件和消息,其实,应用程序的主执行线程CWinAPP对象就是一个用户界面线程,当应用程序启动时自动创建和启动,同样它的终止也意味着该程序的结束,进城终止。工作者线程用来执行程序的后台处理任务,比如计算、调度、对串口的读写操作等,它和用户界面线程的区别是它不用从CwinThread类派生来创建,对它来说最重要的是如何实现工作线程任务的运行控制函数。工作线程和用户界面线程启动时要调用同一个函数的不同版本;最后需要读者明白的是,一个进程中的所有线程共享它们父进程的变量,但同时每个线程可以拥有自己的变量。
随着拥有多个硬线程CPU(超线程、双核)的普及,多线程和异步操作等并发程序设计方法也受到了更多的关注和讨论。本文主要是想与各位高手一同探讨一下如何使用并发来最大化程序的性能。
多线程和异步操作的异同
多线程和异步操作两者都可以达到避免调用线程阻塞的目的,从而提高软件的可响应性。甚至有些时候我们就认为多线程和异步操作是等同的概念。但是,多线程和异步操作还是有一些区别的。而这些区别造成了使用多线程和异步操作的时机的区别。
异步操作的本质
所有的程序最终都会由计算机硬件来执行,所以为了更好的理解异步操作的本质,我们有必要了解一下它的硬件基础。 熟悉电脑硬件的朋友肯定对DMA这个词不陌生,硬盘、光驱的技术规格中都有明确DMA的模式指标,其实网卡、声卡、显卡也是有DMA功能的。DMA就是直接内存访问的意思,也就是说,拥有DMA功能的硬件在和内存进行数据交换的时候可以不消耗CPU资源。只要CPU在发起数据传输时发送一个指令,硬件就开始自己和内存交换数据,在传输完成之后硬件会触发一个中断来通知操作完成。这些无须消耗CPU时间的I/O操作正是异步操作的硬件基础。所以即使在DOS这样的单进程(而且无线程概念)系统中也同样可以发起异步的DMA操作。
线程的本质
线程不是一个计算机硬件的功能,而是操作系统提供的一种逻辑功能,线程本质上是进程中一段并发运行的代码,所以线程需要操作系统投入CPU资源来运行和调度。
异步操作的优缺点
因为异步操作无须额外的线程负担,并且使用回调的方式进行处理,在设计良好的情况下,处理函数可以不必使用共享变量(即使无法完全不用,最起码可以减少共享变量的数量),减少了死锁的可能。当然异步操作也并非完美无暇。编写异步操作的复杂程度较高,程序主要使用回调方式进行处理,与普通人的思维方式有些初入,而且难以调试。
多线程的优缺点
多线程的优点很明显,线程中的处理程序依然是顺序执行,符合普通人的思维习惯,所以编程简单。但是多线程的缺点也同样明显,线程的使用(滥用)会给系统带来上下文切换的额外负担。并且线程间的共享变量可能造成死锁的出现。
适用范围
在了解了线程与异步操作各自的优缺点之后,我们可以来探讨一下线程和异步的合理用途。我认为:当需要执行I/O操作时,使用异步操作比使用线程+同步I/O操作更合适。I/O操作不仅包括了直接的文件、网络的读写,还包括数据库操作、Web Service、HttpRequest以及.net Remoting等跨进程的调用。
而线程的适用范围则是那种需要长时间CPU运算的场合,例如耗时较长的图形处理和算法执行。但是往往由于使用线程编程的简单和符合习惯,所以很多朋友往往会使用线程来执行耗时较长的I/O操作。这样在只有少数几个并发操作的时候还无伤大雅,如果需要处理大量的并发操作时就不合适了。
存储过程
CREATE PROCEDURE dbo.UspOutputData
@tablename sysname
AS
declare @column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @name sysname
declare @objectId int
declare @objectname sysname
declare @ident int
set nocount on
set @objectId=object_id(@tablename)
if @objectId is null -- 判斷對象是否存在
begin
print 'The object not exists'
return
end
set @objectname=rtrim(object_name(@objectId))
if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
begin
print 'object not in current database'
return
end
if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判斷對象是否是table
begin
print 'The object is not table'
return
end
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' ON'
declare syscolumns_cursor cursor
for select c.name,c.xtype from syscolumns c
where c.id=@objectid
order by c.colid
open syscolumns_cursor
set @column=''
set @columndata=''
fetch next from syscolumns_cursor into @name,@xtype
while @@fetch_status < >-1
begin
if @@fetch_status < >-2
begin
if @xtype not in(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理
begin
set @column=@column+case when len(@column)=0 then'' else ','end+@name
set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','end
+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier
else @name end
end
end
fetch next from syscolumns_cursor into @name,@xtype
end
close syscolumns_cursor
deallocate syscolumns_cursor
set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename
print '--'+@sql
exec(@sql)
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' OFF'
GO
再通过 exec UspOutputData 表名 执行此存储过程会得到SQL 语句
如下图所示
脚本如下
insert Suppliers(SupplierId,SupplierName,SupplierType,Balance,Priority,Enable,MobileShangHai,MobileOther,UnicomShangHai,UnicomOther,TelecomShangHai,TelecomOther,MobileShangHai1,MobileOther1,UnicomShangHai1,UnicomOther1,TelecomShangHai1,TelecomOther1) values( 6 , N'蜗牛游戏' , N'3' , 0.00 , 0 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 )
缺点和问题:
得到导出数据的语句,但image,text,ntext,sql_variant 列不出现在语句
另外 还可以通过SQL SERVER 本身的脚本来完成异出数据脚本功能,如下文文
SQL Server数据导入导出工具BCP使用详解2009-06-05 上午 09:57BCP是SQL Server中负责导入导出数据的一个命令行工具,它是基于DB-Library的,并且能以并行的方式高效地导入导出大批量的数据。本文介绍如何利用BCP导入导出数据。
BCP是SQL Server中负责导入导出数据的一个命令行工具,它是基于DB-Library的,并且能以并行的方式高效地导入导出大批量的数据。BCP可以将数据库的表或视图直接导出,也能通过SELECT FROM语句对表或视图进行过滤后导出。在导入导出数据时,可以使用默认值或是使用一个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中。
下面将详细讨论如何利用BCP导入导出数据。
1. BCP的主要参数介绍
BCP共有四个动作可以选择。
(1) 导入。
这个动作使用in命令完成,后面跟需要导入的文件名。
(2) 导出。
这个动作使用out命令完成,后面跟需要导出的文件名。
(3) 使用SQL语句导出。
这个动作使用queryout命令完成,它跟out类似,只是数据源不是表或视图名,而是SQL语句。
(4) 导出格式文件。
这个动作使用format命令完成,后而跟格式文件名。
下面介绍一些常用的选项:
-f format_file
format_file表示格式文件名。这个选项依赖于上述的动作,如果使用的是in或out,format_file表示已经存在的格式文件,如果使用的是format则表示是要生成的格式文件。
-x
这个选项要和-f format_file配合使用,以便生成xml格式的格式文件。
-F first_row
指定从被导出表的哪一行导出,或从被导入文件的哪一行导入。
-L last_row
指定被导出表要导到哪一行结束,或从被导入文件导数据时,导到哪一行结束。
-c
在使用密码登录时需要将-U后的用户名和-P后的密码加上双引号。
注:BCP除了可以在控制台执行外,还可以通过调用SQL Server的一个系统存储过程xp_cmdshell以SQL语句的方式运行BCP。如上述第一条命令可改写为
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password"'
执行xp_cmdshell后,返回信息以表的形式输出。为了可以方便地在SQL中执行BCP,下面的命令都使用xp_cmdshell执行BCP命令。
(2) 对要导出的表进行过滤。
BCP不仅可以接受表名或视图名做为参数,也可以接受SQL做为参数。通过SQL语句可以对要导出的表进行过滤,然后导出过滤后的记录。
EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -c -U"sa" -P"password"'
BCP还可以通过简单地设置选项对导出的行进行限制。
EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -F 10 -L 13 -c -U"sa" -P"password"'
这条命令使用了两个参数-F 10和-L 13,表示从SELECT TOP 20 * FROM AdventureWorks.sales.currency所查出来的结果中取第10条到13条记录进行导出。
3. 如何使用BCP导出格式文件
BCP不仅可以根据表、视图导入导出数据,还可以配合格式文件对导入导出数据进行限制。格式文件以纯文本文件形式存在,分为一般格式和xml格式。用户可以手工编写格式文件,也可以通过BCP命令根据表、视图自动生成格式文件。
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format1.fmt -c -T'
上述命令将currency表的结构生成了一个格式文件currency_format1.fmt,下面是这个格式文件的内容。
9.0
3
1 SQLCHAR 0 6 " " 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 " " 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 " " 3 ModifiedDate
这个格式文件记录了这个表的字段(共3个字段)类型、长度、字符和行分割符和字段名等信息。
BCP还可以通过-x选项生成xml格式的格式文件。
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format2.fmt -x -c -T'
xml格式文件所描述的内容和普通格式文件所描述的内容完全一样,只是格式不同。
4. 如何使用BCP导入数据
BCP可以通过in命令将上面所导出的currency1.txt和currency2.txt再重新导入到数据库中,由于currency有主键,因此我们将复制一个和currency的结构完全一样的表。
SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency
将数据导入到currency1表中
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -T'
导入数据也同样可以使用-F和-L选项来选择导入数据的记录行。
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -F 10 -L 13 -T'
在导入数据时可以根据已经存在的格式文件将满足条件的记录导入到数据库中,不满足则不导入。如上述的格式文件中的第三个字段的字符长度是24,如果某个文本文件中的相应字段的长度超过24,则这条记录将不被导入到数据库中,其它满足条件的记录正常导入。
使用普通的格式文件
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -f c:currency_format1.fmt -T'
使用xml格式的格式文件
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -x -f c:currency_format2.fmt -T'
总结
BCP命令是SQL Server提供的一个快捷的数据导入导出工具。使用它不需要启动任何图形管理工具就能以高效的方式导入导出数据。当然,它也可以通过xp_cmdshell在SQL语句中执行,通过这种方式可以将其放到客户端程序中(如delphi、c#等)运行,这也是使客户端程序具有数据导入导出功能的方法之一。
使用char类型做为存储类型,没有前缀且以" "做为字段分割符,以" "做为行分割符。
-w
和-c类似,只是当使用Unicode字符集拷贝数据时使用,且以nchar做为存储类型。
-t field_term
指定字符分割符,默认是" "。
-r row_term
指定行分割符,默认是" "。
-S server_name[ instance_name]
指定要连接的SQL Server服务器的实例,如果未指定此选项,BCP连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例,只需要指定机器名即可。
-U login_id
指定连接SQL Sever的用户名。
-P password
指定连接SQL Server的用户名密码。
-T 指定BCP使用信任连接登录SQL Server。如果未指定-T,必须指定-U和-P。
-k 指定空列使用null值插入,而不是这列的默认值。
2. 如何使用BCP导出数据
(1) 使用BCP导出整个表或视图。
BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password" --使用密码连接
或
BCP AdventureWorks.sales.currency out c:currency1.txt -c -T --使用信任连接
下面是上述命令执行后的输出结果
Starting copy...
105 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)
下面是currency1.txt的部分内容
AED Emirati Dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
... ... ...
... ... ...
ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000
在以上SQL 执行过程中,可能会出现如下提示到的问题
SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问
解决办法 :
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
其它参考文章
http://www.cnblogs.com/nonlyli/archive/2009/02/13/1390206.html
http://www.cnblogs.com/hardrock/archive/2005/12/16/298327.html 使用mygeneration生成脚本
http://www.weste.net/2007/9-6/0949169547.html
http://hi.baidu.com/yanzuoguang/blog/item/f5999cfb362bcd9f59ee90f2.html
HDR=NO 即无字段
HDR=yes 即有字段,一般默认excel表中第1行的列标题为字段名,如姓名、年龄等
还有问题IMEX有三个值0,1,2,其他两个值分别表示什么
IMEX 表示是否强制转换为文本
特别注意
Extended Properties='Excel 8.0;HDR=yes;IMEX=1'
A: HDR ( HeaDer Row )设置
若指定值为Yes,代表 Excel 档中的工作表第一行是栏位名称
若指定值為 No,代表 Excel 档中的工作表第一行就是資料了,沒有栏位名称
B:IMEX ( IMport EXport mode )设置
IMEX 有三种模式,各自引起的读写行为也不同,容後再述:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
我这里特别要说明的就是 IMEX 参数了,因为不同的模式代表著不同的读写行为:
当 IMEX=0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。
当 IMEX=1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。
当 IMEX=2 时为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
意义如下:
0 ---输出模式;
1---输入模式;
2----链接模式(完全更新能力)
在SQL Server 中插入一条数据使用Insert语句,但是如果想要批量插入一堆数据的话,循环使用Insert不仅效率低,而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法:Bulk和表值参数(Table-Valued Parameters)。
运行下面的脚本,建立测试数据库和表值参数。
--Create DataBase
create database BulkTestDB;
go
use BulkTestDB;
go
--Create Table
Create table BulkTestTable(
Id int primary key,
UserName nvarchar(32),
Pwd varchar(16))
go
--Create Table Valued
CREATE TYPE BulkUdt AS TABLE
(Id int,
UserName nvarchar(32),
Pwd varchar(16))
下面我们使用最简单的Insert语句来插入100万条数据,代码如下:
Stopwatch sw = new Stopwatch();
SqlConnection sqlConn = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);//连接数据库
SqlCommand sqlComm = new SqlCommand();
sqlComm.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");//参数化SQL
sqlComm.Parameters.Add("@p0", SqlDbType.Int);
sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar);
sqlComm.Parameters.Add("@p2", SqlDbType.VarChar);
sqlComm.CommandType = CommandType.Text;
sqlComm.Connection = sqlConn;
sqlConn.Open();
try
{
//循环插入100万条数据,每次插入10万条,插入10次。
for (int multiply = 0; multiply < 10; multiply++)
{
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
{
sqlComm.Parameters["@p0"].Value = count;
sqlComm.Parameters["@p1"].Value = string.Format("User-{0}", count * multiply);
sqlComm.Parameters["@p2"].Value = string.Format("Pwd-{0}", count * multiply);
sw.Start();
sqlComm.ExecuteNonQuery();
sw.Stop();
}
//每插入10万条数据后,显示此次插入所用时间
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
Console.ReadLine();
耗时图如下:

由于运行过慢,才插入10万条就耗时72390 milliseconds,所以我就手动强行停止了。
下面看一下使用Bulk插入的情况:
bulk方法主要思想是通过在客户端把数据都缓存在Table中,然后利用SqlBulkCopy一次性把Table中的数据插入到数据库
代码如下:
public static void BulkToDB(DataTable dt)
{
SqlConnection sqlConn = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
bulkCopy.DestinationTableName = "BulkTestTable";
bulkCopy.BatchSize = dt.Rows.Count;
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
if (bulkCopy != null)
bulkCopy.Close();
}
}
public static DataTable GetTableSchema()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]{
new DataColumn("Id",typeof(int)),
new DataColumn("UserName",typeof(string)),
new DataColumn("Pwd",typeof(string))});
return dt;
}
static void Main(string[] args)
{
Stopwatch sw = new Stopwatch();
for (int multiply = 0; multiply < 10; multiply++)
{
DataTable dt = Bulk.GetTableSchema();
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
{
DataRow r = dt.NewRow();
r[0] = count;
r[1] = string.Format("User-{0}", count * multiply);
r[2] = string.Format("Pwd-{0}", count * multiply);
dt.Rows.Add(r);
}
sw.Start();
Bulk.BulkToDB(dt);
sw.Stop();
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
}
Console.ReadLine();
}
耗时图如下:

可见,使用Bulk后,效率和性能明显上升。使用Insert插入10万数据耗时72390,而现在使用Bulk插入100万数据才耗时17583。
最后再看看使用表值参数的效率,会另你大为惊讶的。
表值参数是SQL Server 2008新特性,简称TVPs。对于表值参数不熟悉的朋友,可以参考最新的book online,我也会另外写一篇关于表值参数的博客,不过此次不对表值参数的概念做过多的介绍。言归正传,看代码:
public static void TableValuedToDB(DataTable dt)
{
SqlConnection sqlConn = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
const string TSqlStatement =
"insert into BulkTestTable (Id,UserName,Pwd)" +
" SELECT nc.Id, nc.UserName,nc.Pwd" +
" FROM @NewBulkTestTvp AS nc";
SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);
SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);
catParam.SqlDbType = SqlDbType.Structured;
//表值参数的名字叫BulkUdt,在上面的建立测试环境的SQL中有。
catParam.TypeName = "dbo.BulkUdt";
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
{
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
}
public static DataTable GetTableSchema()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]{
new DataColumn("Id",typeof(int)),
new DataColumn("UserName",typeof(string)),
new DataColumn("Pwd",typeof(string))});
return dt;
}
static void Main(string[] args)
{
Stopwatch sw = new Stopwatch();
for (int multiply = 0; multiply < 10; multiply++)
{
DataTable dt = TableValued.GetTableSchema();
for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)
{
DataRow r = dt.NewRow();
r[0] = count;
r[1] = string.Format("User-{0}", count * multiply);
r[2] = string.Format("Pwd-{0}", count * multiply);
dt.Rows.Add(r);
}
sw.Start();
TableValued.TableValuedToDB(dt);
sw.Stop();
Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
}
Console.ReadLine();
}
耗时图如下:

比Bulk还快5秒。
最近工作中写了几个存储过程,需要向存储过程中传递字符串,因为SQL Server 2000中没有内置类似于 split 的函数,只好自己处理,将前台数据集中的一列用逗号拆分存到一个List中,再转化为字符串传给存储过程,很是麻烦。今天看了下SQL Server 2008的新特性,发现有表变量的使用,及其将DataTable作为参数的用法,就尝试了一下,简单谈谈心得。
一、测试环境
1、Windows Server 2008 R2 DataCenter
2、Visual Studio 2008 Team System With SP1
3、SQL Server 2008 Enterprise Edition With SP1
由于是SQL Server 2008新特性,所以只能用2008。
二、测试概述
测试项目很简单,就是添加新用户

三、准备数据
1、建立数据库、表、类型、存储过程
代码
| 1 IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('Users') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 2 BEGIN 3 CREATE TABLE dbo.Users 4 ( 5 UserID INT IDENTITY(-1, -1) NOT NULL, 6 UserName VARCHAR(20) NOT NULL, 7 UserPass VARCHAR(20) NOT NULL, 8 Sex BIT NULL, 9 Age SMALLINT NULL, 10 CONSTRAINT PK_Users_UserID PRIMARY KEY(UserID) 11 ) 12 END 13 IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'UserTable' AND is_user_defined = 1) 14 BEGIN 15 CREATE TYPE UserTable AS TABLE 16 ( 17 UserName VARCHAR(20) NOT NULL, 18 UserPass VARCHAR(20) NOT NULL, 19 Sex BIT NULL, 20 Age SMALLINT NULL 21 ) 22 END 23 GO 24 代码 1 IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('sp_InsertSingleUser') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 2 BEGIN 3 DROP PROCEDURE dbo.sp_InsertSingleUser 4 END 5 GO 6 CREATE PROCEDURE dbo.sp_InsertSingleUser 7 ( 8 @User UserTable READONLY 9 ) 10 AS 11 12 SET XACT_ABORT ON 13 BEGIN TRANSACTION 14 15 INSERT INTO dbo.Users(UserName, UserPass, Sex, Age) 16 SELECT UserName, UserPass, Sex, Age FROM @User 17 18 COMMIT TRANSACTION 19 SET XACT_ABORT OFF 20 GO |
前台搭建好表单,后台主要是一个函数:
代码
| 1 public void fnInsertSingleUser(DataTable v_dt) 2 { 3 try 4 { 5 SqlConnection cn = new SqlConnection(CONN); 6 SqlCommand cmd = cn.CreateCommand(); 7 cmd.CommandType = CommandType.StoredProcedure; 8 cmd.CommandText = @"sp_InsertSingleUser"; 9 SqlParameter p = cmd.Parameters.AddWithValue("@User", v_dt); 10 11 DataSet ds = new DataSet(); 12 SqlDataAdapter da = new SqlDataAdapter(cmd); 13 da.Fill(ds); 14 } 15 catch (Exception ex) 16 { 17 throw ex; 18 } 19 } |
点击【添加】按钮时调用存储过程。测试是完成了,也很简单,传递一个DataTable做参数确实很方便吧,能够轻松完成原先需要很多编码的工作。关于表变量还是有些道道的,如创建时判断其是否存在的语句,删除表变量前需要先删除引用表变量的存储过程等。一般开发我大多会选择用临时表,处理起来比较方便,表变量可以作为存储过程参数确实是一个独特的优势,希望在SQL Server的未来版本中能够继续增强对表变量和临时表的支持,尤其是早日支持临时表调试。
有一个查询如下:
1 |
SELECT c.CustomerId, CompanyName |
2 |
FROM Customers c |
3 |
WHERE EXISTS( |
4 |
SELECT OrderID FROM Orders o |
5 |
WHERE o.CustomerID = cu.CustomerID) |
这里面的EXISTS是如何运作呢?子查询返回的是OrderId字段,可是外面的查询要找的是CustomerID和CompanyName字段,这两个字段肯定不在OrderID里面啊,这是如何匹配的呢?
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。
EXISTS 指定一个子查询,检测行的存在。语法:EXISTS subquery。参数 subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。结果类型为 Boolean,如果子查询包含行,则返回 TRUE。
- 在子查询中使用 NULL 仍然返回结果集
这个例子在子查询中指定 NULL,并返回结果集,通过使用 EXISTS 仍取值为 TRUE。
1 |
SELECT CategoryName |
2 |
FROM Categories |
3 |
WHERE EXISTS (SELECT NULL) |
4 |
ORDER BY CategoryName ASC |
- 比较使用 EXISTS 和 IN 的查询
这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。
1 |
SELECT DISTINCT pub_name |
2 |
FROM publishers |
3 |
WHERE EXISTS |
4 |
(SELECT * |
5 |
FROM titles |
6 |
WHERE pub_id = publishers.pub_id |
7 |
AND type = \'business\') |
1 |
SELECT distinct pub_name |
2 |
FROM publishers |
3 |
WHERE pub_id IN |
4 |
(SELECT pub_id |
5 |
FROM titles |
6 |
WHERE type = \'business\') |
- 比较使用 EXISTS 和 = ANY 的查询
本示例显示查找与出版商住在同一城市中的作者的两种查询方法:第一种方法使用 = ANY,第二种方法使用 EXISTS。注意这两种方法返回相同的信息。
1 |
SELECT au_lname, au_fname |
2 |
FROM authors |
3 |
WHERE exists |
4 |
(SELECT * |
5 |
FROM publishers |
6 |
WHERE authors.city = publishers.city) |
1 |
SELECT au_lname, au_fname |
2 |
FROM authors |
3 |
WHERE city = ANY |
4 |
(SELECT city |
5 |
FROM publishers) |
- 比较使用 EXISTS 和 IN 的查询
本示例所示查询查找由位于以字母 B 开头的城市中的任一出版商出版的书名:
1 |
SELECT title |
2 |
FROM titles |
3 |
WHERE EXISTS |
4 |
(SELECT * |
5 |
FROM publishers |
6 |
WHERE pub_id = titles.pub_id |
7 |
AND city LIKE \'B%\') |
1 |
SELECT title |
2 |
FROM titles |
3 |
WHERE pub_id IN |
4 |
(SELECT pub_id |
5 |
FROM publishers |
6 |
WHERE city LIKE \'B%\') |
- 使用 NOT EXISTS
NOT EXISTS 的作用与 EXISTS 正相反。如果子查询没有返回行,则满足 NOT EXISTS 中的 WHERE 子句。本示例查找不出版商业书籍的出版商的名称:
1 |
SELECT pub_name |
2 |
FROM publishers |
3 |
WHERE NOT EXISTS |
4 |
(SELECT * |
5 |
FROM titles |
6 |
WHERE pub_id = publishers.pub_id |
7 |
AND type = \'business\') |
8 |
ORDER BY pub_name |
又比如以下 SQL 语句:
1 |
select distinct 姓名 from xs |
2 |
where not exists ( |
3 |
select * from kc |
4 |
where not exists ( |
5 |
select * from xs_kc |
6 |
where 学号=xs.学号 and 课程号=kc.课程号 |
7 |
) |
把最外层的查询xs里的数据一行一行的做里层的子查询。
中间的 exists 语句只做出对上一层的返回 true 或 false,因为查询的条件都在 where 学号=xs.学号 and 课程号=kc.课程号这句话里。每一个 exists 都会有一行值。它只是告诉一层,最外层的查询条件在这里成立或都不成立,返回的时候值也一样回返回上去。直到最高层的时候如果是 true(真)就返回到结果集。为 false(假)丢弃。
1 |
where not exists |
2 |
select * from xs_kc |
3 |
where 学号=xs.学号 and 课程号=kc.课程号 |
这个 exists 就是告诉上一层,这一行语句在我这里不成立。因为他不是最高层,所以还要继续向上返回。
select distinct 姓名 from xs where not exists (这里的 exists 语句收到上一个为 false 的值。他在判断一下,结果就是为 true(成立),由于是最高层所以就会把这行的结果(这里指的是查询条件)返回到结果集。
几个重要的点:
- 最里层要用到的醒询条件的表比如:xs.学号、kc.课程号等都要在前面的时候说明一下select * from kc,select distinct 姓名 from xs
- 不要在太注意中间的exists语句.
- 把exists和not exists嵌套时的返回值弄明白