SQL Server Database Setup

This is part two of the SQL Server Database Setup. By now you should have the database successfully connected using Visual Studio 2010. However if you arrived at this page from a google search or just random clicking then use this link to start at the SQL Server Database Setup page. You can skip that section though if you don't need help setting up a database in Visual Studio 2010 as well.

In this lesson you will be able to enter in the web application below that will allow you to save Customer data information that is entered on forms into a SQL Server database. At the moment this will consist of using only basic Web Forms so that you can see how to transfer information from textfields into a table.

The reason I have opted to use Web Forms at this time is to stay current with the beginning curve. However much later in the advanced sections you can use XML, LINQ, WFC (Windows Communication Forms), etc to display your data as well.

As usual though if this section is a little too advanced, then I highly recommend starting with the beginning tutorial sections such as on the class page

I will also show a lot of visual step by step examples to help you get an overview of what is taking place in the code.

Before we dive into the C# code it is necessary to enter the xml code below which will setup the labels, textboxes, and buttons for the Customer Information screen. Notice though that this page is actually called AdminPage.aspx. That is because I initially wanted to create a section where the customer data can be edited by an administrator. However since this program is already complete, just use the same name for now to stay consistant with this lesson.

Attached here is a download for those who wish to skip the typing part.

First it is important to see what the screen will look like once the code has been entered.

programming with sql

Unfortunately I am not going to attempt to explain everything here since it uses basic web controls such as labels, textboxes, buttons, etc. There is also some html mixed in to create tables. However remember that these lessons are not necessary for the absolute beginner since I tend to try to present a learning curve to keep the intermediate users happy. So it is kind of a balance. Anyway you can always watch the beginner ASP.NET/C# tutorials in this section by clicking on this link.

Here is the XML code below.

sql programming course

programming learning

ms sql tutorial

advanced sql course

sql server education

where to learn code

how can I learn to code

learning database design

Next you will need to setup using statements that use direct calls to classes that allow you to use things like Web Controls, Http calls, Linq, MVC, etc. Just for now enter the using statements as listed below in this screenshot for the first part of this lesson. If anyone follows the path of ASP.NET's history these were one time known as include statements. In object oriented programming an include statement will implement other classes into your program.

Once again however the complete file is available here to download at this link. This file is for the C# section called AdminPage.aspxcs.zip.

sql programming language tutorial

Now you will setup the first class for this application. This class will manage the customer information data for our application. This class cannot be referenced by other classes since the method here is protected. Button calls usually do not usually allow instantiation of other classes. I'm not sure if it has been done, but since the basic defaults are setup for protected I will stay on track with how the default Button methods are setup in ASP.NET.

sql server data

Textbox reference

The first part of this method for Button3_Click is initializing variables that will be used by the database later. If you look closely at these variables for custid, first, last, etc. you will notice that they are being referenced by textboxes. So what you are looking at is how to store data from a textbox in a variable. As an example if you type in 789 in the Customer ID field then the variable called custid will be equal to 789. This is because txtCustID.Text = "789". The same thing will happen for the remaining variables. They will contain the data entered in those corresponding fields into the textbox that each one is assigned to. It is always a good idea to name a textbox something close to what its' purpose is for so the program can be understand by other developers.

Validating fields

Now examine the next section that says // Validate fields. This part is checking to make sure that something has been typed into the textboxes that were setup above. For this demonstration I am only concentrating on validating the textboxes pertaining to basic customer information.

The if (first == "" is checking to see if the field for first (which is the textbox where it says Name) is blank. The next part that shows || is the operator called OR. If you have some basic programming knowledge then you will know that this allows a comparison of a series of condition statements. In this example it is going from one field to the next until all of the customer information fields have been evaluated.

For those who need a refresher course with conditions you can visit that page here .

The next part that shows Response.Write("<script>alert('Please enter data in the fields.')</script>"); is creating a java call to a messagebox that will display a message if information is not entered into the textboxes. By using the <script&qt; tags you can embed java scripts directly inside ASP.NET.

SQL Server Database Connection

The next line for few lines below setup a call to intialize the SQL Server database. If you haven't seen that part already on this site you can visit that section by clicking on this link. Here is a screenshot of the database setup.

free sql training for beginners

Try/Catch Error Validation

The else part of this section is checking to see if the textboxes have data entered in them and if so then it proceeds to the next part for setup the database. This is done by using the command try. This then goes into something called a try/catch block that will execute code based on a number of specific conditions. So if the database was successfully connected to the correct table then it will use a SQL command called INSERT that will insert records into the database called CustOnlineInfo. The INSERT CustOnlineINfo then will use columns that will transfer information into the correct parameters to reach the database.

Also please note that the smaller screenshot is the continuing statement line where it shows dataCommand.CommandText("INSERT CustOnlineInfo (. It was too long to fit in one part of a screenshot so I was left with no choice except to add it below.

free sql online training

free online sql database

I will show you a screenshot of the SQL Server database that I setup to receive these fields.

Also if you need a quick recap to open the tables in SQL Server you will first click on the Server Explorer tab on the left in Visual Studio 2008/2010. Then you will look for the database you setup or the example I created for stephen-pc\sqlexpress.aspnetTest.dbo.

online programming course

After this open up the Tables folder by clicking on the white arrow. Then you should see the table created called CustOnlineInfo. To view the data that was inserted into this table you will right click on the table called CustOnlineInfo and click on Show Table Data.

I have also obscured some of the table data since I used my own personal information in this example.

sql server for beginners

SQL Server Parameters (for Security)

Notice the lines that show (CustID, FirstName, LastName, DOB, SSN, Address, etc . These are the correspond to matching columns in the CustOnlineInfo table. The dataCommand.Parameters. AddWithValue are used to setup a security level for the database to prevent SQL injection attacks. A SQL injection attack can happen if someone is trying to breach the security of your database by manipulating information put into textboxes.

online it training videos

Updating Parameter fields

The next part where you see @"custid;" is the custid variable that was setup earlier. The same thing goes with all the other remaining variables that show first, last, dob,ssn, and so on.

The command dataCommand.ExecuteNonQuery() is how ASP.NET handles SQL commands that you call within an application. Just know that you will always usually need to include this command in when using SQL statements.

The next line catch (Exception ex) is used to capture any errors that occur in the application. If one is intercepted then the Response.Write message will print out an error message to the browser window.

The last part for finally executes each time you run through a try/catch statement whether or not an error occurs. It is basically used for clean up, messages, and closing connections. So directly below this the lines that show dataConnection.Dispose() and dataConnection.Close() clean up the database and close all the connections.

I hope you enjoyed this brief tutorial on the SQL Server Database example. I look forward to expanding this section much later and hope to see you back here.

Questions? Ask Here

Subscribe to the Programmermind newsletter
Privacy Notice: Programmer Mind will not compromise your privacy