Implement Business Logic with the SqlDataSource Control
In the past I've opted to use the ObjectDataSource control instead of SqlDataSource control because I had the mindset that separating the business logic from the presentation layer was a must and in many cases it is. But as I've come to develop some more simple web applications which would be hosted on a shared hosting account I've come around a little on that. Many of those sites really didn't have a whole lot of extra logic surrounding each connection to the database. I found by keeping these smaller web sites in a 2-tier architecture I saved myself a lot of time. I like the "keep it simple" method.
With this decision I've come to really like using the SqlDataSource control. Most of the time all the configuration can be done in the markup. But what about those few special cases where you do need to implement some extra logic?
Creating the Database Table
Let's take for example a situation where you've created a web form for a user to add new records to a table in your database. Let's call the table Persons. Naturally, you can gather what this table will be used for. Let's add a few fields to such as an identity plus FirstName, LastName and SocialSecurityNumber. Below is a script to perform this.
CREATE TABLE dbo.Persons
(
PersonID int identity(1,1) constraint PK_Persons primary key clustered
, FirstName nvarchar(50) not null
, LastName nvarchar(50) not null
, SocialSecurityNumber varchar(11) not null constraint UK_Persons1 unique nonclustered
)
Notice I added a unique constraint to the social security field. That will allow us another way to uniquely identify each person.
Setting up the Web Form
Our web form will expose three text boxes for the corresponding columns in the table. Below is an example of what the markup would look like for the inputs. Naturally, you would want to add in validation controls but because I'm not going to be covering that topic here I will leave them out. For now we will assume that any input will be valid.
<table border="0" cellpadding="2" cellspacing="0">
<tr>
<td>
First Name:
</td>
<td>
<asp:TextBox ID="FirstNameValue" runat="server" />
</td>
</tr>
<tr>
<td>
Last Name:
</td>
<td>
<asp:TextBox ID="LastNameValue" runat="server" />
</td>
</tr>
<tr>
<td>
Social Security Number:
</td>
<td>
<asp:TextBox ID="SocialSecurityNumberValue" runat="server" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="SubmitButton" runat="server" OnClick="SubmitButton_Click" Text="Submit" />
</td>
</tr>
</table>
You'll notice that I've added in the markup for the button to call a method in the code when it's clicked. Below is what the code looks like.
protected void SubmitButton_Click(object sender, EventArgs e)
{
PersonDataSource.Insert();
}
Configuring the SqlDataSource control
The form is setup as well as the database. Let's move on to the meat of this topic. The SqlDataSource will contain all the logic to persist the values the user input into the database. Below is the markup I created.
<asp:SqlDataSource ID="PersonDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:MyDatabase %>" InsertCommand="INSERT INTO dbo.Persons (FirstName, LastName, SocialSecurityNumber) VALUES (@FirstName, @LastName, @SocialSecurityNumber)" InsertCommandType="Text">
<InsertParameters>
<asp:ControlParameter ControlID="FirstNameValue" Name="FirstName" Size="50" Type="String" />
<asp:ControlParameter ControlID="LastNameValue" Name="LastName" Size="50" Type="String" />
<asp:ControlParameter ControlID="SocialSecurityNumberValue" Name="SocialSecurityNumber" Size="11" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
With what I've shown you so far this form will allow users to add records to the Persons table. The only thing we've written in the code-behind is an event handler for the button to call the Insert method. But what happens if the user tries to add a new person whose social security number already exists in the table? With this setup an exception would be thrown. One way of dealing with this is in the Page_Error event. However, I don't consider that a best practice.
How to check for the existence of a record
Instead of just catching an exception we will implement some logic to check for the existence of the record. If it does exist then cancel the insert. So first we need to setup another data source for this. Very simple really. All it will do is count how many records have the specified social security number. If the number is greater than zero then we need to cancel.
<asp:SqlDataSource ID="ExistingRecordCheckDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:MyDatabase %>" SelectCommand="SELECT COUNT(*) FROM dbo.Persons WHERE SocialSecurityNumber = @SocialSecurityNumber" SelectCommandType="Text">
<SelectParameters>
<asp:ControlParameter ControlID="SocialSecurityNumberValue" Name="SocialSecurityNumber" Size="11" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Now we could setup the code to check the result of this new datasource in the click event handler of the button but I really feel the best place to do this is the Inserting event of our first data source. We'll do this by first creating a method to handle the "Inserting" event. The markup for the data source will now look like this.
<asp:SqlDataSource ID="PersonDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:MyDatabase %>" InsertCommand="INSERT INTO dbo.Persons (FirstName, LastName, SocialSecurityNumber) VALUES (@FirstName, @LastName, @SocialSecurityNumber)" InsertCommandType="Text" OnInserting="PersonDataSource_Inserting">
<InsertParameters>
<asp:ControlParameter ControlID="FirstNameValue" Name="FirstName" Size="50" Type="String" />
<asp:ControlParameter ControlID="LastNameValue" Name="LastName" Size="50" Type="String" />
<asp:ControlParameter ControlID="SocialSecurityNumberValue" Name="SocialSecurityNumber" Size="11" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
The code-behind will have a new method added as such.
protected void PersonDataSource_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
IEnumerable enumerable = ExistingRecordCheckDataSource.Select(new DataSourceSelectArguments());
IEnumerator enumerator = enumerable.GetEnumerator();
enumerator.MoveNext();
DataRowView drv = enumerator.Current As DataRowView;
int count = (int)drv.Row[0];
if (count > 0)
{
e.Cancel = true;
}
}
Summary
Let's recap. We create a table and a form with a datasource to insert rows into the table. The datasource now has code which will check for the existence of a similar record anytime the Insert method is called. Therefore, the only line of code that is needed in the button's click event is a call to the Insert method.
In Addition
Per the request of NET Critic I am going to write how you would add the data sources programmatically. First we declare the data sources as module-level variables so that all methods in the class may access them.
SqlDataSource PersonDataSource;
SqlDataSource ExistingRecordCheckDataSource;
Now that the variables are declared we need to configure each data source. I prefer to do this in the Init event of the page. Below is my implementation of such code.
protected void Page_Init(object sender, EventArgs e)
{
PersonDataSource = new SqlDataSource();
PersonDataSource.ID = "PersonDataSource";
PersonDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
PersonDataSource.InsertCommand = "INSERT INTO dbo.Persons (FirstName, LastName, SocialSecurityNumber) VALUES (@FirstName, @LastName, @SocialSecurityNumber)";
PersonDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
PersonDataSource.Inserting += new SqlDataSourceCommandEventHandler(PersonDataSource_Inserting);
ControlParameter firstNameParameter = new ControlParameter("FirstName", "FirstNameValue");
firstNameParameter.Size = 50;
firstNameParameter.Type = TypeCode.String;
PersonDataSource.InsertParameters.Add(firstNameParameter);
ControlParameter lastNameParameter = new ControlParameter("LastName", "LastNameValue");
lastNameParameter.Size = 50;
lastNameParameter.Type = TypeCode.String;
PersonDataSource.InsertParameters.Add(lastNameParameter);
ControlParameter socialSecurityNumberParameter = new ControlParameter("SocialSecurityNumber", "SocialSecurityNumberValue");
socialSecurityNumberParameter.Size = 11;
socialSecurityNumberParameter.Type = TypeCode.String;
PersonDataSource.InsertParameters.Add(socialSecurityNumberParameter);
Form.Controls.Add(PersonDataSource);
ExistingRecordCheckDataSource = new SqlDataSource();
ExistingRecordCheckDataSource.ID = "ExistingRecordCheckDataSource";
ExistingRecordCheckDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
ExistingRecordCheckDataSource.SelectCommand = "SELECT COUNT(*) FROM dbo.Persons WHERE SocialSecurityNumber = @SocialSecurityNumber";
ExistingRecordCheckDataSource.SelectCommandType = SqlDataSourceCommandType.Text;
ControlParameter socialSecurityNumberParameter2 = new ControlParameter("SocialSecurityNumber", "SocialSecurityNumberValue");
socialSecurityNumberParameter2.Size = 11;
socialSecurityNumberParameter2.Type = TypeCode.String;
ExistingRecordCheckDataSource.SelectParameters.Add( socialSecurityNumberParameter2);
Form.Controls.Add(ExistingRecordCheckDataSource);
}