Programmatically Updating Databases using ASP.NET 2.0 SqlDataSource

(Page 1 of 4 )

In the previous article we talked about using the SqlDataSource control programmatically to obtain a result set from the database using a Data Reader object and a Data View object. We used an HtmlTable control to create a visual representation to hold the returned result set. Today we are going to use the SqlDataSource control to insert data into the Employees table of the Northwind database.

In case you haven't read the previous article I will show you the relevant code from it again, but I advise you to read the complete article when you can. Meanwhile, before we learn how to insert data, let's run a similar version of the previous article's code again. Next is the complete code for the code behind the  Default.aspx.cs file.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Collections;
using System.Data.Common;

  public partial class _Default : System.Web.UI.Page
 {
  SqlDataSource SqlDataSource1 = new SqlDataSource();

  protected void Page_Load(object sender, EventArgs e)
 {
   InitializeDataSource();
}

 protected void InitializeDataSource()
{
  this.Controls.Add(SqlDataSource1);
SqlDataSource1.ConnectionString = "Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True";

SqlDataSource1.SelectCommand = "SELECT EmployeeID, FirstName,
LastName, Title FROM Employees";

IEnumerable iteratorObject = SqlDataSource1.Select
(DataSourceSelectArguments.Empty);

  HtmlTable table = new HtmlTable();
  

   HtmlTableRow arow = new HtmlTableRow();

   HtmlTableCell cellth = new HtmlTableCell("th");

    cellth.InnerText = "EmployeeID";

    cellth.Width = "80";

     arow.Cells.Add(cellth);
 
    cellth = new HtmlTableCell("th");

    cellth.InnerText = "First Name";

    cellth.Width = "80";

     arow.Cells.Add(cellth);

    cellth = new HtmlTableCell("th");

    cellth.InnerText = "Last Name";

    cellth.Width = "80";

     arow.Cells.Add(cellth);

   cellth = new HtmlTableCell("th");

   cellth.InnerText = "Title";

   cellth.Width = "80";

    arow.Cells.Add(cellth);

table.Rows.Add(arow);

  if (SqlDataSource1.DataSourceMode ==
SqlDataSourceMode.DataReader)
{
   foreach (DbDataRecord record in iteratorObject)
{
  HtmlTableRow row = new HtmlTableRow();

  HtmlTableCell cell = new HtmlTableCell();

  cell.Width = "80";

cell.InnerHtml = "<b>" + record["EmployeeID"].ToString() +
"</b>";

  row.Cells.Add(cell);

  cell = new HtmlTableCell();

  cell.Width = "80";

cell.InnerHtml = "<b>" + record["FirstName"].ToString() + "</b>";

  row.Cells.Add(cell);

  cell = new HtmlTableCell();

  cell.Width = "80";

cell.InnerHtml = "<b>" + record["LastName"].ToString() + "</b>";

  row.Cells.Add(cell);

  cell = new HtmlTableCell();

  cell.Width = "80";

cell.InnerHtml = "<b>" + record["Title"].ToString() + "</b>";
row.Cells.Add(cell);

  table.Rows.Add(row);

 }
 

}

  else if (SqlDataSource1.DataSourceMode ==
SqlDataSourceMode.DataSet)

{
  

   foreach (DataRowView record in iteratorObject)

{

  HtmlTableRow row = new HtmlTableRow();

HtmlTableCell cell = new HtmlTableCell();

  cell.Width = "80";

cell.InnerHtml = "<b>" + record["EmployeeID"].ToString() +
"</b>";

  row.Cells.Add(cell);

  cell = new HtmlTableCell();

  cell.Width = "80";

cell.InnerHtml = "<b>" + record["FirstName"].ToString() + "</b>";

  row.Cells.Add(cell);

  cell = new HtmlTableCell();

  cell.Width = "80";

cell.InnerHtml = "<b>" + record["LastName"].ToString() + "</b>";
row.Cells.Add(cell);

  cell = new HtmlTableCell();

  cell.Width = "80";

cell.InnerHtml = "<b>" + record["Title"].ToString() + "</b>";
row.Cells.Add(cell);


  table.Rows.Add(row);

 }

}

  this.Controls.Add(table);

}

  protected void InsertRow()

{

 }

}

When you run the code you will get the result shown in the next screen shot.

We have made some minor modifications to the previous article's code. We've simply moved the code that initializes the SqlDataSource control to a method called InitializeDataSource() and called this method from the Page_Load() event handler method. We have also added column headers to the table, as well as the InsertRow() method which we will implement today.

If you don't understand how we got this result set back without writing any ADO.NET Data Access code, it's the SqlDataSource control that accomplished this for us. We passed the connection string and the T-SQL SELECT statement through the SqlDataSource.ConnectionString and the SqlDataSource.SelectCommand properties, and it opened the database for us and retrieved the data. The catch here is that we need to write code to handle this returned result set, which can be a DataView object or a Data Reader object; actually it's an object of type IDataReader, depending on the value of the SqlDataSource.DataSourceMode property.

Both the DataView and the IDataReader implement the IEnumerable interface, and this is what we are asking for. We use the IEnumerable interface on the returned object to iterate through its collection of rows, create HtmlTableRow objects for each row and add them to the table. Please consult my previous article for more information. Now let's continue to the next section where we are going to add the functionality of inserting rows into the table.

Next: Adding code for the Insert Functionality >>
 

More ASP.NET Articles
More By Michael Youssef

Comments On: Programmatically Updating Databases using ASP.NET 2.0 SqlDataSource

>>> Be the FIRST to comment on this article!