Transcript Chapter 14

COP2360 – C# Programming
Chapter 14 – Nov 25, 2015
Announcements

Three Weeks Left
–
Next Week


–
December 9



–
Part III of Assignment 5
Final Exam Review
Final Exam Programming Assignment
December 16


ASP.NET (Web) Development
Part II of Assignment 5
Final Exam
Please Sign on to Panther Web and complete the Student
Assessment for this course!! I might bring better candy to the
final if you all do!!
Tonight


Database Access
Assignment 5 – Part I
Flat File Access to Data


Fields are “hard” to define and have to be converted to
variables once the data is read
Relationships between data difficult to define
–
–

Multiple access to the same information almost impossible
–

For example, our Assignment 4 had Questions and Answers with
the Answers being related to specific questions. The best we
could do was to have the Answer follow the Questions.
We needed to show which answer was correct, so we used an
asterisk.
Once a File is opened by a program, it is locked from being
opened by other programs.
Every file needed it’s own set of definitions to be
understandable by the program.
What is a Database

A large, integrated collection of data.
–
–
Models a real-world enterprise.
Entities



–
a Noun
(e.g., students, courses)
Attributes – The fields within the Entity
Relationships



a Verb that relates two or more Nouns
Usually answers a question
(e.g., Which courses is Madonna taking this summer?)
What is a Database Management
System (DBMS)

A software package designed to store and
manage databases
Why Use a DBMS?






Data independence and efficient access.
Reduced application development time.
Data integrity and security.
Uniform data administration.
Concurrent access
Data Security/Recoverability
Relational Data Model



Main concept: relation, basically a table with
rows (sometimes called tuples) and columns.
Every relation has a schema, which
describes the columns or fields (or attributes)
within the table.
Both entities and relationships can be
described in a relation.
Major Relational Players

Oracle
–
Large scale corporation databases



Microsoft SQL Server
–
–
–

financials
inventory
Small to medium corporation databases
Integrated tightly with other Microsoft products
Similar product support as Oracle
MySQL (now owned by Oracle)
–
–
#1 Open Source database
used by many social media sites (Facebook, Twitter, etc.)
Data Definition Language (DDL)

Used to define the schema and manage the
database structure
–
–
–
Create Table
Alter Table
Create Index
Data Manipulation Language (DML)

Commands to add, update and delete
records in the database
–
–
–

INSERT
UPDATE
DELETE
Commands to query the database
–
SELECT
NoSQL Databases

To try and provide for access to massively large data
sets, several companies have developed what are
named “NoSQL” (for Not Only SQL) databases
–
–
Several different models using different data structures.
Basics include the splitting of data into a self defining
database across multiple servers.



–
XML of JSON (or other) self defining methods employed
As data grows, more small servers can be added instead of
having to increase capacity of one server
When multiple servers are used, “ACID” is going to severely
slow down the update and retrieval processes.
Each version has it’s own unique data definition and data
manipulation languages.
Microsoft Visual Studio
Database Objects

Microsoft SQL Server
–

Oracle
–

Applications using Oracle data sources
Object Linking and Embedding Database (OLE DB)
–

Applications using SQL Server 7.0 or later
Common layer that can access many different database
(including Oracle, SQLServer, Access, MySQL) through the
use of connection libraries for each type of database
Open Database Connectivity (ODBC)
–
An earlier layer for accessing many different database types
Microsoft Visual Studio
Database Objects

Visual Studio has separate libraries for each
of the data types
–
SQL and Oracle layers are more “direct” than
OLEDB – but – it locks in the database


For the stuff I did in the past, I used OLEDB because the
code I had accessed both Oracle and SQLServer
databases.
If you are for sure only going to support one database
platform, (and it’s either Oracle or SQLServer), it would
be better to use the one specific for that DBMS.
The FundFriend Database
Let’s Play With The Data

Some simple single table queries
–




Probably the best we can do with this is against Account
Let’s link the Account to Friends
Let’s link the Account to the Transactions
Let’s put them all together.
OK – Not expecting you to get this in five minutes!!
–
All SQL for our Assignment 5 will be done for you.
A Basic Select Clause

Let’s Use the Server Explorer in VS2012
SELECT field-names
FROM table-name(s)
WHERE condition clause
Accessing a Database through VS
Manual Set-Up Classes

Connection Class
–

Command Class
–

Used to define the command (Select, Insert, Update,
Delete) that needs to be executed.
DataReader Class
–

