Transcript Using SQL inside C# Presentation

Databases – Unit 3 & 4

SQL is not restricted to a specific database,
however in class we will be looking at the two
major database programmes in use

Access – basic database, usable for academic
purposes and basic data purposes

SQL Server – industry recognised database,
more adaptable, more ‘in depth’

Made up of US English based ‘key words’

US? US spelling of words is used throughout
programming languages due to their origins:
 Colour is coded as color (feel free to twitch)

ADO.Net provides a ‘namespace’ for all
possible database connections


System.Data.OleDb
System.Data.SqlClient

Each namespace contains all the functions
you will need to manipulate the database and
data inside it
Access database
SQL Server database

ADO has different versions as it has
developed

In version 2.1 more objects were released,
ADOX is essentially: ADO eXtrensions

In ADOX all objects use Generalisation to
define objects (remember the essays?)

Dataset – stores all data held in ‘cache’

DataAdapter – represents a bridge between
the Dataset and Database holding the:
▪ Connection String
▪ SQL Commands

Connection – the address of your Datasource
(aka database)

Before you can create any functional SQL
your application must be able to connect to
your database.

All database connections are called a
‘connection string’

These can be set as global variables or ‘per
page’ connections may also be set
public void ConnectToAccess()
{
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
Call an instance
of ADO.Net
namespace
conn.ConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source=
C:\Documents and Settings\username\" + @"My
Documents\AccessFile.mdb";
try { conn.Open(); }
catch (Exception ex)
{ MessageBox.Show("Failed to connect to data source"); }
finally { conn.Close(); }
}

Why create a table?
 Temporary tables may be created, filled and
deleted
 Why? Memory Management – A large update is
quicker than many small updates
try
{
conn.Open();
string strTemp = " [FirstName] Text, [LastName] Text ";
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = conn;
myCommand.CommandText = "CREATE TABLE tbl_People("
+ strTemp + ")";
myCommand.ExecuteNonQuery();
}
try
{
conn.Open();
string strTemp = " [FirstName] = " + txtFname.text + ", [LastName] = " +
txtLname.Text;
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = conn;
myCommand.CommandText = “INSERT INTO tbl_People(" + strTemp +
")";
myCommand.ExecuteNonQuery();
}

The Datagrid is a massively useful tool
for showing multiple records of data

Using a simple statement records can
be shown as a read only table or an
editable form
private void Form1_Load(object sender, System.EventArgs e)
{
Try
{
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM
Student","Provider=Microsoft.JET.OLEDB.4.0;data source=C:\\mcb.mdb" );
DataSet ds = new DataSet();
da.Fill(ds, “Fname");
dataGrid1.DataSource = ds.Tables[“FName"].DefaultView ;
}
catch (Exception ex)
{ MessageBox.Show("Failed to connect to data source"); }
finally { conn.Close(); }
}

Create yourself a set of revision notes for the
SQL code used here. This should include:
 A Table of Keywords and their uses
 An explanation of ADO
 An explanation of connection strings
 An explanation of a dataset