none
Using SQL script through vb.net code RRS feed

  • Question

  • Hi,

    I am new to SQL Server. I often find scripts over internet to perform different functions with SQL Server but I donot know how to use them in vb.net.

    For example I want to run the following code through my vb.net application, but donot know how to do so. Please advise

    ALTER LOGIN sa ENABLE ; GO ALTER LOGIN sa WITH PASSWORD = '' ; GO

    Thanks


    • Edited by Furqan Sehgal Wednesday, February 15, 2012 6:03 PM
    • Moved by Bob Beauchemin Wednesday, February 15, 2012 6:37 PM Moving to the group for SQLClient questions (From:.NET Framework inside SQL Server)
    Wednesday, February 15, 2012 6:03 PM

Answers

  • You should be able to run SQL DDL/DML using the System.Data.SqlClient namespace and SqlCommand Class with ExecuteNonQuery method:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 15, 2012 7:17 PM
  •  ADO.NET libraries do not support the GO keyword. And it do now allow multiple DDL statement to execute at one single statement. To resolve your issue, you can choose one of below option.

    Option1: Split your entire script and remove GO keyword and execute one by one in a loop.

    string scriptText = “Your Script as above”;  
    //split the script on "GO" commands  
    string[] splitter = new string[] { "\r\nGO\r\n" };  
    string[] commandTexts = scriptText.Split(splitter,  
    StringSplitOptions.RemoveEmptyEntries);  
    foreach (string commandText in commandTexts)  
    {  
     //execute commandText  
    }

    Option2: For large kind of scripts, you can create a .SQL file and put all your Script (With GO) and use the Server class from SQL Server Management Objects (SMO).

    string connectionString, scriptText; SqlConnection sqlConnection = new SqlConnection(connectionString); ServerConnection svrConnection = new ServerConnection(sqlConnection); Server server = new Server(svrConnection); server.ConnectionContext. ExecuteNonQuery(scriptText);

     Option3: Execute the .SQL file from VB.net/C# using shell class.

    var startInfo = new ProcessStartInfo(); 
    startInfo.FileName = "SQLCMD.EXE";
    startInfo.Arguments = 
    String.Format("-S {0} -d {1}, 
    -U {2} -P {3} -i {4}", server,database, 
    user, password, file); 
    Process.Start(startInfo);



    Lingaraj Mishra

    Thursday, February 16, 2012 7:24 AM

All replies

  • You should be able to run SQL DDL/DML using the System.Data.SqlClient namespace and SqlCommand Class with ExecuteNonQuery method:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 15, 2012 7:17 PM
  •  ADO.NET libraries do not support the GO keyword. And it do now allow multiple DDL statement to execute at one single statement. To resolve your issue, you can choose one of below option.

    Option1: Split your entire script and remove GO keyword and execute one by one in a loop.

    string scriptText = “Your Script as above”;  
    //split the script on "GO" commands  
    string[] splitter = new string[] { "\r\nGO\r\n" };  
    string[] commandTexts = scriptText.Split(splitter,  
    StringSplitOptions.RemoveEmptyEntries);  
    foreach (string commandText in commandTexts)  
    {  
     //execute commandText  
    }

    Option2: For large kind of scripts, you can create a .SQL file and put all your Script (With GO) and use the Server class from SQL Server Management Objects (SMO).

    string connectionString, scriptText; SqlConnection sqlConnection = new SqlConnection(connectionString); ServerConnection svrConnection = new ServerConnection(sqlConnection); Server server = new Server(svrConnection); server.ConnectionContext. ExecuteNonQuery(scriptText);

     Option3: Execute the .SQL file from VB.net/C# using shell class.

    var startInfo = new ProcessStartInfo(); 
    startInfo.FileName = "SQLCMD.EXE";
    startInfo.Arguments = 
    String.Format("-S {0} -d {1}, 
    -U {2} -P {3} -i {4}", server,database, 
    user, password, file); 
    Process.Start(startInfo);



    Lingaraj Mishra

    Thursday, February 16, 2012 7:24 AM