Transcript Low Level ADO.NET Operations

Low Level ADO.NET Operations
Microsoft Visual C# 2008 Step by Step
Chapter 25
1
Objectives
You will be able to
 Write C# programs that perform SQL
Select operations on a database table
and display the results in a Windows
form.
 Open a new Windows form and pass
information to it from the original form.
2
Overview

The Northwind Traders Database

A Windows program to browse a table.


Getting information for connection string at run time
SqlConnection object


SqlCommand object



Connection String
Command String
Peforming a Select command
Browsing the results
3
The Northwind Traders Database


Classic Microsoft Example Database
Free download from Microsoft.


Can install on your own computer if you have MS SQL
Server.



Read-only version on scorpius
Installation instructions in Chapter 25
SQL script instnwnd.sql
We will use scorpius for examples and projects.
4
The Northwind Traders Database
Visual C# 2008 Step by Step, page 500
5
Accessing Scorpius

Unless you are on a wired connection to
the USF network, you will need to set up
a VPN connection. (Virtual Private Network)



https://vpn.usf.edu/
See
http://www.cse.usf.edu/~turnerr/Web_Application_Design/
017_Mapping_a_Drive.pdf


Slides 2 - 7
You do not need to map a drive, as described in the
rest of this presentation.
6
Opening VPN Connection

Once you have installed the VPN software on
your computer, you only need to open the
connection:


All Programs
 Juniper Networks
 Network Connect 6.5.0
 Network Connect
You can put a shortcut to Network Connect on your
desktop.



Right click on the menu command.
Select Create Shortcut
Drag the shortcut to the desktop
7
Network Connect

This says that you are connected
through the VPN:
8
Add Connection for Northwinds Database
In Visual Studio 2008 Server Explorer
Note: Visual Studio 2010 Server Explorer
does not work with Scorpius.
9
Add Connection for Northwinds Database
10
The Northwind Traders Database
11
Products
12
Employees
13
Northwind Product Browser


Simple example to illustrate use of ADO
primitives for low level access.
Will get the username and password
from textboxes.


Open a new Windows form to display
Product information.
For now, display Product Name only.

Will extend to display more infomation.
14
Northwinds Product Browser

Create a new Visual C# Windows Forms application.

.NET Framework 3.5
15
Initial Form
Set UseSystemPasswordChar property of tbPassword to true.
16
Form1.cs
using
using
using
using
using
System;
System.Collections.Generic;
System.Data;
System.Data.SqlClient;
System.Windows.Forms;
namespace Product_Browser
{
public partial class Form1 : Form
{
List<String> Product_List;
public Form1()
{
InitializeComponent();
}
17
btnGetData_Click()
private void btnGetData_Click(object sender, EventArgs e)
{
SqlDataReader rdr;
SqlConnection cn;
Product_List = new List<string>();
try
{
cn = Setup_Connection();
rdr = Get_SqlDataReader(cn);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
18
btnGetData_Click()
while (rdr.Read())
{
String Product_Name = (String)rdr["ProductName"];
Product_List.Add(Product_Name);
}
rdr.Close();
cn.Close();
Product_Information Product_Info_Form =
new Product_Information(Product_List);
Comment this out
temporarily
this.Hide();
Product_Info_Form.ShowDialog();
this.Close();
}
19
Setup_Connection()
private SqlConnection Setup_Connection()
{
String connection_string = "server=scorpius.eng.usf.edu; " +
"database=Northwind;" +
"User=" + tbUserName.Text + "; " +
"Password=" + tbPassword.Text;
SqlConnection conn =
new SqlConnection(connection_string);
conn.Open();
return conn;
}
20
Get_SqlDataReader()
private SqlDataReader Get_SqlDataReader(SqlConnection conn)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText ="SELECT ProductName FROM Products";
cmd.Connection = conn;
return cmd.ExecuteReader();
}
21
Add a Second Windows Form
22
Add a Second Windows Form
23
Design the Form
24
Product_Information.cs
using System;
using System.Collections.Generic;
using System.Windows.Forms;
namespace Product_Browser
{
public partial class Product_Information : Form
{
int Current_Record_Number;
List<String> Product_List;
// Constructor
public Product_Information(List<String> Product_List_Arg)
{
InitializeComponent();
this.Product_List = Product_List_Arg;
Current_Record_Number = 0;
Display_Current_Record();
}
25
Product_Information.cs

Add stub for Display_Current_Record.
void Display_Current_Record()
{
}
26
Back in Form1.cs

At end of btnGetData_Click:
Product_Information Product_Info_Form =
new Product_Information(Product_List);
this.Hide();
Product_Info_Form.ShowDialog();
this.Close();
}

Build and run
27
First Form
28
Second Form
29
Product_Information.cs

Now we need to fill in the code for the
Product Information form to do its
work.
30
Display_Current_Record()
private void Display_Current_Record()
{
tbProductName.Text = Product_List[Current_Record_Number];
tbRecordNumber.Text = "Record " + (Current_Record_Number + 1) +
" of " + Product_List.Count;
Update_Buttons();
}
31
Update_Buttons()
private void Update_Buttons()
{
btnFirst.Enabled = Current_Record_Number > 0;
btnNext.Enabled = Current_Record_Number < Product_List.Count - 1;
btnPrev.Enabled = Current_Record_Number > 0;
btnLast.Enabled = Current_Record_Number < Product_List.Count - 1;
}
Build and run
32
First Form
33
Second Form
34
Button Click Event Handlers

Double click on each button on the
Product_Informaiton form to add an
event handler.
35
Button Click Methods
private void btnFirst_Click(object sender, System.EventArgs e)
{
Current_Record_Number = 0;
Display_Current_Record();
}
private void btnPrev_Click(object sender, System.EventArgs e)
{
Current_Record_Number-=1;
Display_Current_Record();
}
private void btnNext_Click(object sender, System.EventArgs e)
{
Current_Record_Number+=1;
Display_Current_Record();
}
private void btnLast_Click(object sender, System.EventArgs e)
{
Current_Record_Number = Product_List.Count -1;
Display_Current_Record();
36
}
Product Browser in Action
37
Product Browser in Action
38
Product Browser in Action
39
Shutting Down

The Form1 object exists while the
Product_Information form is being used.


When the user clicks the Close button on
the Product_Information form, control
returns to Form1.


But it is not visible on the screen.
Following
Product_Info_Form.ShowDialog();
The Form1 object calls Close()
terminating the program.
40