Navigator: Home - Advanced - Using Stored Procedures to Add to Database in C#

Using Stored Procedures to Add to Database in C#

This tutorial will show us how we can use Stored Procedures instead of explicit SQL Statements to add data to our database. C# version.

Download the Full Working Version of this Project written with Visual Studio.NET Here!

Looking for the VB.NET version? Click here

Looking for more .NET Database Tutorials? Click Here!

Using Stored Procedures instead of explicit SQL statements is very useful - one Stored Procedure can be referenced many times by many different pages, and changes only need to be made once, for example. Using Stored Procedures is barely more difficult to implement than using SQL statements in code.
First, we need the following assembly reference:

using System.Data.SqlClient;

In our Web.config, we declare the connection string:

<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

The ASPX page will look something like this:

<form id="form1" runat="server">
<asp:Label ID="Label1" runat="server"></asp:Label><br />
<table>
<tr>
<td style="width: 100px">
Name:</td>
<td style="width: 100px">
<asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
City:</td>
<td style="width: 100px">
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Add to DB" /></td>
</tr>
</table><br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1" Visible="False" Width="608px">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
SortExpression="id" />
<asp:BoundField DataField="theName" HeaderText="theName" SortExpression="theName" />
<asp:BoundField DataField="theCity" HeaderText="theCity" SortExpression="theCity" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Table1]"></asp:SqlDataSource>
</form>

The Stored Procedure will look something like this:

ALTER PROCEDURE dbo.spInsertData
@name varchar(50),
@city varchar(50)
AS
INSERT INTO Table1
(theName, theCity)
VALUES (@name, @city)
RETURN

The code-behind will look something 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.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
GridView1.Visible = true;
}

protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("spInsertData", new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()));
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@name", txtName.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);

cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

Label1.Text = "<b>" + txtName.Text + "</b> has been added to the database using a Stored Procedure.";
}
}


Download the Full Working Version of this Project written with Visual Studio.NET Here!

Looking for the VB.NET version? Click here

Looking for more .NET Database Tutorials? Click Here!
411asp.net123aspxDotNetFreaksServer Intellect