Adding a Database
To demonstrate how to increment a value using a simple SQL command, we will need a simple web site with a database. At this point I have created a new ASP.NET Empty Web Site. Next, we need to add a database with a small table in it. To do this:

  1. Right click the project in your solution explorer.
  2. Select add ASP.NET folder.
  3. Select App_Data.
  4. Right click the App_Data folder in your solution explorer.
  5. Select add new item…
  6. Select a Sql Server Database.
  7. Click add.
  8. Expand the Database.mdf folder.
  9. Right click the Tables folder.
  10. Select add new table.
  11. Add the following columns with their respective types to the table:
    Column Name  Data Type 
     ID  int
     Number  int
  12. Right click the ID column and select Set Primary Key.
  13. Check the IsIdentity property of the ID column to true.
  14. Save the table as ‘Table1′.

Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.

Next, we need to add a row to the table that will hold the number we want to increment. To do this, add the folowing data to Table1:

 ID  Number 
 1  0

Adding the ConnectionString
Now that we have a database setup we need to add a connection string to it. To do this, open up the Web.Config file for editing and add in the following between the <configuration> and <system.web> tags:

Code Block
Web.Config

The connection string to our database.

<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

I just signed up at Server Intellect and couldn’t be more pleased with my fully scalable & redundant cloud hosting! Check it out and see for yourself.

Adding the Default.aspx Page
Next, we will need to add a simple web form that has a label on it to show the number in the database and a button to call the SQL query to increment it. To do this:

  1. Right click the project in your solution explorer.
  2. Select add new item…
  3. Select a web form.
  4. Name it ‘Default.aspx’.
  5. Click add.
  6. Open Default.aspx up to design mode.
  7. Drag and drop a label onto the web form.
  8. Add a break line under the label.
  9. Drag and drop a button under the label.

Incrementing the Value
Next, we need to add some C# code that will actually increment the value in our database when the button is clicked. To do this, double click the button to generate the Button1_Click event method in the Default.aspx.cs code behind file. Then at the top of the class add in the following using statements:

Code Block
Default.aspx.cs

The using statements we will need.

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

Next, add the following code to the Button1_Click event method:

Code Block
Default.aspx.cs

The Button1_Click event method.

protected void Button1_Click(object sender, EventArgs e)
{
    //INCREMENT THE NUMBER//
    //connect to the db
    SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    //the command to increment the value in the Number column by 1 in the row we added
    SqlCommand cmd = new SqlCommand("UPDATE Table1 SET Number = Number+1 WHERE ID=@ID", conn);
    cmd.CommandType = CommandType.Text;
    //update where ID is 1
    cmd.Parameters.AddWithValue("@ID", 1);

    using (conn)
    {
        //open the connection
        conn.Open();
        //send the query to increment the number
        cmd.ExecuteNonQuery();
    }

    //DISPLAY THE NUMBER//
    //connect to the db
    conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    //the command to select the row from the databse where the Number is that we incremented
    cmd = new SqlCommand("SELECT * FROM Table1 WHERE ID=@ID", conn);
    cmd.CommandType = CommandType.Text;
    //select where ID is 1
    cmd.Parameters.AddWithValue("@ID", 1);

    using (conn)
    {
        //open the connection
        conn.Open();
        //send the query and store the results in a sqldatareader
        SqlDataReader rdr = cmd.ExecuteReader();

        if (rdr.Read())
        {
            //set the value in the Number column of the row we selected to our label
            //to display the new number and ensure that it is being incremented
            Label1.Text = rdr["Number"].ToString();
        }
    }
}

We used over 10 web hosting companies before we found Server Intellect. Our new cloud server was set up in less than 24 hours. We were able to confirm our order over the phone. They responded to our inquiries within an hour. Server Intellect’s customer support and assistance are the best we’ve ever experienced.

This will increment the number in the Number column, and then set our label to the value of that number in our database.

Test
To test this out simply load up the web site and click the button a few times to ensure that the number in your database is being incremented and displayed in the label properly.
incr num sql asp4 csharp