[c#] MS-SQL 연동 Helper

1. [c#] MS-SQL 연동 Helper

- 이번에는 C#에서 sql server와 연동하기 위한 Helper를 작성해 보았습니다.

- Oracle과 별반 차이는 없어 보시고 참고하셔도 좋을것 같습니다.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;

namespace factory.Database
{
    public static class sqlDBHelper
    {
        private static SqlConnection dbConn = null;
        private static string dbConnString;

        public static void Init()
        {
            // {0} : IP, {1} : Port, {2} : DB Name, {3} : userID, {4} : userPW
            dbConnString = $"Data Source={전역변수.gv_sql_svr},{전역변수.gv_sql_port};" +
                $"Initial Catalog={전역변수.gv_sql_tns};User Id={전역변수.gv_sql_user};Password={전역변수.gv_sql_pass};";
        }

        public static void Init(string hostName,
                                string user,
                                string pass,
                                string dbase,
                                string port,
                                bool perfCounter = false)
        {
            // {0} : IP, {1} : Port, {2} : DB Name, {3} : userID, {4} : userPW
            // "data source = {0},{1} ; initial Catalog = {2} ; user id = {3} ;PASSWORD = {4}";
            dbConnString = $"Data Source={hostName},{port};Initial Catalog={dbase};User Id={user};Password={pass};";
        }

        public static DataTable executeProcedure(string PROC_NAME, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                DataTable a = new DataTable();
                List<SqlParameter> filters = new List<SqlParameter>();

                string query = "EXEC " + PROC_NAME;

                bool first = true;
                for (int i = 0; i < parameters.Length; i += 2)
                {
                    filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));
                    query += (first ? " " : ", ") + ((string)parameters[i]);
                    first = false;
                }

                return Query(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static int nonProcedure(String query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                List<SqlParameter> filters = new List<SqlParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));

                using (dbConn = new SqlConnection(dbConnString))
                {
                    SqlCommand command = new SqlCommand(query, dbConn);

                    try
                    {
                        dbConn.Open();
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(filters.ToArray());
                        return command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                        throw ex;
                    }
                    finally
                    {
                        if (dbConn != null)
                            dbConn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static DataTable executeQuery(string query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                List<SqlParameter> filters = new List<SqlParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));

                return Query(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static int executeNonQuery(string query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
                List<SqlParameter> filters = new List<SqlParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));

                return NonQuery(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        public static object executeScalar(string query, params object[] parameters)
        {
            try
            {
                if (parameters.Length % 2 != 0)
                    throw new ArgumentException("Wrong number of parameters sent to query. Expected an even number.");
                List<SqlParameter> filters = new List<SqlParameter>();

                for (int i = 0; i < parameters.Length; i += 2)
                    filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));

                return Scalar(query, filters);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        #region Private Methods
        private static DataTable Query(string query, IList<SqlParameter> parametros)
        {
            DataTable data = new DataTable();

            using (dbConn = new SqlConnection(dbConnString))
            {
                SqlCommand command = new SqlCommand(query, dbConn);

                try
                {
                    dbConn.Open();
                    command.Parameters.AddRange(parametros.ToArray());

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        data.Load(reader);
                    }

                    return data;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    throw ex;
                }
                finally
                {
                    if (dbConn != null)
                        dbConn.Close();
                }
            }
        }

        private static int NonQuery(string query, IList<SqlParameter> parametros)
        {
            using (dbConn = new SqlConnection(dbConnString))
            {
                SqlCommand command = new SqlCommand(query, dbConn);

                try
                {
                    dbConn.Open();
                    command.Parameters.AddRange(parametros.ToArray());
                    return command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    throw ex;
                }
                finally
                {
                    if (dbConn != null)
                        dbConn.Close();
                }
            }
        }

        private static object Scalar(string query, List<SqlParameter> parametros)
        {
            using (dbConn = new SqlConnection(dbConnString))
            {
                SqlCommand command = new SqlCommand(query, dbConn);

                try
                {
                    dbConn.Open();
                    command.Parameters.AddRange(parametros.ToArray());
                    return command.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    throw ex;
                }
                finally
                {
                    if (dbConn != null)
                        dbConn.Close();
                }
            }
        }
        #endregion
    }
}

 

2. 호출방법

- 선언

using factory.Database;

 

- 호출

    string query = "select 컬럼1, 컬럼2, 컬럼3" +
                " from 테이블" +
                " where 컬럼1 = @ag_code" +
                " and 컬럼2 like @ag_name" +
                " order by 컬럼1";
            object[] param = { "@ag_code", 값1, "@ag_name", 값2 };

     sqlDBHelper.Init();
     DataTable retData1 = sqlDBHelper.executeQuery(query, param);

 

3. 파일첨부

sqlDBHelper.cs

0.01MB