SQL Server Database Setup
This page is an introduction to setting up a SQL Server database using Visual Studio 2010. You can also use Visual Studio 2008/Web Developer to do the same thing, but the procedures may vary on each.
First open up Visual Studio and then in the left hand column you should see a tab that shows Server Explorer. Here is a picture of what that looks like. Please note that under Data Connections you may not see anything there unlike my example, but that is perfectly fine.
Setup new SQL Server database
Okay next right click on Data Connections and select Create New SQL Server Database. Once you have done that you should see a picture similar to the following.
After this you will setup the database information. In the Server name field type in .\SQLExpress. Below that in the field for New database name type in mynewdatabase or whatever you want the name for your database to be and then click the OK button. Here is another screenshot.
Once this is complete you should see your new database. On my screen it shows stephen-pc\sqlexpress.mynewdatabase.dbo in the Server Explorer window. Yours may vary, but that is alright. Then look at the section that says Tables. Right click on it and select Add New Table. You are now ready to enter your column names that will be recognized by a SQL query later. Time for another screenshot.
Enter column names into a table
Now you are going to choose your column names. Since I am creating a simple order form for a customer order my column names will consist of CustID, FirstName, LastName, Address, City, State, Zip, and Phone. You can use whatever column names you want according to your needs. This is actually a table you are creating that will be used by SQL command farther down the page. In the Data Type column make sure to select something that matches the column data. As an example CustID is a primary key and is going to be a number value of int (integer, which means it is a number). All of the other columns are characters (consist of no numbers) and will use varchar which allows them to be used as character data. The number in the field for varchar represents the length of the column name. So in the column name State it shows varchar(2) because states are usually recognized by their abbreviation, such as AZ. The last column for Phone shows varchar(10) because with an area code included a phone number will contain 10 digits unless of course you have something like (602)-555-1212. If that were the case you would change it to 14 characters. Count the characters and you will see what I mean. Again here is a new screenshot.
It is time to save your table. Just click on the save icon on the Visual Studio toolbar or you can also click on the menu File - Save All. A dialog box opens requesting the name for the table. In my example I used tblMyOrderForm, but once again feel free to create a table so that it reflects your own needs. Once you are done click the OK button. Here is another example image.
The table is created and can be viewed in the left. Remember to view your database and table information just click on the Server Explorer tab on the left. You can also open this by clicking on the menu View and then Server Explorer in Visual Studio. Once you have Server Explorer open you should see rows for Database Diagrams, Tables, Views, Stored Procedures, Functions, Synonyms, Types, and Assemblies. Click on the Tables tab and then on the tblMyOrderForm (or whatever you named it to) and it will populate to show the column names you created earlier. Take a look here.
Entering your data information
You may be wondering how the data will be entered into the fields. I asked myself the same question when I was first learning this. So to enter in data into the column fields right click on the Server Explorer. Next click on the tab that shows tblMyOrderForm and select Show Table Data. Your screen should now show something like this.
When entering your data for the columns you can copy this example or create your own information. After all this is your database so model it after your own demands. The red exclamation marks you see there just means that the data has not yet been commited to the database. It is nothing to be concerned about. For the CustID just be sure to enter numbers only. After this make sure to save your work. Here is what you may see.
Creating a Stored Procedure
Next you will created a Stored Procedure so you can see a query in action. Right click on the tab called Stored Procedures and select Add New Stored Procedure. The NULL means that field has no data received in it. However in the next part you will be defining the data for these fields. Once you have accomplished that you see see something similiar to the following below.
Please note this is the default for a Stored Procedure, but for simplicity I am going to change this so we can create a simple query. So go ahead and erase all the information on this screen. You can manually erase this or also click on the menu Edit and choose Select All. This will highlight everything in blue. Then just press any key to clear the screen.
After this you will start to create a new Stored Procedure. Be sure to copy the information below exactly as I have typed it. If you try changing the Procedure name where it says CREATE PROCEDURE dbo.StoredProcedure5 you may receive an error message. Also make sure that your statement for Select * from tblMyOrderFormM shows the name you selected earlier for your table. So if you called it mytable just change it to show Select * from mytable. Time to check a picture for accuracy again. Once you save the data you may also notice that it changed a little, but this is nothing to worry about.
Execute SQL Query
Okay it is time to finally see what your query looks like. Hopefully you have selected the correct table name and entered all the data into the fields in the section for Entering your data information to prevent any unexpected errors from creeping up. Please note the * in the query means to select all the fields in this table.
Now if your screen is minimized like the example below you may have to scroll it over to see the query results. To do this look in the section that shows Output window below. You may see something like Running [dbo].[StoredProcedure5] or something close to that. Drag the scroll bar at the bottom of the Visual Studio editor to the left and your data fields should appear. Here is what my screen capture showed.
Once you have scrolled the screen to the left you will see your data (column) fields. I was not able to show you all the fields at once since they are spaced pretty far apart. However just know that once you are seeing this you have successfully created your first query in SQL using a Stored Procedure.
You now have the knowledge to build a database using SQL Server and run a query. Therefore I am now highly recommending to check out my SQL Page here. That will walk you through how to create your own queries and do all kinds of cool stuff. I hope you enjoyed this lesson because I know I had fun creating it.
Questions? Ask Here