Database

1. Database Reading from an SQL Database

2. Lecture 6: Database Server Explorer • Visual Studio includes Server Explorer. • This allows you to attach a database and see the tables and the data • You can use it to create tables. • You can use it to change data. • You can use it to create a connection in C#

3. Lecture 6: Database mdf files • The file World.mdf contains an SQL database. • You can save the mdf on your computer and open it as a database. • You can use New Query to write SQL commands. • You will be able to connect to the database from your C# program.

4. Lecture 6: Database New Query

5. Lecture 6: Database The World Table

6. Lecture 6: Database SQL Queries • A simple SQL Query: SELECT name, region, area, population, gdp FROM World • This will give all the rows and all the columns.

7. Lecture 6: Database Filtering Data • Get only the countries where the region is South America: SELECT name, region, area, population, gdp FROM world WHERE (region = 'South America')

8. Lecture 6: Database Ordering Data • You can get the data in order of the size SELECT name, area, population, gdp FROM world WHERE (region = 'South America') ORDER BY area

9. Lecture 6: Database Limiting Rows • You can say TOP(3) to get the top three rows: SELECT TOP (3) name, area, population, gdp FROM world ORDER BY population DESC

10. Lecture 6: Database Connection String • To connect to the database from C# you need: String ConnectionString = @"Data Source=.."; SqlConnection con = new SqlConnection(cs); con.Open(); String sql = @"SELECT name,region,area,population,gdp FROM world"; SqlCommand cmd = new SqlCommand(sql, con); SqlDataReader sdr = cmd.ExecuteReader(); con.Close();

11. Lecture 6: Database Reading Values SqlCommand cmd = new SqlCommand(sql, con); SqlDataReader sdr = cmd.ExecuteReader(); Table t = new Table(); while (sdr.Read()) { TableRow tr = new TableRow(); TableCell td = new TableCell(); td.Text = sdr["name"].ToString(); tr.Cells.Add(td); t.Rows.Add(tr); } Controls.Add(t); con.Close();

12. Lecture 6: Database SqlDataSource

13. Lecture 6: Database Summary • An mdf database is a single file. • You see the database using Server Explorer • You write SQL to query the database • You can filter and sort and limit • There are two good ways to view the data: • SqldataSource • SqlDataReader