© copyright 1992-2004 by deitel & associates, inc. and pearson education inc. all rights...

1

© Copyright 1992-2004 by Deitel & Associates, Inc. and Pearson Education Inc. All Rights Reserved.

Outline25.1 Test-Driving the ATM Application25.2 Planning the ATM Application25.3 Creating Database Connections25.4 Programming the ATM Application25.5 Wrap-Up

Tutorial 25 - ATM ApplicationIntroducing Database Programming

2


Objectives

• In this tutorial, you will learn to:– Connect to databases.

– View the contents of an Access database.

– Add database controls to Windows Forms.

– Use the Server Explorer window.

– Use the Query Builder dialog.

– Read information from and update information in databases.

3


25.1 Test-Driving the ATM Application

Application Requirements A local bank has asked you to create a prototype automated teller machine (ATM) application to access a database that contains fictitious customer records. Each record consists of an account number, Personal Identification Number (PIN), first name and balance amount. For testing purposes, valid account numbers will be provided in a ComboBox. The ATM application should allow the user to log in to an account by providing a valid PIN. Once logged in, the user can view the account balance and withdraw money from the account (if the account contains sufficient funds). If money is withdrawn, the application should update the database.

4



• The ATM Application– Open ATM application and select Debug > Start to run– OK, Balance, Withdraw, and Done Buttons are disabled

– After account number is entered, user is prompted for a pin– Characters in PIN display as asterisks and enable OK Button

– Clicking the OK Button displays a welcome message and enables the Balance and Withdraw Buttons

– Clicking the Balance Button displays the current account balance

– Clicking Withdraw prompts the user to enter a monetary amount and displays the new balance after the withdrawal

5



Figure 25.1 ATM application Form.

Displays instructions and messages to the user

Keypad for entering PIN and withdrawal amount

ComboBox that displays account numbers

Disabled Buttons

6


25.1 Test-Driving the ATM ApplicationFigure 25.2 Providing PIN for the selected account.

Asterisk is displayed here for each keypad Button pressed

for the PIN

Account number selected

7



Figure 25.3 ATM displaying welcome message.

Welcome message displays in Label when user enters

correct PIN

8



Figure 25.4 ATM application displaying balance after withdrawal.

New balance displays in Label after user

performs a withdrawal

9


25.2 Planning the ATM Application

Figure 25.5 ACE table for the ATM application.

Action Control/Object Event FrmATM

Open connection to the database objOleDbConnection

Read account numbers from database

objReader

Store account numbers in ComboBox cboAccountNumbers

Close the database connection objOleDbConnection

Load

cboAccountNumbers

Prompt the user to enter a PIN lblDisplay

Enable the Done Button btnDone

Clear the TextBox for the PIN txtInput

SelectedIndexChanged

btnOne, btnTwo, btnThree, btnFour, btnFive, btnSix, btnSeven, btnEight, btnNine

Append the number to the PIN

Click

txtInput

Enable the OK Button btnOK

TextChanged

btnOK

Search the database for the account number’s corresponding account information

objOleDbConnection, objSelectAccountData

If the user provided a correct PIN Enable the Balance and Withdraw Buttons

btnBalance, btnWithdraw

Click


10




Action Control/Object Event Disable the ComboBox cboAccountNumbers

Display the user’s name and prompt the user to select a transaction

lblDisplay

Else Prompt the user to enter a valid PIN

lblDisplay

Clear the TextBox txtInput

btnBalance

Display the balance lblDisplay

Click

btnWithdraw

Prompt the user to enter the withdrawal amount

lblDisplay

Click

btnOK

If the withdrawal amount is less than the balance Calculate the new balance and update the database

objOleDbConnection, objUpdateBalance, lblDisplay

Click


11



Action Control/Object Event If the withdrawal amount is less than the balance Calculate the new balance and update the database

objOleDbConnection, objUpdateBalance, lblDisplay

Else Indicate that amount is too large

lblDisplay

Clear the withdrawal amount txtInput

btnDone

Disable the OK, Balance, Withdraw and Done Buttons

btnOk, btnBalance, btnWithdraw, btnDone

Enable the ComboBox cboAccountNumbers

Display instructions for the next customer

lblDisplay

Click


12


25.3 Creating Database Connections

• Adding a database– View > Server Explorer to display Server Explorer

window

– Create a connection object to maintain a connection to a database

13



Figure 25.6 Server Explorer window.

Click the Connect to Database Button

14



