데이타베이스 라이브러리

데이타베이스 작업을 할시에 필요한 라이브러리를 소개합니다.

select, update, delete, insert 모두에 사용되며, ad-hoc 쿼리나 스토어프로시저 모두에 사용 가능합니다.

사용하는 방법

아래 예제는 모두 Contacts class 가 있어서, UI 버튼 클릭시 Contacts 클래스의 property 에 개별적인 값을 대입한 후, 필요한 작업을 수행합니다.

Contacts.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using DataGridViewExample.lib;

namespace DataGridViewExample.data
{
    public class Contacts
    {
        #region Properties
        public int Idx { get; set; }
        public string Name { get; set; }
        public string BirthInfo { get; set; }
        public string ZipCode { get; set; }
        #endregion

        #region Functions
        /// 

        // Add the contacts info
        /// 
        /// 
        public bool AddContacts()
        {
            bool retval = false;
            try
            { 
                SQLOperator sdo = new SQLOperator("SampleDBConnectionString");

                string query = @"insert into [dbo].[tbl_contacts](Name, BirthInfo, ZipCode)
                                    values(@Name, @BirthInfo, @ZipCode);";

                sdo.ExecuteNoneQuery(query,
                                    new SqlParameter[]
                                    {
                                        new SqlParameter("@Name", Name),
                                        new SqlParameter("@BirthInfo", BirthInfo),
                                        new SqlParameter("@ZipCode", ZipCode)
                                    },
                                    CommandType.Text);
                sdo.Dispose();

                retval = true;
            }
            catch (SqlException se)
            {
                Trace.WriteLine(se.Message);
                retval = false;
            }
            catch(Exception ex)
            {
                Trace.WriteLine(ex.Message);
                retval = false;
            }

            return retval;
        }

        /// 

        /// Modify the contacts info
        /// 
        /// 
        public bool ModifyContacts()
        {
            bool retval = false;
            try
            {
                SQLOperator sdo = new SQLOperator("SampleDBConnectionString");

                string query = @"update [dbo].[tbl_contacts]
                                set  Name = @Name, BirthInfo = @BirthInfo, ZipCode = @ZipCode
                                where Id = @Idx;";

                sdo.ExecuteNoneQuery(query,
                                    new SqlParameter[]
                                    {
                                        new SqlParameter("@Name", Name),
                                        new SqlParameter("@BirthInfo", BirthInfo),
                                        new SqlParameter("@ZipCode", ZipCode),
                                        new SqlParameter("@Idx", Idx)
                                    },
                                    CommandType.Text);
                sdo.Dispose();

                retval = true;
            }
            catch (SqlException se)
            {
                Trace.WriteLine(se.Message);
                retval = false;
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
                retval = false;
            }

            return retval;
        }

        public bool RemoveContacts()
        {
            bool retval = false;
            try
            {
                SQLOperator sdo = new SQLOperator("SampleDBConnectionString");

                string query = @"delete from [dbo].[tbl_contacts]
                                where Id = @Idx;";

                sdo.ExecuteNoneQuery(query,
                                    new SqlParameter[]
                                    {
                                        new SqlParameter("@Idx", Idx)
                                    },
                                    CommandType.Text);
                sdo.Dispose();

                retval = true;
            }
            catch (SqlException se)
            {
                Trace.WriteLine(se.Message);
                retval = false;
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
                retval = false;
            }

            return retval;
        }
        #endregion
    }
}

1. select 시

DataTable set 을 가져와서 DataGridView 의 DataSource 에 바인드 합니다.

            SQLOperator sdo = new SQLOperator("SampleDBConnectionString");
            DataTable dt = sdo.ExecuteQuery("select Id, Name, BirthInfo, ZipCode from [dbo].[tbl_contacts]");

            dataGridView1.DataSource = dt;

            dt.Dispose();
            sdo.Dispose();

DataTable set 을 가져와서 List에 넣는 후 DataGridView 의 DataSource 에 바인드 합니다.

            SQLOperator sdo = new SQLOperator("SampleDBConnectionString");
            DataTable dt = sdo.ExecuteQuery("select Id, Name, BirthInfo, ZipCode from [dbo].[tbl_contacts]");

            List<Contacts>  pContact = new List<Contacts>();
            if(dt.Rows.Count > 0)
            {
                foreach(DataRow row in dt.Rows)
                {
                    pContact.Add(new Contacts()
                        {
                            Idx = Convert.ToInt32(row["Id"]),
                            Name = row["Name"].ToString(),
                            BirthInfo = row["BirthInfo"].ToString(),
                            ZipCode = row["ZipCode"].ToString()
                        });
                }
            }

            dataGridView1.DataSource = pContact;

            dt.Dispose();
            sdo.Dispose();

