Tutorial RSS
 
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.

Looking for the VB.NET version? Click here

Looking for more .NET Database Tutorials? Click Here!


Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.


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>

Try Server Intellect for Windows Server Hosting. Quality and Quantity!


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.";
}
}




Looking for the VB.NET version? Click here

Looking for more .NET Database Tutorials? Click Here!

Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!


Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!



100% SPAM FREE! We will never sell or rent your email address!

411asp.net123aspxDotNetFreaksServer Intellect