Programmatically Using SqlDataSource

The SqlDataSource control enables you to use a Web control to access data located in a relational data base, including Microsoft SQL Server and Oracle databases, as well as OLE DB and ODBC data sources. You can use the SqlDataSource control with other controls that display data, such as the GridView, FormView, and DetailsView controls, to display and manipulate data on an ASP.NET Web page, using little or no code.

protected void PopulateComments(string recordNo)
{
    //Connection to database
    string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    
    SqlDataSource sqlDataSource = 
        new SqlDataSource(connString, "SELECT [CommComments], [RelComments], [ManageComments], [RoleComments], [PersonalComments] FROM [tblSurveyResults] WHERE ([SurveyID] = @SurveyID)");

    sqlDataSource.SelectParameters.Add(new Parameter("SurveyID", System.TypeCode.Int32, recordNo));
    
    System.Data.DataView dv = (System.Data.DataView)sqlDataSource.Select(DataSourceSelectArguments.Empty);
    
    //Output Comments to label controls
    lblQuestion1Comments.Text = dv[0][0].ToString();
    lblQuestion2Comments.Text = dv[0][1].ToString();
    lblQuestion3Comments.Text = dv[0][2].ToString();
    lblQuestion5Comments.Text = dv[0][3].ToString();
    lblQuestion4Comments.Text = dv[0][4].ToString();
}
 

As you can see, the data is output from the database in little code. I believe this is far more flexible compared to dragging a dropping a SqlDataSource onto the page. The above code queries the database and then passes the selected fields to a DataView. You can then output each individual column data where you want. In this case, I have output the data to labels:

//Output Comments to label controls
lblQuestion1Comments.Text = dv[0][0].ToString(); //Output CommComments column data
lblQuestion2Comments.Text = dv[0][1].ToString(); //Output RelComments column data
lblQuestion3Comments.Text = dv[0][2].ToString(); //Output ManageComments column data
lblQuestion4Comments.Text = dv[0][3].ToString(); //Output RoleComments column data
lblQuestion5Comments.Text = dv[0][4].ToString(); //Output PersonalComments column data

Simple Way To Use A DataTable

When I normally create a datatable, I use quite a few lines of code just to add columns and input data into my DataTable.  But recently I have been using the following method use a DataTable in my code. The following function validates RadionButtonLists that I have in my page by passing the RadioButtonList size (rblSize) and RadioButtonList name (rblName).

//Function: Validate RadioButtonList for each question group
bool GroupValidateRBL(int rblSize, string rblName)
{
    bool validateOutput = true;
    int counter = 1;
   
    //Create DataTable object
    DataTable dt = new DataTable();

    //Add columns to DataTable
    dt.Columns.Add("No");
    dt.Columns.Add("RadioButtonName");
    dt.Columns.Add("Checked");

    while (counter <= rblSize)
    {
        ContentPlaceHolder cph = (ContentPlaceHolder)Page.Master.FindControl("ContentPlaceHolder1");
        RadioButtonList rbl = (RadioButtonList)cph.FindControl(rblName + counter);

       //Add Rows to Datatable
       dt.Rows.Add(new string[] { counter.ToString(), rblName, rbl.SelectedIndex.ToString() });
       counter++;
    }

    //Iterate through all rows in the DataTable to find all RadioButtonLists that have not been selected
    foreach (DataRow dr in dt.Rows)
    {
        if (Convert.ToInt32(dr["Checked"]) == -1)
        {
            validateOutput = false;
            break;
        }
    }
   return validateOutput;
} 

The DataTable in the function above stores a list of RadioButtonList entries and then iterates through each row in the DataTable to see if they have been selected. The data is entered into the DataTable by the following line:

dt.Rows.Add(new string[] { counter.ToString(), rblName, rbl.SelectedIndex.ToString() });

Looping Through RadioButtonLists

