using System;

using System.Data.SqlClient;

using System.Data;

using System.Collections.Generic;

using System.Xml;

​

public class Program

{

    private SqlConnection sqlConnection = null;

    private DataSet dsData = null;

    private static string strDbConnection = "";

    //

    public static void Main()

    {

        Console.WriteLine("Hello World");

    }

​

    public static SqlConnection GetConnection()

    {

        var conStr = "Data Source=(LocalDb)\v11.0;Initial Catalog=MyDB;Integrated Security=True"; //ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        var sqlConnection = new SqlConnection();

        sqlConnection.ConnectionString = conStr;

        sqlConnection.Open();

        return sqlConnection;

    }

​

    public DataTable SearchVendors(string vendorType = "", string vendorName = "", int id = 0, int isActive = -1)

    {

        string sProcName = "SearchVendors";

        List<SqlParameter> paramList = new List<SqlParameter>();

        //

        paramList.Add(new SqlParameter("@ID", id < 1 ? (object)DBNull.Value : id));

        paramList.Add(new SqlParameter("@VENDOR_TYPE", String.IsNullOrEmpty(vendorType) ? (object)DBNull.Value : vendorType));

        paramList.Add(new SqlParameter("@VENDOR_NAME", String.IsNullOrEmpty(vendorName) ? (object)DBNull.Value : vendorName));

        paramList.Add(new SqlParameter("@IS_ACTIVE", isActive < 0 ? (object)DBNull.Value : isActive));

        //

        return OpenDataTable(sProcName, paramList.ToArray()).Tables[0];

    }

​

    public void OpenConnection()

    {

        try

        {

            sqlConnection = GetConnection();

        }

        catch (Exception ex)

        {

            throw ex;

        }

    }

​

    public void CloseConnection()

    {

        try

        {

            if (sqlConnection != null)

            {

                sqlConnection.Close();

                sqlConnection = null;

            }

        }

        catch (Exception ex)

        {

            throw ex;

        }

    }

​

    public DataSet OpenDataTable(string spName, SqlParameter[] spParams = null)

    {

        try

        {

            OpenConnection();

            //

            using (SqlCommand _sqlCommand = new SqlCommand())

            {

                _sqlCommand.CommandText = spName;

                _sqlCommand.CommandType = CommandType.StoredProcedure;

                _sqlCommand.Connection = sqlConnection;

                _sqlCommand.CommandTimeout = 60;

                //

                if (spParams != null)

                    _sqlCommand.Parameters.AddRange(spParams);

                //

                using (SqlDataAdapter SqlDataAdapter = new SqlDataAdapter(_sqlCommand))

                {

                    dsData = new DataSet();

                    SqlDataAdapter.Fill(dsData);

                }

​

                return dsData;

            }

        }

        catch (Exception ex)

        {

            throw ex;

        }

        finally

        {

            CloseConnection();

        }

    }

}