ASP.NET
  Home arrow ASP.NET arrow Page 2 - Programmatically Updating Databases using ...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ASP.NET

Programmatically Updating Databases using ASP.NET 2.0 SqlDataSource
By: Michael Youssef
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 9
    2007-11-05

    Table of Contents:
  • Programmatically Updating Databases using ASP.NET 2.0 SqlDataSource
  • Adding code for the Insert Functionality
  • Using ControlParameter objects with SqlDataSource
  • Completing the Code Example

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Programmatically Updating Databases using ASP.NET 2.0 SqlDataSource - Adding code for the Insert Functionality


    (Page 2 of 4 )

    We need to provide the user with text box controls so that they can enter values for a new record, and then click on a button to insert these values into the Employees table. The T-SQL INSERT statement that we need looks like this:

    INSERT INTO Employees(FirstName, LastName, Title)
    VALUES(@FirstName, @LastName, @Title)

    Where are @FirstName, @LastName and @Title are parameters? If you have read my ADO.NET articles you must have seen the examples that create SqlParameter objects that are needed in order to execute a parameterized T-SQL statement on the server. With the SqlDataSource control, we don't create SqlParameter objects; we create parameters of type System.Web.UI.Controls.Parameter or from a specific parameter class that inherits from the System.Web.UI.Controls.Parameter class. What is this supposed to mean? First let's see the code and try it, and then we will talk about this parameter issue.

    The markup code for the page looks as follows:

    <%@ Page Language="C#" AutoEventWireup="true"
    CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml" >

    <head runat="server">

      <title>Untitled Page</title>

    </head>

      <body>

       <form id="form1" runat="server">

       <div>

       <table style="width: 278px">

     <tr>

      <td style="width: 71px">

    <asp:Label ID="Label1" runat="server" Text="First
    Name"></asp:Label></td>

      <td>

    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox></td>

     </tr>

     <tr>

      <td style="width: 71px">

    <asp:Label ID="Label2" runat="server" Text="Last
    Name"></asp:Label></td>

      <td>

    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox></td>

     </tr>

     <tr>

      <td style="width: 71px">

    <asp:Label ID="Label3" runat="server"
    Text="Title"></asp:Label></td>

      <td>

    <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox></td>

     </tr>

    </table>

    <br />

    <asp:Button ID="Button1" runat="server" Text="Insert"
    OnClick="Button1_Click" /></div>

    </form>

    </body>

    </html>
     

    And the C# code behind class looks like this:

    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_Init(object sender, EventArgs e)

    {

      this.Controls.Add(SqlDataSource1);

    SqlDataSource1.ConnectionString = "Data Source=(local);Initial
    Catalog=Northwind;Integrated Security=True";

    }

      protected void Page_Load(object sender, EventArgs e)

    {

       try

    {

       if (!IsPostBack)

    {

      InitializeDataSource();

    }

       else

    {

      int rowsAffected = InsertRow();
     

       if (rowsAffected == 1)

    {

    Response.Write("A New Row has been inserted into the Employees
    Table");

    }

     InitializeDataSource();

    }

    }

      catch

    {

      Response.Write("An Error has occured");

    }

    }

       protected void InitializeDataSource()

    {

    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 int InsertRow()

    {

    SqlDataSource1.InsertCommand = "INSERT INTO Employees(FirstName,
    LastName, Title)" +
    "VALUES(@FirstName, @LastName, @Title)";

      ControlParameter parameter = new ControlParameter();

        parameter.Name = "FirstName";

        parameter.ControlID = "TextBox1";

        parameter.PropertyName = "Text";

        parameter.Type = TypeCode.String;

     SqlDataSource1.InsertParameters.Add(parameter);

        parameter = new ControlParameter();

        parameter.Name = "LastName";

        parameter.ControlID = "TextBox2";

        parameter.PropertyName = "Text";

        parameter.Type = TypeCode.String;

     SqlDataSource1.InsertParameters.Add(parameter);

        parameter = new ControlParameter();

        parameter.Name = "Title";

        parameter.ControlID = "TextBox3";

        parameter.PropertyName = "Text";

        parameter.Type = TypeCode.String;

     SqlDataSource1.InsertParameters.Add(parameter);

      return SqlDataSource1.Insert();

    }

      protected void Button1_Click(object sender, EventArgs e)

    {

    }

    }
     

    Run the page and insert values in the text boxes as shown in the following screen shot.

    Click on insert to insert a record into the database. The page will return the new record as part of the table control as shown in the next screen shot.

    Without writing any ADO.NET Data Access code we managed to insert a record into the database table. Before we explain the code that we have written above I have to tell you that you are not going to write such code when you use Data-Bound Controls. If you are using a Data-Bound Control, like the GridView control, the control itself will pass the new values to the SqlDataSource control along with the parameters (in most cases as we are going to discuss in upcoming articles about Data-Bound Controls).

    More ASP.NET Articles
    More By Michael Youssef


       · Michael has offered thorough and easy-to-understand information on a very useful...
     

    ASP.NET ARTICLES

    - Develop Your First ASP.NET Website with Visu...
    - Run ASP.NET in Windows XP Home with Cassini ...
    - How to Test a Web Application
    - How to Add Code and Validation Controls to a...
    - Working in Source and Split Views to Build a...
    - How to Build a Web Form for a One-Page Web A...
    - How to Develop a One-Page Web Application
    - An ASP.NET Web Application in Action
    - Developing ASP.NET Web Applications
    - An Introduction to ASP.NET Web Programming
    - Introduction to the ADO.NET Entity Framework...
    - Completing an In-Text Advertising System und...
    - Programming an In-Text Advertising System un...
    - Building an In-Text Advertising System Under...
    - Developing a Mini ASP.NET AJAX Server Centri...





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 6 Hosted by Hostway
    Stay green...Green IT