How can you write a Common Function Class in C# Dot Net

*******  Here is the Code for Common Function Class ********
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.Collections.Generic;
using System.Data.SqlClient;

///

/// This class contain all common function and procedures used
/// through out the project.
///

public class CommonFunctionClass
{
    SqlCommand _Command = null;
    SqlDataAdapter _DataAdapter = null;
    DataSet _DataSet = null;
    DataTable _DataTable = null;


    public CommonFunctionClass()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public DataSet FillDataSet(String Query)
    {
        _Command = new SqlCommand();

        _Command.Connection = new SqlConnection();
        //_Command.Connection.ConnectionString =
        //    System.Configuration.ConfigurationManager.
        //    ConnectionStrings["Conn"].ConnectionString;

        if (_Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString || _Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["ConnRoc"].ConnectionString)
        {

            _Command.CommandText = Query;
            _DataAdapter = new SqlDataAdapter();
            _DataAdapter.SelectCommand = _Command;
            _DataSet = new DataSet();
            _DataAdapter.Fill(_DataSet);
            _Command.Parameters.Clear();
          

        }
        return _DataSet;
    
        //_Command.CommandText = Query;
        //_DataAdapter = new SqlDataAdapter();
        //_DataAdapter.SelectCommand = _Command;
        //_DataSet = new DataSet();
        //_DataAdapter.Fill(_DataSet);
        //_Command.Parameters.Clear();
        //return _DataSet;
    }



    private List _ParameterCollection = null;

    public List ParameterCollection
    {
        get
        {
            if (_ParameterCollection == null)
            {
                _ParameterCollection = new List();
            }
            return _ParameterCollection;
        }
    }

    public SqlParameter Parameter(String ParameterName)
    {
        if (ParameterName.Contains("@"))
        {
            ParameterName = ParameterName.Replace("@", "");
        }
        foreach (SqlParameter Param in _ParameterCollection)
        {
            if (Param.ParameterName.ToUpper().Contains(ParameterName.ToUpper()))
            {
                return Param;
            }
        }
        return null;
    }

    public void CreateParameterCollection(String TableOrProcedureName)
    {
        _Command = new SqlCommand();

        _Command.Connection = new SqlConnection();
       
       
        //_Command.Connection.ConnectionString =
        //    System.Configuration.ConfigurationManager.
        //    ConnectionStrings["Conn"].ConnectionString;


        if (_Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString || _Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["ConnRoc"].ConnectionString)
        {

            _Command.CommandText = "sp_help '" + TableOrProcedureName + "'";
            _Command.CommandType = CommandType.Text;
            _DataAdapter = new SqlDataAdapter();
            _DataAdapter.SelectCommand = _Command;
            _DataSet = new DataSet();
            _DataAdapter.Fill(_DataSet);
            if (_DataSet.Tables.Count > 1)
            {
                DataTable _DataTable = _DataSet.Tables[1];

                //clear parameter collection
                ParameterCollection.Clear();
                foreach (DataRow _DataRow in _DataTable.Rows)
                {
                    SqlParameter Parameter = new SqlParameter();
                    Parameter.ParameterName = _DataRow[0].ToString(); //column name or parameter name
                    Parameter.Size = Convert.ToInt32(_DataRow["length"]);
                    switch (_DataRow["type"].ToString())
                    {
                        case ("numeric"):
                            Parameter.DbType = DbType.Decimal;
                            Parameter.Scale = Convert.ToByte(_DataRow["scale"]);
                            Parameter.Precision = Convert.ToByte(_DataRow["prec"]);
                            break;
                        case ("varchar"):
                            Parameter.DbType = DbType.String;
                            break;
                        case ("image"):
                            Parameter.DbType = DbType.Binary;
                            break;
                        case ("datetime"):
                            Parameter.DbType = DbType.DateTime;
                            break;
                        case ("char"):
                            Parameter.DbType = DbType.String;
                            break;
                        case ("bit"):
                            Parameter.DbType = DbType.Boolean;
                            break;
                    }
                    ParameterCollection.Add(Parameter);
                }
            }
        }
    }

