In this article I will explain with an example, how to execute multiple SQL Scripts (Queries) using GO statement in C# and VB.Net.
The records returned from multiple SELECT queries (statements) are populated into DataTables and then these DataTables will be inserted into a DataSet.
Database
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below.
HTML Markup
The HTML Markup consists of two GridViews which will be populated using the DataSet.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px" />
</Columns>
</asp:GridView>
<br />
<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="EmployeeName" HeaderText="Employee Name" ItemStyle-Width="150px" />
<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px" />
</Columns>
</asp:GridView>
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Execute multiple SQL Scripts (Queries) using GO statement in C# and VB.Net
Inside the Page Load event of the page, multiple Select queries are executed to fetch data from two Tables i.e. Customers and Employees.
Note: In order to execute queries on multiple Tables, the Select statement of each Table must be separated by GO statement.
The fetched records are populated into a DataSet and finally the DataTables of the DataSet are used to populate the GridViews.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT TOP 10 ContactName, City, Country FROM Customers";
query += " GO ";
query += "SELECT TOP 10 (FirstName + '''' '''' + LastName) EmployeeName, City, Country FROM Employees";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
gvCustomers.DataSource = ds.Tables[0];
gvCustomers.DataBind();
gvEmployees.DataSource = ds.Tables[1];
gvEmployees.DataBind();
}
}
}
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "SELECT TOP 10 ContactName, City, Country FROM Customers"
query += " GO "
query += "SELECT TOP 10 (FirstName + '''' '''' + LastName) EmployeeName, City, Country FROM Employees"
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand(query)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As New DataSet()
sda.Fill(ds)
gvCustomers.DataSource = ds.Tables(0)
gvCustomers.DataBind()
gvEmployees.DataSource = ds.Tables(1)
gvEmployees.DataBind()
End Using
End Using
End Using
End Using
End If
End Sub
Screenshot
From: http://www.aspsnippets.com/