Figure 25.7 Provider tab in the Data Link Properties dialog.

Provider tab

Select this provider

Click to continue

15



Figure 25.8 Connection tab of the Data Link Properties dialog.

Connection tab

Click to select a database

16



Figure 25.9 Select Access Database dialog.

Select the db_ATM.mdb file

– db_ATM.mdb contains data you will query

17


25.3 Creating Database ConnectionsFigure 25.10 Connection tab containing selected database path.

Database path displayed

User name should be Admin

Blank password optionshould be checked

Click to test the connection

18



Figure 25.11 Database connection shown in the Server Explorer window.

Click to expandData connection

Drag and drop the data connection on the Form to

create an OleDbConnection

object

19



Figure 25.12 Storing the database’s password.

– Database has no password, click Don’t include password Button.

20



Figure 25.13 OleDbConnection object added to the Form.

OleDbConnection object added to the Form

21



• Understanding the db_ATM.mdb database structure– AccountInformation is a table in the db_ATM.mdb

database

– Tables store information in rows and columns in a database

– In AccountInformation, the ten records are in table rows and the four fields are table columns

– Primary keys are columns that distinguish rows from one another

– This database’s primary key is the AccountNumber column

– The value of the primary key in each row of the table must be unique

22



Figure 25.14 Viewing the AccountInformation table.

Click to display the Tables node

Right click the AccountInformation

node

Select to view the table’s contents

23



Figure 25.15 AccountInformation table’s content.

Rows (records)

Columns (fields)

Collectively, rows and columns form a table

24



• Data Command Objects– Data command objects retrieve and update data in the

database

– To create a data command object, drag-and-drop an ADO .NET OleDbCommand control from the Data tab in the Toolbox to the Form.

– Query Builder allows you to specify the commands that retrieve information from and modify information in databases

25



Figure 25.16 Properties of objSelectAccount.

Click to specify connection object

Select the existing connection (objOleDbConnection)

Connection property

26



Figure 25.17 Query Builder and Add Table dialogs.

Add Table dialog

Select this table

Click to add selected table

Close Button

27


25.3 Creating Database ConnectionsFigure 25.18 AccountInformation table added to the Query Builder dialog.

AccountInformation window appears once

AccountInformation is added in the Add

Table dialog

Primary key displayed in bold

Partial SQL query

28



• Data Command Objects (continued)– Words SELECT and FROM represent SQL code– Structured Query Language (SQL) often performs

database queries and manipulates data. – Criteria column specifies a specific row to be retrieved or

manipulated

– In the Criteria column, type =? • Adds WHERE to end of SQL code

– Specifies criteria that determine rows to retrieve

– Funnel icon denotes a value in the Criteria column

29



Figure 25.19 Selecting columns in the Query Builder dialog.

Check this CheckBox

Names selected in the AccountInformation

window appear here

SQL SELECT statement

30



Figure 25.20 WHERE SQL keyword added to the SQL statement.

Check this CheckBox

Uncheck this box

This value will be removed when

AccountNumber’s Output CheckBox

is unchecked

Funnel icon

Criteria column

Type this value

31



Figure 25.21 Complete SQL SELECT statement.

AccountNumber CheckBox is now

unchecked

Complete SELECT statement

32



Figure 25.22 Microsoft Development Environment dialog.

33



Figure 25.23 Parameters property of a data command object.

Parameters property

Click to view collection

34



AccountNumber parameter

Parameter name which you will use in C# code to complete the

SQL statement

Figure 25.24 OleDbParameter Collection Editor dialog.

35



Figure 25.25 OleDbCommands displayed in the component tray.

OleDbCommand object

36



Figure 25.26 Specifying an UPDATE command.

Right click the Column

Select this to create an UPDATE statement

37


25.3 Creating Database ConnectionsFigure 25.27 Specifying the BalanceAmount column in the UPDATE command.

Check this CheckBox

New Value column

Type in this value

This expression appears

38



Figure 25.28 UPDATE command displayed in the Query Builder dialog.

Complete UPDATE command with parameters

39


25.4 Programming the ATM Application

Figure 25.29 System.Data.OleDb used by ATM.cs.

Using namespace System.Data.OleDb

40


25.4 Programming the ATM ApplicationFigure 25.30 Rearranging and commenting the control declarations.

41



• Displaying existing account numbers in the ComboBox– OleDbDataReader is a data reader object, or an object

that reads data from a database

– Data readers cannot modify data

42