    public void CreateParameterCollection(String TableOrProcedureName, SqlConnection ConnectionObject)
    {
        _Command = new SqlCommand();

        _Command.Connection = ConnectionObject;

        _Command.CommandText = "sp_help '" + TableOrProcedureName + "'";
        _Command.CommandType = CommandType.Text;
        _DataAdapter = new SqlDataAdapter();
        _DataAdapter.SelectCommand = _Command;
        _DataSet = new DataSet();
        _DataAdapter.Fill(_DataSet);
        if (_DataSet.Tables.Count > 1)
        {
            DataTable _DataTable = _DataSet.Tables[1];

            //clear parameter collection
            ParameterCollection.Clear();
            foreach (DataRow _DataRow in _DataTable.Rows)
            {
                SqlParameter Parameter = new SqlParameter();
                Parameter.ParameterName = _DataRow[0].ToString(); //column name or parameter name
                Parameter.Size = Convert.ToInt32(_DataRow["length"]);
                switch (_DataRow["type"].ToString())
                {
                    case ("numeric"):
                        Parameter.DbType = DbType.Decimal;
                        Parameter.Scale = Convert.ToByte(_DataRow["scale"]);
                        Parameter.Precision = Convert.ToByte(_DataRow["prec"]);
                        break;
                    case ("varchar"):
                        Parameter.DbType = DbType.String;
                        break;
                    case ("image"):
                        Parameter.DbType = DbType.Binary;
                        break;
                    case ("datetime"):
                        Parameter.DbType = DbType.DateTime;
                        break;
                    case ("char"):
                        Parameter.DbType = DbType.String;
                        break;
                    case ("bit"):
                        Parameter.DbType = DbType.Boolean;
                        break;
                }
                ParameterCollection.Add(Parameter);
            }
        }
    }


    public DataSet FillDataSet(String StoredProcedureName, List ParameterCollection)
    {
        _Command = new SqlCommand();

        _Command.Connection = new SqlConnection();
        //_Command.Connection.ConnectionString =
        //    System.Configuration.ConfigurationManager.
        //    ConnectionStrings["Conn"].ConnectionString;



        if (_Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString || _Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["ConnRoc"].ConnectionString)
        {

            _Command.CommandText = StoredProcedureName;
            _Command.CommandType = CommandType.StoredProcedure;

            if (!(ParameterCollection == null))
            {
                foreach (SqlParameter Parameter in ParameterCollection)
                {
                    _Command.Parameters.Add(Parameter);
                }
            }

            _DataAdapter = new SqlDataAdapter();
            _DataAdapter.SelectCommand = _Command;
            _DataSet = new DataSet();
            _DataAdapter.Fill(_DataSet);
            _Command.Parameters.Clear();
           
        }
        return _DataSet;
    }

    public DataSet FillDataSet_SP_WithoutParameters(String StoredProcedureName)
    {
        _Command = new SqlCommand();

        _Command.Connection = new SqlConnection();
        //_Command.Connection.ConnectionString =
        //    System.Configuration.ConfigurationManager.
        //    ConnectionStrings["Conn"].ConnectionString;



        if (_Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString || _Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["ConnRoc"].ConnectionString)
        {

            _Command.CommandText = StoredProcedureName;
            _Command.CommandType = CommandType.StoredProcedure;

            _DataAdapter = new SqlDataAdapter();
            _DataAdapter.SelectCommand = _Command;
            _DataSet = new DataSet();
            _DataAdapter.Fill(_DataSet);
            _Command.Parameters.Clear();
           
        }
        return _DataSet;
    }

    public Object ExecuteScalar(String CommandText, List ParameterCollection, CommandType _CommandType)
    {
        _Command = new SqlCommand();

        _Command.Connection = new SqlConnection();
       
        //_Command.Connection.ConnectionString =
        //     System.Configuration.ConfigurationManager.
        //     ConnectionStrings["Conn"].ConnectionString;

        if (_Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString || _Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["ConnRoc"].ConnectionString)
        {

            _Command.CommandText = CommandText;

            if (_CommandType == CommandType.StoredProcedure && ParameterCollection != null)
            {
                foreach (SqlParameter Parameter in ParameterCollection)
                {
                    _Command.Parameters.Add(Parameter);
                }
            }

            if (_Command.Connection.State == ConnectionState.Closed)
            {
                _Command.Connection.Open();
            }

            _Command.CommandType = _CommandType;
            Object obj = _Command.ExecuteScalar();

            _Command.Parameters.Clear();
            _Command.Connection.Close();
            return obj;   
        }
       
    }


