How to Use MSFT SQL Server to Create and
Connect a SQL Database to a Visual Studio Project |
|
Step 1: |
|
In this tutorial, I used the SQL Server 2005 Express Edition since it is readily
available and free but one could also use the SQL Server 2005 Developer Edition
if one has it install on their computer.
Open SQL Server Management Studio and lets Add a Database that we will call "Students".
|
 |
|
|
|
| Step 2:
|
|
After creating the Database named "Students" let's create a table called
"Classmates" using a Query
|
 |
|
|
 |
|
|
|
After Entering the above code in the Query, click the "Execute" icon and we now
have a 5 column table named "Classmates". The 5 columns are obviously LastName,
FirstName, DOB, GPA, and ID. Because we made the ID our Primary Identity Key it
will auto-increment on it's own and will only have to worry about it on table updates
or edits. |
|
|
 |
|
|
|
We can now see our table called "Classmates". The other table called "Students"
was one I created earlier. |
|
|
|
| Step 3:
|
|
Let's now go to Visual Studio in a Project that I called "Create and Color
Table". Now in Visual Studio, Click on "Tools" then "Connect to Database"
|
|
|
 |
|
|
|
 |
|
|
| Step 4:
|
|
This brings up the "Add Connection" Dialog Box. The Data Source is Microsoft
SQL Server(SQL Client). Under Server Name Select "SQLEXPRESS", Log on to Server
select "Use Windows Authentication". Under the "Connect to a Database", the Database
"Students" should be listed. Click "Test Connection" to be sure you are connected. |
|
|
|
|
|
 |
|
|
 |
|
|
| Step 5:
|
|
Now go back to Server Explorer and highlight our new Database which on my computer
is listed as "hp6910p\sqlexpress.Students.dbo". In the properties window under the
"Connection String" we see our new connection string code. Click "Select All", then
"Copy". |
|
|
|
 |
|
|
| Step 6:
|
|
Take this Code and use it for your Connection String which I call connString. I
had previously created a Public SQL Connection in my Module called conn. Here we
initialize our connection string. I have included the code for the SQL Connection
and a Public Function GetDT that uses the connection to retreive Database Data below.
Remember to either import the System.Data.SqlClient namespace or add it as a reference
to your project. |
|
|
 |
|
 |
|
|
| Step 7:
|
|
Let's Add some Code to Textboxes to Add Data to our new Datatable "Classmates".
I have included the SQL Code Above which uses a Button called "btnAdd" to enter
new Data. Note we do not have to include code for ID variable. The Form below utilizes
Text Boxes to enter Data. |
|
|
 |
|
 |
|
|
| Step 8:
|
|
The Code above shows almost all of our code for our Main Form except for the "btnAdd".
Above we see the imported namespaces, variables, connection string and a Private
Sub called GetGrid(). This Sub obtains the Data from "Classmates" using a "Select
Statement" and the Function GetDT, adds the Last Name in a list box (just for show)
then fills a DataGridView called "grdStudents". Note that I made a new table called
myDT for this form only. Below is our Finished Product. Simple, huh!! |
|
|
 |
|
 |
|
|
|
Step 9: Adding Color to our Column Header |
|
For Fun, I added the Code to add Color to a Column Header if you wanted comething
other than that grey control Color. On a new Form add a new DataGridView that I
called Students. Using a new Subroutine I called Preload, we create a new table
called "Classmates". We add the Columns by Code, designate the First and Last names
as a UniqueConstraint, then use a new GetGrid Subroutine to obtain the Data as above
but this time our table is called DT, which in the bottom image is seen being declared
at the top of the form. We then set the classmates_table equal to DT, then add the
code to change the third colunms header background color to yellow. |
|
|
 |
|
|
 |
|
|
 |
|
| Please Contact Scott E. Pace MD
with any questions,comments or complaints. I will be happy to respond if I can.
|
|
![[Valid RSS]](Images/valid-rss.png "Validate my RSS feed")