Calling a Microsoft Sql Server Stored Procedure utilising ADO.NET in the code behind file.

Web design and development based in Royston, Hertfordshire, United Kingdom

Griffiths Web Design

Calling a Microsoft Sql Server Stored Procedure utilising ADO.NET in the code behind file.

07709 579517

Calling a Microsoft Sql Server Stored Procedure utilising ADO.NET in the code behind file.

By Michael Griffiths

Created: 15.08.2016 00:00:00, last updated: 15.08.2016 00:00:00

Calling a Microsoft Sql Server Stored Procedure utilising ADO.NET in the code behind file.

Calling a Microsoft Sql Server Stored Procedure utilising ADO.NET in the code behind file

What we will do

In a previous article we discussed creating stored procedures in MS SQL Server. So to build on that article I am going to show you how to call a stored procedure from the code behind file of your .aspx page.

In this article we will create a new website. This website won’t contain anything fancy just a form to insert data into the database and a page to display the data. In addition to the website we will also be creating a Microsoft SQL Server database. In the database we will be creating two stored procedures, one for inserting data and one for selecting data. Our database will only consist of one table.

The end result will be a very simple directory of websites, each with an accompanying title and description. Not very exciting I know but it does enable us to play around with some database development, ASP.NET webforms, ADO.NET and C# without too much complexity getting in the way.

Tools we will use

In this tutorial we will be using Microsoft Visual Studio. I am using the 2013 version but the later versions will also be fine. There is no particular reason for using the 2013 edition, in fact as far as I am aware any version from 2010 onwards will suffice for the task at hand. You could also use SQL Server Management Studio for developing the database, which I would heartily recommend as SSMS(SQL Server Management Studio) is an amazing tool that can be used to create an incredibly complex database application in the simplest way possible. We won’t be using SSMS in this example as the database we are creating only has one table. For more information on Sql Server Management Studio please visit Microsoft’s excellent documentation at

Create a new website

Before we can get to the fun stuff, calling stored procedures from code, we need to create a new website in visual studio. So go ahead and open up visual studio and when it has finished loading go to File > New > Website (or Shift + Alt + N)

You will see several items in the list provided by the visual studio dialog. On the left of this window you’ll see the templates tab, which is under the installed tab. Please ensure you have selected Visual C# from the two options (the other option is Visual Basic which is another language by Microsoft but for the purposes of this guide we will be using c#). In the main area of the new website window select ASP.NET Empty Website from the list of choices. Give your website a name and decide where it will live. I have named my website ‘UsingStoredProcedures’ but you can call yours whatever you want.

Next we need to add a few things to our website. We need an App_Data folder to hold our database file and we need two webforms.
So let’s start with the easy part, the webforms. We are just going to create them for now we won’t be adding any mark-up or code just yet. So on the top bar go to Website > Add new item (or Ctrl + Shift + A)
Select webform from the list and give it a name. I have left mine as Default.aspx. This page will be responsible for displaying our database records.

Now we need to repeat the previous operation to create another webform, this time I called mine AddRecord.aspx. This page will be used to add records to our database table.

For the App_Data folder we need to do something slightly different. So as before go to Website but this time instead of navigating to Add New Item we need to find the menu item labelled Add ASP.NET Folder. If you cannot see this item on the list make sure the website root is selected in the Solution Explorer Once Add ASP.NET Folder is highlighted you will be presented with a list of possible folders. Select App_Data from the list.

Build the database

Now that we have the App_Data folder we can go ahead and add a SQL Server Database to our website. To do this go to website > Add New Item and select SQL Server Database from the list. Give it a name, I have called mine usingSP.mdf, and click add.

You will be presented with a confirmation box which informs you that you’re trying to add a special type of file to your website, which in this case is a database file, and that files of this type normally live in an App_Data folder. It then asks if you want to place your database file in the App_Data folder. Click yes and visual studio will build the database and put it into the App_Data folder.
Now that we have a database we need to think about what data we want to store in it. For this example we will be adding a URL, a title and a description. Well that should be simple enough, wouldn’t you agree?

