Transcript Low Level ADO Operations

Low Level ADO.NET Operations II
Microsoft Visual C# 2008 Step by Step
Chapter 25
1
Getting Started


Download and expand the Northwind
Product Browser as we left it last class.
http://www.cse.usf.edu/~turnerr/Software_Systems_Development/
Downloads/2011_03_31_Low_Level_ADO_Operations/

File Northwind_Product_Browser.zip

Build and run
2
SqlDataReader

Class SqlDataReader provides two ways
for us to access various columns of the
current query result:


Column name
Position
3
Access by Column Name

We have seen one example of the
name based method

Form1.cs, btnGetData_Click
while (rdr.Read())
{
String Product_Name = (String)rdr["ProductName"];
Product_List.Add(Product_Name);
}
4
Access by Position

We could have written:
while (rdr.Read())
{
String Product_Name = rdr.GetString(0);
Product_List.Add(Product_Name);
}

Get column 0 of the current query
result, as a String.

No typecast necessary.
5
Reading Other Datatypes
The SqlDataReader class has a postion based
method for each datatype.







GetString()
GetInt32()
GetInt16()
GetDecimal()
GetBoolean()
…
You have to use the right one for the data
type of column you want to retrieve.

But then you don’t have to typecast the result.
6
Product Browser

Let’s extend the Product Browser to
display more information about each
product.
7
Table Products Contents
8
Table Products Definition
9
Product_Information Form Extended
10
Add Class Product
using System;
namespace Product_Browser
{
class Product
{
private String product_name;
private int product_id;
private decimal unit_price;
private int units_in_stock;
private bool discontinued;
}
}
11
Define Properties for Product Fields


Select field.
Refactor > Encapsulate Field
12
Product_List

In Form1.cs modify Product_List to be a
list of Products.
List<Product> Product_List = new List<Product>();
13
Changes to Form1.cs

New SQL command in Form1.cs, Get_SqlDataReader:
SqlCommand1.CommandText =
"SELECT * FROM Products ORDER BY ProductName";
14
Recall Definition of Table Products
15
Update btnGetData_Click
while (rdr.Read())
{
Product p = new Product();
p.Product_name = rdr.GetString(1);
p.Product_id = rdr.GetInt32(0);
Must check any column that could be Null.
if (rdr.IsDBNull(5))
{
p.Unit_price = 0.00M;
A Decimal literal (M for “money”)
}
else
{
p.Unit_price = rdr.GetDecimal(5);
}
16
More Compactly
while (rdr.Read())
{
Product p = new Product();
p.Product_name = rdr.GetString(1);
p.Product_id = rdr.GetInt32(0);
p.Unit_price = rdr.IsDBNull(5) ? 0.0M : rdr.GetDecimal(5);
p.Units_in_stock = rdr.IsDBNull(6) ? 0 : rdr.GetInt16(6);
p.Discontinued = rdr.GetBoolean(9);
Product_List.Add(p);
}
17
Changes to Product_Information.cs


Update references to List<String> to be
List<Product>.
Update Display_Current_Record as
shown on next slide.
18
Chanages to Display_Current_Record()
private void Display_Current_Record()
{
Product p = (Product) Product_List[Current_Record_Number];
tbProductName.Text = p.Product_name;
tbProductID.Text = p.Product_id.ToString();
tbUnitPrice.Text = p.Unit_price.ToString("C");
Format as currency
tbUnitsInStock.Text = p.Units_in_stock.ToString();
cbDiscontinued.Checked = p.Discontinued;
tbRecordNumber.Text =
"Record " + (Current_Record_Number + 1) +
" of " + Product_List.Count;
Update_Buttons();
}
Build and run.
19
Compile Error!
20
Class Product
We have to declare class Product as “public”.
21
Class Product
Try again to build and run.
22
Enhanced Product Browser in Action
End of Section
23
A Better Solution


Forms should be limited to just UI functions.
Knowledge of how products are represented
in the database is better confined to a
separate class.


Separation of responsibilities.
Let class Product be responsible for knowledge of
how to initialize a product object using results of a
database query.
 Anyone else who needs product info should use
the Product class.
24
Class Product

We will add a constructor that takes the
query result as a parameter.


Initializes object with data from a
SqlDataReader.
Add
using System.Data.SqlClient;
25
Class Product Constructor
// This constructor initializes a product using a query result.
public Product(SqlDataReader rdr)
{
Product_name =
rdr.GetString(1);
Product_id =
rdr.GetInt32(0);
Unit_price =
rdr.IsDBNull(5) ? 0.0M : rdr.GetDecimal(5);
Units_in_stock = rdr.IsDBNull(6) ? 0
: rdr.GetInt16(6);
Discontinued =
rdr.GetBoolean(9);
}
Copy from btnGetData_Click()
26
Class Products


Let another class be responsible for
knowledge of how to do the query.
Static class Products will hold a static method
Get_Products.




Code previously included in Form1
Make username and password parameters.
Return List<Product>
No constructor.


The class cannot be instantiated.
Just a home for the static Get_Products method.
27
Get_Products()
public static List<Product> Get_Products(String Username,
String Password)
{
List<Product> Product_List = new List<Product>();
SqlDataReader rdr;
SqlConnection SqlConnection1;
SqlConnection1 = Setup_Connection(Username, Password);
rdr = Get_SqlDataReader(SqlConnection1);
while (rdr.Read())
{
Product p = new Product(rdr);
Product_List.Add(p);
}
rdr.Close();
SqlConnection1.Close();
return Product_List;
}
28
Setup_Connection()
private static SqlConnection Setup_Connection(String Username,
String Password)
{
String connection_string = "server=scorpius.eng.usf.edu; " +
"database=Northwind;" +
"User=" + Username + "; " +
"Password=" + Password;
SqlConnection SqlConnection1 =
new SqlConnection(connection_string);
SqlConnection1.Open();
return SqlConnection1;
}
29
Get_SqlDataReader()
private static SqlDataReader Get_SqlDataReader(
SqlConnection SqlConnection1)
{
SqlCommand SqlCommand1 = new SqlCommand();
SqlCommand1.CommandText =
"SELECT * FROM Products ORDER BY ProductName";
SqlCommand1.Connection = SqlConnection1;
return SqlCommand1.ExecuteReader();
}
30
Form1.cs
private void btnGetData_Click(object sender, EventArgs e)
{
try
{
Product_List = Products.Get_Products(tbUserName.Text,
tbPassword.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
// Instantiate the form that displays product information.
Product_Information pif =
new Product_Information(Product_List);
this.Hide();
pif.ShowDialog();
this.Close();
}
31
A Better Solution

The functionality is unchanged from
previous version.

Class Form1 is now simpler.




Limited to UI functions.
Class Products is responsible for interfacing
with the database.
Class Product is responsible for internal
representation of products.
This is an example of refactoring.
32
Assignment
Before next class:

Explore the Northwind Traders database.


Familiarize yourself with its tables.
Browse with Visual Studio or Management Studio Express.



Try some queries.
If you haven't already done so, download and read the
Northwind Traders Call Center concept document from
the Downloads area of the class web site:
http://www.cse.usf.edu/~turnerr/Software_Systems_Development/
Downloads/Northwind_Call_Center/ File Concept_v1_3.doc
End of Presentation
33