Multiple Queries Execution

Multiple Queries Execution

When we need to select values from two different tables in to two datasets, then normally we will be making a call to the database two times. But we can do this from a single query. Now we will see the syntax to do multiple queries inside a single SqlCommand for SQL Server. What happen is that one call will be made to the database server - and one call back (containing ALL results).

The nice thing about ADO.NET is that is stuffs everything into our dataset tables collection.

The code for this:

string sConn = "Data Source=(local);Network Library=DBMSSOCN;Initial Catalog=Northwind;User ID=test;Password=test;";
string sSQL = "select * from products;select * from categories";
DataSet dd = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(sConn,
 CommandType.Text, sSQL);
dataGridView1.DataSource = dd.Tables[0];
dataGridView2.DataSource = dd.Tables[1];

As you can see the products are put into tables[0] and categories into tables[1].

Messages From SPs to .Net

We can give the print statements in SqlServer StoredProcedures. But while we are running them from .Net, how can we get them in to front end. Here I am giving a small example of how to get those messages in to front end.

I have a Windows Form with the name "GetMessagesFromStoredProcedure" and a Button named "GetMessages". The event "OnInfoMessage" will be getting all the error messages from the stored procedure that we have run.


Imports System.Data.SqlClient

Public Class GetMessagesFromStoredProcedure

Private Sub GetMessages_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetMessages.Click

Dim Con As SqlConnection = New SqlConnection("Data Source=CHAKRI\SQLEXPRESS;Initial Catalog=CHAKRI;Persist Security Info=True;Trusted_connection=yes")
AddHandler Con.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
Dim Com As SqlCommand = New SqlCommand

Try
Com.Connection = Con
Con.Open()
Com.CommandType = CommandType.StoredProcedure
Com.CommandText = "PrintTest"
Com.ExecuteNonQuery()
Con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)

Dim sqlEvent As System.Data.SqlClient.SqlError
For Each sqlEvent In args.Errors
MessageList.Items.Add(sqlEvent.Message)