Connecting New Microsoft Web Projects to a Database

By Dustin Christians on November 10, 2015

Connecting New Microsoft Web Projects to a Database

Every time I start a new .NET web application I forget a few of the steps involved in connecting to the database and inserting my first record and I usually end up searching for the information online. Because I needed to know the information so often I decided it would be nice to document the process. For example, I recently decided to create a web application that would help motivate my wife and me to pay off our mortgage early. The idea is to use the initial parameters of our loan to create an amortization table that includes every payment that will be made over the life of the loan along with the principal and interest that is paid each month. I wanted to add in two other parameters “extra payment” and “extra payment date” so I can track extra payments that are made. I am looking to show two points of data with each extra payment: one, how much interest is saved, and two, how many months have been shaved off the loan. For this blog post I will only be discussing the beginning of the project: the process I used to connect to the SQL Server database with Microsoft .NET so I could insert the four initial loan parameters shown in the screenshot below. The extra payments and extra payment dates will not be discussed.  

Now that we understand what is needed for this project I will begin explaining the process of connecting to the database and inserting the data. Here is a screenshot of the initial Mortgage parameters from the web application that I will be inserting into the database:

Image-1.jpg

To develop this application I connected the SQL server database to the web application using Visual Studio 2015 C# and .NET 4.0. I installed Microsoft SQL Server 2012 and created a “Loan” table in my “Finances” database with SQL Server Management Studio (SSMS).

The first step was determining what needs to be in the database connection string that will eventually be included in the project’s web.config file.  Instead of just writing the connection string in the web.config file from scratch, I decided to use Visual Studio to help me write it.  

From the Visual Studio toolbar, I clicked “Tools” followed by “Connect to Database…” to get to the “Add Connection” window:

Image-2.jpg

The first required parameter is the “Server name”.  I found the server name from the SSMS login screen:

Image-3.jpg

After entering the server name in the “Add Connection” window of Visual Studio I chose “Use SQL Server Authentication” and used an admin account I setup to access the database from my application. I setup the admin account in SSMS by right clicking the security folder under the server name and selecting “New” then “Login…”:

Image-4.jpg

Next, I added a new username called “admin”, selected the “SQL Server Authentication” radio button in the “Logon - New” window of SSMS shown below and entered in a password for the account. I selected “OK” at the bottom of the window and tested out the new user login to verify that is was setup correctly.

Image-5.jpg

Next, I was able to select the name of my database from the “Select or enter a database name” drop-down list in the “Add Connection” window of Visual Studio and test the connection:

Image-6.jpg

After verifying that I could connect to the database, I then copied and pasted the connection string generated by Visual Studio into my web.config file. To do this, first right click the database in Server Explorer of Visual Studio and select properties from the menu.

Image-7.jpg

From the properties window, select and copy the Visual Studio generated connection string:..

Image-8.jpg
 

Paste the connection string into the web.config file:

 

	

 

Next, I wrote a function in my ASPX code behind to insert the inputs into the database:
public void insertLoan(double loan, int term, double rate, double pmi)
	{
    	string sqlConnectionString = ConfigurationManager.ConnectionStrings["Finances"].ConnectionString;
 
    	string sqlQuery = string.Format("INSERT INTO Loan (Loan,Term,Rate,PMI,Date) VALUES(@loan, @term, @rate, @pmi, GETDATE())");
 
    	using (SqlConnection conn = new SqlConnection(sqlConnectionString))
    	{
        	using (SqlCommand cmd = new SqlCommand(sqlQuery))
        	{
            	cmd.Connection = conn;
            	cmd.Parameters.AddWithValue("@loan", loan);
            	cmd.Parameters.AddWithValue("@term", term);
            	cmd.Parameters.AddWithValue("@rate", rate);
         	   cmd.Parameters.AddWithValue("@pmi", pmi);
            	try
            	{
                	cmd.Connection.Open();
                	cmd.ExecuteNonQuery();
            	}
            	catch (Exception e)
            	{
           	     Console.WriteLine(e.ToString());
            	}
            	finally
            	{
                	cmd.Connection.Close();
            	}
        	}
    	}
	}

After pushing the submit button I checked the “Loan” table using SSMS to see if the new data had been inserted into the database:

Image-9.jpg

I was successfully able to access the database and store the loan parameters. After verifying these initial steps I can begin to expand the application. Every time I start a new .NET web application I forget a few of these steps, which causes problems later on. Besides being able to reference this post when creating projects in the future, I hope other developers will find this documentation useful as well.  
 

 

Share This Post:

Twitter Pinterest Facebook Google+
Click here to read more Tutorials posts
Start a Project with Us

About the author

Dustin has gone from satellite communications technician to computer engineer to web developer. He has always been excited about the prospects of technology; all that can be explored and discovered with it fascinates him. When Dustin is not developing software you can find him studying philosophy and preparing for another sailing or skiing adventure with his wife, Molly.

View other posts by Dustin

Subscribe to Email

Enter your email address to subscribe to the BizStream Newsletter and receive updates by email.

‚Äč