Category Archives: Database

Using and learning the Databases.

How to connect ASP.NET website to Remote Database for CRUD operations

Download the attachment for this blog post

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 the 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 in done inside the web.config file where the settings and the connectionString for the database an 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, so 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.

Above connection is 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) does’t need to be the same as your XML object’s name attribute. Interesting thing in this is, that you can use any of the name 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 entire connectionString everytime 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.

ASP.NET CRUD Table properties

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 the 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>

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. Following image depicts the example data.

Products ASP.NET

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 just I am 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 and SqlCommand objects inside it. 

if (IsPost)
{
  // Save the data inside the Database...
  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");
}
 
// For the Create View
<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>
This is the form rendered in the HTML form in the browser.

This is the 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 everytime 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.

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 Deleteing 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");
}
 
// For the Create View
<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 successfull 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.

// For the Delete View
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.

http://www.connectionstrings.com/sql-server/

http://www.asp.net/mvc/overview/getting-started/introduction/creating-a-connection-string

http://msdn.microsoft.com/en-us/library/jj653752(v=vs.110).aspx

MSDN and ASP.NET links have a +1 point for their clarity and their content.

History

First version of the post.

Using a Database

Most of the websites now use a Database to save their data from where it can be accessed whenever needed. Lets take the example of Facebook, Google, Tumblr. and all other websites. They use a Database to use all the user related Text information.

The general example is the messages that you send to the friends on Facebook, posts that you share with your circles or the blogging posts that you share with followers on Tumblr. website.

Everytime you share the post there is a query execution on the server-side code which saves all this data to a database which is connected to the website.

I will assume you created the website using WebMatrix, StarterSite template. Lets continue the process.

Firstly go to the Database workspace, that is just at the left bottom of the WebMatrix. You’ll get to work with the Database without using any other software or management software for the database. That is why I love WebMatrix – All in one software.

You remember how we interacted with the form? Lets continue that job and save the text information that is shared with the website and save it to the Database table.

First, create a table inside the StarterSite database and name it as Forms. Go to database workspace –> Click on Create Table in the top banner and name it as Forms

Then write the First Column name as Id and select int for the database and check the boxes that are infront of it. To be clear and simple for you, the checkboxes that you are going to check mean that this column (Id) will be the primary column and its value will not match with any other of its own value, that’s why a userid of Facebook user is never similar to any other user.

Now name a new column as Text and set its datatype to nvarchar [max(4000)], nvarchar means n number or characters, where n will be the value in the (…). In this case the 4000 will be n.

A tricky thing here is what does 4000 actually mean?

This means that this column will contain a total number of 4000 characters, but the max term with it means a data of 2GB, since each character uses 2 bytes then this means that this column will accommodate upto 1 billion characters. That’s more like it isn’t it? But when you write a value without max, lets say 400 then only 400 characters will be written and others will truncate into a string and will be removed. First 400 will be saved and others will be discarded.

There are some other data types for Sql Server too. Some of them are, bit (which means true false) will only have 2 values, you need to write either True or False to it; it is a bool.

Image datatype is for binary data. You can save images to the database too, but that’s not a good idea for a developer. You can read all other data types here, they will guide you at best.

http://technet.microsoft.com/en-us/library/ms187752.aspx