Griffiths web design based in Royston, Hertfordshire, United Kingdom

 

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

Author:   Michael Griffiths

Date created:   15 August 2016

[postpic=asp-net.png class=img-responsive center-block title=Calling a Microsoft Sql Server Stored Procedure utilising ADO.NET in the ASP.net code behind file alt=Calling a Microsoft Sql Server Stored Procedure utilising ADO.NET in the ASP.net code behind file style=STYLE][/postpic] [hdmd]What we will do[/hdmd] [l]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 asp.net 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.[/l] [hdmd]Tools we will use[/hdmd] [l]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 [url=https://msdn.microsoft.com/en-us/library/ms174173.aspx] https://msdn.microsoft.com/en-us/library/ms174173.aspx[/url] [/l] [hdmd]Create a new website[/hdmd] [l]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.[/l] [hdmd]Build the database[/hdmd] [l]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.[/l] [hdsm]Database Schema[/hdsm] [l]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:[/l] [code] CREATE SCHEMA [Schema] [/code] [l]Delete the word Schema from inside the square brackets and replace it with MyDirectory. It should look like the following:[/l] [code] CREATE SCHEMA [MyDirectory] [/code] [l]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.[/l] [hdsm]A table for the data.[/hdsm] [l]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[/l] [def][term][l]The table name[/l][/term] [desc][l]SiteDetails[/desc][/l] [term][l]The column name and datatypes[/l][/term] [desc][l]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[/l][/desc][/def] [l]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:[/l] [code] CREATE TABLE [MyDirectory].[Details] ( [siteId] INT NOT NULL PRIMARY KEY IDENTITY, [siteName] VARCHAR(250) NOT NULL, [siteDescription] VARCHAR(MAX) NOT NULL, [siteUrl] NVARCHAR(2083) NOT NULL ) [/code] [l]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.[/l] [hdsm]The stored procedures.[/hdsm] [l]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: [url=http://griffithswebdesign.com/Blog/Post/32]http://griffithswebdesign.com/Blog/Post/32[/url][/l] [code] CREATE PROCEDURE [MyDirectory].[addRecord] @name varchar(250) = '', @description varchar(max) = '', @url nvarchar(2083) = '' AS begin insert into MyDirectory.Details(siteName, siteDescription, siteUrl) values(@name, @description, @url) end [/code] [l]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.[/l] [code] CREATE PROCEDURE [MyDirectory].[getRecords] AS begin select siteId, siteName, siteDescription, siteUrl from MyDirectory.Details order by siteId desc end [/code] [hdmd]Connecting to the database.[/hdmd] [l]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:[/l] [code] [/code] [l]For more information regarding connection strings please visit [url=https://www.connectionstrings.com/]https://www.connectionstrings.com/[/url] 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:[/l] [code] using System.Web.Configuration; [/code] [l]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.[/l] [code] private string connectionString = WebConfigurationManager.ConnectionStrings["usingSPConn"].ConnectionString; [/code] [hdmd]Inserting data into the database using a stored procedure[/hdmd] [hdsm]The mark up.[/hdsm] [l]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:[/l] [code]

Title:


URL:


Description:


[/code] [l]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.[/l] [hdsm]The code behind.[/hdsm] [l]The click event of the button:[/l] [code] protected void btnAdd_Click(object sender, EventArgs e) { } [/code] [l]The first step toward the eventual goal of using our addRecord stored procedure is to define the ADO.NET connection object.[/l] [code] SqlConnection con = new SqlConnection(connectionString); [/code] [l]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.[/l] [code] SqlCommand cmd = new SqlCommand("MyDirectory.addRecord", con); [/code] [l]Now we need to tell our code what type of command our command object is. We do that by adding this line:[/l] [code] cmd.CommandType = CommandType.StoredProcedure; [/code] [l]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 [url=http://griffithswebdesign.com/Blog/Post/33] http://griffithswebdesign.com/Blog/Post/33[/url] We also need to give our parameters values, which will be the contents of the text boxes on our form.[/l] [code] 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; [/code] [l]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:[/l] [code] try { con.Open(); int i = cmd.ExecuteNonQuery(); Response.Redirect(@"~/default.aspx"); } catch (Exception er) { Console.Write("Error adding record: " + er.Message); } finally { con.Close(); } [/code] [l]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![/l] [hdmd]Displaying data from a stored procedure in the webpage.[/hdmd] [l]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. [b]Note – [/b]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.[/l] [hdsm]The mark up.[/hdsm] [l]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:[/l] [code]

<%# Eval("siteName") %>

Address:
<%#Eval("siteUrl") %>

Description:
<%# Eval("siteDescription") %>

[/code] [l]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.[/l] [code] 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(); try { con.Open(); adp.Fill(ds, "Details"); rptDirectory.DataSource = ds; rptDirectory.DataBind(); } catch (Exception er) { Console.Write("Error: " + er.Message); } finally { con.Close(); } } [/code] [l]To call this function we need to add a little bit of code to our Page_Load event. Like so:[/l] [code] if (!this.IsPostBack) { getdirectory(); } [/code] [l]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.[/l]

About the author

My name is Michael Griffiths, I am a website designer and developer and the owner of Griffiths Web Design. I am based in Royston, Hertfordshire. Most of my work is local, Hertfordshire, Cambridgeshire, Bedfordshire, Essex etc. but that’s not to say I won’t work elsewhere. One of the wonders of modern technology is remote working. So no matter where the client is located it is always possible to connect.

I specialise in creating ASP.NET webforms applications using C#. I use Microsoft SQL Server for database development and on the front end I use Html, CSS and JavaScript. I am also proficient in the use of jQuery and several CSS frameworks.

One of my greatest passions in life is learning. Which is why I chose to pursue a career in web design and development, technology is in a state of constant and rapid evolution which means there is always something new to learn. I also enjoy writing, both fiction and non-fiction.

Comments

Please feel free to leave a comment below:






Salamanca


Once your body actually starts to feel great, your cravings will naturally subside and you'll be reaping the benefits of lowering the sugar inside your diet. It is significant that you oversee the youngsters if they are brushing their teeth. Headaches are typical high are occasions when most of us experience minor to severe headaches. watch movies online for free - https://v.gd/how_to_secure_wifi_router


 
Griffiths Web Design Logo by Michael Griffiths
Griffiths Web Design Logo by Michael Griffiths
Click to go to the top of the page