At the moment I am working on a Survey that contains tonnes of RadioButtonList controls that users will use to respond to the numerous questions I have on the website. All was going well until I thought about how I would find a quick and efficient way to enter all the RadioButtonList entries into my SQL database without having to do the following:

_rblBLL.InsertResults(1, rblist1.SelectedValue);
_rblBLL.InsertResults(2, rblist2.SelectedValue);
_rblBLL.InsertResults(3, rblist3.SelectedValue);
...
...
...
...

(InserResults() is my BLL function)

As you can see above, this is indeed a long winded way of inserting all my RadioButtonList entries to the database.

But I found a better way by using the FindControl() method:

for (int i=1; i < questionNo; i++)
{      
    RadioButtonList rbl = (RadioButtonList)this.FindControl("rblist" + i);

    if(rbl != null)
    {
         _rblBLL.InsertResults(i, rbl.SelectedValue);
    }
} 

As you can see, I created a for loop that will iterate through all the RadioButtonLists by incrementing the end value of RadioButtonList ID. So you will have to adopt a naming convention that has a number at the end as shown I have used above.

Last Post Of The Year

logo_bullguard Well this is my last post of 2007. Even though I have not made many posts since I started this blog, hopefully next year will give me more time to pad this blog out.

Since I have created this blog entry, I might as well talk about something. Well a day or two ago my Bullguard Internet Security had expired and even though I have been meaning to purchase a new yearly license, it was not on my priority list. Normally, when your Internet Securty expires the only thing that you cannot do is get up-to-date virus definitions, which is not that much of a deal since your computer is still protected.

However, what I found with Bullguard was that the Virus Scanner,  Firewall, and Spam Filter was locked until I purchased my new yearly license. I found this to be quite annoying since I had to find an alternative Internet Security suite until my new version of Bullguard was posted.

This is only a minor grudge from a great piece of Intenet Security though!

Oh yeah! Happy New Year!!!

ASP.NET Login Authentication Problem

I was trying to create a Login page for my website in ASP.NET a couple of days ago and I was stumped that the following piece of code did not work:

private bool SiteLevelCustomAuthenticationMethod(string User, string Password)
{   
   bool boolReturnValue = false;
   DataTable dtAuthUsers = SomeBLL.GetUsers();
   if (dtAuthUsers != null && dtAuthUsers.Rows.Count > 0)
   {
       DataView dvAuthUsers = dtAuthUsers.DefaultView;
       foreach (DataRowView drvAuthUsers in dvAuthUsers)
       {
            if (User == drvAuthUsers.Row["User"].ToString() && Password == drvAuthUsers.Row["Password"].ToString())
            {
                boolReturnValue = true;
            }
        }
    }
    return boolReturnValue;
}

protected void LoginControl_Authenticate(object sender, AuthenticateEventArgs e)
{
    bool Authenticated = false;
    Authenticated = SiteLevelCustomAuthenticationMethod(LoginControl.UserName, LoginControl.Password);
    e.Authenticated = Authenticated;
    if (Authenticated == true)
    {
        FormsAuthentication.RedirectFromLoginPage(string.Format("{0}", LoginControl.UserName), false);
    }
} 

Now after numerous debug sessions on this code, I could not find a thing wrong with it. The correct Username and Password was getting parsed to the Database but still the 'SiteLevelCustomAuthenticationMethod' function was still returning a false.

What was causing this problem was actually quite simple (even though it had taken my a long time to solve!). Basically, in the User's table in my database had the following columns:

  1. User_ID ------> DataType: int
  2. Username ------> DataType: nvarchar
  3. Password ------> DataType: char

Now this table look alright doesn't it? Well it isn't. The problem lies within the 'Password' column. Since this column format is 'char' with a length of 25, when you enter a password that is less than 25 characters in length a space will be added after to fill out the data length. For example, if you had a password that was 10 characters long, an extra 15 characters of spacing will be added after your password.

In order to fix this problem, I changed the Password DataType to 'nvarchar', which solved the problem.

