/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Tuesday, May 3, 2011

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;
    }

No comments:

Post a Comment

Recent Posts

My Blog List