在VS2010中运行SQL脚本 [英] Running sql script in VS2010
问题描述
如何在C#中执行SQL脚本.
我使用的代码如下:-
这是我的三个尝试，但都没有为我工作:
第一次尝试:
FileInfo文件=新的FileInfo(@"F:\ TDT \ TDTScript.sql");
试试
{
字符串脚本= file.OpenText().ReadToEnd();
SqlConnection conn =新的SqlConnection(Properties.Settings.Default.masterConnectionString2);
服务器服务器=新服务器(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
catch(异常e)
{
}
终于
{
file.OpenText().Close();
}
第二次尝试:-
/*
//////如果SQL脚本包含"GO"，ADO.NET将不会引发异常.
字符串[] SqlLine;
正则表达式regex = new Regex("^ GO"，RegexOptions.IgnoreCase |
RegexOptions.Multiline);
字符串txtSQL = GetSql("sql.txt");
SqlLine = regex.Split(txtSQL);
//SqlCommand cmd = sqlCon.CreateCommand();
//cmd.Connection = sqlCon;
SqlCommand cmd =新的SqlCommand(sql，sqlConnection);
sqlConnection.ConnectionString = Properties.Settings.Default.masterConnectionString2;
foreach(SqlLine中的字符串行)
{
cmd.Connection.Open();
如果(line.Length> 0)
{
cmd.CommandText =行;
//cmd.CommandType = CommandType.Text;
//尝试
//{
cmd.ExecuteNonQuery();
//}
//捕获(SqlException)
//{
//回滚
//ExecuteDrop(sqlCon);
//中断；
//}
//最后
//{
//}
}
cmd.Connection.Close();
}
第三次尝试
*/
/////
/*
试试
{
汇编asm = Assembly.GetExecutingAssembly();
流stm = asm.GetManifestResourceStream(asm.GetName().Name +." + sql.txt);
StreamReader reader =新的StreamReader(stm);
返回reader.ReadToEnd();
}
catch(异常e)
{
//MessageBox.Show(在GetSQL中:" + e.Message);
投掷;
}
SqlCommand命令=新的SqlCommand(sql，sqlConnection);
sqlConnection.ConnectionString = Properties.Settings.Default.masterConnectionString2;
command.Connection.Open();
command.Connection.ChangeDatabase(dbName);
试试
{
command.ExecuteNonQuery();
}
终于
{
command.Connection.Close();
}
*/
}
注意:sql.txt和TDTScript.sql是我的脚本文件，并且如下所示:-
GO
/******对象:表格[dbo].[tblTaxonomies]脚本日期:02/23/2011 16:58:46 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
设置ANSI_PADDING ON
GO
如果不存在(从对象对象的SELECT * FROM sys.objects中选择object_id = OBJECT_ID(N''[dbo].[tblTaxonomies]'')并键入(N''U''))
开始
创建表[dbo].[tblTaxonomies](
[TaxonomyLevelID] [int]非空，
[TaxonomyLevel] [varchar](50)NULL，
[ts] [timestamp] NULL，
约束[PK_tblTaxonomy]主键群集
(
[TaxonomyLevelID] ASC
)与(PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON)一起使用[PRIMARY]
)于[PRIMARY]
END
GO
设置ANSI_PADDING OFF
GO
如果不存在(选择*来自tblTaxonomies WHERE TaxonomyLevel ="Analysis")
插入[dbo].[tblTaxonomies]([TaxonomyLevelID]，[TaxonomyLevel])值(1，N''Analysis'')
GO
如果不存在(SELECT * FROM tblTaxonomies WHERE TaxonomyLevel =''Application'')
插入[dbo].[tblTaxonomies]([TaxonomyLevelID]，[TaxonomyLevel])值(2，N''Application'')
GO
如果不存在(选择*从tblTaxonomies WHERE TaxonomyLevel ="Recall")
插入[dbo].[tblTaxonomies]([TaxonomyLevelID]，[TaxonomyLevel])值(3，N''Recall'')
GO
/******对象:表格[dbo].[tblTDTImages]脚本日期:02/23/2011 16:58:46 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
如果不存在(SELECT * FROM sys.objects where where object_id = OBJECT_ID(N''[dbo].[tblTDTImages]'')并键入(N''U''))
开始
创建表[dbo].[tblTDTImages](
[ImageID] [int] IDENTITY(1,1)NOT NULL，
[Image] [image] NOT NULL，
[ts] [timestamp] NULL，
约束[PK_tblTDSImages]主键群集
(
[ImageID] ASC
)与(PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON)一起使用[PRIMARY]
)于[PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/******对象:表格[dbo].[tblTestItemTypes]脚本日期:02/23/2011 16:58:46 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
设置ANSI_PADDING ON
GO
如果不存在(SELECT * FROM sys.objects where where object_id = OBJECT_ID(N''[dbo].[tblTestItemTypes]'')并键入(N''U''))
开始
创建表[dbo].[tblTestItemTypes](
[TestItemTypeID] [int]非空，
[TestItemType] [varchar](50)NULL，
[ts] [timestamp] NOT NULL，
约束[PK_tblTestItemType]主键群集
(
[TestItemTypeID] ASC
)与(PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON)一起使用[PRIMARY]
)于[PRIMARY]
END
GO
设置ANSI_PADDING OFF
GO
如果不存在(SELECT * FROM tblTestItemTypes WHERE TestItemType ="Multichoice'')
插入[dbo].[tblTestItemTypes]([TestItemTypeID]，[TestItemType])值(1，N''Multichoice'')
GO
如果不存在(SELECT * FROM tblTestItemTypes WHERE TestItemType ="True-False'')
插入[dbo].[tblTestItemTypes]([TestItemTypeID]，[TestItemType])值(2，N''True-False'')
GO
如果不存在(SELECT * FROM tblTestItemTypes WHERE TestItemType =''Matching'')
插入[dbo].[tblTestItemTypes]([TestItemTypeID]，[TestItemType])值(3，N''Matching'')
GO
-从简短答案更新为简短答案类型''
更新tblTestItemTypes SET TestItemType =''简短回答类型''WHERE TestItemTypeID = 4
GO
如果不存在(SELECT * FROM tblTestItemTypes WHERE TestItemType =''简短回答类型'')
插入[dbo].[tblTestItemTypes]([TestItemTypeID]，[TestItemType])值(4，N''简短回答类型'')
GO
如果不存在(SELECT * FROM [tblTestItemTypes] WHERE TestItemType =填充为空白")
插入[dbo].[tblTestItemTypes]([TestItemTypeID]，[TestItemType])值(5，N''填入空格'')
GO
/******对象:表格[dbo].[tblTestItems]脚本日期:02/23/2011 16:58:46 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
设置ANSI_PADDING ON
GO
如果不存在(SELECT * FROM sys.objects where where object_id = OBJECT_ID(N''[dbo].[tblTestItems]'')并键入(N''U''))
开始
创建表[dbo].[tblTestItems](
[TestItemID] [int] IDENTITY(1,1)NOT NULL，
[SKID] [int] NULL，
[Stem] [varchar](1000)NULL，
[TestItemType] [int] NULL，
[TaxonomyID] [int] NULL，
[ImageID] [int] NULL，
[ts] [timestamp] NOT NULL，
约束[PK_tblTestItems]主键群集
(
[TestItemID] ASC
)与(PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON)一起使用[PRIMARY]
)于[PRIMARY]
END
GO
设置ANSI_PADDING OFF
GO
/******对象:表格[dbo].[tblTestitemDistractors]脚本日期:02/23/2011 16:58:46 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
设置ANSI_PADDING ON
GO
如果不存在(选择*从sys.objects中查找，则object_id = OBJECT_ID(N''[dbo].[tblTestitemDistractors]'')并键入(N''U''))
开始
创建表[dbo].[tblTestitemDistractors](
[TestitemDistractorID] [int] IDENTITY(1,1)NOT NULL，
[TestItemID] [int] NULL，
[DistractorDetails] [varchar](max)NULL，
[ts] [timestamp] NULL，
约束[PK_tblTestitemDistractors]主键已聚集
(
[TestitemDistractorID] ASC
)与(PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON)一起使用[PRIMARY]
)于[PRIMARY]
END
GO
设置ANSI_PADDING OFF
GO
/******对象:表格[dbo].[tblTestStatuses]脚本日期:02/23/2011 16:58:46 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
设置ANSI_PADDING ON
GO
如果不存在(SELECT * FROM sys.objects where where object_id = OBJECT_ID(N''[dbo].[tblTestStatuses]'')并键入(N''U''))
开始
创建表[dbo].[tblTestStatuses](
[TestStatusID] [tinyint] IDENTITY(1,1)NOT NULL，
[TestStatus] [varchar](50)NULL，
[ts] [timestamp] NULL，
约束[PK_tblTestStatus]主键已聚集
(
[TestStatusID] ASC
)与(PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON)一起使用[PRIMARY]
)于[PRIMARY]
END
GO
设置ANSI_PADDING OFF
GO
设置IDENTITY_INSERT [dbo].[tblTestStatuses]开启
如果不存在(SELECT * FROM tblTestStatuses WHERE TestStatus =''Published'')
插入[dbo].[tblTestStatuses]([TestStatusID]，[TestStatus])值(1，N''Published'')
GO
如果不存在(SELECT * FROM tblTestStatuses WHERE TestStatus =正在开发中'')
插入[dbo].[tblTestStatuses]([TestStatusID]，[TestStatus])值(2，N''开发中'')
GO
如果不存在(SELECT * FROM tblTestStatuses WHERE TestStatus ="Inactive'')
插入[dbo].[tblTestStatuses]([TestStatusID]，[TestStatus])值(3，N''Inactive'')
GO
设置IDENTITY_INSERT [dbo].[tblTestStatuses] OFF
/******对象:表格[dbo].[tblTests]脚本日期:02/23/2011 16:58:46 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
设置ANSI_PADDING ON
GO
如果不存在(SELECT * FROM sys.objects where where object_id = OBJECT_ID(N''[dbo].[tblTests]'')并键入(N''U''))
开始
创建表[dbo].[tblTests](
[TestID] [int] IDENTITY(1,1)NOT NULL，
[CORID] [int] NULL，
[Notes] [varchar](max)NULL，
[TestStatusID] [tinyint] NULL，
[ts] [timestamp] NULL，
约束[PK_tblTests]主键群集
(
[TestID] ASC
)与(PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON)一起使用[PRIMARY]
)于[PRIMARY]
END
GO
设置ANSI_PADDING OFF
GO
/******对象:表格[dbo].[rsTblTest_TestItem]脚本日期:02/23/2011 16:58:46 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
如果不存在(SELECT * FROM sys.objects where where object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]'')并键入(N''U''))
开始
创建表[dbo].[rsTblTest_TestItem](
[ID] [int] IDENTITY(1,1)NOT NULL，
[TestId] [int] NOT NULL，
[TestItemID] [int]非空，
[TestOrder] [int] NOT NULL，
[ts] [timestamp] NULL，
约束[PK_rstblTest_TestItem]主键已聚集
(
[ID] ASC
)与(PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON)一起使用[PRIMARY]
)于[PRIMARY]
END
GO
/******对象:ForeignKey [FK_rsTblTest_TestItem_tblTestItems]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_rsTblTest_TestItem_tblTestItems]'')和parent_object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]''))
更改表[dbo].[rsTblTest_TestItem]，并勾选添加约束[FK_rsTblTest_TestItem_tblTestItems]外键([TestItemID])
参考[dbo].[tblTestItems]([TestItemID])
GO
如果存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_rsTblTest_TestItem_tblTestItems]''))和parent_object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]''))
更改表[dbo].[rsTblTest_TestItem]检查约束[FK_rsTblTest_TestItem_tblTestItems]
GO
/******对象:ForeignKey [FK_rsTblTest_TestItem_tblTests]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(SELECT * FROM sys.foreign_keys where object_id = OBJECT_ID(N''[dbo].[FK_rsTblTest_TestItem_tblTests]''))和parent_object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]''))
更改表[dbo].[rsTblTest_TestItem]，并勾选添加约束[FK_rsTblTest_TestItem_tblTests]外键([TestId])
参考[dbo].[tblTests]([TestID])
GO
如果存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_rsTblTest_TestItem_tblTests]''))和parent_object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]''))
更改表[dbo].[rsTblTest_TestItem]检查约束[FK_rsTblTest_TestItem_tblTests]
GO
/******对象:ForeignKey [FK_tblTestitemDistractors_tblTestItems]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestitemDistractors_tblTestItems]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestitemDistractors]''))
ALTER TABLE [dbo].[tblTestitemDistractors]，带有检查添加约束[FK_tblTestitemDistractors_tblTestItems]外键([TestItemID])
参考[dbo].[tblTestItems]([TestItemID])
GO
如果存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestitemDistractors_tblTestItems]''))和parent_object_id = OBJECT_ID(N''[dbo].[tblTestitemDistractors]''))
更改表[dbo].[tblTestitemDistractors]检查约束[FK_tblTestitemDistractors_tblTestItems]
GO
/******对象:ForeignKey [FK_tblTestItems_tblSkillsKnowledge]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(从sys.foreign_keys中选择*，从object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblSkillsKnowledge]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
带有检查添加约束的ALTER TABLE [dbo].[tblTestItems] [FK_tblTestItems_tblSkillsKnowledge]外键([SKID])
参考资料[dbo].[tblSkillsKnowledge]([SKID])
GO
如果存在(从SYS.foreign_keys中选择* *，而object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblSkillsKnowledge]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
更改表[dbo].[tblTestItems]检查约束[FK_tblTestItems_tblSkills知识]
GO
/******对象:ForeignKey [FK_tblTestItems_tblTaxonomy]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(从sys.foreign_keys中选择*，请从object.id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTaxonomy]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
更改表[dbo].[tblTestItems]，并勾选添加约束[FK_tblTestItems_tblTaxonomy]外部键([TaxonomyID])
参考[dbo].[tblTaxonomies]([TaxonomyLevelID])
GO
如果存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTaxonomy]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
更改表[dbo].[tblTestItems]检查约束[FK_tblTestItems_tblTaxonomy]
GO
/******对象:ForeignKey [FK_tblTestItems_tblTDTImages]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(SELECT * FROM sys.foreign_keys where object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTDTImages]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
更改表[dbo].[tblTestItems]，并勾选添加约束[FK_tblTestItems_tblTDTImages]外键([ImageID])
参考[dbo].[tblTDTImages]([ImageID])
GO
如果存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTDTImages]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
更改表[dbo].[tblTestItems]检查约束[FK_tblTestItems_tblTDTImages]
GO
/******对象:ForeignKey [FK_tblTestItems_tblTestItemTypes]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTestItemTypes]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
带有检查添加约束的ALTER TABLE [dbo].[tblTestItems] [FK_tblTestItems_tblTestItemTypes]外键([TestItemType])
参考[dbo].[tblTestItemTypes]([TestItemTypeID])
GO
如果存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTestItemTypes]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
更改表[dbo].[tblTestItems]检查约束[FK_tblTestItems_tblTestItemTypes]
GO
/******对象:ForeignKey [FK_tblTests_tblCourses]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(从sys.foreign_keys中选择*，从object_id = OBJECT_ID(N''[dbo].[FK_tblTests_tblCourses]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTests]''))
带有检查添加约束的ALTER TABLE [dbo].[tblTests] [FK_tblTests_tblCourses]外键([CORID])
参考文献[dbo].[tblCourses]([CORID])
GO
如果存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTests_tblCourses]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTests]''))
ALTER TABLE [dbo].[tblTests]检查约束[FK_tblTests_tblCourses]
GO
/******对象:ForeignKey [FK_tblTests_tblTestStatus]脚本日期:02/23/2011 16:58:46 ******/
如果不存在(从sys.foreign_keys中选择*，请从object.id = OBJECT_ID(N''[dbo].[FK_tblTests_tblTestStatus]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTests]''))
带有检查添加约束的ALTER TABLE [dbo].[tblTests] [FK_tblTests_tblTestStatus]外键([TestStatusID])
参考[dbo].[tblTestStatuses]([TestStatusID])
GO
如果存在(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTests_tblTestStatus]'')和parent_object_id = OBJECT_ID(N''[dbo].[tblTests]''))
ALTER TABLE [dbo].[tblTests]检查约束[FK_tblTests_tblTestStatus]
GO
Hi,
How to execute a sql script in c#.
the code i used is as as follows:-
these are my three Attempts but neither of them working for me:
1st Attempt:
FileInfo file = new FileInfo(@"F:\TDT\TDTScript.sql");
try
{
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(Properties.Settings.Default.masterConnectionString2);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
catch (Exception e)
{
}
finally
{
file.OpenText().Close();
}
2nd Attempt:-
/*
/////ADO.NET will not throw an exception if the SQL script contains "GO".
string[] SqlLine;
Regex regex = new Regex("^GO", RegexOptions.IgnoreCase |
RegexOptions.Multiline);
string txtSQL = GetSql("sql.txt");
SqlLine = regex.Split(txtSQL);
//SqlCommand cmd = sqlCon.CreateCommand();
// cmd.Connection = sqlCon;
SqlCommand cmd= new SqlCommand(sql, sqlConnection);
sqlConnection.ConnectionString = Properties.Settings.Default.masterConnectionString2;
foreach (string line in SqlLine)
{
cmd.Connection.Open();
if (line.Length > 0)
{
cmd.CommandText = line;
// cmd.CommandType = CommandType.Text;
// try
// {
cmd.ExecuteNonQuery();
//}
// catch (SqlException)
// {
//rollback
// ExecuteDrop(sqlCon);
// break;
// }
//finally
//{
//}
}
cmd.Connection.Close();
}
3rd Attempt
*/
/////
/*
try
{
Assembly asm = Assembly.GetExecutingAssembly();
Stream stm = asm.GetManifestResourceStream(asm.GetName().Name + "." + sql.txt);
StreamReader reader = new StreamReader(stm);
return reader.ReadToEnd();
}
catch (Exception e)
{
// MessageBox.Show("In GetSQL: " + e.Message);
throw;
}
SqlCommand command = new SqlCommand(sql, sqlConnection);
sqlConnection.ConnectionString = Properties.Settings.Default.masterConnectionString2;
command.Connection.Open();
command.Connection.ChangeDatabase(dbName);
try
{
command.ExecuteNonQuery();
}
finally
{
command.Connection.Close();
}
*/
}
Note: sql.txt and TDTScript.sql are my script file and are same as follows:-
GO
/****** Object: Table [dbo].[tblTaxonomies] Script Date: 02/23/2011 16:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tblTaxonomies]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[tblTaxonomies](
[TaxonomyLevelID] [int] NOT NULL,
[TaxonomyLevel] [varchar](50) NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_tblTaxonomy] PRIMARY KEY CLUSTERED
(
[TaxonomyLevelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS(SELECT * FROM tblTaxonomies WHERE TaxonomyLevel=''Analysis'')
INSERT [dbo].[tblTaxonomies] ([TaxonomyLevelID], [TaxonomyLevel]) VALUES (1, N''Analysis'')
GO
IF NOT EXISTS(SELECT * FROM tblTaxonomies WHERE TaxonomyLevel=''Application'')
INSERT [dbo].[tblTaxonomies] ([TaxonomyLevelID], [TaxonomyLevel]) VALUES (2, N''Application'')
GO
IF NOT EXISTS(SELECT * FROM tblTaxonomies WHERE TaxonomyLevel=''Recall'')
INSERT [dbo].[tblTaxonomies] ([TaxonomyLevelID], [TaxonomyLevel]) VALUES (3, N''Recall'')
GO
/****** Object: Table [dbo].[tblTDTImages] Script Date: 02/23/2011 16:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tblTDTImages]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[tblTDTImages](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[Image] [image] NOT NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_tblTDSImages] PRIMARY KEY CLUSTERED
(
[ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
/****** Object: Table [dbo].[tblTestItemTypes] Script Date: 02/23/2011 16:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tblTestItemTypes]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[tblTestItemTypes](
[TestItemTypeID] [int] NOT NULL,
[TestItemType] [varchar](50) NULL,
[ts] [timestamp] NOT NULL,
CONSTRAINT [PK_tblTestItemType] PRIMARY KEY CLUSTERED
(
[TestItemTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS(SELECT * FROM tblTestItemTypes WHERE TestItemType=''Multichoice'')
INSERT [dbo].[tblTestItemTypes] ([TestItemTypeID], [TestItemType]) VALUES (1, N''Multichoice'')
GO
IF NOT EXISTS(SELECT * FROM tblTestItemTypes WHERE TestItemType=''True-False'')
INSERT [dbo].[tblTestItemTypes] ([TestItemTypeID], [TestItemType]) VALUES (2, N''True-False'')
GO
IF NOT EXISTS(SELECT * FROM tblTestItemTypes WHERE TestItemType=''Matching'')
INSERT [dbo].[tblTestItemTypes] ([TestItemTypeID], [TestItemType]) VALUES (3, N''Matching'')
GO
--Update From Short Answer to Short Answer Type''
UPDATE tblTestItemTypes SET TestItemType=''Short Answer Type'' WHERE TestItemTypeID=4
GO
IF NOT EXISTS(SELECT * FROM tblTestItemTypes WHERE TestItemType=''Short Answer Type'')
INSERT [dbo].[tblTestItemTypes] ([TestItemTypeID], [TestItemType]) VALUES (4, N''Short Answer Type'')
GO
IF NOT EXISTS(SELECT * FROM [tblTestItemTypes] WHERE TestItemType=''Fill in the blank'')
INSERT [dbo].[tblTestItemTypes] ([TestItemTypeID], [TestItemType]) VALUES (5, N''Fill in the blank'')
GO
/****** Object: Table [dbo].[tblTestItems] Script Date: 02/23/2011 16:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tblTestItems]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[tblTestItems](
[TestItemID] [int] IDENTITY(1,1) NOT NULL,
[SKID] [int] NULL,
[Stem] [varchar](1000) NULL,
[TestItemType] [int] NULL,
[TaxonomyID] [int] NULL,
[ImageID] [int] NULL,
[ts] [timestamp] NOT NULL,
CONSTRAINT [PK_tblTestItems] PRIMARY KEY CLUSTERED
(
[TestItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblTestitemDistractors] Script Date: 02/23/2011 16:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tblTestitemDistractors]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[tblTestitemDistractors](
[TestitemDistractorID] [int] IDENTITY(1,1) NOT NULL,
[TestItemID] [int] NULL,
[DistractorDetails] [varchar](max) NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_tblTestitemDistractors] PRIMARY KEY CLUSTERED
(
[TestitemDistractorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblTestStatuses] Script Date: 02/23/2011 16:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tblTestStatuses]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[tblTestStatuses](
[TestStatusID] [tinyint] IDENTITY(1,1) NOT NULL,
[TestStatus] [varchar](50) NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_tblTestStatus] PRIMARY KEY CLUSTERED
(
[TestStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tblTestStatuses] ON
IF NOT EXISTS(SELECT * FROM tblTestStatuses WHERE TestStatus=''Published'')
INSERT [dbo].[tblTestStatuses] ([TestStatusID], [TestStatus]) VALUES (1, N''Published'')
GO
IF NOT EXISTS(SELECT * FROM tblTestStatuses WHERE TestStatus=''In Development '')
INSERT [dbo].[tblTestStatuses] ([TestStatusID], [TestStatus]) VALUES (2, N''In Development '')
GO
IF NOT EXISTS(SELECT * FROM tblTestStatuses WHERE TestStatus=''Inactive'')
INSERT [dbo].[tblTestStatuses] ([TestStatusID], [TestStatus]) VALUES (3, N''Inactive'')
GO
SET IDENTITY_INSERT [dbo].[tblTestStatuses] OFF
/****** Object: Table [dbo].[tblTests] Script Date: 02/23/2011 16:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[tblTests]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[tblTests](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[CORID] [int] NULL,
[Notes] [varchar](max) NULL,
[TestStatusID] [tinyint] NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_tblTests] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[rsTblTest_TestItem] Script Date: 02/23/2011 16:58:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[rsTblTest_TestItem](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TestId] [int] NOT NULL,
[TestItemID] [int] NOT NULL,
[TestOrder] [int] NOT NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_rstblTest_TestItem] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object: ForeignKey [FK_rsTblTest_TestItem_tblTestItems] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_rsTblTest_TestItem_tblTestItems]'') AND parent_object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]''))
ALTER TABLE [dbo].[rsTblTest_TestItem] WITH CHECK ADD CONSTRAINT [FK_rsTblTest_TestItem_tblTestItems] FOREIGN KEY([TestItemID])
REFERENCES [dbo].[tblTestItems] ([TestItemID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_rsTblTest_TestItem_tblTestItems]'') AND parent_object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]''))
ALTER TABLE [dbo].[rsTblTest_TestItem] CHECK CONSTRAINT [FK_rsTblTest_TestItem_tblTestItems]
GO
/****** Object: ForeignKey [FK_rsTblTest_TestItem_tblTests] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_rsTblTest_TestItem_tblTests]'') AND parent_object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]''))
ALTER TABLE [dbo].[rsTblTest_TestItem] WITH CHECK ADD CONSTRAINT [FK_rsTblTest_TestItem_tblTests] FOREIGN KEY([TestId])
REFERENCES [dbo].[tblTests] ([TestID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_rsTblTest_TestItem_tblTests]'') AND parent_object_id = OBJECT_ID(N''[dbo].[rsTblTest_TestItem]''))
ALTER TABLE [dbo].[rsTblTest_TestItem] CHECK CONSTRAINT [FK_rsTblTest_TestItem_tblTests]
GO
/****** Object: ForeignKey [FK_tblTestitemDistractors_tblTestItems] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestitemDistractors_tblTestItems]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestitemDistractors]''))
ALTER TABLE [dbo].[tblTestitemDistractors] WITH CHECK ADD CONSTRAINT [FK_tblTestitemDistractors_tblTestItems] FOREIGN KEY([TestItemID])
REFERENCES [dbo].[tblTestItems] ([TestItemID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestitemDistractors_tblTestItems]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestitemDistractors]''))
ALTER TABLE [dbo].[tblTestitemDistractors] CHECK CONSTRAINT [FK_tblTestitemDistractors_tblTestItems]
GO
/****** Object: ForeignKey [FK_tblTestItems_tblSkillsKnowledge] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblSkillsKnowledge]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
ALTER TABLE [dbo].[tblTestItems] WITH CHECK ADD CONSTRAINT [FK_tblTestItems_tblSkillsKnowledge] FOREIGN KEY([SKID])
REFERENCES [dbo].[tblSkillsKnowledge] ([SKID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblSkillsKnowledge]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
ALTER TABLE [dbo].[tblTestItems] CHECK CONSTRAINT [FK_tblTestItems_tblSkillsKnowledge]
GO
/****** Object: ForeignKey [FK_tblTestItems_tblTaxonomy] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTaxonomy]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
ALTER TABLE [dbo].[tblTestItems] WITH CHECK ADD CONSTRAINT [FK_tblTestItems_tblTaxonomy] FOREIGN KEY([TaxonomyID])
REFERENCES [dbo].[tblTaxonomies] ([TaxonomyLevelID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTaxonomy]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
ALTER TABLE [dbo].[tblTestItems] CHECK CONSTRAINT [FK_tblTestItems_tblTaxonomy]
GO
/****** Object: ForeignKey [FK_tblTestItems_tblTDTImages] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTDTImages]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
ALTER TABLE [dbo].[tblTestItems] WITH CHECK ADD CONSTRAINT [FK_tblTestItems_tblTDTImages] FOREIGN KEY([ImageID])
REFERENCES [dbo].[tblTDTImages] ([ImageID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTDTImages]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
ALTER TABLE [dbo].[tblTestItems] CHECK CONSTRAINT [FK_tblTestItems_tblTDTImages]
GO
/****** Object: ForeignKey [FK_tblTestItems_tblTestItemTypes] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTestItemTypes]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
ALTER TABLE [dbo].[tblTestItems] WITH CHECK ADD CONSTRAINT [FK_tblTestItems_tblTestItemTypes] FOREIGN KEY([TestItemType])
REFERENCES [dbo].[tblTestItemTypes] ([TestItemTypeID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTestItems_tblTestItemTypes]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTestItems]''))
ALTER TABLE [dbo].[tblTestItems] CHECK CONSTRAINT [FK_tblTestItems_tblTestItemTypes]
GO
/****** Object: ForeignKey [FK_tblTests_tblCourses] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTests_tblCourses]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTests]''))
ALTER TABLE [dbo].[tblTests] WITH CHECK ADD CONSTRAINT [FK_tblTests_tblCourses] FOREIGN KEY([CORID])
REFERENCES [dbo].[tblCourses] ([CORID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTests_tblCourses]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTests]''))
ALTER TABLE [dbo].[tblTests] CHECK CONSTRAINT [FK_tblTests_tblCourses]
GO
/****** Object: ForeignKey [FK_tblTests_tblTestStatus] Script Date: 02/23/2011 16:58:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTests_tblTestStatus]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTests]''))
ALTER TABLE [dbo].[tblTests] WITH CHECK ADD CONSTRAINT [FK_tblTests_tblTestStatus] FOREIGN KEY([TestStatusID])
REFERENCES [dbo].[tblTestStatuses] ([TestStatusID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[dbo].[FK_tblTests_tblTestStatus]'') AND parent_object_id = OBJECT_ID(N''[dbo].[tblTests]''))
ALTER TABLE [dbo].[tblTests] CHECK CONSTRAINT [FK_tblTests_tblTestStatus]
GO
推荐答案
看看此链接:
http://smehrozalam.wordpress.com /2009/05/12/c-executing-batch-t-sql-scripts-with-go-statements/ [
Have a look at this link:
http://smehrozalam.wordpress.com/2009/05/12/c-executing-batch-t-sql-scripts-with-go-statements/[^]
Good luck!
尝试一下，
Try This,
FileInfo ObjFile = new FileInfo(@"F:\TDT\TDTScript.sql");
string strScript = ObjFile .OpenText().ReadToEnd();
SqlConnection objCon = new SqlConnection(strConnection);
// Where strConnection is your connection string.
Server ObjServer = new Server(new ServerConnection(objCon));
ObjServer.ConnectionContext.ExecuteNonQuery(strScript);
这篇关于在VS2010中运行SQL脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