Broadband Speed Shock Horror!!!

A few weeks ago I was watching "The Gadget Show", which in the UK is a fun and informative show about gadgets (yeah the name of the show gave it away) and technology. Anyway, there was one episode which really got my attention. It was about the varied Broadband speed each household is getting around the UK. They believed that many people falling short of the "up to" speeds offered by their ISP, we're campaigning for the truth: advertised speeds reflecting what we're actually getting.

I decided to test their speculation out for myself by using one of these online "Broadband Speed Checker's". I used the following:

Get your own broadband speed check

How Does It Work?

The speed checker downloads a file from the server and measures how long your connection takes to download it. The size of the file will be different according to your line speed. The speed checker measures the speed at the time of the test so if your network is running slow at that time then speed checker will report a slow speed. This does not necessarily mean that your internet connection is slow at the other times.

My Findings

I currently have a 2MB internet conneciton speed that is supplied by NTL (well now under Virgin Media). I carried out the test and not surprisingly my connection speed was not 2MB it was around 1.5MB. I know that compared to other users, my connection speed is not too much of a reduction. But still I am quite angry that I am not getting exactly what I paid for.

Well what are you waiting for? Give it a go and you might be surprised that you didn't get what you paid for! Leave a comment and share your results.

WOW! Windows Vista is...

...quite slow! This is what I thought as I was setting up my mothers laptop running on Windows Vista (compared to Windows XP Professional Edition running on a similar spec computer. I decided to run the "Windows Experiance Index" where Windows Vista determines the performance rate you are achieving. The score I achieved was a mere 3.3!!!

Anyway, how can I can I improve the general performance? Well I carried out the following:

1) Disable Indexing Service

The Indexing Service is a little program that uses a hell a lot of memory that indexes and updates lists of all the files that are on your computer. It does this so that when you do a search for something on your computer, it will search faster by scanning the index lists. If you don’t search your computer often, then why have something you don't need. To disable, cary out the following:

  • If you want, you can just remove any extra areas of search, so you can keep your fast searching for some areas.
  • Go to My Computer, right click on C: drive, go to the General tab, and uncheck Index this drive for faster searching, select Include subfolders and files. Select Add/Remove Programs.

2) Removing Sidebar

I have to say that the Sidebar looks great and displays information in the form of widgets really nicely! However, it can take some system resources and adds a longer startup time. This is dependent on the amount on info displayed on your Desktop.

3) Clear Up Restore Points

From the very first day you start to use your PC, Windows stores shadow copies of your system files. Over time, the amount of shadow copies can waste precious space. Windows Vista allows you to remove all restore points up to the most date. To do this carry out the following:

  • Goto Start, Accessories, and System Tools.
  • Click on Disk Cleanup.
  • When the Disk Cleanup window appears, click on the 'More Options' tab.
  • Under 'System Restore and Shadow Copies' section click 'Clean Up...'

Voila! You will see the amount of disk space that has been freed! Awesome!

4) Hibernation is a No No!

Yeah I have to admit the Hibernation feature is cool feature that allows you to startup your PC in the condition you left it. The only thing is that the amount of memory that it consumes is quite large. So its now a good idea to overuse this function.

Watch out for the Automatic hibernation process runs constantly in the background using up resources. If you’re happy to hibernate manually, and you’re not using a laptop on battery power, switch it off by the carrying out the following:

  • Open Control Panel.
  • Select System and Maintenance and click 'Change when the computer sleeps'.
  • Set 'Put the computer to sleep' to Never and click Save changes.

5) Tweak VI

This software provides a wealth of tools for messing with Vista's guts, including a boot-configuration manager, a CPU optimizer, browser-settings managers and tons more. Whats even better...its free!!!!

6) Disable User Account Control

