Wrapper class for Database connectivity and query execution

Have you ever tried to to wrap up code when dealing with DataSets, SqlDataAdapters, SqlConnections and SqlTransactions??? It can be really tricky sometimes.

You can download the wrapper class here
It comes along with a test project, to check whether it meets your requirements. Make sure you change the Connection String in the app.config to point to your Database
 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="cnString" connectionString="YOUR CONNECTION STRING HERE" />
    </connectionStrings>
</configuration>

Also change the Application.Run(FORM TO RUN) to check out the corresponding form and method calls of the wrapper class

        /// <summary>
        /// The main entry point for the application.
        /// In Application.Run(new executeNonQuery_handle()) enter Command_Handle,     //Connection_handle,   //DataSet_handle, executeNonQuery_handle,
        /// Parameter_handle, SqlDataReader_handle to check out the corresponding form
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new DataSet_handle());
        }

Prortion of the wrapper class, concerning DataSets, can be seen below

    public static DataSet getDataSet(string commandText, CommandType commandType, SqlConnection connection, int isCaseSensitive,  string dataSetName, int enforceConstraints, params SqlParameter[] sqlParameters)
    {
        DataSet ds = null;
        SqlDataAdapter ad = null;

        try
        {
            ad = new SqlDataAdapter();
            ad.SelectCommand = getSqlCommand(commandText,commandType, sqlParameters);
            if (connection != null)
            {
                ad.SelectCommand.Connection = connection;
            }
            else
            {
                ad.SelectCommand.Connection = getConnection();
            }

            ds = new DataSet();
           
            if (isCaseSensitive != -1)
            {
                ds.CaseSensitive = Convert.ToBoolean(isCaseSensitive);
            }
            if (!dataSetName.Equals(string.Empty))
            {
                ds.DataSetName = dataSetName;
            }
            if (enforceConstraints != -1)
            {
                ds.EnforceConstraints = Convert.ToBoolean(enforceConstraints);
            }

            ad.Fill(ds);
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
            ad.Dispose();
        }
        return ds;
    }


    public static DataSet getDataSet(string commandText, params SqlParameter[] sqlParameters)
    {
        return getDataSet(commandText, CommandType.StoredProcedure, null, -1, string.Empty, -1, sqlParameters);
    }

    public static DataSet getDataSet(SqlCommand command, CommandType commandType, SqlConnection connection, int isCaseSensitive, string dataSetName, int enforceConstraints, params SqlParameter[] sqlParameters)
    {
        DataSet ds = null;
        SqlDataAdapter ad = null;

        try
        {
            ad = new SqlDataAdapter(command);
            ad.SelectCommand.CommandType = commandType;
            if (connection != null)
            {
                ad.SelectCommand.Connection = connection;
            }
            else
            {
                ad.SelectCommand.Connection = getConnection();
            }

            foreach (SqlParameter _param in sqlParameters)
            {
                ad.SelectCommand.Parameters.Add(_param);
            }

            ds = new DataSet();
            if (isCaseSensitive != -1)
            {
                ds.CaseSensitive = Convert.ToBoolean(isCaseSensitive);
            }
            if (dataSetName != string.Empty)
            {
                ds.DataSetName = dataSetName;
            }
            if (enforceConstraints != -1)
            {
                ds.EnforceConstraints = Convert.ToBoolean(enforceConstraints);
            }

            ad.Fill(ds);
        }
        //The value parameter is null
        catch (ArgumentNullException ex)
        {
            throw ex;
        }
        //The SqlParameter specified in the value parameter is already added to this or another SqlParameterCollection
        catch (ArgumentException ex)
        {
            throw ex;
        }
        //The parameter passed was not a SqlParameter
        catch (InvalidCastException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            ad.Dispose();
        }

        return ds;
    }

    public static DataSet getDataSet(SqlCommand command, params SqlParameter[] sqlParameters)
    {
        return getDataSet(command, CommandType.StoredProcedure, null, -1, string.Empty, -1, sqlParameters);
    }

What to avoid while programming

Here are some things to avoid doing when programming

1. Hard coded connection strings. They make the maintenance of the code very difficult. Instead one should use web.config in the case of Web Sites or app.config in the case of Windows Forms

2.Hard codded SQL Statement. They can easily lead to SQL Injection, a practice used by intruders, to gain access to the projects database

