Griffiths web design based in Royston, Hertfordshire, United Kingdom

 

ASP.NET Tutorials

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

[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]

Downloading files in ASP.NET using C#

[l]In this article I will be discussing with you how to download files from the server. We will be creating a Generic Handler to do all the heavy lifting. That way the code can be re-used whenever and wherever it is needed. As usual I recommend creating a new website to play around in, so open up visual studio and create a new empty asp.net website (File > new > website) or (Shift + Alt + N) In your shiny new website we need to add a generic handler, so go to (website > add new item) or (Ctrl + Shift + A) Select Generic Handler from the list, I called mine Download.ashx. Visual studio will do some of the hard work for you by generating the following code: [/l][code]<%@ WebHandler Language="C#" Class="Download" %> using System; using System.Web; public class Download : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Write("Hello World"); } public bool IsReusable { get { return false; } } } [/code] [l]Inside the process request part of the generated code you will need to delete its current contents, i.e. these lines: [/l] [code]context.Response.ContentType = "text/plain"; context.Response.Write("Hello World"); [/code][l]Once you have deleted those lines add the following code, I will explain what it does after:[/l] [code]string file = context.Request.QueryString["file"]; if (!string.IsNullOrEmpty(file) && File.Exists(context.Server.MapPath(file))) { context.Response.Clear(); context.Response.ContentType = "application/octet-stream"; context.Response.AddHeader("content-disposition", "attachment;filename=" + Path.GetFileName(file)); context.Response.WriteFile(context.Server.MapPath(file)); // This would be the ideal spot to collect some download statistics and / or tracking // also, you could implement other requests, such as delete the file after download context.Response.End(); } else { context.Response.ContentType = "text/plain"; context.Response.Write("File not be found!"); } [/code] [l]What does this code do? Well I’m glad you asked. First it creates a string from the query string value, which is the file to be downloaded by the user. Next it checks to make sure the newly created string is not empty and that the file exists, if it passes these checks the code can move on but if not the code takes you to the else block which informs the user that the file cannot be found. Now the interesting stuff happens. First we clear the output before we fill it in case there is any data lurking around in there that we’ve forgotten about. Next we set the content type to ‘application/octet-stream’ which is a binary file, that way all file types are covered. Then we move onto the next line, ‘AddHeader(("content-disposition", "attachment;filename=" + Path.GetFileName(file))’. This lets the user save the file to their computer and then decide how to use it, instead of the browser trying to use the file. Next we write the file then call Response.End(), which sends all currently buffered output to the client, stops execution of the page, and raises the System.Web.HttpApplication.EndRequest event. To use the generic handler we have just created you will need to add a link into your webform, like so: [/l][code]
cat pic[/code] [l] As you can see you link to the Download.ashx file with a query string that informs the code which file the user wishes to download, in this case it is an image file called cat1.jpg which resides in a folder called images. That is all there is to it. Easy to implement, reusable code. Wonderful isn’t it? Now you can start offering some downloadable goodies to your website users. I hope you found this article helpful and informative. If you have any comments, questions, queries etc. then please feel free to comment below or get in touch via email, michael@griffithswebdesign.com I look forward to hearing your thoughts. [/l]

Uploading files with ASP.NET’s asp:fileupload control in c#

[c]We’ve all seen, and almost certainly used, websites that allow users to upload files to the server. And now we will discover how they do it. We will be using ASP.NET’s file upload control, there are plenty of other options but you will be hard pressed to find one that is easier to use and implement. At this point I want to state that allowing users to upload files can be dangerous if misused, so be careful how you use it. In our example we will only be allowing the upload of image files, specifically png’s and jpeg’s. In this article we will allow users to upload files to the server by using the asp:fileupload control. We will also validate the file extension and only allow the previously mentioned file types. We will also use an asp:repeater to display the uploaded images, so the user can see that it has worked. First open visual studio and create an empty asp.net website. Note - Even if you want to implement this into an existing application I would still recommend creating a new empty site to play around in then once you have it down you can then implement it into your existing application with little to no issues. In the website we just created we need to add a new folder, I’m calling mine imageUploads. This folder is where we will store the uploaded images. Next we need to create a new webform. As this is the only page in our site mine is called ‘Default.aspx’. This is where the fun begins. On our webform we need an asp:fileUploadControl and an asp:button. The file upload control comes with the button to browse the file system but doesn’t come with the button to fire the event to validate and save the file, hence the extra button. Your mark-up for the two controls should look like this: [/c] [code] [/code] [c]We also need a repeater control. Your repeater should look like this: [/c] [code]
[/code] [c][i]Note –[/i] As you can see I have left the OnClick of the button blank and the ImageUrl of the image control is also blank. Don’t worry we will come back to those soon to fill them in. For the purposes of displaying error messages I also created an empty label control, like so: [/c] [code][/code] [c]That is all the mark-up that is required, which as you can see is not very much at all. Now we need to think about the mechanics of it all. What do we need to do to achieve our goal? We need some way of getting the url’s of the images into the repeater and we also need to validate and save files. I started with the repeater first. .Net has a wonderful class by the name of FileInfo which save us from most of the hard work. We will create a List of fileInfo objects. The code for this should look like the following: [/c] [code]public List GetImageNames() { string imgPath = Server.MapPath("~/imageUploads/"); List images = new List(); DirectoryInfo directoryInfo = new DirectoryInfo(imgPath); FileInfo[] fileInfo = directoryInfo.GetFiles(); foreach (FileInfo file in fileInfo) { images.Add(file); } return images; }[/code] [c]As you can see it’s nothing complex. First we created a string to hold the path to the folder. Next we created an empty List called images, which will store objects of type FileInfo. Then we initialised a new instance of DirectoryInfo and used the GetFiles() method which returns a file list from the specified directory. Then it was a simple case of using a foreach loop to loop through the files in this list and add them to our empty list we created. This creates the list but it does not bind it to the repeater, so to do that we will need to add some code to the Page_Load event.[/c] [code]if (!this.IsPostBack) { rptImgs.DataSource = GetImageNames(); rptImgs.DataBind(); }[/code] [c]First it checks to see if this is a post back and if it is not it then assigns ‘getImageNames()’ as the data source for the repeater. The last line calls the repeaters data bind method. We also need to add something to the mark-up, specifically the image url. The image control in the item template of the repeater should look as follows: [/c] [code][/code] [c]Test it out by dropping some image files into the folder ‘imageUploads’ and run the page, you’ll see the images displayed in the repeater. Now we can get down to what you came for in the first place, uploading files. First go to your upload button in the mark-up. Add an OnClick event and hopefully it should look like:[/c] [code][/code] [c]In your code behind file you need to add the following using statement:[/c][code]using System.IO;[/code] [c]Then add this line into your page class:[/c] [code]private string upDir;[/code] [c]Then in your Page_Load event you need to add a value, which in this case is the physical application path of the folder ‘imageUploads’[/c] [code]upDir = Path.Combine(Request.PhysicalApplicationPath, "imageUploads");[/code] [c]Now locate your upload buttons click event. In that event add the code that follows. I will explain it all at the end.[/c] [code]// check if a file is being submitted if (FileUpload1.PostedFile.FileName != "") { // check extension string ext = Path.GetExtension(FileUpload1.PostedFile.FileName); switch (ext.ToLower()) { case ".png": case ".jpg": case ".jpeg": break; default: lblError.Text = "Unfortunately the selected file type is not currently supported, sorry..."; return; } // using the following 2 lines of code the file will retain its original name. string sfn = Path.GetFileName(FileUpload1.PostedFile.FileName); string fPath = Path.Combine(upDir, sfn); try { FileUpload1.PostedFile.SaveAs(fPath); rptImgs.DataSource = GetImageNames(); rptImgs.DataBind(); } catch (IOException ex) { lblError.Text = "Error uploading file: " + ex.Message; } catch (Exception er) { lblError.Text += "Unknown error: " + er.Message; } }[/code] [c]First this code makes sure there is a file selected. Next we created a string to hold the file extension, then we make sure it is an allowed file type by use of a switch statement. After it passes that check we get the file name and add it to the path set by our ‘upDir’ string. Within the try block we have the following line:[/c] [code]FileUpload1.PostedFile.SaveAs(fPath);[/code] [c]This is what saves our file to the server. (For a call to the SaveAs to work, the ASP.NET application must have write access to the directory on the server). We have also re-bound the repeater to show the new images. [b]Something to be aware of:[/b] If you are using IIS to hostyour application, then the default upload file size is 4MB. To increase it, please add the following section to your web.config file:[/c] [code] [/code] [c]For IIS7 and above, you also need to add: [/c] [code] [/code] [c][b]Note: [/b]maxAllowedContentLength is measured in bytes while maxRequestLength is measured in kilobytes, which is why the values differ in this example. (Both are equivalent to 1 GB.) And there you have it. I hope this helps you in your future endeavours. If you have any comments or questions please don’t hesitate to get in touch.  [/c]

A Simple blogging system in ASP.NET

[l]In today’s economic climate blogging is an essential part of almost any business. No matter the industry there will be somebody somewhere that will be blogging about it. You yourself may have read some of these blogs and even learnt from some of them. There are so many content management systems out there and many of them include a blogging platform of some kind. Take WordPress for example, the entire content management system is built for and around this very feature. “But I already have a website” I hear you say. That’s where this article comes in. Throughout the course of this article we will create a simple yet effective platform from which you can publish your words of wisdom. What we will build will be simple but at the end I will suggest some further functionality that you can go off and do yourself, although I am always happy to answer any of your questions anytime. Our blogging system will allow you to add blog posts and display them. We will also create a means of allowing the readers of your blog to leave comments. In order to create a blog we first need to consider the database structure. Specifically the tables and how we will interact with these tables. So grab your pen and paper and start scribbling down what Schema, tables and columns you think we may need. Here’s what I came up with: A Schema to which our blog table and comments table will belong. I have called mine Blog. Imaginative I know. A table for the Blog Posts themselves, which I have called Posts. This table will consist of the following columns: [/l] [unord] [item]An identity column (PostId int identity(1,1) not null). [/item] [item]A column to contain the post title (PostTitle varchar(200) not null).[/item] [item]A column to contain the date of the post (DatePosted date not null). [/item] [item]Another date column but this time it will be to display the date the post was last edited (DateEdited date null).[/item] [item]A column for the post itself (PostContent nvarchar(max) not null).[/item] [item]And finally a column to store the authors name (PostAuthor varchar(150) not null). [/item] [/unord] [l]A table for the comments which I have named Comments. This table will contain the following columns;[/l] [unord] [item]An identity column (CommentId int identity(1,1) not null).[/item] [item]Column for the commenters name (Name varchar(150) not null). [/item] [item]Date of comment (DateCommented Date not null). [/item] [item]And the comment itself (Comment nvarchar(max)).[/item] [item]Also a column to link the comment to a particular post (PostId int not null).[/item] [/unord] [l]Now we have planned our data structure the hard part is over with. Now we can get on with actually building the application. First we need to create a new website. So open up visual studio and go to File > New > Website (or press Shift + Alt + N). You will be greeted by the following window: [/l] [postpic=newsimpleblog.jpg class=img-responsive title=TITLE alt=ALT style=STYLE][/postpic] [l]In this window select ASP.NET Empty Web Site and give it a name. (Note: I have called mine SimpleBlog but you can call yours anything you like.) Now go to Website > Add ASP.NET Folder > App_Data Now select the App_Data folder and go to Website > Add New Item… (or Ctrl + Shift + A) and you will get the following window: [/l] [postpic=addDB.jpg class=img-responsive title=TITLE alt=ALT style=STYLE][/postpic] [l]Select Sql Server Database and give it a name then click on add (Note: I called mine SimpleBlog.mdf but again call it whatever you wish) Next open up you web.config file and in the configuration section add the following: [/l] [code] [/code] [l]Now we need to create the schema. To do this we will right click on our database from within the Sql Server Object Explorer window and select New Query in which we will add the following line of T-Sql Code: [/l] [code]CREATE SCHEMA [Blog][/code] [l]Run the query and you will have created a new schema. Now we need to create our tables. There are 2 ways you could do this within Visual Studio. You could use the visual designer or you could write the T-Sql code yourself. Either option is fine, I personally am going to write the T-Sql code. To create the post table I used the following T-Sql script: [/l] [code]CREATE TABLE [Blog].[Posts] ( [PostId] INT NOT NULL PRIMARY KEY IDENTITY, [PostTitle] VARCHAR(200) NOT NULL, [DatePosted] DATE NOT NULL, [DateEdited] DATE NULL, [PostContent] NVARCHAR(MAX) NOT NULL, [PostAuthor] VARCHAR(150) NOT NULL ) [/code] [l]To create the Comments table I have used the following script:[/l] [code]CREATE TABLE [Blog].[Comments] ( [CommentId] INT NOT NULL PRIMARY KEY IDENTITY, [PostId] INT NOT NULL, [Name] VARCHAR(150) NOT NULL, [CommentDate] DATE NOT NULL, [Comment] NVARCHAR(MAX) NOT NULL, CONSTRAINT [FK_Comments_Blog] FOREIGN KEY ([PostId]) REFERENCES [Blog].[Posts]([PostId]) ) [/code] [l] Our next step is to write some stored procedures to add / display posts and comments. Add the following stored procedures to your database: [/l][code] CREATE PROCEDURE [Blog].[addPost] @pTitle varchar(200), @pContent nvarchar(max), @pAuthor varchar(150) AS BEGIN INSERT INTO Blog.Posts(PostTitle, DatePosted, PostContent, PostAuthor) VALUES (@pTitle, GETDATE(), @pContent, @pAuthor) END[/code] [code]CREATE PROCEDURE [Blog].[displayPosts] AS BEGIN SELECT PostTitle, PostId FROM Blog.Posts END[/code] [code] CREATE PROCEDURE [Blog].[displayPost] @postId int AS BEGIN SELECT PostTitle, PostAuthor, DatePosted, PostContent FROM Blog.Posts WHERE PostId = @postId END[/code] [code] CREATE PROCEDURE [Blog].[addComment] @postId int, @cName varchar(150), @comment nvarchar(max) AS BEGIN INSERT INTO Blog.Comments(PostId, Name, CommentDate, Comment) VALUES(@postId, @cName, GETDATE(), @comment) END[/code] [code]CREATE PROCEDURE [Blog].[displayComments] @PostId int AS BEGIN SELECT Name, CommentDate, Comment FROM Blog.Comments WHERE PostId = @PostId END [/code] [l] Now we have created our insert and display stored procedures for the Posts and Comments tables we need to write some C# code. We will be creating two class files. One for displaying and adding blog posts and one for displaying and adding comments. We will start with the blog posts class file. First go to Website > Add New Item (or Ctrl + Shift + A). When the Add New Item window appears select Class from the list, give it a name (I have called mine BlogPosts.cs) and click add. You will get an alert box appear which will say that files of this type are normally stored in the App_Code folder and it will ask if you wish to create an App_Code folder and add your file to it. Click yes. Now you will be presented with an empty class file. Remove all code from the page and add the following: [/l] [code]using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Web.Configuration; /// /// This class will be responsible for calling the Stored Procedures /// public class BlogPosts { private static string connectionString = WebConfigurationManager.ConnectionStrings["SimpleBlog"].ConnectionString; public static DataSet getPosts() { DataSet ds = new DataSet(); SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("Blog.displayPosts", con); SqlDataAdapter adp = new SqlDataAdapter(cmd); cmd.CommandType = CommandType.StoredProcedure; try { con.Open(); adp.Fill(ds, "Posts"); } catch(Exception er) { Console.WriteLine(er.Message); } finally { con.Close(); } return ds; } public static DataSet getPost(int postident) { DataSet ds = new DataSet(); SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("Blog.displayPost", con); SqlDataAdapter adp = new SqlDataAdapter(cmd); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@postId", SqlDbType.Int)); cmd.Parameters["@postId"].Value = postident; try { con.Open(); adp.Fill(ds, "Post"); } catch (Exception er) { Console.WriteLine(er.Message); } finally { con.Close(); } return ds; } public static void addPost(string title, string cont, string auth) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("Blog.addPost", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@pTitle", SqlDbType.VarChar, 200)); cmd.Parameters["@pTitle"].Value = title; cmd.Parameters.Add(new SqlParameter("@pContent", SqlDbType.NVarChar, -1)); cmd.Parameters["@pContent"].Value = cont; cmd.Parameters.Add(new SqlParameter("@pAuthor", SqlDbType.VarChar, 150)); cmd.Parameters["@pAuthor"].Value = auth; int added = 0; try { con.Open(); added = cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { con.Close(); } } }[/code] [l]Now we need to create two webforms. One to display blog posts and one to add them. So go to Website > Add New Item (or Ctrl + Shift + A). From the list select webform. Give it a name then click add. Do this twice, once for adding posts(which I have named addPost.aspx) and once more for displaying the posts(this one I called displayPosts.aspx). Go to your addPost.aspx file and open it up. On your page add the following mark-up: [/l] [code]

Add Post




[/code] [l]We have added text boxes for the post title, the post author and the post itself. We also added a button. Now we need to go to the code behind file and locate the click event of our button. Inside that event add the following line of code: [/l] [code]BlogPosts.addPost(txtTitle.Text, txtCont.Text, txtAuth.Text);[/code] [l]Once you have done that run the page(press f5) and fill in the form with some data. After clicking add go back into visual studio and check the database table to see if the data you just entered on the page is present in the table (Go to Sql Server Explorer, locate the Posts table and right cluck on it. You will be presented with a menu full of possible options you will need to select View Data which will bring up all the data in that table.) If your data is present then what we have created so far has worked. Yay! Now we need to go to the displayPosts.aspx file and open it up. (Note: For the sake of simplicity I am going to use an asp:FormView control to display the data but that is by no means the only or even the best way to display the data. Your decision on which data control to use depends entirely on your circumstances and requirements.) On your displayPosts page add the following markup:[/l] [code]

Display the posts.






[/code] [l] Now we need to go to the code behind and add in the following code: [/l] [code]protected void Page_Load(object sender, EventArgs e) { DataSet ds = BlogPosts.getPosts(); drpPosts.DataSource = ds; drpPosts.DataTextField = ds.Tables[0].Columns["PostTitle"].ToString(); drpPosts.DataValueField = ds.Tables[0].Columns["PostId"].ToString(); drpPosts.DataBind(); } protected void btnDisplay_Click(object sender, EventArgs e) { DataSet dat = BlogPosts.getPost(Convert.ToInt32(drpPosts.SelectedValue)); frmPosts.DataSource = dat; frmPosts.DataBind(); } [/code][l]Don’t forget to add this using statement to the top of the code behind:[/l] [code]using System.Data;[/code] [l]Run your page and try it out. If you followed the steps correctly you will be able to select a post from the drop down list which will then display the post underneath it. Now we have built that part of our blogging platform we can move onto the comments part. There is nothing difficult or complex about what we are about to do, in fact it’s not all that different to what we have just done. So, our first step is to create a class file which will be responsible for calling the relevant stored procedures that we created earlier. I have named mine Comments.cs As before this file belongs in the App_Code folder. In your comments.cs file add the following: [/l] [code]using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Web.Configuration; /// /// This class will be responsible for calling the Stored Procedures /// public class Comments { private static string connectionString = WebConfigurationManager.ConnectionStrings["SimpleBlog"].ConnectionString; public static DataSet GetComments(int post) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("Blog.displayComments", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter adp = new SqlDataAdapter(cmd); cmd.Parameters.Add(new SqlParameter("@PostId", SqlDbType.Int)); cmd.Parameters["@PostId"].Value = post; DataSet dSet = new DataSet(); try { con.Open(); adp.Fill(dSet, "Comments"); } catch (Exception er) { Console.WriteLine(er.Message); } finally { con.Close(); } return dSet; } public static void addComment(int post, string name, string com) { SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("Blog.addComment", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@postId", SqlDbType.Int)); cmd.Parameters["@postId"].Value = post; cmd.Parameters.Add(new SqlParameter("@cName", SqlDbType.VarChar, 150)); cmd.Parameters["@cName"].Value = name; cmd.Parameters.Add(new SqlParameter("@comment", SqlDbType.NVarChar, -1)); cmd.Parameters["@comment"].Value = com; int added = 0; try { con.Open(); added = cmd.ExecuteNonQuery(); } catch (Exception er) { Console.WriteLine(er.Message); } finally { con.Close(); } } } [/code][l] Now head over to your displayPosts.aspx page and underneath your formview add the following mark-up: [/l] [code]

Comments

Add a comment






[/code] [l]Then go into your code behind and add the following code: [/l] [code]protected void getcomms() { DataSet ds = Comments.GetComments(Convert.ToInt32(drpPosts.SelectedValue)); rptComments.DataSource = ds; rptComments.DataBind(); } protected void btnAddComm_Click(object sender, EventArgs e) { Comments.addComment(Convert.ToInt32(drpPosts.SelectedValue), txtName.Text, txtComm.Text); getcomms(); }[/code] [l]In your btnDisplay_Click event, after all the code we wrote previously add this line: [/l] [code]getcomms();[/code] [l]And that’s all there is to it. We have not built a very basic blogging system that you can incorporate into your ASP.NET web application. As promised way back in the beginning of this article I have a few suggestions to further improve your brand new blogging system; you could incorporate ASP.NET Identity to allow more users to add posts. You could create a table and the accompanying code to put your posts into categories. Also you could create facilities to update and delete posts and comments. That’s just a few ideas to get you going but the sky is the limit. Just make sure you don’t set yourself too big a task at once, chop them up into smaller more manageable goals. I hope you found this article informative and helpful. If you have suggestions for improvements I would love to hear them. [/l]

Sending E-Mails in the code behind with ASP.NET using C#

[l]I was recently asked, several times by varying different people, how to send emails in ASP.NET. When I explained to them how to do so they were amazed at how simple it was. I thought I would share with you this little nugget of wisdom as it is a very common feature of nearly all websites out in the wild. In this tutorial we will be creating a new website but if you are just looking to add the email feature to an existing site then please skip the first step. [b]The steps:[/b] Create a new asp.net empty website. Name it and save it. I have called mine ‘sendemail’ but you can name yours whatever you like. Next we need to add a web form. So go over to the solution explorer and right click on the website, a menu will appear, click ‘Add’ then ‘Add New Item’ (or ctrl + Shift + A). Select webform from the list, name it, then click ‘Add’ Now we have our webform we need to add some mark up to the page. Nothing complicated just some text inputs, labels and a button, like so: [code]






[/code] Add the following using statements in your code behind: [code] using System.Net; using System.Net.Mail; using System.Text.RegularExpressions; [/code] Next add the following code to the click event of the button: [code] protected void btnSend_Click(object sender, EventArgs e) { // create variables and assign values string Name = txtFirstName.Text; string CompanyName = txtCompanyName.Text; string PhoneNumber = txtPhone.Text; string EmailAddress = txtEmail.Text; string SendAddress = @""; string Subject = txtSubject.Text; // preserve mew lines in the body of the message string Message = Regex.Replace(txtMessage.Text, @"\r\n?|\n", "
"); // create new message MailMessage msg = new MailMessage(); msg.To.Add(new MailAddress(@"")); msg.Subject = Subject; msg.From = new MailAddress(SendAddress); msg.IsBodyHtml = true; msg.Body = "

Contact Name: " + Name + "
"; msg.Body += "Company Name: " + CompanyName + "
"; msg.Body += "Phone Number: " + PhoneNumber + "
"; msg.Body += "Email Address: " + EmailAddress + "
"; msg.Body += "Message:
" + Message + "

"; // set the reply to address to the address entered in the form msg.ReplyToList.Add(new MailAddress(EmailAddress)); // set up smtp client and credentials SmtpClient smtpClnt = new SmtpClient(smtp.your-isp.com) smtpClnt.Credentials = new NetworkCredential(EmailAddress and password goes here); // send the message smtpClnt.Send(msg); // inform user that message has sent lblError.Text = "Message sent!"; } [/code] Now run your application and test it out. For the sake of security I have removed my details from the example . If you have any comments or question then please leave me a comment below, or if preferred you can head over to the contact page and fill in the form. I would like to add that the contact form on this very site uses the same script to send the results directly to my email. I hope this helps somebody and thank you for taking the time to read. Please share it around as much as you can. Any and all shares/tweets/etc. are very much appreciated. [/l]

Paging ASP.NET ListView control with a DataPager control.

Paging ASP.NET ListView control with a DataPager control. [l]Today I would like to discuss paging a listview control. First I will show you paging a ListView that uses a SqlDataSource control then I will show you paging a ListView that does not use a DataSource control. The DataPager control makes paging almost effortless when used in conjunction with a DataSource control as you will see On your aspx page add the following markup: [code] TestCol1:
TestCol2:
TestCol3:
No data was returned.
TestCol1:
TestCol2:
TestCol3:
[/code] We have added a SqlDataSource control, a ListView control and a DataPager control. In the ListView control we have defined an EmptyDataTemplate which, as the name suggests, is displayed if no rows were returned. We have also defined an ItemTemplate which defines the layout of each individual item, A GroupTemplate which contains a placeholder for the item and a LayoutTemplate which defines the layout of the listview and also contains our DataPager control. If you run the application you’ll see that it displays the first 5 items and when you click the little number 2 you’ll see the final 2 items. And that’s it you now have a ListView that pages through data without writing a single line of C# code. Wonderful isn’t it? If you don’t use a DataSource control there are a few modifications required for it to work. I’ll show you why. On your aspx page add the following markup: [code]
No data was returned.
TestCol1:
TestCol2:
TestCol3:
[/code] As you can see the only difference between this example and the previous example is there is not a DataSource control. In your code behind add the following to populate the ListView with data. [code] private string connectionString = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { getData(); } } protected void getData() { string queryString = "select * from dbo.TestTable"; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(queryString, con); SqlDataAdapter adp = new SqlDataAdapter(cmd); try { con.Open(); DataSet ds = new DataSet(); adp.Fill(ds, "TestTable"); ListView1.DataSource = ds; ListView1.DataBind(); } catch (Exception er) { lblError.Text = "Error retrieving data: " + er.Message; } finally { con.Close(); } } [/code] First we added reference to the connection string that is stored in the web.config file. Then we used an ADO.NET select statement to query the database, put the data into a dataset then used the dataset as the data source of the listview. Go ahead and run the application and see what happens. It displays the first page just fine but when you click on the little number 2 you have to click it twice to see the next page but then you are unable to view the previous page again. In order to correct this you will need to implement the following steps: First add the OnPagePropertiesChanging event to the listview as follows: [code] [/code] Then in your code behind add the following code: [code] protected void ListView1_PagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e) { DataPager dp = (DataPager)ListView1.FindControl("DataPager1"); //set current page startindex, max rows and rebind to false dp.SetPageProperties(e.StartRowIndex, e.MaximumRows, false); //rebind List View getData(); } [/code] What this code does is first it finds the DataPager control. Then it sets the current page start index, max rows and rebind to false. Then it re-binds the ListView. If you run the application you’ll see that now it pages like a dream. I have included a downloadable project for you to examine and play with. I hope you found this tutorial helpful and easy to follow if you have any questions or comments please add them below in the comments section or if you prefer head over to the contact page and fill in the form to send a message directly to my email. If you know of anyone who may find this article interesting or helpful then please share it around.[/l]

Binding data to an ASP.NET Repeater Control

[l]In this tutorial I want to tell you how to bind data to an asp:Repeater control. For the example we will make a simple drop down list of colours and modify the background colour of each list item so the user can see each colour. I’ll assume that you have created a new project or opened the project you wish to apply this to. With that assumption in mind our first task it to call up a list of colours. We will use the system colours, but we also want to exclude colours such as [b]ActiveBorder[/b] or [b]MenuHighlight[/b] To get the desired list of colours use the following code:[/l] [code]public List getColours() { List cols = new List(); foreach (string c in Enum.GetNames(typeof(KnownColor)).Where( item => !item.StartsWith("Active") && !item.StartsWith("Menu") && !item.StartsWith("Scroll") && !item.StartsWith("Control")).OrderBy(item => item)) { if (!Color.FromName(c).IsSystemColor) { cols.Add(c.ToString()); } } return cols; }[/code] [l]This will give a list of colours but no means to display that data. In order to achieve what we want I have opted to use a
Previous 1 Next 
 
Griffiths Web Design Logo by Michael Griffiths
Griffiths Web Design Logo by Michael Griffiths