User Account Control puts in a layer of security that stops you from making system changes without confirming them. More experienced users willing to take the security risk can disable this to speed things up when performing low-level tasks. User Account Control can slow down and can be irritating. To disable, carry out the following:

  • Open Control Panel.
  • Choose User Accounts and Family Safety.
  • Select User Accounts and click Turn User Account Control off.

7) General Updates

Ensure your PC is up-to-date with the most current patches. This will also help improve performance, but very slightly.

8) Disable Unused Startup Programs

Try and reduce the amount of programs that need to be run on startup. You will find the utility to disable any unwanted startup programs in Windows Defender. Yes, I thought that this was a strange place as well! But I have to say it is a good idea because the Software Explorer feature lets you take a close look at every running program and remove or disable those that are starting automatically.

Well give it a go and if you find any extra things to add here, please leave a comment!

UNION ALL

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

One of my work colleagues at work showed me something that I never knew about UNION SQL statement. By default the UNION statement does the exact same thing as a SELECT DISTINCT on the resulting data. But what if we want to carry out a UNION without distincting the data result? Thats where the UNION ALL comes into play.

Example

Table 1: tblCars-UK

Car_ID Car_Name
1 Volvo
2 Volkswagen
3 Chevrolet
4 Nissan
5 BMW

Table 2: tblCars-US

| Car_ID | Car**_Name** | | 1 | Pontiac | | 2 | Chrysler | | 3 | Chevrolet | | 4 | Dodge | | 5 | BMW |

If we used a UNION statement, the results would be as follows:

Select car_name from tblCars-UK
UNION
Select car_name from tbleCars-US 

| Car_Name | | Volvo | | Volkswagen | | Chevrolet | | Nissan | | BMW | | Pontiac | | Chrysler | | Dodge |

As you can see from the results above that the duplicate car entries have been removed and only displayed once. Now this is what will happen if we use UNION ALL statement:

Select car_name from tblCars-UK
UNION ALL
Select car_name from tbleCars-US

| Car_Name | | Volvo | | Volkswagen | | Chevrolet | | Nissan | | BMW | | Pontiac | | Chrysler | | Chevrolet | | Dodge | | BMW |

Fixing BlogEngine HTML Editor Problem

I have to say that after using BlogEngine for my first couple of posts, I can't think of any other Blogging tool to use. However, the thing that really annoys me about BlogEngine is the HTML editor when I want to create my postings. I have found that the TinyMCE HTML editor rejects some HTML tags. For example, <code> and <iframe> tags.

I have been desperately looking through the BlogEngine forum posting's to see if there is anyway to fix this problem. There was no specific way of rectifying this problem.

However, I found a way to fix this!!! Laughing

You will need to make a simple change to the tinyMCE.ascx file located in the admin folder (/wwwroot/admin/). Find the following line:

extended_valid_elements : "hr[class|width|size|noshade],font[face|size|color|style],span[class|align|style],script[charset|defer|language|src|type]",

Now you will see in this line that there are tag elements listed as valid. All you have to do is add tags to this line in order for them to be accepted by the TinyMCE HTML editor. For example, I want my <code> and <iframe> tags to be accepted:

extended_valid_elements : "hr[class|width|size|noshade],font[face|size|color|style],span[class|align|style],script[charset|defer|language|src|type],code,iframe",

And there you have it!

Hope this helps!

Inline If Statement

Standard IF statements are great. But I found that when I am using very simple conditions within my IF Statement I waste a lot of space in my code.

Example 1:

public int IfExample(bool bolFlag)
{
int result = 0;
if (bolFlag)
{
result = 1;
}
else
{
result = 2;
}
return result;
} 

There is no problem using Example 1. But there is a much quicker and less tedius way of using a simple conditional IF statement.

Example 2:

public int IfExample(bool bolFlag)
{
return (bolFlag) ? 1 : 2;
}

This example is basically saying: "return if (bolFlag) 1 else 2". So you are in theory assigning the IF condition to the variable.

I know it takes a little while to understand the syntax. But it is really good for those less complex IF conditions.