So first we need to create a database schema to which our table will belong. So in visual studio you will have a window called solution Explorer (if you cannot see it then go to View > Solution Explorer, or you can use the keyboard shortcut Ctrl + Alt + L).

Locate the App_Data folder and expand it to view its contents. There you will see the database file we previously created. Double click on that file and visual studio will open another window, this time it’s called Server Explorer.

In Server Explorer you will see usingSP.mdf, right click on it. You will now be presented with a drop down menu. Find and click on the entry labelled Browse in SQL Server Object Explorer. This, as I’m sure you’ve already guessed, will bring up another window called SQL Server Object Explorer.

Database Schema

Under your database you will need to find the folder/tab labelled Security. When you have expanded this tab you’ll be presented with several options. We need the tab labelled as Schemas. This time rather than left clicking to expand I want you to right click on it instead which will give you two options. Add New Schema and refresh. Obviously at this point we need the first option, Add New schema.

This action will bring up a new query window which will be pre-populated with the following:


Delete the word Schema from inside the square brackets and replace it with MyDirectory. It should look like the following:


Click update, at the top of the window. Or you can use the keyboard shortcut Shift + Alt + U. Visual studio will generate a preview of the update and asks if you wish to generate a T-Sql script. Or if you wish to update the database or to cancel the operation. Click on Update. You will see a message in the Data Tools Operations window at the bottom of the screen that informs you of success.

If you wish to double check the success of this operation you can head back over to the Sql Server Object Explorer window and right click on the Schemas tab/folder view and select Refresh from the very short list of two options. After you have performed this action you can expand the Schemas tab. You will see several other schemas in the list as well as the one we created. Do not mess with them they are part of the inner workings of a MS Sql Server database.

A table for the data.

Now we have a schema we can start to think about the table. What data types will we need? Will we need an identity column? Upon pondering these questions I decided our table would be as follows

The table name


The column name and datatypes

siteId, which will be an Int column.
This column will be the primary key for this table. The value will be generated by the auto increment identity property.
SiteName, varchar with a 250 character limit.
SiteDescrition, varchar with its character limit set to maximum.
siteUrl, nvarchar with a character limit of 2083. We have given it this limit because the varying different browsers limit URL’s to different lengths. The most restrictive of these is Microsoft’s very own Internet Explorer which limits URL’s to 2080 characters

To create this table find the Tables tab under your database in Sql Server Object Explorer. Once found right click on it and it will present you with a couple of options. Select Add New Table.

Now visual studio will launch its table designer, which if you have ever used SSMS before then you’ll notice that this interface is quite similar.

So go ahead and add your columns. You’ll notice that there are two windows that make up the table designer, one is the GUI which enables you to create tables without using any T-SQL and the other is a code window which displays the T-SQL that the GUI generates. You will also need to remember to replace its current Schema (dbo) with the Schema that we created together (MyDirectory). When you have added in all your columns and updated the schema the T-SQL being displayed in the code window should look similar to this:

CREATE TABLE [MyDirectory].[Details]
[siteName] VARCHAR(250) NOT NULL,
[siteDescription] VARCHAR(MAX) NOT NULL,
[siteUrl] NVARCHAR(2083) NOT NULL

Hit Update in the top left of the design window, which as with the creation of the Schema will display a preview window. Hit update in the preview window and visual studio will then create the table for you.

The stored procedures.

Now that we have a table we need to think about how we are going to add and retrieve data. For this example we will be using stored procedures. One for creation of data and one for retrieval of data. Our stored procedures will be simple, really simple.
First I will show you the stored procedure we will use to insert data. I will give a brief explanation after I have shown you the code but for a more in depth discussion on writing stored procedures please read ‘An introduction into stored procedures in MS SQL Server’ at the following web address:

CREATE PROCEDURE [MyDirectory].[addRecord]
@name varchar(250) = '',
@description varchar(max) = '',
@url nvarchar(2083) = ''
insert into MyDirectory.Details(siteName, siteDescription, siteUrl)
values(@name, @description, @url)

As you can see there’s nothing fancy or complex here just a simple insert. As you can see we have parameters that will be used as placeholders for the actual data.

And now we need a stored procedure to retrieve the data from the table. This stored procedure will be even more simplistic than our previous one. Don’t let the simplistic nature of these queries put you off. Too often out in the wilds of the internet I see massive over complication of code which just makes life a nightmare further down the road. So the moral of this little tale is this: simple = awesome. Anyway back to our stored procedure. We will write a simple select query to retrieve the rows from the table. Also we will be ordering by the identity column but in descending order so that the newest entries are displayed first.

CREATE PROCEDURE [MyDirectory].[getRecords]

select siteId, siteName, siteDescription, siteUrl
from MyDirectory.Details
order by siteId desc

Connecting to the database.

Now we have our stored procedure’s we need to think about how to connect our database to our website.

What we need is a connection string. A connection string tells our website which database we need to access and which server the database lives on. In your web.config file locate the node labelled as configuration. After the opening configuration tag add the following to connect to the database:

<add connectionstring="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\usingSP.mdf;Initial Catalog=usingSP;Integrated Security=True" providername="System.Data.SqlClient" name="usingSPConn">

For more information regarding connection strings please visit

Now we have added our connection string to our web.config file we need to open up the code behind files of both of the webforms that we created.
The reason we need to open the code behind files is because we need to add to each of them a using directive that will enable us to access the web configuration manager. At the top of your code behind files you will see a block of using statements. In that block, anywhere you like, add in the following:

using System.Web.Configuration;

Remember to do it for both webfoms. Once you have completed this action you will need to add a string to both webforms, defined as a private variable for the page class which retrieves the connection string by name. Place it above your Page_Load event.

private string connectionString =

Inserting data into the database using a stored procedure

The mark up.

Now that we have to ability to connect to our shiny new database it’s about time we added some data to our database. To do this we are going to add some form fields on the addRecord webform. Nothing fancy just three text boxes and a button.

It should look something like:

<asp:textbox id="txtTitle" runat="server" textmode="SingleLine" backcolor="WhiteSmoke"></asp:textbox><br>
<asp:textbox id="txtUrl" runat="server" textmode="Url" backcolor="WhiteSmoke"></asp:textbox><br>
<asp:textbox id="txtDesc" runat="server" textmode="MultiLine" rows="5" backcolor="WhiteSmoke"></asp:textbox><br>
<asp:button id="btnAdd" onclick="btnAdd_Click" runat="server" text="Add Record Now!">

As you can see we have declared btnAdd_Click in the OnClick event of the button. When you do this visual studio should create the event in the code behind. If it hasn’t then do not despair we can manually add it in. Inside the click event of the button we will call our stored procedure.

The code behind.

The click event of the button:

protected void btnAdd_Click(object sender, EventArgs e)
{ }

The first step toward the eventual goal of using our addRecord stored procedure is to define the ADO.NET connection object.

SqlConnection con = new SqlConnection(connectionString);

As you can see our connection object references our private string which in turn references the connection string stored in the web configuration file.

Next we move onto the Sql command object. When we define the command object we need to feed it two parameters, the first is the name of the stored procedure and the second is a reference to the connection object.

SqlCommand cmd = new SqlCommand("MyDirectory.addRecord", con);

Now we need to tell our code what type of command our command object is. We do that by adding this line:

cmd.CommandType = CommandType.StoredProcedure;

The next step is to set up our parameters. We need to locate the parameters defined in the stored procedure and then tell our code which .NET data type they are. For more information on SQL to .NET datatypes please visit

We also need to give our parameters values, which will be the contents of the text boxes on our form.

cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 250));
cmd.Parameters["@name"].Value = txtTitle.Text;
cmd.Parameters.Add(new SqlParameter("@url", SqlDbType.VarChar, 2080));
cmd.Parameters["@url"].Value = txtUrl.Text;
cmd.Parameters.Add(new SqlParameter("@description", SqlDbType.VarChar, -1));
cmd.Parameters["@description"].Value = txtDesc.Text;

We can now open the connection to the database and execute the stored procedure. We will wrap this in a try…catch…finally clause.

In the try block we will call our open the connection to the database, execute the stored procedure and redirect the user to the page responsible for displaying the data.

If any errors occur at any stage we will write the error to the console. This will be done in the catch block. We will be looking for all exceptions and, as stated only moments ago, writing the error message to the console. In a real world application you would have some sort of error logging process in place but writing to the console will suffice for this example.

The finally block will contain code to close the connection to the database, regardless of success or failure. The last thing you want is a server left hanging with an open connection. That’s just asking for trouble.

So here is the code:

int i = cmd.ExecuteNonQuery();
catch (Exception er)
Console.Write("Error adding record: " + er.Message);

You can now run the page in the browser. Try adding some information into the form fields then click the button. Hopefully you will be taken to the, currently blank, Default.aspx page.

To check the database to ensure that the data was indeed added to the table go over to the Sql Server Object Explorer in Visual Studio. Locate the table in the tables tab under the database, right click on it and select View Data. This will query the database for you and show you all the rows in the table. If you have correctly followed along up to this point you will have in your table, the data you just entered on the AddRecord webform. Awesome!

Displaying data from a stored procedure in the webpage.

The stored procedure we wrote for displaying the data does not have any parameters so this will be even easier to implement than the insert stored procedure. To display the data on the page we will be using a repeater.

Note – In a real world solution the repeater probably wouldn’t be a very sensible choice as it does not support pagination out of the box. A list view combined with a data pager control may fit the bill a little better. It is possible to add pagination capabilities to the repeater but with many other data controls that already have this capability built-in it hardly seems worth the effort.

The mark up.

The mark up for the repeater is very simple. All of our database columns are string values, one header and two paragraph tags. One of those paragraph tags will contain a link as well. The most noteworthy part of the mark-up is the databinding statements. E.g. <%# Eval("ColumnName") %>

Here’s the mark-up:

<asp:repeater id="rptDirectory" runat="server">
<div style="width:33%;height:auto;float:left;text-align:left;padding:5px;">
<h2><%# Eval("siteName") %></h2>
<p><strong>Address:</strong><br><a href='<%#Eval("siteUrl") %>' target="_blank"><%#Eval("siteUrl") %></a></p>
<p><strong>Description:</strong><br><%# Eval("siteDescription") %></p>

The Final step in our journey will be to create a function that calls our stored procedure, then binds the returned data to the repeater. The process will be pretty similar to the code used to add data to our database.

public void getdirectory()
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("MyDirectory.getRecords", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds, "Details");
rptDirectory.DataSource = ds;

catch (Exception er)
Console.Write("Error: " + er.Message);


To call this function we need to add a little bit of code to our Page_Load event. Like so:

if (!this.IsPostBack)


Run your website in the browser. Start with the ‘AddRecord’ page, add a new record and see it being displayed on the Default.aspx page. If you have followed along correctly then everything should run smoothly. If you have any problems at all please feel free to leave a comment or two.

If you could share this article around as much as possible it would be very much appreciated. Our mission is to help our fellow developers improve their craft, which ensures that the world wide web is a better, happier place to be. So all social media shares etc. really helps us with our mission.


About Michael Griffiths

Web Developer and Social Media Coach


Comment posted by: Terryponee

10.07.2020 02:38:42

