Structured Query Language

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