How to: Database

Database Basics

A database is the most common way to store vast amounts of data. In theory a developer could just update a website with HTML and CSS to display information. In practice, this is quite rare as developers make websites for those who are not. Storing information in a database allows nondevelopers to manipulate the website without a developer. The developer is required to set up the website and database so that the nondeveloper can operate their website.

There are four distinct features of a database that allow a website to be interactive and accessible to all levels of users: create, read, update, and delete (CRUD). Databases allow users to create data to be stored into a database. When you are registering for a mailing list, you are inserting a row into the database with your information. Users can read the contents of a database. When you're shopping online or an admin is looking at their list of users, you are viewing the results of SELECT statements of database contents. Updating your information should be readily available should information change. When you are editing your profile on a website, you are executing UPDATE statments behind the scenes. Finally, databases allow users to delete information. If you've deleted a profile in the past, the database is using DELETE statements to remove those rows. Not all organizations do this, though. Facebook gives you the option to "reactivate" your account. That likely means that Facebook does not delete your profile's row in the database, but rather just changes a field to "inactive" so your information is retained for anaylsis and recovery.

Before you continue, I recommend reviewing a good amount of SQL commands as it will not be covered here. You may also want Server Explorer open for the duration. To open Server Explorer go to View > Server Explorer.


Setting up the database

After you have initialized a web form project, go to Solution Explorer and right-click App_Data. Add a new item. Under Data select SQL Server Database and name the database. Take a look at Server Explorer and you should see your new database there.

Make a new database

A database isn't without tables so right click your database and add a table.

Adding a table to database

Here is where you define a table. What columns it will contain and the data types are defined here. Don't forget to name the table or Visual Studio will just name it "Table".

Define your tables

If you want the id column to start at 1 and automatically increment by 1 every time a row is inserted, right click on the id row, select Properties, go to Identity Specification. If Identity Specification is false, then set it to true. Here you can choose what number id should start at and by how it should increment when each row is added.

Once you have defined your tables, it is important to click on the Update button to the top left of the table and then click Update Database so your database changes can be committed. The Generate Script option creates the SQL script that you can then use in other databases.

If successful, the Server Explorer should should your newly create table(s) and their columns.

Database with table

You can add values directly to a database by right clicking on a table and clicking Show table data.

Show table data

Before you are finished with your database, right click on your database and click properties. One of them is called Connection String. You will need this string to connect the database to your application so copy and paste it in a separate file for clarity. Go to Web.config in Solution Explorer.

The view of the Web Config, an xml file

What you are looking at is an xml file. Xml files are text files with tags that store text in a meaningful way that can be retreived across many mediums. HTML is itself from XML adapted for web. Your browser interprets a predefined set of tags as elements to transform plain text into more styled, meaningful text. For example, <b></b> is interpreted by the browser as bold text.

Between the <configuration> tags, add an opening and closing <connectionStrings> tags. Inside <connectionStrings> tags add a <add> tag like so:

            <add name="[give a name]" providerName="System.Data.SqlClient" connectionString="[connection string from database properties]" / >
            

The add tag requires a name, which you can define yourself, a providerName attribute which for our purposes will be "System.Data.SqlClient", and the connection string you copy and pasted earlier. If you just copy and paste the connection string into connectionString attribute it will not work. Make sure there are no quotation marks inside the connection string. The connectionString attribute should begin and end with a set a quotation marks and no more.

Once you are finished, create a Web Form page.


Read

ASP.NET offers many ways to connect and manipulate a database. What I'm demonstrating here is just one of those many ways.

The database requires a place for output. In your HTML, create an ASP GridView element like so:

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

Before we can continue with the C# code, we need to tell .NET to include code .NET has already prepared for us that handles database operations. By default, these are not declared for you at the top of your C# code files so we'll need to declare them like so:

Include code to handle database operations

For basic database interaction, we need to use System.Data.SqlClient to interact with the database and System.Configuration to use the Web.config file we just altered.

In your C# code behind, let's use the Page_Load function to display the database details once the page loads.

Before the Page_Load function, store the connection string in a string variable. C# uses something called ConfigurationManager that can store your connection string that you added to Web.config using the name of the connection string you defined. This allows you to change the connection string in Web.config without having to declare it again in your C# or HTML code.

Connection string stored in a variable

Now define how the contents of the database will be shown when the page loads. Note that in this example there is label in the HTML informing the user if the database has rows.

Function to display database results

With success you should see the contents of the database in table form.

A successful read of the database

Create

