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).
Next: Using ControlParameter objects with SqlDataSource >>
More ASP.NET Articles
More By Michael Youssef
/ 7 
