Understanding SQL Injection and Creating SQL Injection Proof ASP.NET Applications

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DBHelper
/// </summary>
public class DBHelper
{
    SqlConnection con = null;

	public DBHelper()
	{

	}

    public bool IsUserAuthenticated_Bad(string username, string password)
    {
        DataTable result = null;
        try
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select userID from Users where userID = '" + username + "' and password = '" + password + "'";
                    
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        result = new DataTable();
                        da.Fill(result);

                        //check if any match is found
                        if (result.Rows.Count == 1)
                        {
                            // return true to indicate that userID and password are matched.
                            return true;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            //Pokemon exception handling
        }

        //user id not found, lets treat him as a guest        
        return false;
    }

    public DataTable GetProductsAssigner_Bad(string userID)
    {
        DataTable result = null;
        try
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from Products where AssignedTo = '" + userID + "'";

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        result = new DataTable();
                        da.Fill(result);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            //Pokemon exception handling
        }

        //user id not found, lets treat him as a guest        
        return result;
    }

    public bool IsUserAuthenticated_Good(string username, string password)
    {
        DataTable result = null;
        try
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "CheckUser";
                    cmd.Parameters.Add(new SqlParameter("@userID", username));
                    cmd.Parameters.Add(new SqlParameter("@password", password));

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        result = new DataTable();
                        da.Fill(result);

                        //check if any match is found
                        if (result.Rows.Count == 1)
                        {
                            // return true to indicate that userID and password are matched.
                            return true;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            //Pokemon exception handling
        }

        //user id not found, lets treat him as a guest        
        return false;
    }

    public DataTable GetProductsAssigner_Good(string userID)
    {
        DataTable result = null;
        try
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from Products where AssignedTo = @userID";
                    cmd.Parameters.Add(new SqlParameter("@userID", userID));

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        result = new DataTable();
                        da.Fill(result);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            //Pokemon exception handling
        }

        //user id not found, lets treat him as a guest        
        return result;
    }
}