databases unit 3 4 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Structured Query Language PowerPoint Presentation
Download Presentation
Structured Query Language

Loading in 2 Seconds...

play fullscreen
1 / 14

Structured Query Language - PowerPoint PPT Presentation


  • 55 Views
  • Uploaded on

Databases – Unit 3 & 4. Structured Query Language. What Database?. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Structured Query Language' - koren


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
what database
What Database?
  • 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’
sql language
SQL Language
  • 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
ADO.NET
  • 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

but the code says adox
But the Code Says ADOX??!
  • 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?)
ado net keywords
ADO.NET - Keywords
  • 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)
c and your database
C# and Your 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
connection strings
Connection Strings

public void ConnectToAccess()

{

System.Data.OleDb.OleDbConnectionconn = new System.Data.OleDb.OleDbConnection();

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(); }

}

Call an instance of ADO.Net namespace

Create the ‘string’ address for your database

Try Catch Statement

create a new table
Create A New Table
  • Why create a table?
    • Temporary tables may be created, filled and deleted
    • Why? Memory Management – A large update is quicker than many small updates
create a new table1
Create A New Table

try

{

conn.Open();

string strTemp = " [FirstName] Text, [LastName] Text ";

OleDbCommandmyCommand = new OleDbCommand();

myCommand.Connection = conn;

myCommand.CommandText = "CREATE TABLE tbl_People(" + strTemp + ")";

myCommand.ExecuteNonQuery();}

insert a record into a table
Insert A Record Into a Table

try

{

conn.Open();

string strTemp = " [FirstName] = " + txtFname.text + ", [LastName] = " + txtLname.Text;

OleDbCommandmyCommand = new OleDbCommand();

myCommand.Connection = conn;

myCommand.CommandText = “INSERT INTO tbl_People(" + strTemp + ")";

myCommand.ExecuteNonQuery();}

view the datagrid
View – The Datagrid
  • 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
view all records in a table
View All Records In a Table

private void Form1_Load(object sender, System.EventArgs e){

Try

{OleDbDataAdapterda = new OleDbDataAdapter("SELECT * FROM Student","Provider=Microsoft.JET.OLEDB.4.0;data source=C:\\mcb.mdb" );

DataSetds = 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(); }

}

homework due in next friday
Homework – Due in Next Friday
  • 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