Let's make a mini student repository of Humber College where the user can insert, delete, and update students with the results updated promptly on the screen. Students have a first and last name, a student number, an age, and a gender. Make a table in the database containing those rows.

Set up the HTML where the user can provide information to be inserted into the database like so:

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

                <h2>Enter a student</h2>
                <div>
                    <asp:Label ID="lbl_first_name" Text="First name: " runat="server"></asp:Label>
                    <asp:TextBox ID="first_name" runat="server"></asp:TextBox>
                </div>
                <div>
                    <asp:Label ID="lbl_last_name" Text="Last name: " runat="server"></asp:Label>
                    <asp:TextBox ID="last_name" runat="server"></asp:TextBox>
                </div>
                <div>
                    <asp:Label ID="lbl_number" Text="Student number: " runat="server"></asp:Label>
                    <asp:TextBox ID="number" runat="server"></asp:TextBox>
                </div>
                <div>
                    <asp:Label ID="lbl_age" Text="Age: " runat="server"></asp:Label>
                    <asp:TextBox ID="age" runat="server"></asp:TextBox>
                </div>
                <div>
                    <asp:Label ID="lbl_gender" Text="Gender: " runat="server"></asp:Label>
                    <asp:DropDownList ID="gender" runat="server">
                        <asp:ListItem Text="M"></asp:ListItem>
                        <asp:ListItem Text="F"></asp:ListItem>
                    </asp:DropDownList>
                </div>
                <asp:Button ID="btn_submit" Text="Submit" runat="server" OnClick="btn_submit_Click" />
                <asp:Label ID="lbl_message" runat="server" Text=" "></asp:Label>
            </form>
            

Assign an OnClick event to the button. In this case, btn_submit_Click in the C# code will execute when the button is clicked.

A function to insert data into the database could look something like this:

            protected void btn_submit_Click(object sender, EventArgs e)
            {
                //set variables
                //Validate user input
                //check if all values were provided
                if (string.IsNullOrWhiteSpace(first_name.Text) || string.IsNullOrWhiteSpace(last_name.Text) || string.IsNullOrWhiteSpace(number.Text) || string.IsNullOrWhiteSpace(age.Text) || string.IsNullOrWhiteSpace(gender.Text))
                {
                    lbl_message.Text = "All fields required";
                    lbl_message.ForeColor = System.Drawing.Color.Red;
                }
                else
                {
                    fName = first_name.Text;
                    lName = last_name.Text;
                    sNum = number.Text;
                    sAge = age.Text;
                    sGender = gender.Text;

                    //string query = "INSERT INTO students(first_name, last_name, number, age, gender) VALUES('" + fName + "', '" + lName + "', '" + sNum + "', '" + sAge + "', '" + sGender + "');"; //REMEMBER TO ADD SINGLE QUOTES
                    string query = "INSERT INTO students(first_name, last_name, number, age, gender) VALUES(@fName, @lName, @sNum, @sAge, @sGender);";
                    SqlConnection conn = new SqlConnection(cs); //connecting to the database
                    SqlCommand cmd = new SqlCommand(query, conn); //using the SQL query on the connection into a SqlCommand object I'm calling cmd
                    cmd.Parameters.AddWithValue("@fName", fName); //replacing @values with user values 
                    cmd.Parameters.AddWithValue("@lName", lName); // C# will look at our variables as references instead of using them directly as SQL commands to prevent SQL injection
                    cmd.Parameters.AddWithValue("@sNum", sNum);
                    cmd.Parameters.AddWithValue("@sAge", sAge);
                    cmd.Parameters.AddWithValue("@sGender", sGender);

                    conn.Open();

                    int rowsInserted = cmd.ExecuteNonQuery(); //using the SqlCommand variable rowsInserted is storing the number of rows inserted into the database. 
                    //We can use this number to find out if a row has been inserted

                    if(rowsInserted == 1)
                    {
                        lbl_message.Text = sNum + " registered";
                        lbl_message.ForeColor = System.Drawing.Color.Green;
                    }
                    else
                    {
                        lbl_message.Text = sNum + " failed to register";
                        lbl_message.ForeColor = System.Drawing.Color.Red;
                    }

                    conn.Close();
                }
            }
            

There are some important things about this function. Notice that I've commented out the first query string. That's because if we directly use user input for SQL queries this can lead to SQL injection. If the user were to type in

; DROP TABLE students;
then the INSERT statement wouldn't work but the DROP TABLE one will. To keep the database secure, we must provide substituion values in our SQL statments instead. In C# @[name] is used. C# will process these values as something to the referenced later on. After you have assigned your SqlCommand variable, you can reference your subsitution values using the .Parameters property and the .AddWithValue("@[name]", [string to reference]) method.

