主题：[原创]如何在VB中执行SQL脚本文件
xclw
[专家分：380] 发布于 2006-04-14 11:24:00
今天给大家介绍一下如何通过VB代码执行SQL脚本文件：
在MSSQL SERVER的查询分析器中可通过输入如下命令，来执行一个外部的*.sql文件：
EXEC master.dbo.xp_cmdshell 'osql -U username -P password -i "filename"'
注意格式：xp_cmdshell后面的全部字符串要用单引号括起来，由于外部文件名中可能含有空格，所以要把filename字符串再用双引号括起来。比如你的SQL SERVER的用户名为sa,密码为sa,有一个外部文件为C:\Data Base.sql.那么这条语句就应该写成：
EXEC master.dbo.xp_cmdshell 'osql -U sa -P sa -i "C:\Data Base.sql"'.
说了半天，这还是在SQL SERVER中的操作，那么在VB中用代码如何实现呢？
我用VB写了一个小工程，全部代码如下：
Private Const ServerName = "Rock"
Private Const UserID = "sa"
Private Const PWD = "Rock"
Private Const ExternFile = "E:\Wang JX\DATABASE.SQL"
Private Sub Form_Load()
Dim conn As New Connection
Dim rs As New Recordset
Dim sql As String
sql = "master.dbo.xp_cmdshell 'osql -U " & UserID & " -P " & PWD & " -i """ & ExternFile & """'"
conn.Open "provider=sqloledb;data source=" & ServerName & ";user id=" & UserID & ";pwd=" & PWD
conn.Execute sql '这一句执行的可能会慢一些
End Sub
相信大家都能看明白吧？
当然还有别的办法解决这个问题，比如解析*.sql文件，遇到“GO”的话，就执行一次。直到整个文件读完，这个方法的代码就不写出来了。
回复列表 （共6个回复）
沙发
xclw [专家分：380] 发布于 2006-04-14 11:23:00
最后为了方便大家调试，附我的脚本文件。是通过SQL SERVER导出来的。
（没找到上传附件的地方，只好贴出来了，请大家原谅）
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Wang JX')
DROP DATABASE [Wang JX]
GO
CREATE DATABASE [Wang JX] ON (NAME = N'Wang JX_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Wang JX_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'Wang JX_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Wang JX_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO
exec sp_dboption N'Wang JX', N'autoclose', N'false'
GO
exec sp_dboption N'Wang JX', N'bulkcopy', N'false'
GO
exec sp_dboption N'Wang JX', N'trunc. log', N'false'
GO
exec sp_dboption N'Wang JX', N'torn page detection', N'true'
GO
exec sp_dboption N'Wang JX', N'read only', N'false'
GO
exec sp_dboption N'Wang JX', N'dbo use', N'false'
GO
exec sp_dboption N'Wang JX', N'single', N'false'
GO
exec sp_dboption N'Wang JX', N'autoshrink', N'false'
GO
exec sp_dboption N'Wang JX', N'ANSI null default', N'false'
GO
exec sp_dboption N'Wang JX', N'recursive triggers', N'false'
GO
exec sp_dboption N'Wang JX', N'ANSI nulls', N'false'
GO
exec sp_dboption N'Wang JX', N'concat null yields null', N'false'
GO
exec sp_dboption N'Wang JX', N'cursor close on commit', N'false'
GO
exec sp_dboption N'Wang JX', N'default to local cursor', N'false'
GO
exec sp_dboption N'Wang JX', N'quoted identifier', N'false'
GO
exec sp_dboption N'Wang JX', N'ANSI warnings', N'false'
GO
exec sp_dboption N'Wang JX', N'auto create statistics', N'true'
GO
exec sp_dboption N'Wang JX', N'auto update statistics', N'true'
GO
use [Wang JX]
GO
板凳
xclw [专家分：380] 发布于 2006-04-14 11:23:00
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TblPerm_TblCustom]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TblPerm] DROP CONSTRAINT FK_TblPerm_TblCustom
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TblTemp_TblCustom]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TblTemp] DROP CONSTRAINT FK_TblTemp_TblCustom
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TblPerm_TblItem]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TblPerm] DROP CONSTRAINT FK_TblPerm_TblItem
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TblTemp_TblItem]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TblTemp] DROP CONSTRAINT FK_TblTemp_TblItem
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblCustom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblCustom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblItem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblPerm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblPerm]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblTemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TblTemp]
GO
CREATE TABLE [dbo].[TblCustom] (
[CustomID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomCode] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CustomName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TblItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ItemName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TblPerm] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NOT NULL ,
[CustomID] [int] NOT NULL ,
[ItemID] [int] NOT NULL ,
[Currency_Kind] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Rate] [float] NULL ,
[Foreign] [money] NULL ,
[RMB] [money] NULL ,
[Producer] [nchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Assessor] [nchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TblTemp] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NOT NULL ,
[CustomID] [int] NOT NULL ,
[ItemID] [int] NOT NULL ,
[Currency_Kind] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Rate] [float] NULL ,
[Foreign] [money] NULL ,
[RMB] [money] NULL ,
[Producer] [nchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Assessor] [nchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Saved] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblCustom] WITH NOCHECK ADD
CONSTRAINT [PK_TblCustom] PRIMARY KEY CLUSTERED
(
[CustomID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblItem] WITH NOCHECK ADD
CONSTRAINT [PK_TblItem] PRIMARY KEY CLUSTERED
(
[ItemID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblTemp] WITH NOCHECK ADD
CONSTRAINT [PK_TblTemp] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblPerm] ADD
CONSTRAINT [FK_TblPerm_TblCustom] FOREIGN KEY
(
[CustomID]
) REFERENCES [dbo].[TblCustom] (
[CustomID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_TblPerm_TblItem] FOREIGN KEY
(
[ItemID]
) REFERENCES [dbo].[TblItem] (
[ItemID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[TblTemp] ADD
CONSTRAINT [FK_TblTemp_TblCustom] FOREIGN KEY
(
[CustomID]
) REFERENCES [dbo].[TblCustom] (
[CustomID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_TblTemp_TblItem] FOREIGN KEY
(
[ItemID]
) REFERENCES [dbo].[TblItem] (
[ItemID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
3 楼
xclw [专家分：380] 发布于 2006-04-14 17:50:00
没有人顶吗？
4 楼
kskksk [专家分：620] 发布于 2006-04-15 16:31:00
支持！！！
5 楼
lwace [专家分：0] 发布于 2008-08-27 16:53:00
顶
6 楼
z544844753 [专家分：0] 发布于 2008-09-09 09:16:00
偶虽然看不懂，但还是顶一下，哈哈
我来回复
您所在位置：