25.4 Programming the ATM ApplicationFigure 25.31 Opening the connection to the database and creating a data reader.

43



Figure 25.32 Filling the ComboBox with account numbers.

• Read method begins to read information from the database and returns false if there are no more rows to be read

44



Figure 25.33 Specifying the AccountNumber parameter value of the data command object and connecting to the database.

Opening the database connection

Setting the AccountNumber

parameter value of the command object

45



Figure 25.34 OleDbDataReader for reading a row’s data.

Creating the data reader

Use data reader to read from database

Setting instance variable values to values retrieved

from the database

Closing the reader

Closing the database connection

46



Figure 25.35 Connecting to the database to update a column.

Specifying objUpdateBalance‘s

BalanceAmount parameter value

Specifying objUpdateBalance’s

ORIGINAL_AccountNumber parameter value

47


25.4 Programming the ATM ApplicationFigure 25.36 Executing an UPDATE command before closing the database connection.

Opening the database connection

Executing the UPDATE command

Closing the database connection

• Update command executed by calling ExecuteNonQuery from the objUpdateBalance object.

Outline48


ATM.csPart (1 of 18)

1 using System;

2 using System.Drawing;

3 using System.Collections;

4 using System.ComponentModel;

5 using System.Windows.Forms;

6 using System.Data;

7 using System.Data.OleDb;

8

9 namespace ATM

