Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
Now let's look at assembling some of the pieces we're going to use to create our web application that will communicate with our SQL Server stored procedures. Here in Visual Web Developer, I am going to create a new website. I'd like to use the template--a C# template for an ASP.NET Empty Web Site. I'd like to drop that in our Exercise folder and we'll call it the AuthorsSite. So this gives me an empty solution with no data in there.
I'd like to add a new basic web form, so I'll right-click and say Add New Item, I'd like to add a web form and I'd like to call that web form, AuthorsPage. The machine will write some code for me, but that--I'm not going to use that. I've already written some code that should be in the exercise files. We'll look at the code real quick; we have some styles that we're setting up. Two styles, one for the headings, and one for the body, and then we have a table, that's creating a space for a list of all active authors, and another space for us to create a form to insert new authors.
If we flip over to Design view, we can see this, our two headings and two empty spaces available and ready for us to put exciting stuff in there. Now I'd like to bring this home with something a little more practical. Obviously, users won't go directly to the database to update and insert records. More likely, they would go to an application or a website to manipulate the records in our database. So I am going to go ahead and create a small web application that connects to our database. It'll use some stored procedures and functions, and we'll see that if we take some time and effort to set up our stored procedures and functions correctly, then the level of effort and the amount of code we have to write on the website is very easy.
The next thing we'd like to do is create a connection between our web application and our database. We'll do this off of a Window called Database Explorer. Inside of Database Explore, the top option is Data Connections. We can right-click that and say Add Connection. Up there you'll need to give it the name of your SQL Server or the IP address. There is mine. We have the option to Use Windows Authentication of SQL Authentication. I'm going to stick with Windows and we will need to choose which database we're interested in; my database that we were working in previous examples, hit Test Connection, it says succeeded.
That's good. And now we see a new connection named thusly, basically it's the name of our server.name of the database. To open that up in Explorer, we see the tables we were working with previously. And we also see the stored procedures we worked with previously, and the functions we worked with previously. So we are very close to being able to run the stored procedures off of the web page.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.