Using Captcha Image in Claasic asp

Read More »

SQL SERVER 2005: Expot to Excel using OPENROWSET

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use these queries

1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use
EXEC sp_makewebtask
@outputfile = 'd:\testing.xls',
@query = 'Select * from Database_name..SQLServerTable',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

Read More »

SQL SERVER 2005: How to enable ‘Ad Hoc Distributed Queries’ SQL SERVER 2005

Enabling  ‘Ad Hoc Distributed Queries’ SQL SERVER 2005
Frequently, we need to use OPENROWSET queries to connect to remote database servers. To enable this feature on SQL Server 2005, you should first configure the database to enable Ad Hoc Distributed Queries.
We can Enable this feature by two ways:
1. SQL Server Surface Area Configuration.
2. by sp_configure option.
Lets check with first way, by SQL Server Surface Area Configuration.
Open surface Area configuration, you will get this screen:

Click on second option, Surface Areas Configuration for Features. you will get this screen, where you need to check to Enable OPENROWSET and OPENDATASOURCE support.

Lets see second option to enable this feature with sp_configure option:
sp_configure
If you run this command, you will lists of SQL configuration settings. There are 14 items in the list in which ‘Ad Hoc Distributed Queries’ is not exist. To see this, we need to enable the ‘show advanced options’ configuration parameter.
You can enable advance options by:
sp_configure ‘show advanced options’,1
When we run this command we will get this message:
“Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.”
so we need to execute reconfigure command as:
reconfigure
so now if we run sp_configure again, we will get result set as follows:

Here, we can find that config_value for “Ad Hoc Distributed Queries” is “0”. We need to set it to 1 to enable this feature. so to do that we need to use following:
sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure
so, now if we run sp_configure, we will get result as follows:

Here, we can find that now config_value for “Ad Hoc Distributed Queries” is “1”.
That’s it, now you can use OPENROWSET and OPENDATASOURCE to connect with remote database without Linked server.
Let me know if it helps you in any way.

Read More »

T-SQL to change schema/owner name in Sql Server

Read More »

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 »