10 {

11 /// <summary>

12 /// Summary description for FrmATM.

13 /// </summary>

14 public class FrmATM : System.Windows.Forms.Form

15 {

16 // Panel with Label and TextBox inside to display

17 // instructions and messages to the user

18 private System.Windows.Forms.Panel pnlDisplay;

19 private System.Windows.Forms.Label lblDisplay;

20 private System.Windows.Forms.TextBox txtInput;

21

22 // GroupBox containing Buttons

23 private System.Windows.Forms.GroupBox fraButtons;

24

Using System.Data.OleDb namespace

Outline49



25 // Buttons for entering PIN and withdrawal amount

26 private System.Windows.Forms.Button btnZero;

27 private System.Windows.Forms.Button btnOne;

28 private System.Windows.Forms.Button btnTwo;

29 private System.Windows.Forms.Button btnThree;

30 private System.Windows.Forms.Button btnFour;

31 private System.Windows.Forms.Button btnFive;

32 private System.Windows.Forms.Button btnSix;

33 private System.Windows.Forms.Button btnSeven;

34 private System.Windows.Forms.Button btnEight;

35 private System.Windows.Forms.Button btnNine;

36

37 // Buttons to take an action

38 private System.Windows.Forms.Button btnOK;

39 private System.Windows.Forms.Button btnBalance;

40 private System.Windows.Forms.Button btnWithdraw;

41 private System.Windows.Forms.Button btnDone;

42

43 // GroupBox with Label and ComboBox inside for choosing

44 // an account number

45 private System.Windows.Forms.GroupBox fraAccountNumber;

46 private System.Windows.Forms.Label lblAccountNumber;

47 private System.Windows.Forms.ComboBox cboAccountNumbers;

48

49 // OleDbConnection for the account information database

50 private System.Data.OleDb.OleDbConnection objOleDbConnection;

Outline50



51

52 // OleDbCommand to select account number from database

53 private System.Data.OleDb.OleDbCommand objSelectAccount;

54

55 // OleDbCommand to select account data from database based

56 // on account number

57 private System.Data.OleDb.OleDbCommand objSelectAccountData;

58

59 // OleDbCommand to update a balance based

60 // on the account number

61 private System.Data.OleDb.OleDbCommand objUpdateBalance;

62

63 /// <summary>

64 /// Required designer variable.

65 /// </summary>

66 private System.ComponentModel.Container components = null;

67

68 // variable to store user-entered PIN number

69 private string m_strUserPIN;

70

71 // variables to store account balance and user's first name

72 private decimal m_decBalance;

73 private string m_strFirstName;

74 private string m_strPIN;

75

Outline51



76 // variable to indicate action being performed

77 private string m_strAction = "Account";

78

79 public FrmATM()

80 {

81 //

82 // Required for Windows Form Designer support

83 //

84 InitializeComponent();

85

86 //

87 // TODO: Add any constructor code after InitializeComponent

88 // call

89 //

90 }

91

92 /// <summary>

93 /// Clean up any resources being used.

94 /// </summary>

95 protected override void Dispose( bool disposing )

96 {

97 if( disposing )

98 {

99 if (components != null)

100 {

Outline52



101 components.Dispose();

102 }

103 }

104 base.Dispose( disposing );

105 }

106

107 // Windows Form Designer generated code

108

109 /// <summary>

110 /// The main entry point for the application.

111 /// </summary>

112 [STAThread]

113 static void Main()

114 {

115 Application.Run( new FrmATM() );

116 }

117

118 // invoke when 0 Button is clicked

119 private void btnZero_Click(

120 object sender, System.EventArgs e )

121 {

122 InputNumber( "0" ); // invoke method with argument 0

123

124 } // end method btnZero_Click

125

Outline53




127 private void btnOne_Click(


129 {


131

132 } // end method btnOne_Click

133


135 private void btnTwo_Click(


137 {


139

140 } // end method btnTwo_Click

141


143 private void btnThree_Click(


145 {


147

148 } // end method btnThree_Click

149

Outline54




151 private void btnFour_Click(


153 {


155

156 } // end method btnFour_Click

157


159 private void btnFive_Click(


161 {


163

164 } // end method btnFive_Click

165


167 private void btnSix_Click(


169 {


171

172 } // end method btnSix_Click

173

Outline55




175 private void btnSeven_Click(


177 {


179

180 } // end method btnSeven_Click

181


183 private void btnEight_Click(


185 {


187

188 } // end method btnEight_Click

189


191 private void btnNine_Click(


193 {


195

196 } // end method btnNine_Click

197

Outline56



198 // determines what text will display in TextBox

199 private void InputNumber( string strNumber )

200 {

201 // if user is entering PIN number display * to

202 // conceal PIN entry; store entered PIN in variable

203 if ( m_strAction == "PIN" )

204 {

205 txtInput.Text += "*";

206 m_strUserPIN += strNumber;

207 }

208 else // otherwise display number

209 {

210 txtInput.Text += strNumber;

211 }

212

213 } // end method InputNumber

214

215 // invoke when OK Button is clicked

216 private void btnOK_Click(


218 {

219 // determine what action to perform

220 switch ( m_strAction )

221 {

222 // if user provided PIN number

Outline57



223 case "PIN":

224

225 RetrieveAccountInformation(); // invoke method

226

227 // determine if PIN number is within valid range

228 if ( m_strUserPIN == m_strPIN )

229 {

230 // enable Buttons and disable ComboBox

231 btnBalance.Enabled = true;

232 btnWithdraw.Enabled = true;

233 cboAccountNumbers.Enabled = false;

234

235 // display status to user

236 lblDisplay.Text = "Welcome " + m_strFirstName +

237 ", select a transaction.";

238

239 // change action to indicate that no user-action

240 // is expected

241 m_strAction = "NoAction";

242 }

243 else

244 {

Outline58



245 // indicate that incorrect PIN was provided

246 lblDisplay.Text =

247 "Sorry, PIN number is incorrect."

248 + "Please re-enter the PIN number.";

249

250 // clear user's previous PIN entry

251 m_strUserPIN = "";

252 }

253

254 txtInput.Clear(); // clear TextBox

255 break;

256

257 // if user provided withdrawal amount

258 case "Withdrawal":

259

260 // invoke Withdrawal method with decimal argument

261 Withdrawal( Convert.ToDecimal( txtInput.Text ) );

262 txtInput.Clear();

263 m_strAction = "NoAction";

264 break;

265

266 } // end switch

267

268 } // end method btnOK_Click

269

Outline59



270 // invoked when Withdraw Button is clicked

271 private void btnWithdraw_Click(


273 {

274 // display message to user

275 lblDisplay.Text =

276 "Enter the amount you would like to withdraw.";

277

278 // change action to indicate user will

279 // provide withdrawal amount

280 m_strAction = "Withdrawal";

281

282 } // end method btnWithdraw_Click

283

284 // determine new balance amount

285 private void Withdrawal( decimal decWithdrawAmount )

286 {

287 // determine if amount can be withdrawn

288 if ( decWithdrawAmount <= m_decBalance )

289 {

290 // determine new balance amount after withdrawal

291 m_decBalance -= decWithdrawAmount;

292

293 UpdateBalance(); // invoke method to update database

294

Outline60



295 // display balance information to user

296 lblDisplay.Text = "Your current balance is " +

297 String.Format( "{0:C}", m_decBalance );

298 }

299 else

300 {

301 // indicate amount cannot be withdrawn

302 lblDisplay.Text = "The withdrawal amount is too large."

303 + " Select Withdraw and enter a different amount.";

304 }

305

306 } // end method WithDrawal

307

308 // invoked when Balance Button is clicked

309 private void btnBalance_Click(


311 {

312 // display user's balance

313 lblDisplay.Text = "Your current balance is " +

314 String.Format( "{0:C}", m_decBalance );

315

316 } // end method btnBalance_Click

317

Outline61



318 // invoked when Done Button is clicked

319 private void btnDone_Click(


321 {

322 lblDisplay.Text = "Please select your account number.";

323

324 // change action to indicate that user will

325 // provide account number

326 m_strAction = "Account";

327 m_strUserPIN = "";

328


330 btnOK.Enabled = false; // disable OK Button

331 btnBalance.Enabled = false; // disable Balance Button

332 btnWithdraw.Enabled = false; // disable Withdraw Button

333 btnDone.Enabled = false; // disable Done Button

334 cboAccountNumbers.Enabled = true; // enable ComboBox

335 cboAccountNumbers.Text = ""; // clear selected account

336

337 } // end method btnDone_Click

338

339 // invoke when user inputs information in TextBox

340 private void txtInput_TextChanged(


342 {

Outline62



343 btnOK.Enabled = true; // enable OK Button

344

345 } // end method txtInput_TextChanged

346

347 // invoke when selection is made in ComboBox

348 private void cboAccountNumbers_SelectedIndexChanged(


350 {

351 // change action to indicate that user will

352 // provide account number

353 m_strAction = "PIN";

354

355 // prompt user to enter PIN number

356 lblDisplay.Text = "Please enter your PIN number.";

357 btnDone.Enabled = true; // enable Done Button;


359

360 } // end method cboAccountNumbers_SelectedIndexChanged

361

362 // load application Form

363 private void FrmATM_Load( object sender, System.EventArgs e )

364 {

365 objOleDbConnection.Open(); // open database connection

366

Opens database connection

Outline63



367 // create database reader to read information from database

368 OleDbDataReader objReader =

369 objSelectAccount.ExecuteReader();

370

371 // fill ComboBox with account numbers

372 while ( objReader.Read() )

373 {

374 cboAccountNumbers.Items.Add(

375 objReader[ "AccountNumber" ] );

376 }

377

378 objOleDbConnection.Close(); // close database connection

379

380 } // end method FrmATM_Load

381

382 // invoke when user provides account number

383 private void RetrieveAccountInformation()

384 {

385 // specify account number of row from which data

386 // will be retrieved

387 objSelectAccountData.Parameters[ "AccountNumber" ].Value =

388 cboAccountNumbers.SelectedItem;

389


391

Creates data reader

Fills ComboBox with account numbers

Closes database connection

Setting the AccoutNumber parameter of objSelectAccountData


Outline64



392 // create database reader to read information from database

393 OleDbDataReader objReader =

394 objSelectAccountData.ExecuteReader();

395

396 objReader.Read(); // open data reader connection

397

398 // retrieve PIN number, balance amount and first name

399 // information from database

400 m_strPIN = Convert.ToString(

401 objReader[ "PIN" ] );

402 m_decBalance = Convert.ToDecimal(

403 objReader[ "BalanceAmount" ] );

404 m_strFirstName = Convert.ToString(

405 objReader[ "FirstName" ] );

406

407 objReader.Close(); // close data reader connection


409

410 } // end method RetrieveAccountInformation

411

412 // update BalanceAmount in database

413 private void UpdateBalance()

414 {

415 // specify new BalanceAmount to update in database

416 objUpdateBalance.Parameters[ "BalanceAmount" ].Value =

417 m_decBalance;

Creates data reader

Reads with data reader

Sets instance variables to

read data from database

Closes data reader


Sets BalanceAmount

parameter of objUpdateBalance

Outline65



418

419 // specify row whose BalanceAmount will update

420 objUpdateBalance.Parameters[

421 "Original_AccountNumber" ].Value =

422 cboAccountNumbers.SelectedItem;

423


425

426 // execute update statement

427 objUpdateBalance.ExecuteNonQuery();

428


430

431 } // end method UpdateBalance

432

433 } // end class FrmATM

434 }

Sets parameter of objUpdateBalance


Executes UPDATE command


© copyright 1992-2004 by deitel & associates, inc. and pearson education inc. all rights...

Documents

atm application slide

atm application figure

atm application form

pearson education

deitel associates

withdrawal slide

balance button

correct pin slide