1 / 14

Structured Query Language

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

koren
Download Presentation

Structured Query Language

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Databases – Unit 3 & 4 Structured Query Language

  2. 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’

  3. 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)

  4. 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

  5. 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?)

  6. 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)

  7. 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

  8. 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

  9. 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

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

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

  12. 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

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

  14. 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

More Related