Wednesday, October 6, 2004

Green Notebook: Getting data from DB in ASP.NET

using System.Data.SqlClient;

string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

System.Text.StringBuilder htmlStr = new System.Text.StringBuilder("");
string strSQL = @"SELECT * FROM Customers
      WHERE Last_name = @lastname";
    
using (SqlConnection conn = new SqlConnection(connectionString))
{
 using (SqlCommand cmd = new SqlCommand(strSQL, conn))
 {
  conn.Open();
 
  SqlParameter param = new SqlParameter();
  param.ParameterName = "@lastname";
  param.Value = Request.QueryString["lastname"];
  cmd.Parameters.Add(param);
   
  SqlDataReader reader = cmd.ExecuteReader();
  htmlStr.Append("<table><tr>");
  for(int field = 0; field < reader.FieldCount; field++)
  {
   htmlStr.Append("<th>");
   htmlStr.Append(reader.GetName(field).ToString());
   htmlStr.Append("</th>");
  }
  htmlStr.Append("</tr>");
  while(reader.Read())
  {
   htmlStr.Append("<tr>");
   for(int field = 0; field < reader.FieldCount; field++)
   {
    htmlStr.Append("<td>");
    htmlStr.Append(reader.GetValue(field).ToString());
    htmlStr.Append("</td>");
   }
   htmlStr.Append("</tr>");
  }
  htmlStr.Append("</table>");
 }
}