Setting Up Your Workspace
In this chapter, your workspace is going to be an ASP.NET Web Form. If you prefer working in an application that's similar to Microsoft's SQL Server Query Analyzer, you also can run all the queries in that.
The first step is to create a new web form. In the example in Listing 4.1, I created one called myqueries.aspx. Listing 4.1 contains the code you're going to be using to walk through the different SQL statements. Since this code is for demonstration purposes only, we won't be spending a lot of time looking at it; by now you should be familiar with most of the ADO.NET code after going through Chapter 2, “What Is ADO.NET?” and Chapter 3, “ADO.NET Managed Providers.” In Chapter 5, “Using a Basic DataGrid,” the DataGrid will be explained in greater detail.
Myqueries.aspx uses the DataGrid to display the result from our SQL Statements, although not all of the SQL statements that we'll be going through have a result. It uses the SQLConnection object and the SQLDataSetCommand for the database connectivity and the DataSet object for the in memory cache of data. You can test all the example code by typing it directly into the text area of the page in your browser and clicking the Submit Query button, or you can choose to hand-code it in the source code and execute the page. Listing 4.1 contains the source code for myqueries.aspx. You can also find myqueries.aspx in the Chapter 4 folder on the disk located in the back of the book.
Listing 4.1. This ASP.NET Web Form Can Be Used for Your Workspace
01: <%@ Import Namespace="System.Data" %>
02: <%@ Import Namespace="System.Data.SqlClient" %>
03: <html>
04: <head>
05: <script language="C#" runat="server">
06: void Page_Load(Object src, EventArgs e)
07: {
08: try{
09: if (Page.IsPostBack) {
10:
11: string strSQL;
12: strSQL = SQL.Text;
13:
14: SqlConnection sCon = new SqlConnection("SERVER=LOCALHOST;" +
15: "UID=sa;DataBase=Northwind");
16: SqlDataAdapter sda;
17: sda = new SqlDataAdapter(strSQL,sCon);
18: DataSet ds = new DataSet();
19: sda.Fill(ds,"Products");
20: datagrid1.DataSource = ds.Tables[0].DefaultView;
21: Page.DataBind();
22: }
23: } catch (SqlException Sex)
24: {
25: Message.Text = "<b>Error Processing Query - [" + Sex.Message.ToString() +
26: " ] Check Syntax and Try Again...</b>"; }
27: }
28: </script>
29: <title>Go To DotNetJunkies.com for .NET news and
30: tutorials!!</title>
31: </head>
32: <body>
33: <form method="post" runat="server">
34: <asp:Label
35: Runat="Server"
36: ForeColor="Red"
37: Id="Message"
38: MaintainState="False" />
39: <br>
40: <asp:TextBox id="SQL"
41: Runat="server"
42: Width="400"
43: Height="200"
44: TextMode="MultiLine"
45: MaintainState="True" />
46: <asp:Button id="button1"
47: Runat="server"
48: Text="Submit Query" />
49: <asp:DataGrid id="datagrid1"
50: Runat="server"
51: MaintainState="False" font-size="10"/>
52: </form>
53: </body>
54: </html>
|
If you choose to hand-code your SQL queries, you can simply change line 12 from this:
12: strSQL = SQL.Text;
to this:
12: strSQL = "SQL statement to be executed"
“SQL statement to be executed” is where the SQL code you want to execute should be placed. Also, take out lines 40–45, which create the large text field where you type in your SQL statements.
If you're using an Access database as your data source, or any other data source besides Microsoft's SQL Server, you must use the OleDbConnection and OleDbDataAdapter objects and change the code accordingly.