Learning C# and .NET by Creating Simple Meeting Room Reservation System: Part 2

Displaying Data from Database

For this part, make sure you have SQLExpress and SQL Management Studio installed.

Open Management Studio and create a database called MRooms and a Table Rooms.

Here is the SQL command:

Create database MRooms;
GO
use MRooms;
create table rooms (
ID int IDENTITY(1,1000) NOT NULL,
Name varchar (10),
Location varchar (10),
Description varchar (100)
);

Fill the table with some values. You can use this query:

insert into rooms (Name,Location,Description)
values
('Room 1','1st Floor','Big room with 15 sits'),
('Room 2','2nd Floor','Small Room'),
('Room 3','3rd Floor','Managerial Meeting Room')
;

Ok, let's create a page and display the content of the Data.

On your Visual Studio, right click the MeetingRoom project in solution explorer and add new item.

Find Web Form with Master Page, assign a name Rooms.aspx and create the page.

Let's add GridView from toolbox to display the data.

Drag and drop GridView and expand the tiny arrow (right of GridView) and select add Datasource, select Database and create new connection, make sure you check create connection string.

Type . on the server name (it means use local server) and on the database dropdown, select the MRooms database you created earlier. Click next all the way.

That’s it, hit run and see the data displayed.

But how did the data get loaded and where is the connection code? The magic happened when you create a data source above and proceed with default configuration, you’ve added a connection string which describes what and where the database is and how to connect to it (you used default Windows authentication method) and you have also added a query to select all rooms. So it used that query and the connection string to fill your data.

Let's see how we could have done it manually. (If you used database in WindowsForm (VB or C#), you may know this already.)

Go to rooms.aspx source view, delete all the content (generated when you drag drop the grid view inside <asp:Content> </asp:Content> tag. This will reset the page to empty page.

From toolbox, drag drop the GridView, you will see:

<asp:GridView ID="GridView1" runat="server"></asp:GridView>

Inserted.

Now go to code-behind (F7) and inside page_load function (which is called everytime the page is loaded).

Write:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings
                    ["MeetingRoomsConnectionString"].ConnectionString);
        //--- Select Query.
        string cmdText = "SELECT * FROM Rooms order by Id desc";

        SqlDataAdapter adp = new SqlDataAdapter(cmdText, con);
        DataSet ds = new DataSet();
        adp.Fill(ds);

        GridView1.DataSource = ds;
        GridView1.DataBind();

You will see it doesn't yet know objects like SqlDataAdapter, but if you add:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

on the top of the file, errors would be gone.

Again, you may have an idea of what Datasets and Data adapters are if you have done some Database connection before.

Quote:

Basically, Datasets are objects that holds data temporarily

Dataadapters are objects that can execute sqlQuery and Fill the Datasets with data.

You then use the Dataset as the Datasource for the GridView. Data Binding in this case means update the Grid UI whenever the dataset changes.

By the way, I am assuming you have already created a connection string named “MeetingRoomsConnectionString” to make sure to open web.config file and verify this line is there, if not, add it (that your connection string, see the Catalog (which means the Database, and Integrated security means Windows Authentication).

<connectionStrings>
  <add name="MeetingRoomsConnectionString"
  connectionString="Data Source=.;Initial Catalog=MRooms;Integrated Security=True"
    providerName="System.Data.SqlClient" />
</connectionStrings>

Hit run and you should see similar Data view to the previous one.

Adding New Room

Now we create another form for inserting new Room.

Right click on the project, add a new Item as you did before selecting webForm with master page, name it NewRoomForm.aspx.

Then from the toolbox, drag TextInput field , and drag two more TextInputs and a button. You can change the text of the button and other properties if you wish.

Double click the button (in designer view) and you will be redirected to the event handler function.

protected void Button1_Click(object sender, EventArgs e)
{
   SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings
   ["MeetingRoomsConnectionString "].ConnectionString);

            //--- Insert Query.
            string cmdText = "INSERT INTO Rooms 
            (Name,Location,Description) VALUES (@Name,@Location,@Description)";

            //--- Providing information to SQL command object about which query to
            //--- execute and from where to get database connection information.
            SqlCommand cmd = new SqlCommand(cmdText, con);

            //--- Adding parameters/Values.
            cmd.Parameters.AddWithValue("@Name", TextBox1.Text);
            cmd.Parameters.AddWithValue("@Location", TextBox2.Text);
            cmd.Parameters.AddWithValue("@Description", TextBox3.Text);

            //--- To check current state of the connection object. 
            //--- If it is closed, open the connection
            //--- to execute the insert query.
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            //--- Execute Query.
            cmd.ExecuteNonQuery();

            //--- close the connection.
            con.Close();
            Response.Redirect("/Rooms");
}

Also make sure you have added:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

at the top.

Run the app, insert something in the textboxes and click the button, you should see the new entry added.

…….

Calling the connection string, creating a command and executing every time when data access is needed is too much work. Fortunately, Microsoft has a better method. The Typed Dataset object, where redundant actions like querying, filling, inserting and updating are automatically generated based on the database.

So let's see how we use typed dataset. Right click the project and add new item. In the data section, you will see Dataset, name it MeetingRoomsDataset.xsd and click on the Server Explorer. Here, you will find your connection strings, if you can’t see it, you can create new connection by right clicking data connection and Add Connection, like last time, use . as a server and select MRooms Database.

Drag the table rooms into the dataset window, and save (Ctrl+S). Ok, now let's connect it with the datagrid view, open Rooms.aspx and on designer view, click the arrow at the top of the grid view.

Click add new Datasource, select object and click next and make sure you checked “show only data components” in the drop downs, you will see roomsTableAdapter (automatically generated), select that.

Clear page-load handler in your code-behind:

protected void Page_Load(object sender, EventArgs e)
        {
           //delete every thing in here
        }

Hit run and your data will be displayed. That means Microsoft has generated the code for us to connect with the database, open connection, execute SQL query, fill the dataset and bind it to GridView automatically. Cool!

So let's use this Typed Dataset to insert Rooms.

Open the MeetingRoomsDataset.xsd file you create earlier, right click on roomTableAdapter (bottom section of rooms table) -> Add query, next, select insert, click finish.

Open NewRoomForm.aspx and in the code-behind, clear everything on the button-click handler (the manual code we’ve added earlier):

protected void Button1_Click(object sender, EventArgs e)
        {
             // clear everything here,
        }

Above the function Insert:

roomsTableAdapter rta = new roomsTableAdapter();
 and inside the function
rta.Insert(TextBox1.Text, TextBox2.Text, TextBox3.Text);
            Response.Redirect("/rooms");

Also add:

using MeetingRooms.MeetingRoomsDatasetTableAdapters; 

at the top.

So the whole file should look like this:

using MeetingRooms.MeetingRoomsDatasetTableAdapters;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace MeetingRooms
{
    public partial class NewRoomForm : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        roomsTableAdapter rta = new roomsTableAdapter();

        protected void Button1_Click(object sender, EventArgs e)
        {
            rta.Insert(TextBox1.Text, TextBox2.Text, TextBox3.Text);
            Response.Redirect("/rooms");
        }
    }
}

You can run and test adding rooms. What you did now is replace all the manual code with just one line.

If it was 2008, Typed Dataset would be hot. Now there are better options like ADO Data Model and EF, but they require the concept of Object Oriented Programming. We will cover them later on in this series.

History

• 5^th February, 2020: Initial version