1 / 13

Database Server Management and Query Techniques

Learn how to manage and query SQL databases using Visual Studio tools. Explore features like Server Explorer, New Query, SQL Queries, and data filtering. Understand connection strings and interact with databases in C#.

ivo
Download Presentation

Database Server Management and Query Techniques

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

More Related