Using ExecuteNonQuery() on our SqlCommand variable returns an Int number on the number of rows that have been inserted, updated, or deleted. This is a number greater than 0. In our case, it's going to return the number of rows inserted into the database. If 0 rows were inserted, then I'll let the user know that their submission was not entered into the database. If 1 row was inserted, I'll let the user know that their submission was entered into the database.


Update

When you update you want all details to be present. Some information will change, others will not. In practice, the user would be redirected to another page with all the user's info presented. For this instance, let's upload student details by student number, then clicking a button that will output database details into the input fields. First, add an HTML button with an OnClick function that will take database information and put them into the appropriate input fields ready to be edited:

HTML button that uploads student info

And the function that will output database details into the input fields:

C# function that grabs DB info

Notice that instead of ExecuteNonQuery(), ExecuteReader() is used instead. We are not altering database contents yet, just reading those contents. The Read() method is then used on the variable containting ExecuteReader() (our variable is called reader in this case) which will return true or false if query used to find a row in the database is succesful or not. Using ExecuteReader() and Read() also allows us to grab specific column values with the name of the columns in [] brackets in "". For example, to grab the result of the column first_name, we will have to use ["first_name"]. Because the ASP HTML elements have the Text attribute, we can take individual values from the DB and assign Text to those values which will then be outputed on the screen in HTML.

Now that the user has current information from the database, we can alter that information. Ideally, one property should stay unchanged. Let's keep student number as an unchangable value and use that as a reference in SQL as to what row should be updated. Set up a button in HTML with an OnClick function that will update an exsiting database row:

HTML button that updates student info

And the function that will update database details based on the input fields:

            protected void btn_update_Click(object sender, EventArgs e)
            {

                sNum = number.Text.Trim();
                fName = first_name.Text.Trim();
                lName = last_name.Text.Trim();
                sAge = age.Text.Trim();
                sGender = gender.Text.Trim();
                if(string.IsNullOrWhiteSpace(sNum))
                {
                    lbl_message.Text = "Student number required";
                    lbl_message.ForeColor = System.Drawing.Color.Red;
                }
                else
                {
                    //string query = "UPDATE students SET first_name = '" + fName + "', last_name = '" + lName + "', age = '" + sAge + "', gender = '" + sGender + "'"; 
                    //The commented out query leads to SQL injection
                    string query = "UPDATE students SET first_name = @fName, last_name = @lName, age = @sAge, gender = @sGender WHERE number = @sNum;";
                    SqlConnection conn = new SqlConnection(cs);
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.Parameters.AddWithValue("@fName", fName);
                    cmd.Parameters.AddWithValue("@lName", lName);
                    cmd.Parameters.AddWithValue("@sNum", sNum);
                    cmd.Parameters.AddWithValue("@sAge", sAge);
                    cmd.Parameters.AddWithValue("@sGender", sGender);

                    conn.Open();

                    int rowsUpdated = cmd.ExecuteNonQuery();

                    if(rowsUpdated == 1)
                    {
                        lbl_message.Text = sNum + " successfully updated";
                        lbl_message.ForeColor = System.Drawing.Color.Green;
                    }
                    else
                    {
                        lbl_message.Text = sNum + " did not update";
                        lbl_message.ForeColor = System.Drawing.Color.Red;
                    }

                    conn.Close();
                }
            }
            

Notice that this function is not too different than the function that inserted rows into our database. The SqlCommand statement created an object of SqlCommand that updates an existing row based on the query provided and ExecuteNonQuery() is used to insert that information while determining if a row was inserted based on the Int number returned.


Delete

Databases have the ability to delete data from their tables. In this example you can enable deletion of data by the user. First, provide a button that on click of it will delete a row in the database.

A button that deletes database data

Sticking with a value that will be unlikely changed, use the student number as a reference for deletion. A function to delete a student by their number could look like this:

A function that deletes database data

A function to delete data from a database is not too different than a function that inserts data. The ExecuteNonQuery() method is still used and will return a number from 0 onwards. In this case, ExecuteNonQuery() counts the number of rows deleted from a database table so the returned number should be greater than 0 if successful deletion has occurred.

In practice, when you delete you'll want a record of that deletion in a separate table. You wouldn't want to assign to a new student the number of a previous student! Now when inserting a new student, with a deletion record in the database, you can check if a current student has that student number and if that new student number belonged to someone who is no longer a student.