Running sql script in VS2010

Question

0.00/5 (No votes)

See more:

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

Posted 1-May-11 22:05pm

abhishek.singh.valeteck

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2011-05-01T22:16:00

Solution 1

Have a look at this link:
http://smehrozalam.wordpress.com/2009/05/12/c-executing-batch-t-sql-scripts-with-go-statements/[^]

Good luck!

Posted 1-May-11 22:16pm

E.F. Nijboer

LaxmikantYadav · Answer 2 · 2011-05-01T22:30:00

Solution 2

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

Posted 1-May-11 22:30pm

LaxmikantYadav

Comments

abhishek.singh.valeteck 2-May-11 6:48am

Thanks guys but the above i used earlier and i got an error"mixed mode assembly is built against version 'v2.0
for this i edited the config file located in:-
C:\Program Files (x86)\Common Files\microsoft shared\DevServer\10.0\WebDev.WebServer40.exe.config file with the lines as-
<pre lang="xml"><?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" />
</startup>
<runtime>
<generatePublisherEvidence enabled="false" />
</runtime>
</configuration></pre>

also added the refrence of system.configuration

But the above error is still raising