Project 4 - SQL tutorial

Version 1.0, last updated 08/07/2006

You'll need some SQL queries to extract information from the Blinky Database.  Below are sample SQL commands; try entering these into Gayle Laakmann's 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 Blogs table:
  select *
from Blogs
select just the UserID, BlogID columns from Blogs:
  select UserID, BlogID
from Blogs
select all the rows in Blogs where the UserID is 'Gayle'
  select *
from Blogs
where UserID = 'Gayle'
select all the rows in Blogs where the UserID is not 'Gayle'
  select *
from Blogs
where UserID <> 'Gayle'
select everything that Gayle posted on or after Jan 16, 2003
  select *
from Blogs
where UserID = 'Gayle'
and DatePosted > '2004-01-16'
select everything that Gayle posted after Jan 16, 2003 at 6:00pm (note that SQL can handle many different date syntaxes)
 

 

 select *
from Blogs
where DatePosted > 'Jan 16, 2004 6:00pm'
select the PageNumber and PageBody columns in the BlogPages table where the BlogID is 1483787270
  select *
from BlogPages
where BlogID = 1483787270
get a list of all the users that have posted a blog
  select distinct UserID
from Blogs

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