    public Int32 ExecuteNonQuery(String CommandText, List ParameterCollection, CommandType _CommandType)
    {
        _Command = new SqlCommand();

        _Command.Connection = new SqlConnection();

        //_Command.Connection.ConnectionString =
        //     System.Configuration.ConfigurationManager.
        //     ConnectionStrings["Conn"].ConnectionString;

        if (_Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString || _Command.Connection.ConnectionString == System.Configuration.ConfigurationManager.ConnectionStrings["ConnRoc"].ConnectionString)
        {

            _Command.CommandText = CommandText;

            if (_CommandType == CommandType.StoredProcedure && ParameterCollection != null)
            {
                foreach (SqlParameter Parameter in ParameterCollection)
                {
                    _Command.Parameters.Add(Parameter);
                }
            }

            _Command.CommandType = _CommandType;

            if (_Command.Connection.State == ConnectionState.Closed)
            {
                _Command.Connection.Open();
            }

            Int32 NumberOfRowsEfected = _Command.ExecuteNonQuery();

            _Command.Parameters.Clear();
            _Command.Connection.Close();
            return NumberOfRowsEfected;  
        }
       
    }

    public Int32 ExecuteNonQuery(String CommandText, List ParameterCollection, CommandType _CommandType, SqlConnection ConnectionObject)
    {
        _Command = new SqlCommand();

        _Command.Connection = ConnectionObject;

        _Command.CommandText = CommandText;

        if (_CommandType == CommandType.StoredProcedure && ParameterCollection != null)
        {
            foreach (SqlParameter Parameter in ParameterCollection)
            {
                _Command.Parameters.Add(Parameter);
            }
        }

        _Command.CommandType = _CommandType;

        if (_Command.Connection.State == ConnectionState.Closed)
        {
            _Command.Connection.Open();
        }

        Int32 NumberOfRowsEfected = _Command.ExecuteNonQuery();

        _Command.Parameters.Clear();
        _Command.Connection.Close();
        return NumberOfRowsEfected;
    }  
    
}


  public DataSet GetAllBanmajraData()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = new SqlConnection();
        cmd.Connection.ConnectionString =
            System.Configuration.ConfigurationManager.
            ConnectionStrings["ConnRoc"].ConnectionString;

        //cmd.Parameters.Add("@SelectDate", SqlDbType.DateTime);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Get_MIS_Banmaajra_Details";
        SqlDataAdapter DA = new SqlDataAdapter();
        DA.SelectCommand = cmd;
        DataSet DS = new DataSet();
        DA.Fill(DS);
        return DS;

    }

Read More »

Step by Step Process of Creating a Setup and Deployment Project

Why should we use a setup and deployment project?
The answer is simple. If you develop a product that you will either provide as a commercial service of an open source project to share with others it is beneficial to have a mechanism in place for setup and deployment. In this article, I will cover the basics of creating a Windows Installer. 
This article will walk through the step by step process of building a Setup Project.
There are five different templates that are available when you select the project type 'Setup and Deployment Projects'.

Figure 1: Add New Project Dialog

1. Setup Project: Creates a Windows Installer project to which files can be added. This project type is the most generic of the five.
2. Web Setup Project: Creates a Windows Installer web project to which files can be added.
3. Merge Module Project: Creates a Windows Installer Merge Module project to which files can be added. This project type is most commonly used for using third party products.
4. Setup Wizard: Creates a Windows Installer project with the aid of a wizard.
5. Cab Project: Creates a Cab project to which files can be added.

For the purpose of this article, I will be using the template Setup Project. Provide a name for the project as well choose the location where this project will be established. (See Figure 1) Once you have done this click 'OK' and you should now see a screen comparable to the following.

