Retrieving Data
| I l @ ve RuBoard |
| Now that you've seen how to connect to the database, it's time to start issuing commands. The easiest way to send commands to the database is with the SqlCommand object. Using the SqlCommand ObjectThe SqlCommand object contains all the functionality that you will need to issue Select, Insert, Update, and Delete statements. The SqlCommand object can return a DataReader , giving you the ability to iterate through the result set return from a Select. Finally, a SqlCommand allows you to call stored procedures, specifying the input and output arguments, and providing access to the return value. Executing SQL StatementsListing 4.2 shows the SqlCommand object in action. Listing 4.2 Retrieving data using the SqlCommand . // // Connect to the database // SqlConnection cn = new SqlConnection( "server=localhost;" + "database=pubs;" + "uid=sa;" + "password="); cn.Open(); // // Create a SqlCommand // System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = cn; cmd.CommandText = "select * from authors"; // // Execute the Command // SqlDataReader dr; dr = cmd.ExecuteReader(); // // Loop through the results // while (dr.Read()) { Label1.Text += dr["au_lname"] + ", " + dr["au_fname"] + "<br>"; } Before this code will compile, you also need to add a "using" directive that references the System.Data.SqlClient namespace to the top of the file as shown in Listing 4.3. Listing 4.3 Using Directive namespace ado_net_by_example { using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient; In this example, we use the SqlCommand object to retrieve data from the database. We do this by setting the Connection property of the command object so that it knows what database we want to talk to. The CommandText contains the SQL statement that we want to execute. The results of the command will be stored in a DataReader object when the execute method is invoked. We'll take a closer look at the DataReader later. To test this code, right-click on SQLCommand.aspx in the Solution Explorer, and select "Set as Start Page". Then, press CTRL-F5 to start the application. You should see Internet Explorer launch, and the names of the authors listed. The DataReader ObjectThe DataRepeater is populated when the Execute method of the SqlCommand object is called. The data repeater lets you scroll through a result set, one record at a time. To scroll through the result set, you call the Read method. This method must be called once, prior to accessing the first record. Read will return false when you have reached the last record. A common problem with traditional ADO was that you could forget to call MoveNext , resulting in your code entering an infinite loop. As we can see, this is an impossibility with ADO.NET and the DataReader . To access the columns of the result set, you simply index the DataReader with the column name : LastName = dr["LastName"]; The DataReader is really optimized for batch-type operations on a large result set. The DataReader contacts the database whenever you request the next record, so that only one record is stored in memory at a time. Calling Stored ProceduresIn addition to executing ad-hoc queries, we can also execute stored procedures. In Visual Studio.NET, select the Project, Add Web Form, menu command. Name the new web form "StoredProc.aspx". Add a label control to the page by double-clicking the Label icon in the toolbox. In the solution explorer, right-click on "StoredProc.aspx", and select "View Code". When the code window opens, add the following line to the list of "using" directives at the top of the file: using System.Data.SqlClient; Add the code shown in Listing 4.4 to the Page_Load method to call a stored procedure with input and output parameters. Listing 4.4 Calling stored procedures with the SqlCommand object.// Connect to the Database SqlConnection cn = new SqlConnection( "server=localhost;" + "database=pubs;" + "uid=sa;" + "password="); cn.Open(); // Create a command object System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = cn; // Set CommandText to the name of the // stored procedure cmd.CommandText = "AuthorCount"; // Indicate that we're calling a stored proc cmd.CommandType = CommandType.StoredProcedure; // Add parameters to the command object that // mirror the actual parameters of the stored // procedure SqlParameter TempParam; TempParam = new SqlParameter( "@state",SqlDbType.VarChar,2); // For input paramteres, supply a value TempParam.Value = "CA"; cmd.Parameters.Add(TempParam); TempParam = new SqlParameter( "@count", SqlDbType.Int); // For output parameters, specify the direction. // If we don't specify a direction, it will default // to input. TempParam.Direction = ParameterDirection.Output; cmd.Parameters.Add(TempParam); // Execute the proc cmd.ExecuteNonQuery(); // Retrieve the data from the output parameter // and display it on the page. Label1.Text = cmd.Parameters["@count"].Value.ToString(); SqlParameterSqlParameterSqlParameter The procedure for calling a stored procedure is similar to executing an ad-hoc query. We still have to set the Connection object to the SqlConnection . The CommandText property is set to the name of the stored procedure we want to execute. This stored procedure has two parameters. An input parameter that takes a two-character state code, and an output parameter that will contain the number of authors from that state. To access these parameters, we have to manually add them to the Parameters collection of the SqlCommand object. To add the parameters, we create a variable called TempParam that is of the type SqlParameterSqlParameter . When we create an instance of this variable, we can pass arguments to the constructor to specify the name of the parameter, its data type, and optionally its size (for parameters that are a character type). For input parameters, we also need to specify a value, which we do through the value property of the SqlParameter object. After the parameter is created, we add it to the Parameters collection of the SqlCommand object. For output parameters, we follow the same steps, with several exceptions. We need to specify that this is an output parameter by setting the Direction property of the SqlParameter object to Output . Also, for output parameters, we don't specify a value because the value will be calculated by the stored procedure. After the parameters are added, we execute the command. Because this stored procedure doesn't return any records, we execute the command with a call to ExecuteNonQuery . After the command executes, the results are waiting for us in the output parameter named @count . We retrieve this value with the following line of code: Label1.Text = cmd.Parameters["@count"].Value.ToString(); Remember, cmd.Parameters is the collection of parameters for the stored procedure. @count is the name of the output parameter. .Value says that we want the value for this parameter (versus its size, or direction). The Value property returns an object. To convert it into something that we can display in a label, we use the ToString method. This method is available with most objects. To test this page, you will first need to add the AuthorCount stored procedure to the pubs database. To add this procedure, open the Server Explorer in Visual Studio.NET. In the Server Explorer, drill down through the server name, and expand Sql Servers. Expand your server name, then expand the Pubs database. Right-click on "Stored Procedures", and select "New Stored Procedure". In the stored procedure editor, add the code shown in Listing 4.5. Listing 4.5 AuthorCount Stored Procedure.CREATE PROC AuthorCount( @state varchar(2), @count int output ) AS Select @count=count(*) from authors where state=@state Finally, in the Solution Explorer, right-click on "StoredProc.aspx", and select "Set as Start Page". Press CTRL-F5 to start the application. You should see Internet Explorer launch, and display a page indicating that there are 15 authors in California. The DataReader is great for dealing with large result sets, but if you aren't returning a huge number of rows, the DataSet offers many advantages. The SqlDataAdapter ObjectThe SqlDataAdapter acts as a bridge between the SqlConnection and the DataSet . The SqlConnection provides access to the underlying database. The DataSet holds the actual data. The SqlDataAdapter moves data between the DataSet and the database, through the SqlConnection . To start working with the SqlDataAdapter , add a new page to the project called SqlDA1.aspx. Add a DataGrid to this page from the toolbox. Switch to the code view, and add the code shown in Listing 4.6. Listing 4.6 Using the SqlDataAdapter to populate a DataSet .SqlConnection cn = new SqlConnection( "server=localhost;" + "database=pubs;" + "uid=sa;" + "password="); SqlDataAdapter cmd = new SqlDataAdapter( "select * from authors",cn); DataSet ds = new DataSet(); cmd.Fill(ds,"authors"); DataGrid1.DataSource = ds.Tables["authors"]; DataGrid1.DataBind(); Regardless of the database operation, we still need a connection object. As you can tell, we use exactly the same connection for a SqlDataAdapter as we used previously for the SQLDataReader . Next, we create an instance of the SqlDataAdapter object. The constructor for this object takes a SQL statement, and a reference to the SqlConnection , as arguments. It's important to note that with the SqlDataAdapter , we don't have to explicitly open the connection to the database. The SqlDataReader works in a "connected" mode, meaning that you need to keep a connection to the database open while you're accessing the data. The SqlDataAdapter works in a "disconnected" mode, meaning that when you execute the command, it will connect to the database, issue the SQL statement, get the results, and then disconnect. You don't maintain a connection while you're working with the actual data. Next we see that we create an instance of the DataSet object. There are a few things to pay attention to here also. You'll notice that the objects we've been using so far have all been prefixed with "Sql". For example, "SqlConnection," "SqlDataAdapter," and "SqlDataReader". Why aren't we creating an instance of "SqlDataSet"? Is this just a typo? Absolutely not. All the objects that begin with "Sql" are specific to SQL Server. "OleDbConnection" may work differently, and have different properties and methods . The DataSet , however, is just a place to put and modify the data. It doesn't care where the data comes from, and it never talks directly to the underlying database. In other words, the DataSet is completely generic, and provides exactly the same functionality regardless of the underlying data source. To actually populate the DataSet , we call the FillDataSet method of the SqlDataAdapter object. We pass the DataSet as an argument, as well as a text name for this result set. Here, we're calling the result set "authors," but we could use any string we want. "Why name the result set?" you ask. A DataSet is fundamentally different from any object you've ever worked with. You can store multiple result sets in a single DataSet . For example: cmdAuthors.FillDataSet(ds,"authors"); cmdPubs.FillDataSet(ds,"pubs"); You can even tell the DataSet that the two result sets are related on a particular column ( pubId , for example). Later, when you want to display data from a particular result set, you need a way to identify which one. Hence the need for naming the result set when you populate the DataSet . Finally, we want to display the results somewhere. ASP.NET makes it so simple to display data from a database. We simply bind the DataSource property of a DataGrid to the table in the DataSet that stores a particular set of records. Listings 4.7 and 4.8 illustrate the complete listing, as well as the output. Listing 4.7 Displaying data using the SqlDataAdapter ”SqlDA1.aspx.<%@ Page language="c#" Codebehind="SqlDA1.aspx.cs" AutoEventWireup="false" Listing 4.8 Displaying data using the SqlDataAdapter ”SqlDA1.aspx.cs. using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient; namespace ado_net_by_example { /// <summary> /// Summary description for SqlDA1. /// </summary> public class SqlDA1 : System.Web.UI.Page { protected System.Web.UI.WebControls.DataGrid DataGrid1; public SqlDA1() { Page.Init += new System.EventHandler(Page_Init); } private void Page_Load(object sender, System.EventArgs e) { SqlConnection cn = new SqlConnection( "server=localhost;" + "database=pubs;" + "uid=sa;" + "password="); SqlDataAdapter cmd = new SqlDataAdapter( "select * from authors",cn); DataSet ds = new DataSet(); cmd.Fill(ds,"authors"); DataGrid1.DataSource = ds.Tables["authors"]; DataGrid1.DataBind(); } private void Page_Init(object sender, EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); } #region Web Form Designer generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion } } The DataSet ObjectPopulating a DataSet is just the beginning. As you'll see, the DataSet offers a substantial amount of functionality. Data BindingThe concept of having a server control populate itself with data from a DataSet is known as "data binding." Actually, under .NET, data binding is a very powerful feature. Controls can bind to DataSets , DataReaders , arrays, variables , functions, or just about anything. SortingThrough the DataView , you can re- sort the contents of the DataSet , without accessing the database. In this scenario, we'll let the user choose what column they want to sort the results set on. To avoid doing a trip back to the database, we'll cache the DataSet for five minutes. First, let's factor out the code that retrieves and caches the authors from the database (see Listing 4.9). Listing 4.9 Caching a DataSet . protected DataSet LoadAuthors() { DataSet ds; if (Cache["authors"] == null) { SqlConnection cn = new SqlConnection( "server=localhost;" + "database=pubs;" + "uid=sa;" + "password="); SqlDataAdapter cmd = new SqlDataAdapter( "select * from authors",cn); ds = new DataSet(); cmd.Fill(ds,"authors"); Cache.Insert("authors",ds, null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(5)); } else { ds = (DataSet)Cache["authors"]; } return ds; } The code is similar to the preceding example, with several exceptions. We check to see if the DataSet is already cached. If the DataSet is not cached, we connect to the database and populate it as before. We then cache the DataSet for five minutes with the call to Cache.Insert . Here, we're using something called a "sliding expiration," which means that if this cache item doesn't get hit for five minutes, it gets trashed. The other option is an absolute expiration, which will throw the cache object away in X amount of time, no matter what. This simplifies the Page_Load procedure to Listing 4.10. Listing 4.10 SqlDataAdapter.cs ” Page_Load . protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataSet ds = LoadAuthors(); DataGrid1.DataSource = ds.Tables["authors"]; DataGrid1.DataBind(); } } Initially, we'll let the user choose the column to sort on by clicking on a button. Listing 4.11 shows how the buttons are added to the page. Listing 4.11 SqlDA.aspx.<%@ Page language="c#" Codebehind="SqlDataAdapter.cs" AutoEventWireup="false" Inherits="ado_net_by_example.SqlDataAdapter" %> <html><head> <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0"> <meta name="CODE_LANGUAGE" Content="C#"></head> <body> <form method="post" runat="server"> <h1>Authors:</h1> <asp:Button id=cmdLastName runat="server" Text="Last Name"/> <asp:Button id=cmdFirstName runat="server" Text="First Name"/> <p> </p> <asp:DataGrid id=DataGrid1 runat="server"/> </form> </body></html> Finally, we'll add code in the code-behind page to handle the click events for these buttons (see Listing 4.12). Listing 4.12 SqlDataAdapter.cs: Click Events. protected void cmdFirstName_Click (object sender, System.EventArgs e) { DataSet ds = LoadAuthors(); DataView dv = ds.Tables["authors"].DefaultView; dv.Sort = "au_fname"; DataGrid1.DataSource = dv; DataGrid1.DataBind(); } protected void cmdLastName_Click (object sender, System.EventArgs e) { DataSet ds = LoadAuthors(); DataView dv = ds.Tables["authors"].DefaultView; dv.Sort = "au_lname"; DataGrid1.DataSource = dv; DataGrid1.DataBind(); } When you re-compile and display the results in the browser, you should see output similar to Figure 4.2. Figure 4.2. Displaying data from the database. The first time the user views the page, the Page_Load event calls LoadAuthors . LoadAuthors determines that the author information doesn't exist in the cache, and so it connects to the database, populates a DataSet , and inserts it into the cache. It also populates a DataGrid so that the author information shows up on the page. When the user clicks the "First Name" or "Last Name" button, we end up in the appropriate click event. The click event also calls LoadAuthors , but this time the DataSet does exist in the cache. The DataSet is retrieved from the cache, and we set a reference to its DefaultView . By modifying the Sort property of this DataView , we change the way that the information will be rendered by the DataGrid . We haven't actually changed the DataSet , just the way we view its data. FilteringFiltering data is equally simple. First, we'll add a drop-down list to our form so that the user can filter authors by state (see Listing 4.13). Listing 4.13 Adding a drop-down list.<%@ Page language="c#" Codebehind="SqlDataAdapter.cs" AutoEventWireup="false" Inherits="ado_net_by_example.SqlDataAdapter" %> <html><head> <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0"> <meta name="CODE_LANGUAGE" Content="C#"></head> <body> <form method="post" runat="server"> <h1>Authors:</h1> <asp:Button id=cmdLastName runat="server" Text="Last Name"/> <asp:Button id=cmdFirstName runat="server" Text="First Name"/> State : <asp:dropdownlist id=lstState runat=server autopostback=True> <asp:listitem>All</asp:listitem> <asp:listitem>CA</asp:listitem> <asp:listitem>KS</asp:listitem> <asp:listitem>UT</asp:listitem> </asp:dropdownlist> <p> </p> <asp:DataGrid id=DataGrid1 runat="server"/> </form> </body></html> Then we'll write code for the SelectedIndex event. Because the drop-down has "autopostback" set to true, this server-side event will automatically fire whenever the user changes the selection in the drop-down. Listing 4.17 shows the code for the event procedure. Listing 4.14 lstState_SelectedIndex event. protected void lstState_SelectedIndex (object sender, System.EventArgs e) { DataSet ds = LoadAuthors(); DataView dv = ds.Tables["authors"].DefaultView; String State = lstState.SelectedItem.ToString().ToUpper(); if (State != "ALL") { dv.RowFilter = "State = '" + State + "'"; } else { dv.RowFilter = ""; } DataGrid1.DataSource = dv; DataGrid1.DataBind(); } As you can see, filtering is very similar to sorting. Again, we call LoadAuthors to retrieve the DataSet . It may come from the database, or the cache. When we have the DataSet , we filter it by changing the RowFilter property of its DefaultView . To specify a filter, we use the syntax of a SQL WHERE clause. For example: dv.RowFilter = "State = 'CA'" In this case, the state is specified by the SelectedItem property of the drop-down list. If the user selects "All" from the drop-down, we don't want to do any filtering. We indicate no filtering by setting the RowFilter property to "", an empty string. Listings 4.15 and 4.16 show the complete listing, with output. Listing 4.15 Sorting and filtering with ADO.NET ”SqlDataAdapter.aspx.<%@ Page language="c#" Codebehind="SqlDataAdapter.cs" AutoEventWireup="false" Inherits="ado_net_by_example.SqlDataAdapter" %> <html><head> <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0"> <meta name="CODE_LANGUAGE" Content="C#"></head> <body> <form method="post" runat="server"> <h1>Authors:</h1> <asp:Button id=cmdLastName runat="server" Text="Last Name"/> <asp:Button id=cmdFirstName runat="server" Text="First Name"/> State : <asp:dropdownlist id=lstState runat=server autopostback=True> <asp:listitem>All</asp:listitem> <asp:listitem>CA</asp:listitem> <asp:listitem>KS</asp:listitem> <asp:listitem>UT</asp:listitem> </asp:dropdownlist> <p> </p> <asp:DataGrid id=DataGrid1 runat="server"/> </form> </body></html> Listing 4.16 Sorting and filtering with ADO.NET ”SqlDataAdapter.cs. namespace ado_net_by_example { using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; /// <summary> /// Summary description for SqlDA. /// </summary> public class SqlDA : System.Web.UI.Page { protected System.Web.UI.WebControls.DataGrid DataGrid1; protected System.Web.UI.WebControls.DropDownList lstState; protected System.Web.UI.WebControls.Button cmdFirstName; protected System.Web.UI.WebControls.Button cmdLastName; public SqlDA() { Page.Init += new System.EventHandler(Page_Init); } protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataSet ds = LoadAuthors(); DataGrid1.DataSource = ds.Tables["authors"].DefaultView; DataGrid1.DataBind(); } } protected DataSet LoadAuthors() { DataSet ds; if (Cache["authors"] == null) { SqlConnection cn = new SqlConnection( "server=localhost;" + "database=pubs;" + "uid=sa;" + "password="); SqlDataAdapter cmd = new SqlDataAdapter( "select * from authors",cn); ds = new DataSet(); cmd.Fill(ds,"authors"); Cache.Insert("authors",ds, null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(5)); } else { ds = (DataSet)Cache["authors"]; } return ds; } protected void Page_Init(object sender, EventArgs e) { // // CODEGEN: This call is required by the ASP+ Windows Form // Designer. // InitializeComponent(); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { cmdFirstName.Click += new System.EventHandler (this.cmdFirstName_Click); cmdLastName.Click += new System.EventHandler (this.cmdLastName_Click); lstState.SelectedIndexChanged += new System.EventHandler (this.lstState_SelectedIndex); this.Load += new System.EventHandler (this.Page_Load); } protected void lstState_SelectedIndex (object sender, System.EventArgs e) { DataSet ds = LoadAuthors(); DataView dv = ds.Tables["authors"].DefaultView; String State = lstState.SelectedItem.ToString().ToUpper(); if (State != "ALL") { dv.RowFilter = "State = '" + State + "'"; } else { dv.RowFilter = ""; } DataGrid1.DataSource = dv; DataGrid1.DataBind(); } protected void cmdFirstName_Click (object sender, System.EventArgs e) { DataSet ds = LoadAuthors(); DataView dv = ds.Tables["authors"].DefaultView; dv.Sort = "au_fname"; DataGrid1.DataSource = dv; DataGrid1.DataBind(); } protected void cmdLastName_Click (object sender, System.EventArgs e) { DataSet ds = LoadAuthors(); DataView dv = ds.Tables["authors"].DefaultView; dv.Sort = "au_lname"; DataGrid1.DataSource = dv; DataGrid1.DataBind(); } } } When you compile the page, and display the results in the browser, you should see output similar to Figure 4.3. Figure 4.3. Sorting and filtering data. |
Inherits="ado_net_by_example.SqlDA1" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0"> <meta name="CODE_LANGUAGE" Content="C#"> <meta name="vs_defaultClientScript" content="JavaScript (ECMAScript)"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie3-2nav3-0"> </HEAD> <body MS_POSITIONING="FlowLayout"> <form id="SqlDA1" method="post" runat="server"> <H1> Authors </H1> <P> <asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid> </P> </form> </body> </HTML> 
| I l @ ve RuBoard |
