run SQL server scripts from inside a C# code

[Eslam Afifi]

You can use the SqlConnection and SqlCommand in the System.Data.SqlClient namespace. Or you can use the ServerConnection in the Microsoft.SqlServer.Management.Common namespace (reference the Microsoft.SqlServer.ConnectionInfo assembly).

Eslam Afifi

[Harvey Saayman]

i wrote a program a while back to run all my sql scripts for me, and its FORCES the db to drop connections aswell(this is for dev purposes only) it uses SQLCMD.exe, and you need sql2005 for that... heres the code if your interested

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Threading;
using System.IO;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Wmi;
using Microsoft.SqlServer.Management.Common;

namespace CreateDB
{
public class Program
{
public static string database;
public static string userName;
public static string password;

    static void Main(string\[\] args)
    {
        getVariables();

        FileStream fs = new FileStream(@"C:\\scripts.txt", FileMode.Open, FileAccess.Read);
        StreamReader sr = new StreamReader(fs);

        killDatabase();

        string line = sr.ReadLine();
        while (line != null)
        {
            ProcessStartInfo StartInfo = new ProcessStartInfo("sqlcmd", "-S " + database + " -d master" + " -U " + userName + " -P " + password + " -i " + line);
            Process myProcess = new Process();

            StartInfo.UseShellExecute = false;
            StartInfo.RedirectStandardOutput = true;

            myProcess.StartInfo = StartInfo;
            myProcess.Start();

            Console.Write("Started Process --> ");
            myProcess.WaitForExit();

            StreamReader outputReader = myProcess.StandardOutput;
            Console.WriteLine("Finnished Process ---> output:" + "\\r\\n");
            Console.WriteLine(outputReader.ReadToEnd());

            Console.WriteLine();
            Console.WriteLine("|------------------------------------------------------------------------------|");
            Console.WriteLine();

            line = sr.ReadLine();
        }
        Console.ForegroundColor = ConsoleColor.Green;
        Console.WriteLine("DDDD      OOOO    N    N  EEEEEE  ");
        Console.WriteLine("D   D    O    O   NN   N  E       ");
        Console.WriteLine("D    D  O      O  N N  N  EEE     ");
        Console.WriteLine("D    D  O      O  N  N N  EEE     ");
        Console.WriteLine("D   D    O    O   N   NN  E       ");
        Console.WriteLine("DDDD      OOOO    N    N  EEEEEE  ");
        C

[Harvey Saayman]

i tried that when i wrote the 1st version of my "execute SQL scripts" app, it didn't work, although i thought i would :doh: check my other post to see what i ended up doing

Harvey Saayman - South Africa Junior Developer .Net, C#, SQL you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer) 1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111

[Giorgi Dalakishvili]

Thanks for the info.

Giorgi Dalakishvili #region signature my articles #endregion

[Harvey Saayman]

your welcome :)

Harvey Saayman - South Africa Junior Developer .Net, C#, SQL you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer) 1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111

[Sabry1905]

Thanks Harvey, this is exactly what i was looking for.

[Harvey Saayman]

Glad to help bud, let me know if you have any troubles with it

Harvey Saayman - South Africa Junior Developer .Net, C#, SQL you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer) 1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111

[Sabry1905]

i have an issue in running my script for each line in the script i got this a message like this Started Process --> Sqlcmd: ')': Invalid filename. Finnished Process ---> output: !!!

[Harvey Saayman]

can you post what you have in scripts.txt... and is that file located on the C:\ drive?

Harvey Saayman - South Africa Junior Developer .Net, C#, SQL you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer) 1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111

[Sabry1905]

i had sent you the script through mail, yes the script lie on the C:\ partition