2. update 시

            Contacts contact = new Contacts();

            contact.Idx = Convert.ToInt32(lblIdx.Text);
            contact.Name = txtName.Text.Trim();
            contact.BirthInfo = txtBirthInfo.Text.Trim();
            contact.ZipCode = txtZipCode.Text.Trim();

            if (contact.ModifyContacts())
            {
                toolStripStatusLabel1.Text = Properties.Resources.AddConfirmMsg;
            }
            else
            {
                toolStripStatusLabel1.Text = Properties.Resources.AddErrorMsg;
            }

3. delete 시

            Contacts contact = new Contacts();

            contact.Idx = Convert.ToInt32(lblIdx.Text);
            string message = string.Format("[{0}] 정보를 삭제하시겠습니까", txtName.Text.Trim());

            if (MessageBox.Show(message, "확인", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.OK)
            { 
                if (contact.RemoveContacts())
                {
                    toolStripStatusLabel1.Text = Properties.Resources.RemoveConfirmMsg;
                }
                else
                {
                    toolStripStatusLabel1.Text = Properties.Resources.RemoveErrorMsg;
                }
            }

4. insert 시

            Contacts contact = new Contacts();

            contact.Name = txtName.Text.Trim();
            contact.BirthInfo = txtBirthInfo.Text.Trim();
            contact.ZipCode = txtZipCode.Text.Trim();

            if (contact.AddContacts())
            {
                toolStripStatusLabel1.Text = Properties.Resources.AddConfirmMsg;
            }
            else 
            { 
                toolStripStatusLabel1.Text = Properties.Resources.AddErrorMsg;
            }

데이타베이스 라이브러리는 아래와 같습니다.

SQLOperator.cs

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

namespace DataGridViewExample.lib
{
    /// 


    /// Summary description for SqlDataOperator
    /// 
    public class SQLOperator : IDisposable
    {
        private SqlConnection conn;
        private SqlCommand comm;
        private SqlDataAdapter adap;
        private SqlTransaction tran;

        public SqlConnection Connection
        {
            get
            {
                return conn;
            }
            set
            {
                conn = value;
            }
        }

        public SqlCommand Command
        {
            get
            {
                return comm;
            }
            set
            {
                comm = value;
            }
        }

        public SqlTransaction Transaction
        {
            get
            {
                return tran;
            }
        }

        public SQLOperator()
            : this("default")
        {
        }

        public SQLOperator(string connectionStringName)
        {
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString);
            //conn = new SqlConnection(Properties.Settings.Default.SampleDBConnectionString);

            comm = new SqlCommand();
            comm.Connection = conn;
            adap = new SqlDataAdapter();
        }

        public SqlTransaction BeginTransaction()
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            tran = conn.BeginTransaction();

            return tran;
        }

        public void RollBackTran()
        {
            if (tran != null)
                tran.Rollback();
        }

        public void CommitTran()
        {
            if (tran != null)
                tran.Commit();
        }

        public SqlDataReader ExecuteReader(string query, SqlParameter[] parameters, CommandType commType)
        {
            comm.CommandType = commType;
            comm.CommandText = query;
            AddParameters(parameters);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

            return dr;
        }

        public SqlDataReader ExecuteReader(string spName, SqlParameter[] parameters)
        {
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = spName;
            AddParameters(parameters);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

            return dr;
        }

        public SqlDataReader ExecuteReader(string query)
        {
            comm.CommandType = CommandType.Text;
            comm.CommandText = query;

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

            return dr;
        }

        public object ExecuteScalar(string sql)
        {
            comm.CommandText = sql;
            comm.CommandType = CommandType.Text;

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            object result = comm.ExecuteScalar();
            comm.Parameters.Clear();

            return result;
        }

        public object ExecuteScalar(string spName, SqlParameter[] parameters)
        {
            comm.CommandText = spName;
            comm.CommandType = CommandType.StoredProcedure;
            AddParameters(parameters);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            object result = comm.ExecuteScalar();
            comm.Parameters.Clear();

            return result;
        }

        public object ExecuteScalar(string spName, SqlParameter[] parameters, CommandType commType)
        {
            comm.CommandText = spName;
            comm.CommandType = commType;
            AddParameters(parameters);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            object result = comm.ExecuteScalar();
            comm.Parameters.Clear();

            return result;
        }

        public object ExecuteScalar(string spName, SqlParameter[] parameters, CommandType commType, SqlTransaction tran)
        {
            comm.Transaction = tran;

            return ExecuteScalar(spName, parameters, commType);
        }

        public object ExecuteScalar(string spName, SqlParameter[] parameters, SqlTransaction tran)
        {
            comm.Transaction = tran;

            return ExecuteScalar(spName, parameters);
        }

        public void ExecuteNoneQuery(string spName, SqlParameter[] parameters)
        {
            comm.CommandText = spName;
            comm.CommandType = CommandType.StoredProcedure;
            AddParameters(parameters);

            SqlParameter para = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
            para.Direction = ParameterDirection.ReturnValue;

            comm.Parameters.Add(para);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
        }

        public void ExecuteNoneQuery(string spName, SqlParameter[] parameters, CommandType commType)
        {
            comm.CommandText = spName;
            comm.CommandType = commType;
            AddParameters(parameters);

            SqlParameter para = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
            para.Direction = ParameterDirection.ReturnValue;

            comm.Parameters.Add(para);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
        }

        public int ExecuteNoneQuery_Retrun_Param(string spName, SqlParameter[] parameters, CommandType commType)
        {
            comm.CommandText = spName;
            comm.CommandType = commType;
            AddParameters(parameters);

            SqlParameter para = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
            para.Direction = ParameterDirection.ReturnValue;

            SqlParameter return_param = comm.Parameters.Add(para);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            comm.ExecuteNonQuery();
            var return_param_1 = return_param.Value;
            comm.Parameters.Clear();
            return (int)return_param_1;
        }

        public void ExecuteNoneQuery(string spName, SqlParameter[] parameters, CommandType commType, SqlTransaction tran)
        {
            comm.Transaction = tran;

            ExecuteNoneQuery(spName, parameters, commType);
        }

        public void ExecuteNoneQuery(string spName, SqlParameter[] parameters, SqlTransaction tran)
        {
            comm.Transaction = tran;

            ExecuteNoneQuery(spName, parameters);
        }

        public void ExecuteNoneQuery(string sql, SqlTransaction tran)
        {
            comm.Transaction = tran;

            ExecuteNoneQuery(sql);
        }

        public void ExecuteNoneQuery(string sql)
        {
            comm.CommandText = sql;
            comm.CommandType = CommandType.Text;

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            comm.ExecuteNonQuery();
        }

        public DataTable ExecuteQuery(string tableName, string spName, SqlParameter[] parameters)
        {
            DataTable dtResult = new DataTable(tableName);
            comm.CommandText = spName;
            comm.CommandType = CommandType.StoredProcedure;
            AddParameters(parameters);
            adap.SelectCommand = comm;

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            adap.Fill(dtResult);

            comm.Parameters.Clear();
            return dtResult;
        }

        public DataTable ExecuteQuery(string tableName, string spName, SqlParameter[] parameters, CommandType commType)
        {
            DataTable dtResult = new DataTable(tableName);
            comm.CommandText = spName;
            comm.CommandType = commType;
            AddParameters(parameters);
            adap.SelectCommand = comm;

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            adap.Fill(dtResult);

            comm.Parameters.Clear();
            return dtResult;
        }


        public DataSet ExecuteQueryToDS(string spName, SqlParameter[] parameters)
        {
            DataSet dsResult = new DataSet();
            comm.CommandText = spName;
            comm.CommandType = CommandType.StoredProcedure;
            AddParameters(parameters);

            adap.SelectCommand = comm;

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            adap.Fill(dsResult);
            comm.Parameters.Clear();

            return dsResult;
        }

        public DataTable ExecuteQuery(string tableName, string sql)
        {
            DataTable dtResult = ExecuteQuery(sql);

            dtResult.TableName = tableName;

            return dtResult;
        }

        public DataTable ExecuteQuery(string sql)
        {
            DataTable dtResult = new DataTable();
            comm.CommandText = sql;
            comm.CommandType = CommandType.Text;
            adap.SelectCommand = comm;

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            adap.Fill(dtResult);
            return dtResult;
        }

        private void AddParameters(SqlParameter[] parameters)
        {
            comm.Parameters.Clear();

            foreach (SqlParameter parameter in parameters)
            {
                comm.Parameters.Add(parameter);
            }
        }

        public string AntiInjection(string val)
        {
            val = RemoveScriptTag(val).Replace("'", "''");

            return val;
        }

        public static string RemoveScriptTag(string s)
        {
            System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex("(?<capture><[/]?script[a-zA-Z_0-9=\"\'\\s]*>)");

            System.Text.RegularExpressions.MatchEvaluator evaluator = new System.Text.RegularExpressions.MatchEvaluator(MatchReplace);

            return reg.Replace(s, evaluator);
        }

        public static string MatchReplace(System.Text.RegularExpressions.Match m)
        {
            string s = m.Groups["capture"].Value;

            s = s.Replace("<", "<").Replace(">", ">");

            return s;
        }

        #region IDisposable 멤버

        public void Dispose()
        {
            comm.Parameters.Clear();

            if (conn.State == ConnectionState.Open)
                conn.Close();
        }

        #endregion
    }
}