Figure 2: Default File System View
Now that you have an empty Setup Project established let us now discuss the available editors.
Setup Project Editors And The Functionality They Provide

The next item we will discuss are the various editors that are available. File System Editor (displayed in Figure #2) Registry Editor File Types Editor User Interface Editor Custom Actions Editor Launch Conditions Editor There are several ways you can access these editors. Here I will illustrate two of these methods. Figure 3: Access the various editors As you can see in the above figure I have indicated two ways to access these editors. In example number one a single click on the setup project you have created with Solution Explorer provides a menu of the various editors. Whereas in example number two if you right click on the setup project and then click ?View? you are then provided access to the various editors. Basically it is simply a matter of choice as to which method you desire. File System Editor The File System (Figure 4) provides you the essential tools to work with the application folder, user's desktop, and the user's programs menu. Figure 4: File System Properties The first thing we want to do here is to include the relevant files and folders we wish to install on the user's machine. This is accomplished by right clicking the Application Folder and selecting 'Add'. In this case I will choose 'Folder' and give this newly established folder the name of 622. Now to add files to this folder, right click the folder 622 and choose 'Add'. Merely add one or more of the files that is vital for your application. Now that you have you files and folders in place you could take additional steps such as manipulating the user's desktop with items such as a shortcut, as well you can add a program group to the user's program group. The following screenshot is an example of something similar you may see. Figure 4.1: File System Properties If you have a requirement to modify the user's registry then the Registry Editor is a straight forward tool that provides this capability. Figure 5: Registry Editor Next is the File Types Editor. If you need to establish file associations on the user's computer with your application then this editor will achieve the desired task. Figure 6: File Types Editor The next item you find of immense importance is the User Interface Editor. Inside of this editor you may eliminate or include dialogs that are needed for this install project. The following screenshot is the default User Interface that is created when I initially created this setup project (See Figure 1). The only variation is that I have added a Customer Information dialog. Figure 7: User Interface Editor To include the Customer Information dialog that I spoke of follow the following steps. Under Install right click on the 'Start' node Choose 'Add Dialog' Figure 7.1: User Interface Editor Select the Customer Information Dialog and click 'OK' As you can see there are is an ample variety of dialogs you can choose from. Take the time to look over each one and understand the use behind them. Figure 8: Custom Actions Editor The Custom Actions Editor allows you to specify additional actions to be performed on the target computer at the end of an installation. For the purpose of this article, I am not going into details relating to custom actions rather to learn more on this subject read up on Custom Actions Management in Deployment. Figure 9: Launch Conditions Editor In the above example, I have added a condition to verify that the user's computer has at least 128MB of RAM. If the user's computer does not meet this stipulation then the application installer will graciously inform the user of this stipulation and exit the install process. As you have probably already concluded this feature can be significant in many aspects, for example; free space on the hard drive and the amount on installed memory just to name a couple. There is nothing more embarrassing from a developer's point of view to have your application fail at the install process. If you take time and think of this, would you be impressed with an application you are looking forward to using only to have the install process fail? Now we will take a moment to look at the Physical Memory launch condition that I have created. To add new conditions do the following. Right click Launch Conditions and choose 'Add' Provide a name for this condition such as I have in this example Right click the 'PhysicalMemory' condition and choose 'Properties' In this case the condition I have established is that the physical memory must be greater than 128 MB. Be sure to look at the Windows Installer Property Reference regarding the various properties that are available when working within the Launch Conditions Editor.

The Setup Project All Comes Together

As I have indicated it is fairly simple and straight forward to create a Setup and Deployment Project. I have covered adding a new project, the six types of editors available and examples of how to best utilize these editors. While I have not covered the all the complete features of the Windows Installer I should have provided enough to peak your curiosity. While some may state that the Windows Installer may not be sufficient, I must disagree. As with anything else the choice of the installer is based upon requirements. Take the time to create your own Windows Installer yourself and see what you opinion is. Here is a screenshot of this Windows Installer that we have just discussed.

Read More »