Adult dating at 35 years old: Beautiful girls for sex in your city AU: Adult dating american ladies online: The best women for sex in your town: Adult dating sites in east london eastern cape: I’ve caught up with her ex husband four months back over coffee as he just moved nearby and is pretty much a neighbour now. Nothing of a sexual nature or even remotely romantic. I told my best friend I caught up with her ex husband over coffee and she acted ok on the phone only to ring him and make outlandish accusations that I was out to get preganant and that he be better be careful with me. What the hell? The reason divorce lawyers counsel against dating while the divorce is pending, even if separated, is that it has the potential to increase both the cost and the stress of the divorce trial. You are not supposed to date if you are married. Judges, however, rarely punish someone who begins dating – sexually or otherwise – once they have physically separated from their spouse. Status Duration Length Dating 15th Oct 2016 - 15th Oct 2017 1 year Engaged 15th Oct 2017 - 1st May 2019 1 year, 6 months Married 1st May 2019 - present 1 year, 1 month Total Oct 2016 - present 3 years, 8 months. Hawaii 's best 100% FREE gay dating site . Want to meet single gay men in Hawaii , Hawaii ? Mingle2 's gay Hawaii personals are the free and easy way to find other Hawaii . Tags such as “desperate” are attached to people who use online dating sites. But with the growing popularity of searching for a mate online, those tags seem to be slowly disappearing.

Comment posted by: Haroldquist

10.07.2020 00:05:19

Adult american dating free online usa: Sex dating online with photo. Is free: Sex dating in Canada | Girls for sex in Canada: Adult number 1 dating app for iphone: Adult american dating websites online: Dating sites and free dating sites do not focus on helping singles find love in Pueblo, CO like we do. Here you can find single men and women who are looking for love! We make it easy to search for singles in Pueblo, CO or anywhere. The biggest mainstream dating site . Largest user base. Offers the most matches and best search tools. Daily suggested matches. Connect faster with instant messenger. Send winks. 3-Day Free Trial. If you are dating in 2019, I bet you are doing it online. Gone are the days when people used to hook up in the local bar. The Internet has taken adult dating to the next level. So, if you date for fun, then, adult hookup sites like Spdate will suit your needs. Here, you will get hooked up to hot singles from the different part of the world. This is the avenue where thousands of adult singles and married people get connected and become friends with benefit. What is a SpeedLA Dating ‘Date Mate’? A ‘Date-Mate’ means you have a match! You listed each other on the Top 5 portion of your scorecard and the email address you registered with will be exchanged with this person 24 hours after the event. Only daters that garner matches are notified approx. 24 hours after the event. Should you wish to be notified if you do not garner matches or have any inquiries after the event - feel free to email us at We’re here 24/7. Teacher who quit to move in with student girlfriend charged with sexual assault.

Comment posted by: wacereats

09.07.2020 22:50:50

get a free credit report - free credit karma official site credit score scale how to improve my credit score

Comment posted by: Illona

09.07.2020 10:58:14

<a href=>секрет счастливой семейной жизни</a>

Comment posted by: Raymondcab

09.07.2020 04:22:38

<a href=>Фотосъемка и видеосъемка в СПб</a> Студия фото и видеосъемки -

Comment posted by: ShaneBic

08.07.2020 11:14:03

Adult american dating free online: Beautiful girls for sex in your city UK: Adult online dating whatsapp numbers: Adult number 1 dating app for iphone: Dating site for sex with girls in Canada: Why is it that a joke seems hilarious to one person but dating to another? Ask yourself the simple question before making this literally life-altering decision? Am I ready right this moment to marry this person? Am I prepared right at this very moment to potentially end a life? Then ask the only question worth asking: Why do it? Not teaching our children about God’s view of sexuality and morality is the greatest mistake parents can ever make it will stunt their image of God, of themselves, of their eventual partners, and this cycle may very well continue with their own children. 1. Dating us doesn't make you gay. Unless you're a guy, of course! But ladies, if you're attracted to men, fancying a trans guy doesn't suddenly change your sexuality. It means you're seeing them for the man that they are. If you suspect that your teen has low self-esteem or is depressed, talk to your child's doctor. Your child's pediatrician can screen for potential problems and also provide information about safe sex and birth control options. Dating Game Questions : Back in those times, dating used to be one serious affair. But in these days it is like the child’s play for the adults. You can even find like hundreds of apps through which you can date anyone you like. Not even these apps but social media is also a fun way to date someone and know them better. Still, in this process we really need some uncanny dating game questions to know the guy or the girl better.

