none
How can i execute multiple sql queries with one database hit ? RRS feed

  • Question

  • Hello !

    I'm creating a application on vb.net with a sql server database.

    I have a situation when on runtime I have several sql queries ( Update and delete ) that are generated and I want to excute them with one database hit.

    For example :

    sql1="UPDATE MyTable1 SET Name = 'Test2' WHERE Id = 2"
    sql2="Delete * from TB2 WHERE Id = 31"

    .....

    ( I may have 50+ queries like these ( Update or delete ) . I can run one by one but with several database hit this is slow.

    Is there any way to send all the queries to sql server and to execute with one database hit ?

    Thank you !

    Monday, June 29, 2015 10:30 AM

All replies

  • Hello - You can try these options:

    Approach 1 - Send all as one Batch:

    /*Instead of this*/
    sql1="UPDATE MyTable1 SET Name = 'Test2' WHERE Id = 2"
    sql2="Delete * from TB2 WHERE Id = 31"
    
    /*Use this*/
    sql1 = "UPDATE MyTable1 SET Name = 'Test2' WHERE Id = 2; Delete * from TB2 WHERE Id = 31"


    Approach 2 - Put everything in one Stored Procedure:

    -- Create Stored Procedure in Database 
    CREATE PROCEDURE procSQL
    (
    	@p1 INT, @p2 INT
    )
    AS
    BEGIN 
    	UPDATE MyTable1 SET Name = 'Test2' WHERE Id = @p1
    	Delete from TB2 WHERE Id = @p1`
    END
    
    -- In .NET
    sql1 = "execute procSQL " & p1 & "," & p2
    

    Note: In the second example, I have ust used raw method to pass parameters, you can use other more appropriate ways of passing them such as by using Add method in command object, such as objCommand.Add ()

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Proposed as answer by Albeart Leaon Monday, June 29, 2015 7:52 PM
    Monday, June 29, 2015 10:45 AM
  • See my blog about the subject

    http://dimantdatabasesolutions.blogspot.co.il/2009/01/executing-multiple-script-file-via.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 29, 2015 11:05 AM
    Answerer
  • Hello - You can try these options:

    Approach 1 - Send all as one Batch:

    /*Instead of this*/
    sql1="UPDATE MyTable1 SET Name = 'Test2' WHERE Id = 2"
    sql2="Delete * from TB2 WHERE Id = 31"
    
    /*Use this*/
    sql1 = "UPDATE MyTable1 SET Name = 'Test2' WHERE Id = 2; Delete * from TB2 WHERE Id = 31"


    Approach 2 - Put everything in one Stored Procedure:

    -- Create Stored Procedure in Database 
    CREATE PROCEDURE procSQL
    (
    	@p1 INT, @p2 INT
    )
    AS
    BEGIN 
    	UPDATE MyTable1 SET Name = 'Test2' WHERE Id = @p1
    	Delete from TB2 WHERE Id = @p1`
    END
    
    -- In .NET
    sql1 = "execute procSQL " & p1 & "," & p2

    Note: In the second example, I have ust used raw method to pass parameters, you can use other more appropriate ways of passing them such as by using Add method in command object, such as objCommand.Add ()

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Thank you , but I can't create stored procedure because all the queries are created dynamically at runtime ( I create the queries step by step on runtime depending on several conditions).

    All the queries are Update or delete queries , but they may contain several conditions.

    If I merge the queries like you have suggested ,

    sql1 = "UPDATE MyTable1 SET Name = 'Test2' WHERE Id = 2; Delete * from TB2 WHERE Id = 31"

    After how can I execute this ?

    I'm using entity framework , but EF doesn't support executing batch queries ( support only one query at time).,

    Sop please , can you give me another way how can I execute the sql1 ?


    Monday, June 29, 2015 11:15 AM
  • See my blog about the subject

    http://dimantdatabasesolutions.blogspot.co.il/2009/01/executing-multiple-script-file-via.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    If you have read my first post , I need to execute these from VB.net not from SQL CMD
    Monday, June 29, 2015 11:16 AM
  • I have not seen you mentioned VB.NET in you first post. Anyway, I am afraid you won't be able do it without creating a batch. BUT, the below approach seems to work but pretty open fro SQL Injection

    Build a string in .NET

    Connect to SQL Server

    Issue EXEC(@sql)

    /*

    declare @sql as varchar(max) ='create table #t (c int);insert into #t values (1);select * from #t; delete from #t;select * from #t;'
    exec (@sql)

    */


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 29, 2015 11:24 AM
    Answerer
  • Here is a sample code:

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Try
    con.ConnectionString = "Data Source=your_server;Initial Catalog=your_db;Integrated Security=SSPI"
    con.Open()
    cmd.Connection = con
    cmd.CommandText = "UPDATE MyTable1 SET Name = 'Test2' WHERE Id = 2; Delete * from TB2 WHERE Id = 31"
    cmd.ExecuteNonQuery()
    
    Catch ex As Exception
    MessageBox.Show("Error while updating record on table..." & ex.Message, "Update Records")
    Finally
    con.Close()
    End Try


    Please click "Mark As Answer" if my post helped.

    Monday, June 29, 2015 11:33 AM
  • I have this code , but I don't know why it's not working with the merged queries :

    Public Sub CreateCommand(ByVal queryString As String, _
      ByVal connectionString As String)
        Using connection As New SqlConnection(connectionString)
            Dim command As New SqlCommand(queryString, connection)
            command.Connection.Open()
            command.ExecuteNonQuery()
        End Using 
    End Sub

    Monday, June 29, 2015 11:33 AM
  • Try this technique to get a string

    http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 29, 2015 12:29 PM
    Answerer
  • Hi

    You use the below concept with VB.NET

    private void ExecuteBatchNonQuery(string sql, SqlConnection conn) {
        string sqlBatch = string.Empty;
        SqlCommand cmd = new SqlCommand(string.Empty, conn);
        conn.Open();
        sql += "\nGO";   // make sure last batch is executed.
        try {
            foreach (string line in sql.Split(new string[2] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries)) {
                if (line.ToUpperInvariant().Trim() == "GO") {
                    cmd.CommandText = sqlBatch;
                    cmd.ExecuteNonQuery();
                    sqlBatch = string.Empty;
                } else {
                    sqlBatch += line + "\n";
                }
            }            
        } finally {
            conn.Close();
        }
    }


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Monday, June 29, 2015 7:27 PM
  • Convert it in VB.NET 

    public partial class ExcuteScript : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";
    
        string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");
    
        SqlConnection conn = new SqlConnection(sqlConnectionString);
    
        Server server = new Server(new ServerConnection(conn));
    
        server.ConnectionContext.ExecuteNonQuery(script);
        }
    }

    Monday, June 29, 2015 8:02 PM
  • Hi

    You use the below concept with VB.NET

    private void ExecuteBatchNonQuery(string sql, SqlConnection conn) {
        string sqlBatch = string.Empty;
        SqlCommand cmd = new SqlCommand(string.Empty, conn);
        conn.Open();
        sql += "\nGO";   // make sure last batch is executed.
        try {
            foreach (string line in sql.Split(new string[2] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries)) {
                if (line.ToUpperInvariant().Trim() == "GO") {
                    cmd.CommandText = sqlBatch;
                    cmd.ExecuteNonQuery();
                    sqlBatch = string.Empty;
                } else {
                    sqlBatch += line + "\n";
                }
            }            
        } finally {
            conn.Close();
        }
    }


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Sorry , how should I call your function with these 2 queries :

    sql1="UPDATE MyTable1 SET Name = 'Test2' WHERE Id = 2"
    sql2="Delete * from TB2 WHERE Id = 31"

    If I call with each sql query alone , works , but if I call with merged queries like this sql1 & ";" & sql2 , doesn't work.


    • Edited by dcode25 Tuesday, June 30, 2015 2:05 AM
    Tuesday, June 30, 2015 1:56 AM