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].
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)