Defines the type and location of the database
For Select statements, returns one record at a time so that it
can be manipulated.
DataAdapter Class
–
For Select statements, holds all records returned by the
Select, and can be used to subsequently update the
database (although I really don’t like that feature!!)
Connecting to the Database
(Microsoft SQL Server)

Add using directives
–
–

using System.Data.Sql;
using System.Data.SqlClient;
Instantiate an object of connection class
–
Send connection string that includes the actual database
provider and the data source (name of the database)
string sConnection;
sConnection = @"Data
Source=(LocalDB)\v11.0;AttachDbFilename=" +
Application.StartupPath + @"\FUNDFRIEND.MDF;Integrated
Security=True";
SqlConnection dbConn = new SqlConnection(sConnection);
dbConn.Open();
19
Once Connected…


Set up a Command Class to pass the SQL
statements to the database management routines:
dbCmd = new SqlCommand();
dbCmd.CommandText = sSQL;
dbCmd.Connection = dbConn;
For Update type commands, one would then
Execute the command and it usually returns the
number of records changed.
int nRecordsChanged = dbCmd.ExecuteNonQuery()
Retrieving Data

If you are retrieving data, you have two choices:
–
Record by record using a data Reader:
dbReader = dbCmd.ExecuteReader();
while (dbReader.Read())
{
// Do stuff with the data from the reader
}
dbReader.Close();
Retreiving Data

Or, one can pull the entire result set from the query
into a Dataset using a DataAdapter Class
dbCmd.CommandText = sSQL;
dbCmd.Connection = dbConn;
dbTranAdapter = new SqlDataAdapter();
dbTranAdapter.SelectCommand = dbCmd;
dbTranSet = new DataSet();
dbTranAdapter.Fill(dbTranSet, "Transactions");
Let’s Access The Data Through a
Program Using the Manual Way
“Automated” Data Access in
Visual Studio Windows Forms

Data Aware Controls can be configured with
database connections and SQL and kinda
work by magic.
–

Not a fan because you lose a great deal of
control, but will walk through this anyway in case
you see this somewhere else.
Includes
–
GridView, Combo Box, Details View
“Magical” database access




Creating a “data source”
Binding that data source with a data aware
control
Linking that to the SQL statements for
retrieving and updating the data.
I will now fail miserably and giving an
example of this, but have one already done
just in case!!
A Word About LINQ

There is a big “disconnect” between application program and database
access
–
The “standard” way of accessing data makes use of libraries that bride the
gap between the program and the database


But basically you are passing SQL strings to the DBMS and returning datasets
that need to be interpreted
Several other systems have tried to “Marry” programs to DBMSs.
–
–
–
Java uses JSQL, which basically is a pre-compiler that allows a program to
code “SQL” statements are regular programming statements.
And old database access technique call PRO-C did a similar set-up for “C”
programs
I have always moved the database calls out of the main program and into
classes or web services so all of the crap you have to do is hidden from the
main routines.
A Word About Linq

With Visual Studio, Microsoft introduced their “Language
Integrated Query” which allows a program to “connect” to a
data source and then have the columnar information available
for direct manipulation by the program.
–

What we have now requires us to convert the data to C# variables,
and the convert it back when we update the database.
Goods and Bads
–
Integration is nice, but it has a cost




Some complex things you can do with SQL, you cannot do with LINQ
Whatever LINQ looks like, it ends up being SQL, so it may not be as
efficient as doing it by hand.
But it does make the coding of a program more direct and concise.
And it can be used for more than just databases.
Moving On



Our Assignment 5 will be to develop a web
based application that can run on a smart
phone that implements a “PayPal” or “Bill
Pay” System.
The system will be based on our FundFriend
database
Tonight, we will do a quick design of what we
need to incorporate into the system
FundFriend Web Application




This week you must complete the basic screen designs for the
system and turn them into Blackboard before next week (it
would be tonight, but I’m guessing as I write this that several
students won’t be here). That will be worth 3 points.
Next week we will go over the basics of Web Development
using VS and C#, and then mock up our design in ASP.NET
forms. These form designs would then be turned in and will be
worth 3 more points.
The last week of class, we will do our best to add the actual
code to the system to link the forms together and update the
database. Whatever we get done that evening before the
review will be worth 4 points.
Each week I will have a “starting” point so that we are all on the
same “page”.
Fund Friend
What do we need?