Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Connecting your Web Application to SQL Server 2005 Express Edition

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
In this video we're going to look at creating a web application from scratch that utilizes SQL Server 2005 Express Edition databases with tool support from Visual Web Developer 2005. Then we'll continue to build the application using a couple of different techniques to insert data into a database and then report it back out in a simple administrative web page. We'll be creating the example that I demonstrated in the very first video in this series, which is just a simple signup for a newsletter and an administrative page that allows you to review and edit the entries that the users actually made. And the purpose of this is just to show you the basic steps of creating the application. If you've watched the other videos in the Express Edition series, there's going to be some similar ideas even if we do things slightly differently. For example, when it comes time to insert data into our database, I'll show you how to do it purely from code without using any of the data-bound controls. So we'll get some familiarity with ADO.NET 2.0's API from a programmatic standpoint. One other item that you're going to need to know about is that when we write code I'll be doing it both in Visual Basic and C# within the same video. So if you're not interested in one language or the other, just use Windows Media Player's user interface to fast-forward through the parts you're not interested in. There's going to be a couple of initial steps that might not be completely interesting or will be time intensive and not germane to our discussion of adding database functionality to our application, so I'll stop the recording and work a little bit on my own and then turn the recording back on, and I'll tell you when I do that. So let's go ahead and get started. I'll begin by creating the Visual Basic version, which I'm going to call DashSkateboardsVB. In fact, I'll be using the Visual Basic version primarily as we go through this example. I'll also be creating a C# version called DashSkateboardsCS. So if you're interested in C#, open that folder instead and follow along. I'll tell you when the paths start to diverge between the VB and C# versions, and then we'll do as I explained a little bit earlier. Let's go ahead and get started by creating a new project. I'm going to go to File, New Web Site, and make sure that ASP.NET Web Site is selected. Over in the location I'm going to go back to the WebSites\ and then type in DashSkateboardsVB and click OK. And after a moment or two we have the shell of our project. I want to add a few things to begin with. I'm going to right click in my Solution Explorer, select Add New Item, and I want to add a master page that I'm going to call DashMaster.master and click Add. And we're going to do the same thing for another file. This time we're going to create a CSS file. We're going to select Style Sheet and call this dash.css. Okay. I'm going to take a few moments to populate the dash.css and the DashMaster. These are not really things that are that important for our example, so I'm going to pause the recording for just a moment as I get some of this initial work set up. Okay, so only a few moments have elapsed since I originally paused the video. However, I had the benefit of just simply copying and pasting the CSS information and the master information from some work that I had done previous to this. So when I was first working through the look and the feel of the application with the master and the CSS, it took me a couple of hours to get it to look the way that I wanted it to look like. What you're looking at may represent a couple of hours to get the overall appearance. And notice that what we have in the master is essentially just the shell, the outer portion into which the individual .aspx pages will display inside of this ContentPlaceHolder. So this will get used by all of the .aspx pages that use this master file, and we'll show how to do that in just a moment. It employs some graphics, as you can see that I added an image folder. These images were created by a graphic artist. He designed the way that he felt the website should look like, including the colors and the fonts and things of that nature, and then I went through the practice of translating those into actual HTML code, taking the images that he sliced out, and then trying to use the CSS file for everything that I felt like I could do just using text and so forth. Just as a quick review of how master pages work, let's go and add one of the pages that we're going to be working on a little bit more later. I'm going to right click here and select Add New Item. I'm going to select a web form and call this web form newsletter.aspx. The language is Visual Basic. We're going to not place the code in a separate file. We're just going to allow the use of the script tags and then be able to include our Visual Basic right in the same .aspx page. I could put this into a separate file if I wanted to, but we're not going to do that for simplicity's sake. I am going to click the Select master page and then click the Add button. When I do, it shows a series of folders. It allows me to select a specific folder to find the master page that I want to use. We could use multiple master pages for our application and then select on an ad hoc basis which of the master pages we want to use. In this case I've only got one, so we'll select it and click the OK button. So now you can see with our new newsletter.aspx page we have a directive at the very top, and one of the first things that it does in the directive is to tell the ASP.NET Runtime Engine what master page it should rely on to paint, essentially, the rest of that page. So now if we look at this in the Design view, what you'll notice is that we have our master page. It's kind of grayed out a little bit, but within our main content area is where we can write our code, drag and drop our form elementsm and things of that nature. It's just a visual way of showing us what the page will look like once it includes in the master and everything. We'll come back to this in just a moment. What I want to do before I start working on the pages themselves is to give some thought to the database structure. I know that there are some things about my data that I want to retrieve and collect from the user. For example, I know that I want to obviously collect the email address of the users since this is an application that's going to be used to collect email addresses to send out newsletters. I also want to be able to collect a DateTimeStamp when they signed up and the IP address that they're using to sign up. So if there's any discrepancy or if anybody challenges this in the future I can say, "Well, we've collected this information." And so it's just kind of a best practice whenever you're collecting email addresses to collect some extra information so that you can protect yourself as somebody who sends out email to show that these are people that actually did request your newsletter. And if they did not, then at least you have some evidence about where you can look for where that request came from. Since this is going to be a simple database with one table I'm kind of envisioning, it's also going to have the ability to allow the user to opt out. So we may want to historically keep that data and not delete it from our database, but we don't want to accidentally send another email to somebody who's requested that they opt out of the mailing. So what we'll probably do is add some sort of a flag— we may call it OptOut—that allows us to designate whether that person is still interested in receiving our newsletters or not. Let's go ahead and create a new database. To do so, I'm going to just right click and select Add New Item within the Solution Explorer and select SQL Database, and we'll call this dash.mdf file and click Add. And it asks if we want to put this into the App_Data folder. We certainly do. So we'll click Yes to the little message box. So the Database Explorer opens, a connection is created to our database, and now we will start creating a new table. I'm going to right click Tables, select Add New Table, and start designing my table. Truth be told, I've sat down with a little scrap sheet of paper and ahead of time I work out the database structure. Whenever I'm developing applications, I tend to think in terms of the database structure first. Of course that depends on the type of application that I'm trying to build. I'm usually thinking about the user interface and the database kind of in conjunction with each other. I'm thinking about what data I need to collect that drives how I create the user interface, and then I think about how I want to display things to the user, and that sometimes drives the design of the database itself. So for me personally when I'm working on small applications, it's a give-and-take process. If I'm working in a more enterprise environment where there's more people to communicate my design ideas to, I might go through some more formal steps of identifying high-level objects and things of that nature and let those drive the design process. Again, this is a very simple application I'm just developing for myself, so typically, a napkin or a scrap piece of paper is enough for me in those scenarios since I've been through this a number of times. If you need to sit down and think through this a little bit more closely, you might want to take a little bit more formal process, use Visio, for example, to design your forms and your database layout and then hand them to a friend or two or coworkers and get them to validate what will go where and how the whole thing will work. Or you can even employ very formal methodologies like entity relationship diagrams if you're so inclined to do that. However, we're going to keep this very simple. I know off the top of my head when thinking through this example I'm only going to need one table to store the information I need. So that's pretty much the process behind what I'm about to do. I know I'm going to want a primary key for my table even if I don't plan on relating it to other tables. And I know I want that to be an integer, and I don't want to allow nulls, and I am going to set that as a primary key, and I am going to turn on the Identity Specification and leave all the rest of the values by default. Okay, so that's great. The next thing I want to do is collect an email address. We'll keep it easy and just call it EmailAddress. It needs to be large enough to contain an email. Most people's email addresses are 50 characters or less. We're going to play it on the safe side. We're not going to give it too much space, but I think a varchar 100 should be sufficient. And we're not going to allow this to be a nullable field. We need that piece of information. The next thing we're going to do is a DateTimeStamp, and this will be the time that the record was added to the database. Right now I have some decisions to make. I could select datetime, but I have to ask myself, "Do I really need the thousandths of a second precision?" I probably don't. I just need a fairly general time frame down to the second. Even to the minute would be fine. So I'm going to choose smalldatetime instead of the regular datetime. I do not want that to be a nullable field either. I need to know—and I think I can provide—information at least to when the signup had occurred. The next piece of information I'm going to collect is the IP address, and I can do that using the .NET framework, and we'll show how to do that in just a moment. While I know that this is typically a series of 3 numbers, a dot, 3 numbers, a dot, 3 numbers, a dot, what kind of data type should I be thinking about in those terms? I could think of a varchar. However, I know that currently IP addresses can't be more than, say, 15 characters in length. So what I'll do is just select the char, and we'll change this to 15 because I know it's a finite length. Most will be 12, 13, 14, or 15 characters depending on what the values are. The big question is, should I make this nullable or not? In most cases the .NET framework is going to be able to return back to me the IP address. I've seen a couple instances where it's not accurate or I'm not able to attain that information. I'm not even sure exactly why. So just based on that knowledge, I might leave this as a nullable field for now. And then finally, we talked about having some type of flag that I could use to allow the user to opt out. And so we'll do OptOut. And what could we use as a flag? A yes or no, an on or an off. I look through my data types and I realize that bit probably is the best one to choose there. So I'm going to not allow that to be null. I have the columns defined the way that I want them. I think I want to call this my Email table, so as I click the Save button it asks me to choose a name for my table. Email and click OK. That's a little bit of the thought process that went behind building the database. Let's go ahead and close down the designer for our database tables and now turn our attention back to writing the code for our newsletter.aspx page. I'll give this page a little title at the very top, Newsletter Signup. And then now I've got to think about the controls I'm going to use. I'm going to maybe give it another space there and then type Email Address. My intent here is to put a text box, so let's go ahead and find one. I'm going to drag and drop it, put it right there on my designer surface, and then I want a Submit button. Let's go ahead and drag and drop a button on the designer, and let's call this submitButton, and let's give it some good text like Submit. Okay. Here we're going to call this just emailAddressTextBox. Let's go ahead and change the name of that. I also want to use some client-side validation to make sure that the value that's being typed in actually resembles an email address. It may not be a valid email address, but at least it will appear like an email address. It will have all the makings of an email address, like the @ symbol and a domain name at the very end. To do that, I'm going to go to my Toolbox and scroll down to the section where I have validation controls. I think that the RegularExpressionValidator will fit the bill, so I'm going to drag and drop that onto the design surface as well right next to the text box. First of all, I want to give it a good name. I could get pretty creative here, but I'm just going to call this regExValidator. One of the neat things about the RegularExpressionValidator is that it has a regex builder, so you don't have to know regular expressions, which is great, because I honestly am not that familiar with regular expressions. So I can use the little box here if I can find it. Let's see. ValidationExpression. There we go. I'm going to click the ellipses, it opens up the Regular Expression Editor, and I can find Internet email address as one of my options, which is perfect. That's exactly what I want to use. I'm going to click the OK button, and it will create the ValidationExpression for me. For error messages we're going to type something like Please input a valid email address. And since we don't want that to be displayed right there, we're going to use a ValidationSummary control as well in just a moment. For display purposes, though, what I may want to use in the text is just a star to let people know that this is a required field. So the star will draw their attention to the fact that there will be some additional error message information which the summary control will pull from the ErrorMessage property of our regExValidator. One last thing I need to do with this is actually tie this to this text box as opposed to any other controls. So we have to set the ControlToValidate property, and since we only have one control that can be validated, it shows up there. I think we're in good shape so far. The next thing we'll want to do, I'll just do this right underneath the Submit button, go back to my Toolbox and we'll pull the ValidationSummary control. We'll call this something like newsletterValidationSummary. We'll set the DisplayMode to instead of a bullet list just a single paragraph since we only have one control. It makes more sense that way. For the sake of completeness, what I'd probably do at this point is go to the Source view and start looking at the source code that was generated by all my efforts and try to make things look more attractive using my Cascading Style Sheet. So I'm going to take a few moments and change some things around, and I'll highlight those in just a moment. After a minute or two, I was able to find the style sheets that I was looking for. This is probably bad. I should have used a paragraph tag, but I used a div tag instead, put a div around the email address all the way to the end of my validation control. Let's go ahead and take a look at that real quick. Just used a div classical input label, and since I wanted to keep it all on the same line, I didn't end that div tag till all the way on the right-hand side of the definition for the RegularExpressionValidator. The only other thing I really did was the newsletterValidationSummary I created a tag called Warning. I just used that here by setting the CssClass property to Warning, which is in my CSS page. Now it looks pretty good. There's no one right way to do this, so I think what I'll just do at this point is add a couple more of the pages. I kind of messed things up here a little bit. Didn't quite do that the way I wanted to. That's okay. I'll add a couple more of the pages that I want to work on. If, for example, we hit the Submit button and there's a problem writing the values to the database, then I'll want to give the users some kind of indication, a page that says, "Look, there was a problem. We know that it occurred." "Just want to let you know that if this problem continues to happen, please write us if you feel comfortable doing that." Or if it was successful, then we want to show them a different message that says, "Thanks a lot. You'll expect your first message here in a couple days." So let's go ahead and add those pages into our project right now. I'm going to right click, select Add New Web Form, and we'll call this one newsletter_confirm. This will be the one that will display if we were successful. Again, we have by default Select master page already selected because of our previous selection. So we'll just need to select the DashMaster as the one we want to use. And we'll do the same thing for the other page, which is the failure page. We will Add New Item. We'll call this one newsletter_problem.aspx. We'll make sure that we have Select master page as an option and click Add and again click our DashMaster.master file. And so now we have 2 pages. I don't think we're really going to do a lot programmatically with these pages. These will be simple display pages. We could have done htm pages as opposed to the aspx, but we wanted to take advantage of the master pages so that the entire site has the same look and feel. So that's the reason why we chose .aspx pages instead of just straight HTML. Okay. What we're going to do is go through the exercise of typing in some of the friendly text here and configuring that, so I'm going to pause the recording while I do that step. It only took a minute or so, but you can see we have some text in our confirm page and in our problem page. There is really nothing very interesting about these. I probably could have done a better job spacing out here from the text to the actual master page. If I had spent some more time tweaking it, I probably could have gotten it to look a little bit better. But those are all incidentals. What we want to focus on now is really the meat and the core of why we're doing this in the first place, and that is to actually get data from our form that we've created here after the user clicks the Submit button to actually save it into our dash.mdf database. Let me just take one quick second and talk about why I chose to write code as opposed to data bind. If we take a look at our Toolbox, we could use, for example, the FormView control. The reason I didn't want to use the FormView is because some of the information will not be visible— the IP address, for example, and the OptOut. And there was no real easy way for me to get rid of those in an insertion scenario without having to go through a lot of work. And so I decided rather than do that, I would instead just take care of some of those details behind the scenes and write the code manually. Anyway, I think that this might be a little bit better in some regards because I'll have more control over how the code is executed. And it gives us an opportunity to look past just simple data binding into what really the data binding is doing behind the scenes. So I'm going to double click the Submit button. It allows us to get into the Code view, which, as you recall, is on the same page as our .aspx page. If you're new to 2005 but you're familiar with the previous versions of ASP.NET, this might be a little bit of a change in the way that you perceive how you could work with .aspx pages and code-behinds and things of that nature. I just wanted to highlight that as well. So now we need to start looking at writing some code here. At this point we're going to start talking about Visual Basic code specifically, and I'll come back and do this again with C#. You can see the lower third at the bottom here, that indicates that we're currently working with Visual Basic code. If you want to fast-forward now to look for C#, you certainly can. When we use ADO.NET 2.0, the object that we'll be working with primarily is the DataSource object, the SQL DataSource object specifically, and it will allow us first of all to define where our database is through the use of a connection string, then we'll be able to tell it what type of command we want to perform. We'll set some of the properties for insertion. We'll tell it, for example, what the command type is, whether we're just going to have inline SQL or whether we're going to call a stored procedure. We're going to do both in this example. And then we may need to send in some parameters, some values that we want to actually insert into our SQL statement or into our stored procedure, and then we'll try to actually execute the insertion by calling the Insert method on the data source. Let's do this. Let's start by going Dim and we'll call this dashDataSource as new Sql— we'll let IntelliSense catch up with us here— SqlDataSource. The first thing we'll need to do is tell it which database we want to connect to. We'll go dashDataSource.ConnectionString = and now we have to tell it our connection string. A couple different ways we can go about this. I could just hard code a connection string into my application. That would really, really, really be a bad idea for a couple of different reasons, not the least of which is that it really limits and requires a recompilation of the code every time that I want to put this on a different computer. So the better alternative here is to add a web.config file to my project, which you can see has already been added, and then add a connection string section with one connection string that we'll use for our little project here. There's a couple of different ways I can go about this. The shortcut that I'd recommend that we use— and I'm just going to do this for the moment; I'm going to comment that line of code out— is that I already know that I'm going to be working with some data binding, and I know that whatever I data bind it creates the connection string for me within the web.config. So what I might do here is stop what I'm working on and add my little admin page that I'm going to come back to in just a few moments. I'm going to Add New Item. We're just going to call this admin. We're not going to use master page, and it doesn't really matter if we place it in a separate file or not. I'm just going to go ahead and click Add. On this admin page that we have now in our main area what I want to do—in fact, I think I can just build the whole thing right now, what I had in mind. Let's go to the Database Explorer, the Tables here. We've got Email. I'm just going to drag and drop that onto our designer surface. By doing that, look at everything that it did for me. It created the table for me, I have my data source configured, and actually if we take a look now at our web.config, let's see if it did this for us as well. Yeah, it sure did. It created the connection string for us that we can now use. Pretty neat. And I did that all without having to figure out the connection string on my own and kind of work through that. Even if I didn't want to use this within my application, it's still a nice little shortcut for creating the connection string to my database. So you may want to keep that in mind. I have also copied this out from a previous version where I could just type the whole thing in myself if I wanted to. But I didn't really want to go through all that work. I'm kind of lazy and I like to take shortcuts. So all I need to keep in mind is the name here, dashConnectionString1. Let's go back to our newsletter.aspx, go back to our dashDataSource.ConnectionString and set that equal to— There are a set of objects within the .NET framework that know how to communicate well with the web.config file, specifically the connection string section, so we'll use ConfigurationManager.ConnectionStrings and then we'll pass it the name of the connection string that we want to use. I believe that was dashConnectionString1.ToString. Let me just make sure that that is indeed the right. I have a short-term memory situation here. Let's get rid of that. We're going to look at the web.config file one more time. Okay. I'll just copy it and, just to make 100% sure, paste it in here. Now if I ever put this application on a different server, all I need to do is just change potentially some of the settings here in this connection string and I won't have to recompile my entire application. That's the benefit of doing it that way. The next thing that we do after we've got our connection string set up is that we want to tell it specifically what we're intending to do, which is we're going to work with an insertion of data. So we have to not only tell it that but also then tell it how we plan on communicating with it the actual commands. So let's do this: dashDataSource.InsertCommandType. This will set the command type for the Insert command. If we wanted to do an Update command, a Delete command, we'd have to set those independently of this. But we're going to be working primarily with just the collection of Insert methods and properties for the data source. So we can either tell it that we're going to use inline text, inline SQL, or stored procedure. We'll do both eventually. Let's start out with a text example. What does that really mean? Let's go ahead and show you. We'll set the InsertCommand equal to— and this is where our knowledge of Transact-SQL will come in handy. We're going to write basically a SQL insertion statement. So INSERT INTO Email. We'll give it the fields that we want to insert. (EmailAddress, IPAddress, and the DateTimeStamp) VALUES. Here's where we're going to use variables that are going to be placeholders for the actual values that we'll be setting in our code in just a moment. The way that we do this is through the use of the @ symbol. We can call these things anything we want to. Typically a good convention is to name them the exact same thing as the field names themselves. So EmailAddress. We have an @EmailAddress, @IPAddress, and then @DateTimeStamp. This is the actual SQL statement that's going to get executed. We set it into the InsertCommand property of our data source. The next thing that we need to do now is to actually populate those variables, that @EmailAddress, @IPAddress, @DateTimeStamp, with actual values. How do we do that? We use an InsertParameters collection. We have an UpdateParameters collection. We have, I believe, a DeleteParameters collection as well. So here's what we'll do. .InsertParameters.Add. We're going to add a new parameter, and there are several overloaded versions of this. This is the overloaded version that I want to use. EmailAddress. Notice this time I did not have to use the @ symbol. It's just not necessary in this particular case. So ("EmailAddress", emailTextBox.Text). I believe we called it EmailAddress, right? There we go. Okay. And we'll do this 2 more times, dashDataSource.InsertParameters.Add. This time we're going to populate the IPAddress, and the way we're going to get to the IPAddress, we have an object within ASP.NET that allows us to access the headers and all other information that was sent from the client who requested this page. It's called the request object. So we're going to use Request. and here we can find out a bunch of information in this about the request and the requester. In this case we want to use the UserHostAddress, which popped up automatically for me, and set that ToString. And InsertParameters.Add and the DateTimeStamp, DateTime.Now. You have to be careful whenever you're doing this part. If you were to misspell anything here in the Insert command or any of the parameter names, just like I almost did right there, you would definitely have an issue whenever you try to actually run this application. So these are the first things that you should do is review the spelling of all of the table names, the column names, and these input values as well. What will happen as we actually run our application and call the Insert method in just a moment is that ADO.NET will take these values that we've just worked on here in adding them into the InsertParameters and will collate them into the values for our Insert statement and then send those to SQL Server. The next step for us is to actually call the Insert statement. Before I do that, I'm going to do this: Dim rowsAffected as an Integer and I'll initialize that to 0. This will allow me to determine how many rows we actually insert into our database. Obviously the answer should be 1 if it was successful. If it's 0, that means there was definitely a problem, our record didn't get inserted for some reason, but there was no reason for it to throw an error, for example, in which case we would definitely want to send our user to the newsletter_problem.aspx to say, "Look, something happened, we don't know exactly what." "We recorded it," and at least let them know that we've given it some thought. Let's do this: we're going to start out with a Try-Catch block. Within our Try statement we're going to do rowsAffected, which will be the return value of our dashDataSource.Insert command. If there's a problem, what we'll probably want to do and what I'm not going to demonstrate here is we want to Probably would want to write to the Windows error log. We're not going to do that for now, but we want some way of capturing the fact that there was an exception thrown whenever we did the insert into the database. But either way we want to do this: Server.Transfer. We're going to send the user to a different page, newsletter_problem.aspx, and also what we'll want to do since either way, whether there was a problem or there wasn't a problem, I want to be a good citizen and I want to destroy things, and I'm going to take the step in setting it equal to nothing. We're done with our data source now. But I'm not done with the value that was collected from the insert. How many rows of data were inserted into the database? So if rowsAffected are anything but 1, then we want to server.Transfer to newsletter_confirm. Whoops. Anything but 1 we would want to send them to the problem.aspx page. And otherwise, we want to send them to the newsletter_confirm.aspx page. Okay. Just a quick recap of what we're doing here, very simple. We are defining our DataSource object, which is the main ADO.NET 2.0 object we're going to work with. We're going to set the connection string so that it knows where the database is located. We're going to set the properties for an insert, including what type of insert we're going to perform— in this particular case we're going to send them an inline SQL statement— so it knows what it has to do internally in order to get ready for that. We're going to give it the Insert command itself, which is simply an Insert SQL statement. We're going to populate the parameters that were defined in our SQL statement through the use of the @ symbol at the very end of our Insert statement. Then we're going to actually call the Insert statement. When it performs the insertion, we should be able to determine how many rows were affected, which we'll check, and if it is equal to 1, then we'll send them to the confirmation page; otherwise, we'll say there was a problem, we were not able to insert a row. Also, if there is any problem during the insertion of the record— for example, the database is corrupted somehow or we've got a bad connection string or we're pointed in the wrong spot, whatever the case might be— then it's going to catch in our exception and will transfer the user to the problem page and we'd also probably want to do some type of reporting, whether by email or the Windows log. So that in essence is how it works. Let us test our application now. It wouldn't surprise me, having run this for the first time, if there were a bug or two that we had to work through. Let's just go ahead and give it a shot. It says debugging is not enabled. Do you want to enable it? Absolutely. We'll click the OK button. The web server pops open and we're starting to load it up. After a moment or two we get our page. Let's go ahead and type in a name here. Let's do [email protected], hit the Submit button. And we get some sort of problem. What we can do at this point is to watch how this actually works. Go ahead and set a breakpoint, and we'll start walking through our application, see where the error actually occurs. Let's try this again. So we hit our breakpoint. That's good. I'll start stepping through by using the F11 button on the keyboard or by using the little Step Into buttons here, and I'll probably just go ahead and click Step Over because I don't want to get into anything. I imagine that we're going to run into the problem whenever we hit our Insert statement. The good news is that once we hit that problem, I can start inspecting the problem through my Locals window. It's not going to give me much, so I'm going to have to look someplace else for this. Let's execute one more line of code. Now I see down here where the problem was. I was looking in the wrong spot. Invalid column name DataTimeStamp. You remember when I just got done telling you that if you do get an error it's probably because you called something incorrectly. I did that. Unfortunately, muscle memory got the best of me. So I need to stop the execution of the application and I need to look. Okay, I didn't misspell it there. It said the column name, so that would lead me to look in the column definition, and you can see indeed I named it incorrectly. So that is why this didn't work. Let me go ahead and get rid of the breakpoint and run the application one more time and see if this works this time. So [email protected], click the Submit button. There still seems to be a problem, so let's see what we've got this time. Maybe another misspelling. Let's go ahead this time and we'll just stop here on this line of code because we know it's going to hit at least this point because we've gotten that far in the past. Now we're going to hit that [email protected], Submit. Okay. So now let's look. Cannot insert the value NULL into the column OptOut. Okay. So let's do this. Since we don't want to insert anything into that, let's change the definition of our database. I don't want to be forced into inserting a true or false. I'd like to have a default value placed in there for me. So we're going to go to the Database Explorer, we're going to find the Email table there, and we're going to open the table definition. Within our OptOut field, instead of just making it nullable, which I don't want to do, I want to put the default value equal to—we can choose 0 or 1. Zero would be false. We don't want them to automatically be OptOut, so we'll put a 0. If they do hit 1, that means they do want to be opted out. Go ahead and save that and try this again. There will be a default value. Even though we're not overtly setting it, it should still be there whenever that new record is created. One more time. Let's go ahead and click Submit. Okay. This time it seemed to work. We got the Thank you page. "You'll be receiving our next newsletter shortly." The next thing we can do, as you might conclude, is be able to go to the Database Explorer and find our new record in the database. Will that work? Let's try it out and see. Let's select Show Table Data. And indeed it is there. You can see that we have [email protected], DateTimeStamp and the IPAddress. And the OptOut is False. It looks like we got that to work. The other way to get this to work, let's run this application and see. We already worked on our admin page, right? So let's see if there's anything left to do with the admin page. That seemed to come fairly easy to us. We didn't have to do much. We just dragged the table from the Database Explorer onto the design surface, and it created it automatically for me. And look, it shows up there automatically. I want to format this and make it look a little bit different, and I want to add the ability to inline edit, but I feel confident that the newsletter.aspx page works. We didn't expect much functionality out of the confirm or the problem pages, but those seem to work just fine as well. So I'm going to start closing down some things here that I don't want to look at anymore. We're going to just delete this default page because this is only a snippet of an entire application and we're not going to fully code this thing from beginning to end. Let's go ahead and get rid of the web.config and the CSS file. We'll get rid of all of that stuff, get rid of the Start page, so we can just focus now on the admin.aspx page. Before we move on to the admin portion of the application, I want to review the C# version of our newsletter.aspx page. Just to catch up, let's take a look at the Design view for the page. When somebody enters an email address and clicks the Submit button, it will fire an event on the server and will handle that event. Currently it's called submitButton_Click. Within that event what we'll do is create an instance of our SqlDataSource object. This is the main object that we'll use within ADO.NET 2.0 in order to interact with the database. It will handle things like the connection to the database, our intent of what we want to do with the database— in other words, a command—it will help us set the values for the Insert command as well as the parameters that we'll set as input values into our Transact-SQL statement, and then it will allow us to actually perform the insertion. So let's take a look. In line 7 you can see that we have the creation of a new instance of the SqlDataSource object called dashDataSource. Then we'll start to use that instance throughout the rest of our code. The first thing that we need to do is tell the data source where to find the database. And so we set the ConnectionString property in line 8 equal to—and this is where we had some options. If you were watching in the Visual Basic version, we could have hard coded the actual connection string information here. However, it's always a good idea to put that in a more configurable place, and the web.config file is the best place to store that type of information. Let's open up the web.config, and you see that there is a connection string section here that we added. That's actually a misnomer because if you watch the Visual Basic version of this, you'll note that what we did was create a new page called admin.aspx, which we were going to do anyway at some point, and then we went to our Database Explorer and dragged and dropped the Email little icon from our Database Explorer into our designer surface, which automatically created the data view grid, the SqlDataSource, and created that entry in the web.config file. So we're just going to steal that. We basically just copied and pasted that name into our code. Let's go back to the newsletter page if we can find it here. There we go, the newsletter page. Scroll up to the right-hand side. There is a class within the .NET framework that allows you to work easily with the web.config configuration settings called the Configuration Manager. So we use the ConnectionStrings property in order to access our connection string that we are interested in, the dashConnectionString1 value, again, deriving that name from the web.config file. Once we are able to pass that to the data source, it knows now how to communicate with the intended database that we want to work with. The next thing that we need to do is to set up the insertion, and the first thing we need to do is tell it what type of Insert command we're going to give it. So we set the InsertCommandType equal to SqlDataSourceCommandType.Text. That's an enumeration that lets it know that we're going to pass in inline SQL as opposed to a stored procedure. We're going to come back in just a few moments after we cover the admin portion of the application and rewrite the application ever so slightly in order to take advantage of a stored procedure. But for right now you can see what we mean by an inline SQL statement. In the very next line of code, line 11, you can see that we have InsertCommand equal to "INSERT INTO Email (EmailAddress, IPAddress, DateTimeStamp) VALUES and then we have (@EmailAddress, @IPAddress, @DateTimeStamp)"; Using this little @ symbol, we can designate these as input parameters to our inline SQL statement. We're going to then think of these as variables. And so what we want to do is trade out those variables with actual values, and the way that we do that is by accessing the InsertParameters collection of our DataSource object. So lines 13, 14, and 15 are accomplishing just that. As you can see, we use in the InsertParameters collection an Add method passing in the name of the parameter that we want to set. In this case we're not using the @ symbol but we're just using its full name, EmailAddress, setting that equal to, in this case, the text box that's on our web form. In the case of the IPAddress we're going to use the Request objects UserHostAddress property. The Request object is something that's available to us in order to inspect the request from the person who is actually viewing our web page. They're going to send along in the header section several pieces of information, like their originating IP address, what their browser type is and things like that information, and we can inspect that using the Request object. So that's what we did there in line 14, and then in line 15 we add the DateTimeStamp, setting it to the DateTime.Now.ToString. We had to set it to ToString in C# version because of the nature of the types and the fact that Visual Basic takes care of some of that implicit type conversion for you. We don't get that benefit through C#. It's actually a blessing in disguise requiring us to set it ToString so we explicitly know what we're trying to accomplish. The next thing that we need to do is actually run the Insert command. You can see that in line 21 we're performing that dashDataSource.Insert. This will take all the information that we've previously set in lines 7 through 15 and now is about to execute them against the database. Notice that I created in line 17 a variable called rowsAffected, said it equals 0. Whenever we run the Insert method, it's going to return back to us the number of rows that were inserted. If that number is 0, we know that there was a problem that took place. Even though it didn't throw an error or an exception, there was a problem. So we're going to check that in just a moment. So we'll set the rowsAffected—we would expect that to be 1— equal to the dashDataSource.Insert. If there is a problem, what we should probably do—as you can see, we have a try-catch statement wrapped around our Insert method here— in the catch what we probably want to do if there's a problem is write it to the error log within Windows so that we have some record of the problem. And then what we want to do is send that user to our friendly problem page. So we'll do a Server.Transfer sending the user then to the page that says, "There was a problem. We know what it was." "We've reported it to the people in charge. If you still have problems, let us know." And it gives an email address. If there was not a problem or either way, we still need to shut down and get rid of the instance of our object. We're going to help out the .NET garbage collector by setting the reference of our object equal to null. We don't have to do this; it's usually a good practice though. Now we're going to check the rowsAffected. Just as a quick recap, we grabbed the rowsAffected from the Insert method here on line 21. If it's not equal to 0—actually, that should be it's not equal to 1; we would expect it to be equal to 1— then Server.Transfer newsletter_problem. We're going to send them to the problem page. We would expect it to be 1, so if it's not, then we want to send them to the problem page. If it is 1, then we'll send them on to the confirm page. I realize I went through that fairly fast, but if there are some things that didn't make sense, it might still be helpful to go back through and watch the Visual Basic version of the source code as we typed it all out and explained things in great detail. I'm going to go ahead and shut down the DashSkateboardsCS and we're going to take a look at that again in a few moments whenever we come back and change it out to use a stored procedure instead of the inline SQL that we wrote. Going back to the admin page that we were working with, what we want to do is enable this page to allow editing, allow deletion, and also allow paging and sorting as well. The other thing that I want to do is click on Auto Format and change the appearance. Let's pick this one and click Apply. And then I want to try to get in here at the very beginning of the page and put a title on it, something to the effect of our Newsletter Administration Page. And we can modify it and make it look nice and everything if we'd like. Now that we've made those changes, let's go ahead and run the application, specifically our admin page, and see all the functionality that we're able to facilitate by just clicking a few checkboxes and making a few selections here and there. Here in the case of our only record in our database right now, we can edit it, we can change test to test1, for example, we can change the time and even change the OptOut, and when we're ready we can select the Update command, and notice that it made those changes. And we can also delete. So an incredible amount of functionality all enabled through just the dragging and dropping, the use of the little quick tasks menu option here, and that's about it. Behind the scenes what's going on is that code is being written for us, and we can modify this if we want to. They're represented in a series of just tags— for example, the GridView, being able to define the columns, for example, the footer style, the row style, and things of that nature. Also, what are the fields bound to and what should their headers be and things of that nature, all very configurable either through the IDE or by coming in here to the source code view and changing it. The data source itself, all the things that we set— remember, for example, the InsertParameters, look at it— these were the same InsertParameter collection that we were working with in code. We're able to do this purely from using HTML-like tags by setting the parameters here. For example, the Insert command let's take a look at and see if we can find that here. I'm not sure if we have it. But ultimately, what's going on behind the scenes is that— Here we go, an Insert command, "INSERT INTO (EmailAddress), (DateTimeStamp), (IPAddress), (OptOut), the VALUES, and look, it's almost identical to the code that we wrote manually by hand. This is all done for us with very little effort. So you can see that even though it seems like magic when we're using the designer, what's going on behind the scenes is similar to the code that we wrote. It's different insomuch that it's in a tag-like syntax as opposed to Visual Basic or C# type syntax. One last thing that I promised that we would do is take a look at how to enable the use of stored procedures as opposed to inline SQL. There are a couple different benefits you'll recall from the videos when we talked about the stored procedures near the end of, I believe it was Lesson 7. One of the benefits is that the stored procedures can be used across multiple applications, whereas inline SQL hard coded directly into your application is only available to that application. There's also in some cases some pre-compilation benefits in terms of speed and things of that nature. You won't really notice those unless you have a lot of volume and other considerations like that. But at any rate, let's take a look again at our Database Explorer and drill down so that we can create a new stored procedure. You can see one of the folders, Stored Procedures, there's nothing in it. By right clicking and selecting Add New Stored Procedure it gives us some commented out help. I'm going to get rid of all this stuff because, personally, we don't need it. In the very top part what we want to do first of all under the CREATE PROCEDURE, dbo being the database owner, the owner of this particular database object, will be the name. We're going to call this EmailInsert, and then we're going to define the input parameters as EmailAddress, and then we have to give it the data type, varchar(100). These are like input parameters that you might create for methods for your classes. Let's do this: INSERT Email (EmailAddress, IPAddress, DateTimeStamp). I'm going to put this on a separate line because I can and make it more readable. Here I'm going to, just like we did whenever we did the inline SQL, pass in the input values EmailAddress, IPAddress, and in this particular case I'm not going to make an input parameter for the DateTimeStamp. Instead, I'm going to use a special method within SQL Server called GetDate, which will give us the current DateTime. And we'll put it into our database. I'm going to go ahead and just save this. And now what I want to do is come back to the newsletter page, and we're going to comment out some code and insert some new code. In this particular case we're just going to comment out these 2 lines of code and rewrite them. dashDataSource.InsertCommandType this time is going to equal StoredProcedure, and then the dashDataSource.InsertCommand will equal the name of the stored procedure that we want to use. In this case it will be EmailInsert, the name of the stored procedure we just created. We're still going to use our InsertParameters except I'm going to comment out this last one. We're just not going to use it because we're going to allow SQL Server to input the DateTime instead of sending it in from our Visual Basic code. Everything else about our example will stay the same. Let's go ahead and run our application. Let's give it a try, and hopefully it will work. We'll call this [email protected]— And let's just do this for now. Let's not put .net and click Submit, and notice that it looks like our validation summary and our regex is working just fine. That's good. I just wanted to test that briefly. It looks like it worked. We got the successful message. So that's great. We'll just switch over here to our C# version and do the exact same thing over here. Let's go ahead and comment out the lines of code that we know we're not going to use, and then we'll add the 2 lines of code that we know we will use. We'll change the CommandType equal to SqlDataSourceCommandType.StoredProcedure, and then we want to set the InsertCommand equal to "EmailInsert" and that is all that we need to do to make the change from inline SQL to a stored procedure. So our application works. We're ready to deploy our application. We can do that in 1 of 2 ways. We can simply just copy all the files directly from our— Let's open up our Windows Explorer and find our web site by going to Visual Studio 2005, WebSites, and you can see we have our DashSkateboardsCS and our DashSkateboardsVB. We can copy all the files within either of these directories to our web server. The web server has to be configured for ASP.NET 2.0. If your hosting company does not have that enabled on your web server, your applications will not work. If it is enabled, then you should simply be able to FTP this folder's contents up to your web server or you can use the Copy Web Site functionality from the Website menu within Visual Web Developer 2005 Express Edition, which essentially works exactly the same as FTP. For more information about this functionality and for a demonstration of how it's actually used in a real web scenario, I would encourage you to take a look at the Express Edition videos Getting Started with Visual Web Developer 2005 Express Edition. We covered quite a bit of ground in a fairly short period of time, although this is a fairly lengthy video. I apologize for the length. But we were able to create an entire application from beginning to end, albeit a very simple application, that took advantage of ADO.NET 2.0 in a number of different ways. We wrote code using Visual Basic and C# to create a DataSource object which we then configured for an insertion and then actually called the Insert method. We also used data binding extensively for our administrative web page, and we noted that even though it seemed like magic in how we actually went about working with it, if you took a look at the source code, it's actually very logical and it's essentially writing code that accesses ADO.NET 2.0 similarly to how we wrote by hand the code in Visual Basic and C#. So we come to the end of our series on SQL Server 2005 Express Edition. I hope you enjoyed it. Hopefully not only did you learn a little bit about the theory behind relational databases but also the practical methods and techniques and how to apply that knowledge to creating your own databases in SQL Server 2005 Express Edition, the use of the management tools, the integration of the tools into the other Express Edition applications and so on. I hope you learned a lot and that you really benefited from this series. If you enjoyed the series, please visit www.LearnVisualStudio.NET to download over 500 screen-cam video tutorials just like this one on all topics pertaining to .NET, including C#, Visual Basic, ASP.NET and much more.

Video Details

Duration: 1 hour, 5 minutes and 58 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 6
Posted by: neudesicasp on Sep 26, 2013

SQL Server beginners will walkthrough the entire workflow of creating a database driven Web application and use visual tools to quickly create an administration application.

Caption and Translate

    Sign In/Register for Dotsub to translate this video.