3.Bad or no Exception Handling. It is obvious how this can lead to problems

4.No Exception Loging. It is very common, when a project first gets deployed, even if it has undergone repetative testing, that it will have some bugs that will lead to Exceptions. In the catch clause of your Exception Handling implement code to store the Exceptions message, stack trace and if possible the user_fk of the user that caused that Exception, in the database or to a log file.

5.No Disposing of objects that are no longer needed. All object that implement the IDisposable interface can be disposed. If a custom object implements the IDisposable interface you can implement your own Dispose method

6.No Garbage Collecting. If possible and when needed call the Garbage Collector for automatic memory management

7.No classes to represent bussiness logic. Implement wrapper classes to connect to your database and execute stored procedures. Do not have SqlAdapters, DataSet and SqlCommand object all over your aspx.cs file

8.Make use of the Disconnected model instead of the Connected model when connecting to the database for performance reasons. This means using DataSets insted of SqlDataReaders

9.Do not use temporary tables, cursors, retriaval of unessesary database fields, or making use of unessesary order bys when programming your stored procedures for performance reasons

Ajax AutoComplete

Ajax provides a cool way to implement the AutoComplete feature on TextBoxes. AutoComplete is a very useful feature for Web Sites, plus it makes your final product more dynamic in the eyes of the end user and more expensive in the eyes of your client and to top all of that it is very easy to implement.

What you will need is an aspx file and a Web Service

1.In the WebForm that you wish to implement the AutoComplete TextBox, drag & drop a ScriptManager, found under AJAX Extensions and a TextBox

2.Place your mouse over the TextBox and add the AutoCompleteExtender to your TextBox

3.Add a WebService to your solution. The method you must use must have the following signature

[WebMethod]
[System.Web.Script.Services.ScriptMethod] 
public string[] HelloWorld(string prefixText, int count)

//Make sure that you uncomment the line

//just after the using statements

[System.Web.Script.Services.ScriptService]

4.Your WebService method must look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{

    public WebService()
    {
        //InitializeComponent();
    }
    [WebMethod]
    [System.Web.Script.Services.ScriptMethod]
    public string[] HelloWorld(string prefixText, int count)
    {

//Your connectionString will surely be different than mine

SqlConnection conne = new SqlConnection(@"Data Source=MSEN-PC\SQLEXPRESS;Initial Catalog=BookStore;Integrated Security=True");

//Since probably your database will be different too, select the appropriate field from your db table to appear in the AutoComplete

SqlDataAdapter ad = new SqlDataAdapter("select name from book where name like  '" + prefixText + "%'", conne);

        DataSet ds = new DataSet();
        ad.Fill(ds);
        List list = new List();
        count = 0;
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            list.Add(ds.Tables[0].Rows[count]["name"].ToString());
            count++;
        }
        return list.ToArray();
    }
}

5. Finally, your aspx must look like this

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="cc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <!--Make sure you add the Services tag within ScriptManager-->
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        <Services>
        <asp:ServiceReference Path="WebService.asmx" />
        </Services>
        </asp:ScriptManager>
        <br />
        <br />
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <cc1:AutoCompleteExtender
        ID="TextBox1_AutoCompleteExtender"
        runat="server"
        DelimiterCharacters=""
        Enabled="True"
<!-The name of the WebService to use-->
        ServicePath="WebService.asmx"
<!-The name of the WebMethod to use-->
        ServiceMethod="HelloWorld"
        TargetControlID="TextBox1"
<!-The minimum characters before AutoComplete starts-->
        MinimumPrefixLength="1">
        </cc1:AutoCompleteExtender>
    </div>
    </form>
</body>
</html>

Have fun!!!

Hack_This_Site

No! I don't encourage you to hack this site as you might think!!!

HackThisSite is a training ground for hackers and generally people who wish your site crashes, want to steal important information or cause denial of service to a system. I know, I know...many of you mid-level and senior programmers already are familiar with the threat that SQL injection posses to a system, but an entry level programmer might learn something on security studying this site...
The site includes missions that you must complete and in each level the mission becomes harder. And who knows...maybe a student might find a site that uses hardcoded sql statements instead of stored procedures or JAVA prepared statements!!!
Good luck, be white hat!!!