c# for sql script execution

Question

I have 2 forms, first collecting the details like datasource, database name, username and password. Second form collects the sql script file to be executed and the database name to be connected to..

What I want to achieve is that I should be able to execute the sql script file in the selected database.

The code used in the second form is like this:

private void comboBox1_TextChanged(object sender, EventArgs e)
{
 string sel = comboBox1.SelectedItem.ToString(); //Here "sel" is the database selected
 if (sel == "master")
 {
  comboBox2.Items.Clear();
     //Selects a default file
  DirectoryInfo dinfo = new DirectoryInfo(@"D:\Testpgm");
  FileInfo[] Files = dinfo.GetFiles("master.sql", SearchOption.AllDirectories);
  foreach (FileInfo file in Files)
  {
   comboBox2.Items.Add(file.Name);
  }
 }
 else
 {
  comboBox2.Items.Clear();
     //Selects a default file
  DirectoryInfo dinfo = new DirectoryInfo(@"D:\Testpgm");
  FileInfo[] Files = dinfo.GetFiles("dbscript.sql", SearchOption.AllDirectories);
  foreach (FileInfo file in Files)
  {
  comboBox2.Items.Add(file.Name);
  }
 }
}

And the code used in combobox 2 is:

private void comboBox2_TextChanged(object sender, EventArgs e)
{
 string textsel = comboBox2.SelectedItem.ToString(); //textsel is used to select the sql file selected
 if (textsel == "master.sql")
 {
  richTextBox1.Clear();
     //Read the selected sql file and display it in richtextbox
  System.IO.StreamReader myFile = new System.IO.StreamReader(@"D:\Testpgm\master.sql");
  string textentry = myFile.ReadToEnd();
  richTextBox1.AppendText(textentry);
 }
 else
 {
  richTextBox1.Clear();
     //Read the selected sql file and display it in richtextbox
System.IO.StreamReader myFile = new System.IO.StreamReader(@"D:\Testpgm\dbscript.sql");
  string textentry = myFile.ReadToEnd();
  richTextBox1.AppendText(textentry);
 }
}

Now, I want to connect to the database which I selected in combo box 1 and then exceute the sql script included in the sql file that is selected in combo box 2, at the click of a button.

Is this possible? How can I achieve this?

Any comments would be really helpful and appreciated..

Damith · Accepted Answer · 2013-08-16 05:49:24Z

3

by using

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

you can do as below

SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);

answered Aug 16, 2013 at 5:49

Damith

63.1k13 gold badges105 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ehsan · Accepted Answer · 2013-08-16 05:55:24Z

0

Is this possible? How can I achieve this?

if you have both the database and the script file to execute then yes it is possible.

First of all you need to get the connection string for your selected database and make a connection. Then you can use SMO objects to execute your batch queries.

string connectionString = "YourConnectionString";
//make a sql connection   
using (var sqlConnection = new SqlConnection(connectionString))
    {
        var serverConnection = new ServerConnection(sqlConnection);
        var server = new Server(serverConnection);
        server.ConnectionContext.ExecuteNonQuery(textentry); 
    }

NOTE: You should consider using normal queries if you don't have batch statments to execute. As you can get into lots of problems using smo objects

answered Aug 16, 2013 at 5:55

Ehsan

32.8k6 gold badges61 silver badges66 bronze badges

4 Comments

Vysakh Venugopal Over a year ago

But will the script executes in the selected database? Because some scripts must not get executed in master database.I have 2 scripts one for master database and another one for other database.If I have selected master database, the it should get executed in master database. if I have selected any other, then it should get executed in the database which I selected.Is this possible, through the above code?

Ehsan Over a year ago

@VysakhVenugopal you need to make connection to your selected database. Build your connection string according to selected database

Vysakh Venugopal Over a year ago

I have the same connection string for all the databases. I have one credential for login to master.I use that for script execution usually. So, here also I would use the same, but need to execute the script where I specify the database in combo box 1.

Ehsan Over a year ago

if you have same credentials for all users then all you have to do is to replace the database name in connection string.

Collectives™ on Stack Overflow

c# for sql script execution

2 Answers 2

Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Related