#region File Header & Copyright Notice/* * Copyright (C) 2014 XXX, INC. All Rights Reserved. * THIS SOURCE CODE IS CONFIDENTIAL AND PROPRIETARY AND MAY NOT BE USED * OR DISTRIBUTED WITHOUT THE WRITTEN PERMISSION OF XXXX, INC. * */#endregionusing System;using System.Configuration;using System.Data;using System.Data.SqlClient;using Constant;namespace Dal{    /// <summary>    /// This class can not be extends and it provide services for dal.    /// </summary>    public sealed class SqlHelper    {        #region Private Constructor        private SqlHelper() { }        #endregion        #region Returns SqlConnection        /// <summary>        /// Gets the connection with database.        /// </summary>        /// <returns>Return the connection of database.</returns>        public static SqlConnection GetConnection()        {            string connStr = ConfigurationManager.AppSettings["connStr"];            SqlConnection conn = new SqlConnection(connStr);            return conn;        }        #endregion        #region Closes SqlDataReader Method        /// <summary>        /// Closes the sqldatareader and dispose it.        /// </summary>        /// <param name="sqlDataReader">Returns sqldatareader.</param>        public static void CloseSqlDataReader(SqlDataReader sqlDataReader)        {            if (!sqlDataReader.IsClosed)            {                sqlDataReader.Close();                sqlDataReader.Dispose();            }            else            {                //TODO NOTHING            }        }        #endregion        #region Returns SqlDataReader        /// <summary>        /// Gets data from database.        /// </summary>        /// <param name="sqlText">The sql text.</param>        /// <param name="prams">The parameters of the sql text.</param>        /// <returns>Return sqldatareader.</returns>        public static SqlDataReader ExecureReader(string sqlText, SqlParameter[] prams)        {            SqlCommand sqlCommand = null;            SqlConnection sqlConnection = GetConnection();            if (sqlConnection.State != ConnectionState.Open)            {                sqlConnection.Open();            }            sqlCommand = new SqlCommand(sqlText, sqlConnection);            if (null != prams)            {                sqlCommand.Parameters.AddRange(prams);            }            return sqlCommand.ExecuteReader();            //There doesn‘t close the connection with database.  Because in reading the data the connection must be open.        }        #endregion        #region ExecuteNonQuery Method        /// <summary>        /// Does the insert, update, delete function.        /// </summary>        /// <param name="sqlText">The sql text.</param>        /// <param name="parms">The parameters of the sql.</param>        /// <returns>Returns influence number.</returns>        public static int ExecuteNonQuery(string sqlText, SqlParameter[] parms)        {            int i = 0;            using (SqlConnection sqlConnection = GetConnection())            {                if (sqlConnection.State == ConnectionState.Closed)                {                    sqlConnection.Open();                }                using (SqlCommand sqlCommand = new SqlCommand())                {                    sqlCommand.Connection = sqlConnection;                    sqlCommand.CommandText = sqlText;                    sqlCommand.Parameters.AddRange(parms);                    i = sqlCommand.ExecuteNonQuery();                }            }            return i;        }        /// <summary>        /// Gets the influence rows.        /// </summary>        /// <param name="sqlText">The sql text.</param>        /// <param name="parms">The parameters of the sql.</param>        /// <returns>Returns influence number.</returns>        public static int ExecuteNonQuery(string sqlText)        {            int i = 0;            using (SqlConnection sqlConnection = GetConnection())            {                if (sqlConnection.State == ConnectionState.Closed)                {                    sqlConnection.Open();                }                using (SqlCommand sqlCommand = new SqlCommand())                {                    sqlCommand.Connection = sqlConnection;                    sqlCommand.CommandText = sqlText;                    i = sqlCommand.ExecuteNonQuery();                }            }            return i;        }        #endregion        #region Returns Object        /// <summary>        /// Gets the first row and first column data.        /// </summary>        /// <param name="sql">The sql text.</param>        /// <param name="prams">The parameters of the sql text.</param>        /// <returns>Returns an object.</returns>        public static object ExecuteScalar(string sqlText, SqlParameter[] parms)        {            object result;            using (SqlConnection sqlConnection = GetConnection())            {                if (sqlConnection.State != ConnectionState.Open)                {                    sqlConnection.Open();                }                using (SqlCommand sqlCommand = new SqlCommand(sqlText, sqlConnection))                {                    sqlCommand.Parameters.AddRange(parms);                    result = sqlCommand.ExecuteScalar();                }            }            return result;        }        #endregion    }}