Comment posted by: Jasonrurse

08.07.2020 09:04:49

Adult number 1 dating app for android: Free Sex Sex Dating: Dating site for sex with girls in France: Dating for sex with experienced girls from 20 years: The best girls for sex in your town AU: F*ckbook India is part of the Infinite Connections dating network, which includes many other general dating sites. As a member of F*ckbook India, your profile will automatically be shown on related adult dating sites or to related users in the Infinite Connections network at no additional charge. For more information on how this works, click F*ckbook India is part of the Infinite Connections dating network. To help you find more potential matches and members near you, your profile will be also be displayed on other adult dating sites that are part of the Infinite Connections dating network at no additional charge. Age: 48 Sex: Female Sexual Preference: Bisexual Seeking: A Relationship Listed in: Indiana Personals. Of als je vindt dat het allemaal wat te “elite” lijkt, probeer dan een andere geweldige en kwaliteitsvolle dating app: – als je zoekt naar een vaste relatie. Mingle2 - Free Dating Site In The Uk. Here is my quick list of the top five dating sites in Spain. I’ll go into more detail in my reviews later in this article.

Comment posted by: Роwеrful high аuthority redirеct dofollow sеo linк building:

06.07.2020 07:31:20

Redirect dofollow backlinks are one of the most powerful SEO backlinks for ranking websites at top positions in any search engine. Rank any high competition keyword or new website with Powerful Redirect Dofollow Backlinks! Redirect dofollow links is the most powerful technique for increasing site authority and traffic in 2020. Using this technique i will create dofollow seo backlinks. MORE DETAILS ABOUT MY SERVICE My Service: Redirect powerful dofollow backlinks, more 1000 links: 100+ powerful dofollow redirect links with,, google,, DA 52-89 PA 32-43 2 .edu powerful dofollow redirect links with high DA PA 2 .gov powerful dofollow redirect links with high DA PA 900+ others powerful dofollow redirect links with high DA PA TO LEARN MORE Fast ranking results (3-5 weeks) Building high Authority in Search Engines 100% SEO friendly On time delivery Penguin, Panda safe NOTE: I provide backlinks for all kind of websites, blogs, ecommerce stores, YouTube, Pinterest and others social networks MORE DETAILS ABOUT MY SERVICE

Comment posted by: Рowerful rеdirесt dofоllow seо baскlinкs:

06.07.2020 05:20:40

Redirect dofollow backlinks are one of the most powerful SEO backlinks for ranking websites at top positions in any search engine. Rank any high competition keyword or new website with Powerful Redirect Dofollow Backlinks! Redirect dofollow links is the most powerful technique for increasing site authority and traffic in 2020. Using this technique i will create dofollow seo backlinks. MORE DETAILS ABOUT MY SERVICE My Service: Redirect powerful dofollow backlinks, more 1000 links: 100+ powerful dofollow redirect links with,, google,, DA 52-89 PA 32-43 2 .edu powerful dofollow redirect links with high DA PA 2 .gov powerful dofollow redirect links with high DA PA 900+ others powerful dofollow redirect links with high DA PA TO LEARN MORE Fast ranking results (3-5 weeks) Building high Authority in Search Engines 100% SEO friendly On time delivery Penguin, Panda safe NOTE: I provide backlinks for all kind of websites, blogs, ecommerce stores, YouTube, Pinterest and others social networks MORE DETAILS ABOUT MY SERVICE