21# C# Mysql Connection 라이브러리

using System; using System.Collections.Generic; using System.Data; using System.Reflection; using MySql.Data.MySqlClient; using System.Runtime.CompilerServices;   namespace Project.MySql {     /// <summary>     /// MySqlManager의 요약 설명입니다.     /// </summary>     public static class MySqlManager     {         public static Int32 ExecuteScalar(string connectionString, string procedureName, Dictionary<string, object> paramsList)         {             Int32 resultCode = (Int32)ErrorNumbers.SQL_UNKNOWN_FAILED;               using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))             using (MySqlCommand sqlCommand = new MySqlCommand(procedureName, sqlConnection))             {                 sqlCommand.CommandTimeout = 10;                 sqlCommand.CommandType = CommandType.StoredProcedure;                   foreach (KeyValuePair<string, object> pair in paramsList)                 {                     sqlCommand.Parameters.AddWithValue(pair.Key, pair.Value);                 }                   try                 {                     sqlConnection.Open();                     object result = sqlCommand.ExecuteScalar();                     resultCode = Convert.ToInt32(result);                 }                 catch (Exception ex)                 {                     resultCode = (Int32)ErrorNumbers.SQL_EXCEPTION;                     WebServerLogger.Instance.Write(LogTypes.ERROR, 1, string.Format("Error ExecuteScalar - ProcedureName : {0}, ex : {1}", procedureName, ex.Message));                 }             }               return resultCode;         }           public static void ExecuteNonQuery(string connectionString, string procedureName, Dictionary<string, object> paramsList, out Int32 resultCode)         {             resultCode = (Int32)ErrorNumbers.SQL_UNKNOWN_FAILED;               using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))             using (MySqlCommand sqlCommand = new MySqlCommand(procedureName, sqlConnection))             {                 sqlCommand.CommandTimeout = 10;                 sqlCommand.CommandType = CommandType.StoredProcedure;                   foreach (KeyValuePair<string, object> pair in paramsList)                 {                     sqlCommand.Parameters.AddWithValue(pair.Key, pair.Value);                 }                   try                 {                     sqlConnection.Open();                       if (sqlCommand.ExecuteNonQuery() > 0)                         resultCode = (Int32)ErrorNumbers.SUCCESS;                 }                 catch (Exception ex)                 {                     resultCode = (Int32)ErrorNumbers.SQL_EXCEPTION;                     WebServerLogger.Instance.Write(LogTypes.ERROR, 1, string.Format("Error ExecuteNonQuery - ProcedureName : {0}, ex : {1}", procedureName, ex.Message));                 }             }         }           public static List<T> ExecuteReader<T>(string connectionString, string procedureName, Dictionary<string, object> paramsList, out Int32 resultCode) where T : new()         {             return ExecuteReader<T>(connectionString, procedureName, paramsList, Selector<T>, out resultCode);         }           private static T Selector<T>(IDataRecord dr) where T : new()         {             T obj = new T();               foreach (PropertyInfo prop in obj.GetType().GetProperties())             {                 if (!object.Equals(dr[prop.Name], DBNull.Value))                 {                     prop.SetValue(obj, dr[prop.Name], null);                 }             }               return obj;         }           public static List<T> ExecuteReader<T>(string connectionString, string procedureName, Dictionary<string, object> paramsList, Func<IDataRecord, T> selector, out Int32 resultCode) where T : new()         {             var items = new List<T>();             resultCode = (Int32)ErrorNumbers.SQL_UNKNOWN_FAILED;               using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))             using (MySqlCommand sqlCommand = new MySqlCommand(procedureName, sqlConnection))             {                 sqlCommand.CommandTimeout = ServiceConfig.REQUEST_DB_TIMEOUT_SECOND;                 sqlCommand.CommandType = CommandType.StoredProcedure;                   foreach (KeyValuePair<string, object> pair in paramsList)                 {                     sqlCommand.Parameters.AddWithValue(pair.Key, pair.Value);                 }                   try                 {                     sqlConnection.Open();                       using (IDataReader r = sqlCommand.ExecuteReader())                     {                         var properties = ShareLib.Cache.StaticType<T>.ObjectType.GetProperties();                         while (r.Read())                         {                             T obj = new T();                               foreach (PropertyInfo prop in properties)                             {                                 if (!object.Equals(r[prop.Name], DBNull.Value))                                 {                                     prop.SetValue(obj, r[prop.Name], null);                                 }                             }                               items.Add(obj);                         }                           resultCode = (Int32)ErrorNumbers.SUCCESS;                         return items;                     }                 }                 catch (Exception ex)                 {                     resultCode = (Int32)ErrorNumbers.SQL_EXCEPTION;                     WebServerLogger.Instance.Write(LogTypes.ERROR, 1, string.Format("Error List<T> ExecuteReader - ProcedureName : {0}, ex : {1}", procedureName, ex.Message));                 }             }               return items;         }           private static readonly Dictionary<string, object> emptyParameter = new Dictionary<string, object>(0);         public static MySqlTable<T> ExecuteMySqlTable<T>(string connectionString, string procedureName, out Int32 resultCode)         {             return ExecuteMySqlTableImpl<T>(connectionString, procedureName, emptyParameter, out resultCode);         }         public static MySqlTable<T> ExecuteMySqlTable<T>(string connectionString, string procedureName, Dictionary<string, object> paramsList, out Int32 resultCode)         {             return ExecuteMySqlTableImpl<T>(connectionString, procedureName, paramsList, out resultCode);         }           [MethodImpl(MethodImplOptions.AggressiveInlining)]         private static MySqlTable<T> ExecuteMySqlTableImpl<T>(string connectionString, string procedureName, Dictionary<string, object> paramsList, out Int32 resultCode)         {             MySqlTable<T> mysqlTable = new MySqlTable<T>();             resultCode = (Int32)ErrorNumbers.SQL_UNKNOWN_FAILED;               using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))             using (MySqlCommand sqlCommand = new MySqlCommand(procedureName, sqlConnection))             {                 sqlCommand.CommandTimeout = ServiceConfig.REQUEST_DB_TIMEOUT_SECOND;                 sqlCommand.CommandType = CommandType.StoredProcedure;                   foreach (KeyValuePair<string, object> pair in paramsList)                 {                     sqlCommand.Parameters.AddWithValue(pair.Key, pair.Value);                 }                   try                 {                     sqlConnection.Open();                       using (IDataReader r = sqlCommand.ExecuteReader())                     {                         if (r.Read())                             resultCode = (Int32)r.GetInt32(0);                           if (r.NextResult() == true)                             mysqlTable.CreateTable(r);                     }                 }                 catch (Exception ex)                 {                     resultCode = (Int32)ErrorNumbers.SQL_EXCEPTION;                     WebServerLogger.Instance.Write(LogTypes.ERROR, 1, string.Format("Error List<T> ExecuteReader - ProcedureName : {0}, ex : {1}", procedureName, ex.Message));                 }             }               return mysqlTable;         }           public static List<MySqlTable<T>> ExecuteMySqlTableList<T>(string connectionString, string procedureName, Dictionary<string, object> paramsList, out Int32 resultCode)         {             List<MySqlTable<T>> mysqlTableList = new List<MySqlTable<T>>();             resultCode = (Int32)ErrorNumbers.SQL_UNKNOWN_FAILED;               using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))             using (MySqlCommand sqlCommand = new MySqlCommand(procedureName, sqlConnection))             {                 sqlCommand.CommandTimeout = ServiceConfig.REQUEST_DB_TIMEOUT_SECOND;                 sqlCommand.CommandType = CommandType.StoredProcedure;                   foreach (KeyValuePair<string, object> pair in paramsList)                 {                     sqlCommand.Parameters.AddWithValue(pair.Key, pair.Value);                 }                   try                 {                     sqlConnection.Open();                       using (IDataReader r = sqlCommand.ExecuteReader())                     {                         if (r.Read())                             resultCode = (Int32)r.GetInt32(0);                           while (r.NextResult() == true)                         {                             MySqlTable<T> mysqlTable = new MySqlTable<T>();                             mysqlTable.CreateTable(r);                               mysqlTableList.Add(mysqlTable);                         }                     }                 }                 catch (Exception ex)                 {                     resultCode = (Int32)ErrorNumbers.SQL_EXCEPTION;                     WebServerLogger.Instance.Write(LogTypes.ERROR, 1, string.Format("Error List<T> ExecuteReader - ProcedureName : {0}, ex : {1}", procedureName, ex.Message));                 }             }               return mysqlTableList;         }     } }   MySqlManager.cs

 

using System; using System.Collections.Generic; using System.Data;   namespace Project.MySql {     public class MySqlTable<T>     {         public List<string> Columns         {             get;             set;         }           public string AddColumn         {             set             {                 if (Columns.Contains(value) == true)                     new ArgumentException("이미 등록된 컬럼값 입니다.");                   Columns.Add(value);             }         }           public List<List<T>> Rows         {             get;             set;         }           public Int32 Count         {             get             {                 return Rows.Count;             }         }           public T this[int index]         {             get             {                 return Rows[0][index];             }         }           public T this[int row, int index]         {             get             {                 return Rows[row][index];             }         }           public T this[int row, string name]         {             get             {                 Int32 index = Columns.FindIndex(t => t == name);                   if (index == -1)                     new ArgumentException("해당 Column이 존재하지 않습니다.");                   return Rows[row][index];             }         }           public MySqlTable()         {             Columns = new List<string>();             Rows = new List<List<T>>();         }           public void CreateTable(IDataReader reader)         {             try             {                 Columns = new List<string>(reader.FieldCount);                   for (int i = 0; i < reader.FieldCount; i++)                 {                     AddColumn = reader.GetName(i);                 }                   while (reader.Read())                 {                     List<T> row = new List<T>(Columns.Count);                       for (int i = 0; i < Columns.Count; i++)                     {                         if (reader.GetValue(i) == DBNull.Value)                             row.Add(default(T));                         else                             row.Add((T)reader.GetValue(i));                     }                       Rows.Add(row);                 }             }             catch (Exception ex)             {                 new DataException("CreateTable : " + ex.Message);                 //new ArgumentException("해당 Column이 존재하지 않습니다.");             }         }     } }   MySqlTable.cs

 

 

MySqlTable<object> readerList = MySqlManager.ExecuteMySqlTable<object>(ServiceConfig.db_ConnectionString, MySqlProcedureName.sp_BuyShopStar, paramList, out Int32 resultCode);    Int32 resultNumber = MySqlManager.ExecuteScalar(ServiceConfig.db_ConnectionString, MySqlProcedureName.sp_AccountDelete, paramList);   List<MySqlTable<object>> readerMysqlTableList = MySqlManager.ExecuteMySqlTableList<object>(ServiceConfig.db_readonly_ConnectionString, MySqlProcedureName.sp_UserPostList_r, paramList, out Int32 resultCode);

 

출처는 내 머리 -..-