Dealing with Data

I l @ ve RuBoard

With ADO.NET, you have new capabilities for both retrieving and using data. Your application can retrieve data directly into a DataReader much like the classic ASP RecordSet , or into a temporary, in-memory database structure called a DataSet . This DataSet can then be used to populate structures such as DataGrids and Repeaters , used to display the information.

Creating a Connection with ADO.NET

The most common source for data is a database, so before you retrieve any data, you will have to create a connection. Three different objects for creating a connection are included in System.Data.SQL : DBConnection , OleDbConnection , and SQLConnection . DBConnection is a general class inherited by all other classes. OleDbConnection is much like traditional ADO (and was, in fact, called ADOConnection in earlier versions of .NET), in that it is not database-specific . SQLConnection is optimized for making connections to Microsoft SQL Server databases. Make a connection by using a connection string or DSN:

 OleDbConnection cn =      new OleDbConnection("userid=sa;password=;database=nrthwnd;server=myServer") 

or

 OleDbConnection cn = new OleDbConnection("northwind", "sa", "manager") 

Commands

It is the command objects that execute the actual SQL statements. Like connections, there are three versions of commands: DBCommand , OleDbCommand , and SQLCommand . To use them, set a connection and command text, and execute it.

 OleDbCommand cmd = new OleDbCommand(); cmd.Connection = cn; cmd.CommandText =            "insert into products (prod_id, prod_name) values (232, 'Cushions')"; nCount = cmd.ExecuteNonQuery(); 

or the alternate syntax:

 OleDbCommand cmd = new OleDbCommand(           "insert into products (prod_id, prod_name) values (232, 'Cushions')",            cn); nCount = cmd.ExecuteNonQuery(); 

You can also use a command object to execute a stored procedure, passing both IN parameters and OUT parameters, and accessing the OUT parameter values.

 OleDbCommand cmd = new OleDbCommand(); cmd.Connection = cn; cmd.CommandText = "MyProcedure"; cmd.CommandType = CommandType.StoredProcedure; OleDbParameter TempParam; TempParam = new OleDbParameter("myInParam",OleDbDataType.VarChar,2); TempParam.Value = "CA"; cmd.Parameters.Add(TempParam); TempParam = new OleDbParameter("myOutParam", OleDbDataType.Int); TempParam.Direction = ParameterDirection.Output; cmd.Parameters.Add(TempParam); cmd.ExecuteNonQuery(); Label1.Text = cmd.Parameters["myOutParam"].Value.ToString(); 

Retrieving Data

The most commonly executed statements return a set of data, which can be viewed in a number of ways. The simplest is to create a DBDataReader , OleDbDataReader , or SQLDataReader , populated by the execution of the command object:

 OleDbCommand cmd = new OleDbCommand("select * from products", cn); OleDbDataReader dr; cmd.Execute(out dr); while (dr.Read()) {     Label1.Text += dr["pid"] + ": " + dr["prod_name"] + "<br>"; } 

For more complex data retrieval needs, extract data into a DataSet . A DataSet can take data from more than one command and can create keys similar to those created for data integrity in the actual database.

 DataSet ds = new DataSet(); OleDbDataAdapter prodCmd = new OleDbDataAdapter(); prodCmd.Connection = cn; prodCmd.CommandText =                  "select * from products p1, pricing p2 where pid = priceid"; prodCmd.Fill(ds, "productInfo"); OleDbDataAdapter priceCmd = new OleDbDataAdapter("select * from orders", cn); priceCmd.Fill(ds, "orders"); DataColumn prodId = ds.Tables["productInfo"].Columns["pid"] DataColumn orderProdId = ds.Tables["orders"].Columns["pid"] DataRelation productOrdersRel = new DataRelation("productOrders",                                                   prodId,                                                   orderProdId) 

DataSet s and XML

You can also populate a DataSet with XML and use a DataSet to output XML:

 DataSet ds = new DataSet(); ds.ReadXml(Server.MapPath("products.xml")); Response.ContentType="text/xml"; StringWriter s = new StringWriter(); ds.WriteXmlData(s); Response.Write (s.ToString()); 

Viewing Data

When you have the data, you can create a view that can be filtered or sorted. You can then bind this view to a DataGrid or other control.

 DataView dv = ds.Tables["orders"].DefaultView; String orderid = formOrderid.Text; dv.Sort = "pid"; dv.RowFilter = "orderid = " + orderid; DataGrid1.DataSource = dv; DataGrid1.DataBind(); 

Databinding Syntax

Databinding applies to more than just DataGrids . The page itself can also bind data to controls and to sections of the page.

 <%@ Page language="VB" %> <script language="VB" runat="server"> Sub Page_Load(Src As Object, E As EventArgs)     Page.DataBind() End Sub </script> <html> <head><title>Page Data</title></head> <body>     <h2>Data Binding for Fun and Profit</h2>     <form action="myPage.aspx" method="post" runat="server">         Please enter another movie:         <asp:TextBox id="movie" runat="server" />         <asp:Button runat="server" Text="Submit Choice" />     </form>     <p>The last title you entered was <%# movie.Text %>.</p> </body> </html> 

When Page.DataBind() executes, all sections of the page within <%# %> are populated. You can also use databinding to bind a control, as in:

 <asp:Label id="lastChoice" Text="<%# movie.Text %>" /> 

Databinding is also used in a slightly more generic context, such as in using a DataGrid , Repeater , or other templated control. In this case, the static DataBinder.Eval() method allows you to specify a particular piece of data:

 <asp:Repeater id="Repeater1" runat="server">     <template name=HeaderTemplate>         <h1>Products</h1>     </template>     <template name="ItemTemplate">         <%# DataBinder.Eval(Container.DataItem, "pid") %> --         <%# DataBinder.Eval(Container.DataItem, "prod_name") %>         $<%# DataBinder.Eval(Container.DataItem, "price") %>     </template>     <template name="SeparatorTemplate">         <hr>     </template> </asp:Repeater> 

I l @ ve RuBoard