Project 4 - SQL tutorial

Version 1.0, last updated 07/05/2006

You'll need some SQL queries to extract information from the class-time database tables (Courses and MeetingTimes).  Below are sample SQL commands; try entering these into the Query Tester program to see if they work.

SQL COMMAND TEMPLATE:

The basic structure of a SQL command is:

SELECT ...
FROM ...
WHERE ... AND ... AND ...

This is best illustrated by some examples.  Here's a few:

select the entire Courses table:
  select *
from Courses
select just the CourseNumber, CourseTitle columns from Courses:
  select CourseNumber, CourseTitle
from Courses
select all the rows in Courses where the CourseNumber is '110'
  select *
from Courses
where CourseNumber = '110'
select all the rows in Courses where the CourseNumber is not '110'
  select *
from Courses
where CourseNumber <> '110'
select every CSE course with a CourseNumber less than 400
  select *
from Courses
where CourseNumber < '400'
and Department = 'cse'
select every section in the MeetingTimes table where the CourseID is 1912996974
  select *
from MeetingTimes
where CourseID = 1912996974
get a list of all the courses that have at least one entry in MeetingTimes
  select distinct CourseID
from MeetingTimes

SQL SELECT in C# using the SqlDataAdapter object:

This code will select all the comments for a blog with ID id and display the contents in a message box (BlinkySQL is my System.Data.SqlClient.SqlConnection).
  DataTable datatable = new DataTable();

string querystring = "select CommentDate, CommentUserID, CommentBody from BlogComments where OrgBlogID = " + id.ToString();
System.Data.SqlClient.SqlDataAdapter adaptSQL = new System.Data.SqlClient.SqlDataAdapter(querystring, BlinkySQL);
adaptSQL.Fill(datatable);

foreach(DataRow drow in datatable.Rows)
{

  DateTime date = (DateTime) drow["CommentDate"];
string commentuserid = (string) drow["CommentUserID"];
string body = (string) drow["CommentBody"];
MessageBox.Show(date.ToString() + " " + commentuserid.ToString() + " " + body, "Comment for Blog ID = " + m_id.ToString());

}

SQL SELECT in C# using the SqlDataReader object:

Here's a way to scan retrieved records one at a time and get information out of each record:

  string myQuery = "SELECT * FROM Blogs";
SqlCommand myCommand = new SqlCommand(myQuery,sqlConnection1);
sqlConnection1.Open();

SqlDataReader myReader; // forward-only cursor, "fire hose" read of database
myReader = myCommand.ExecuteReader();
if (myReader.HasRows)

{
  while (myReader.Read()) // always read before accessing data

{

  // do something with string myReader.GetSqlString(0).ToString().Trim()
   // do something with string myReader.GetSqlString(1).ToString().Trim()
  // do something with string myReader.GetSqlString(2).ToString().Trim()
  //          ... etc ...

}

}

myReader.Close(); // always close reader when done reading
sqlConnection1.Close(); // close connection

Visitors: Hit Counter