Introduction
This article is an overview for connecting an ASP.NET website or a web application to a remote SQL Server database. This article also covers what is the Database class inside the ASP.NET library to help developers. Also, this post also covers how to create the XML notation for the configuration of websites to automatically connect to the database, through Database class objects.
Furthermore, this post would cover the basics about CRUD operations over database and how ASP.NET can allow the users to perform these actions over databases using SQL language and send the commands to the database (data source) to load data, edit data, create data and delete the data if no longer required. These actions are generally the short form for: Create, Read, Update, Delete (making up CRUD).
ASP.NET Database Object
In WebMatrix, a tool by Microsoft for the web developers to create ASP.NET web sites instantly, there was a special class designed for the developers to write less code to connect and perform other SQL commands on the database connected to their systems. This was written over .NET Framework, making all of the SqlConnection class code shorter so that the developer can only focus on the actual command instead of maintaining and sustaining the objects and the memory, etc.
Database object lets the users to simply call a function Open and connect to the database to perform tasks like SELECT, INSERT INTO and complete the procedures, etc. The actual work is done inside the web.config file where the settings and the connectionString for the database and the server (collectively the data source). The name of the database connection is set in that connectionString node in the web.config file which is then used inside the ASP.NET server-side code to open the connections for the database at that connectionString.
An example of this connectionString can be as the following code:
<connectionStrings>
<add name="MyDatabase"
providerName="System.Data.SqlClient"
connectionString="Data Source=.\SQLEXPRESS;
Initial Catalog=MyDatabase;
Trusted_Connection=true" />
</connectionStrings>
In the above code, the providerName is the name for the library (namespace) to be used to call operations on this database. Since I am going to use SQL Server, I will use this one. If you’re going to use any other namespace (library), then use that one. Inside the connectionString attribute, Data source is the address for the server, and the Initial Catalog is the name of the database I am going to use. Since I used Windows Authentication, that is why instead of User ID and Password, I am passing Trusted_Connection as a true field in the connectionString for the connection to my database.
The above connection is a simple XML object placed inside the web.config file of your ASP.NET database and provides an attribute for you to specify for your website. The important point to note here is that the actual name of your database (actual database file; SQL Server file) doesn’t need to be the same as your XML object’s name attribute. The interesting thing in this is that you can use any of the names inside the name attribute to be consumed inside the ASP.NET connection. But this doesn’t add anything to the security, but just a simplicity for the developers to not have to write the entire connectionString every time they have to write the code. But just to open the connection for the name that they’ve added inside the web.config file.
Using the Attachment
If you’re wanting to use the attachment that I’ve attached to this blog, then you can try first to download it and open it inside your IDE. You might consider creating a new solution and then adding these files to it.
Once done, you’re first required to have a database. You can name it as you want it to have a name, but the schema must be defined as this following schema of the table.
Edit your table to look like this, and name it ‘Products’
I am using SQL Server 2012, you can use your own version or download the SQL Server 2012 Express for development purposes and inside Management Studio, edit the table as the one above.
Once the table has been created inside your database server, you can add that database connection to your ASP.NET website, as in the connectionString setting above. Once the connection has been made, you will be able to query the database. If that doesn’t happen, then there is a problem inside your connectionString, try changing the properties of the connectionString to make it connect correctly. Possible issues might include passing a User Id and a Password instead of Trusted_Connection because of Database authentication mode and so on, possibly you will get your answers from the database engine (SQL Server; or what so ever data source server you’re using).
Performing Operations
There are 4 operations that we can perform for creating our applications, which include creating objects, reading their properties and data, updating them or deleting them once you’re done working with them.
Reading the Data
Reading the data from the server is the most basic and usually the first step in the application’s cycle. You can use the SELECT clause to pass the data from server down to the client.
// Select all the rows
var result = db.Query("SELECT * FROM Products");
<table>
<tr>
<th>ProductID</th>
<th>Name</th>
<th>Description</th>
<th>Price</th>
<th>Actions</th>
</tr>
@foreach (var row in result)
{
<tr>
<td>@row.ProductID</td>
<td>@row.Name</td>
<td>@row.Description</td>
<td>$@row.Price</td>
<td>
<a href="~/application?view=update&id=@row.ProductID">Update</a>
<a href="~/application?view=delete&id=@row.ProductID">Delete</a>
</td>
</tr>
}
</table>
The above code would render all of the data from the database, in my case, it gave me the rows that were present inside the table for the products. The following image depicts the example data.
Available products inside the database
Creating New Objects
Creating new objects can be another state of application in which the user is provided with a form to fill in to create a new object for the database. Most of the validation can be performed here, to make sure that the data is accurate and according to the policies and conditions designed by the DBA. But I am not going to go deeper into them, and I am just going to provide a simple example of saving the data inside the database.
Note: I am going to make a user of the Database object so there won’t be any SqlConnection andSqlCommand objects inside it.
if (IsPost)
{
db.Execute("INSERT INTO Products (ProductID, Name, Description, Price)
VALUES (@0, @1, @2)",
Request.Form["name"], Request.Form["description"], Request.Form["price"]);
Response.Redirect("~/application?view=read&message=created");
}
<form method="post">
<input type="hidden" name="type" value="create" />
Name <input type="text" name="name" /> <br />
Description <input type="text" name="description" /><br />
Price <input type="text" name="price" /> <br />
<input type="submit" value="Submit" />
</form>
Form rendered in the HTML form in the browser
In ASP.NET web pages framework, you can validly write the content inside the file as plain text and it will be rendered as valid HTML in the browser. Also, you can have the ProductID to be an Identity field making it easier to update the field every time a new record is added instead of updating the value yourself.
Once this code would execute, it will return you with the following window. Displaying all of the records in the database including the new row that was added to the database by the user while submitting the form.
5th row is the new row in the table that we added. It depends on your data, the data you pass to the table
Updating and Deleting the Data
Updating and deleting the data from the website doesn’t require any special web page to be generated (apologies, because you still need a form for the user to input the fields) for them to be worked. You can allow the user to enter the values that you want to get from him inside a form and then submit the data to the database for update purposes.
Update process of the data in the database can be run through this example code:
var row = db.QuerySingle("SELECT * FROM Products WHERE ProductID = @0", Request.QueryString["id"]);
if (IsPost)
{
db.Execute("UPDATE Products SET Name =@0, Description =@1, Price =@2 WHERE ProductID = @3",
Request.Form["name"], Request.Form["description"],
Request.Form["price"], Request.QueryString["id"]);
Response.Redirect("~/application?view=read&message=updated");
}
<form method="post">
<input type="hidden" name="type" value="update" />
Name <input type="text" name="name" value="@row.Name" /> <br />
Description <input type="text" name="description" value="@row.Description" /><br />
Price <input type="text" name="price" value="@row.Price" /> <br />
<input type="submit" value="Submit" />
</form>
The above form will allow the user to pass values to the database. A simple query was performed in order to let the user know which data he is going to edit and what is the previous data that is currently stored inside the database. Upon successful execution, the user would be redirected to the default page for results. User is not interested in knowing how, when and where the data is stored. All he wants to read is that the data was stored and updated in the system.
Delete command doesn’t even require a form so there is no need to write any other code. All you can do is run a simple query and the data would be removed from the database. Once that has been done, you can redirect the user back to the default page (read) for the user to continue his work.
var id = Request.QueryString["id"];
db.Execute("DELETE FROM Products WHERE ProductID = @0", id);
Response.Redirect("~/application?view=read&message=deleted");
A parameter (id) was passed in order to let the database know only to delete the objects with that particular condition. Otherwise, all of the records from the database would be removed.
Points of Interest
You can connect the ASP.NET applications and websites, not only to the databases present inside the directory of the website but to any other database, of whose authentication you can take care of, like server address, database name and credentials, etc.
In ASP.NET, you can provide any name for the database object to be used inside the web application, keeping your database’s actual name hidden.
CRUD operations are simple command operations on database, such as SELECT, INSERT INTO. But in a different manner. You can create conditions inside the forms to use and then execute the command over the database using the SQL language. Which would perform the CRUD operations on the Database. CRUD operations stand for: Create, Read, Update, Delete.
It is not necessary to create a page for the Delete and Update messages. You can just execute the commands and come back to the default page (the page to Read the data) and just show the message returned from the server.
Connecting to the remote database will take some extra time depending on the network connection since the connection will be established remotely, thus making the system a little heavier and connections a little slower. However, there is no difference in connecting the application’s own database or a remote database, assuming permissions do not come into action.
The provider for the connection of SQL Server is System.Data.SqlClient. For other database connections and types, their provider must be provided instead of SqlClient namespace. It is required by ASP.NET to create the connections to the databases. You can learn creating connectionStrings for ASP.NET applications for SQL Server from any of the following links:
MSDN and ASP.NET links have a +1 point for their clarity and their content.
History
- 10th January, 2015: Initial version
