FCC/Applications/SinoChem/SinoChemCommonUtilities/SqlCommandUtility.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SinoChemCommonUtilities
{
    public delegate T ConstructThing<T>(SqlDataReader rdr);

    public class SqlCommandUtility
    {
        private SqlConnection conn;
        private string _connectionString;

        public SqlCommandUtility(string connString)
        {
            conn = new SqlConnection(connString);
            _connectionString = connString;
        }

        public T ReadData<T>(string querySql, ConstructThing<T> constructThing)
        {
            T thing = default(T);

            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (var command = new SqlCommand(querySql, connection))
                    using (var sqlDataReader = command.ExecuteReader())
                    {
                        thing = constructThing(sqlDataReader);
                        return thing;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                return thing;
            }

            /*
            try
            {
                // Open the connection
                conn.Open();
                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(querySql, conn);
                // 2. Call Execute reader to get query results
                reader = cmd.ExecuteReader();
                // 3.
                thing = constructThing(reader);

                return thing;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
            */
        }

        public object Insertdata(string insertSql, bool toExeScalar)
        {
            object scalarObj = null;
            try
            {
                // Open the connection
                conn.Open();
                // 1. Instantiate a new command with a query and connection
                SqlCommand cmd = new SqlCommand(insertSql, conn);
                // 2. Call to send command
                if (toExeScalar)
                {
                    scalarObj = cmd.ExecuteScalar();
                }
                else
                {
                    cmd.ExecuteNonQuery();
                }

                return scalarObj;
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public void UpdateData(string updateSql)
        {
            try
            {
                // Open the connection
                conn.Open();
                // 1. Instantiate a new command with command text only
                SqlCommand cmd = new SqlCommand(updateSql);
                // 2. Set the Connection property
                cmd.Connection = conn;
                // 3. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public void DeleteData(string deleteSql)
        {
            try
            {
                // Open the connection
                conn.Open();
                // 1. Instantiate a new command
                SqlCommand cmd = new SqlCommand();
                // 2. Set the CommandText property
                cmd.CommandText = deleteSql;
                // 3. Set the Connection property
                cmd.Connection = conn;
                // 4. Call ExecuteNonQuery to send command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                // Close the connection
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
    }
}