[C#] SqlModule

/**************************************************/
/* Date   : 2017/03/02                            */
/* E-mail : [email protected]                   */
/* Note   :                                       */
/*    char     -> String                          */
/*    nchar    -> String                          */
/*    decimal  -> decimal                         */
/*    date     -> DateTime                        */
/*    datetime -> DateTime                        */
/*    time     -> TimeSpan                        */
/**************************************************/

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

namespace YanLib.SqlModule
{
    public class SqlModule<T> where T : new()
    {
        /// <summary>
        /// 搜尋
        /// </summary>
        /// <param name="SqlServer">主機位址</param>
        /// <param name="SqlName">主機名稱</param>
        /// <param name="SqlUid">登入帳號</param>
        /// <param name="SqlPwd">登入密碼</param>
        /// <param name="TableName">資料表名稱</param>
        /// <param name="Where">條件約束語句，預設為空(搜尋所有)</param>
        /// <param name="OrderBy">資料排序，預設為空(不排序)</param>
        /// <param name="Timeout">連線超時時間，單位為秒(s)，預設為 10 秒</param>
        /// <param name="Trusted_Connection">信任連接，預設為 True</param>
        /// <returns>回傳 List<T>，若搜尋過程中發生 Exception 則傳回 null</returns>
        public static List<T> SqlSearch(string SqlServer, string SqlName, string SqlUid, string SqlPwd, string TableName,
                                        string Where = null, List<OrderByPair> OrderBy = null, int Timeout = 10, bool Trusted_Connection = true)
        {
            // A Result of Dictionary
            List<T> respT = new List<T>();

            // Create a Connection object
            using (SqlConnection connection = new SqlConnection(string.Format("Data Source={0}; Database={1}; User ID={2}; Password={3}; Connection Timeout={4}; Trusted_Connection={5};",
                                                                              SqlServer, SqlName, SqlUid, SqlPwd, Timeout.ToString(), Trusted_Connection.ToString())))
            {
                try
                {
                    // Open the connection
                    connection.Open();
                }
                catch (SqlException)
                {
                    return null;
                }

                try
                {
                    // Set ORDER BY value
                    string orderBy = string.Empty;
                    if (OrderBy != null)
                    {
                        foreach (OrderByPair orderCondition in OrderBy)
                        {
                            orderBy += string.Format("{0} {1},", orderCondition.GetKey(), orderCondition.GetModeStr());
                        }
                        orderBy = orderBy.Trim(',');
                    }
                    // Create a Command object
                    SqlCommand myCmd = new SqlCommand(string.Format("SELECT * FROM {0}", TableName) +
                                                      (string.IsNullOrWhiteSpace(Where) ? "" : string.Format(" WHERE {0}", Where)) +
                                                      (OrderBy == null ? ";" : string.Format(" ORDER BY {0};", orderBy)), connection);
                    // Concatenate the query result into a string
                    SqlDataReader myReader = myCmd.ExecuteReader();

                    while (myReader.Read())
                    {
                        T getItem = new T();
                        foreach (FieldInfo field in typeof(T).GetFields(BindingFlags.Public | BindingFlags.Instance))
                        {
                            for (int i = 0; i < myReader.FieldCount; i++)
                            {
                                if (string.Equals(myReader.GetName(i), field.Name))
                                {
                                    try
                                    {
                                        if (myReader.GetDataTypeName(i).Equals("int"))
                                        {
                                            field.SetValue(getItem, myReader.GetInt32(i));
                                        }
                                        else if (myReader.GetDataTypeName(i).Equals("float"))
                                        {
                                            field.SetValue(getItem, myReader.GetFloat(i));
                                        }
                                        else if (myReader.GetDataTypeName(i).Equals("decimal"))
                                        {
                                            field.SetValue(getItem, myReader.GetDecimal(i));
                                        }
                                        else if (myReader.GetDataTypeName(i).Contains("char"))
                                        {
                                            field.SetValue(getItem, myReader.GetString(i).Trim(' '));
                                        }
                                        else if (myReader.GetDataTypeName(i).Equals("date") || myReader.GetDataTypeName(i).Equals("datetime"))
                                        {
                                            field.SetValue(getItem, myReader.GetDateTime(i));
                                        }
                                        else if (myReader.GetDataTypeName(i).Equals("time"))
                                        {
                                            field.SetValue(getItem, myReader.GetTimeSpan(i));
                                        }
                                    }
                                    catch (System.Data.SqlTypes.SqlNullValueException)
                                    {
                                        field.SetValue(getItem, null);
                                    }
                                    break;
                                }
                            }
                        }
                        respT.Add(getItem);
                    }
                }
                catch (SqlException)
                {
                    return null;
                }
            }
            return (respT);
        }

        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="SqlServer">主機位址</param>
        /// <param name="SqlName">主機名稱</param>
        /// <param name="SqlUid">登入帳號</param>
        /// <param name="SqlPwd">登入密碼</param>
        /// <param name="TableName">資料表名稱</param>
        /// <param name="InsertItem">欲插入之資料</param>
        /// <param name="Timeout">連線超時時間，單位為秒(s)，預設為 10 秒</param>
        /// <param name="Trusted_Connection">信任連接，預設為 True</param>
        /// <returns>傳回長度為 2 之字串陣列，[0] 表示插入結果，[1] 表示受影響之資料數或錯誤訊息</returns>
        public static string[] SqlInsert(string SqlServer, string SqlName, string SqlUid, string SqlPwd, string TableName, T InsertItem, int Timeout = 10, bool Trusted_Connection = true)
        {
            // Create a Connection object

            using (SqlConnection connection = new SqlConnection(string.Format("Data Source={0}; Database={1}; User ID={2}; Password={3}; Connection Timeout={4}; Trusted_Connection={5};",
                                                                              SqlServer, SqlName, SqlUid, SqlPwd, Timeout.ToString(), Trusted_Connection.ToString())))
            {
                try
                {
                    // Open the connection
                    connection.Open();
                }
                catch (SqlException ex)
                {
                    return (new string[] { "false", ex.Message });
                }

                // Do work here; connection closed on following line.
                try
                {
                    string[] Params = new string[] { string.Empty, string.Empty };
                    foreach (FieldInfo field in typeof(T).GetFields(BindingFlags.Public | BindingFlags.Instance))
                    {
                        try
                        {
                            Params[0] += string.Format("[{0}],", field.Name);
                            if (field.GetValue(InsertItem).GetType() == typeof(char))
                            {
                                Params[1] += string.Format("'{0}'", field.GetValue(InsertItem));
                            }
                            else if (field.GetValue(InsertItem).GetType() == typeof(string))
                            {
                                Params[1] += string.Format("'{0}'", field.GetValue(InsertItem));
                            }
                            else if (field.GetValue(InsertItem).GetType() == typeof(DateTime))
                            {
                                Params[1] += string.Format("'{0}'", ((DateTime)(field.GetValue(InsertItem))).ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                            else if (field.GetValue(InsertItem).GetType() == typeof(TimeSpan))
                            {
                                Params[1] += string.Format("'{0}'", field.GetValue(InsertItem).ToString());
                            }
                            else
                            {
                                Params[1] += field.GetValue(InsertItem);
                            }
                            Params[1] += ',';
                        }
                        catch (NullReferenceException)
                        {
                            Params[1] += "null";
                        }
                    }
                    Params[0] = Params[0].TrimEnd(','); Params[1] = Params[1].TrimEnd(',');
                    SqlCommand command = new SqlCommand(string.Format("INSERT INTO {0} ({1}) VALUES ({2});", TableName, Params[0], Params[1]), connection);
                    return (new string[] { "true", command.ExecuteNonQuery().ToString() });
                }
                catch (SqlException ex)
                {
                    return (new string[] { "false", ex.Message });
                }
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="SqlServer">主機位址</param>
        /// <param name="SqlName">主機名稱</param>
        /// <param name="SqlUid">登入帳號</param>
        /// <param name="SqlPwd">登入密碼</param>
        /// <param name="TableName">資料表名稱</param>
        /// <param name="UpdateItem">欲更新之資料</param>
        /// <param name="Where">條件約束語句</param>
        /// <param name="Timeout">連線超時時間，單位為秒(s)，預設為 10 秒</param>
        /// <param name="Trusted_Connection">信任連接，預設為 True</param>
        /// <returns>傳回長度為 2 之字串陣列，[0] 表示更新結果，[1] 表示受影響之資料數或錯誤訊息</returns>
        public static string[] SqlUpdate(string SqlServer, string SqlName, string SqlUid, string SqlPwd, string TableName,
                                         T UpdateItem, string Where, int Timeout = 10, bool Trusted_Connection = true)
        {
            // Create a Connection object
            using (SqlConnection connection = new SqlConnection(string.Format("Data Source={0}; Database={1}; User ID={2}; Password={3}; Connection Timeout={4}; Trusted_Connection={5};",
                                                                              SqlServer, SqlName, SqlUid, SqlPwd, Timeout.ToString(), Trusted_Connection.ToString())))
            {
                try
                {
                    // Open the connection
                    connection.Open();
                }
                catch (SqlException ex)
                {
                    return (new string[] { "false", ex.Message });
                }

                // Do work here; connection closed on following line.
                try
                {
                    // Set UPDATE value
                    string Params = string.Empty;
                    foreach (FieldInfo field in typeof(T).GetFields(BindingFlags.Public | BindingFlags.Instance))
                    {
                        try
                        {
                            if (field.GetValue(UpdateItem).GetType() == typeof(char))
                            {
                                Params += string.Format("'{0}'", field.GetValue(UpdateItem));
                            }
                            else if (field.GetValue(UpdateItem).GetType() == typeof(string))
                            {
                                Params += string.Format("{0}={1}", field.Name, string.Format("'{0}'", field.GetValue(UpdateItem)));
                            }
                            else if (field.GetValue(UpdateItem).GetType() == typeof(DateTime))
                            {
                                Params += string.Format("{0}={1}", field.Name, string.Format("'{0}'", ((DateTime)(field.GetValue(UpdateItem))).ToString("yyyy-MM-dd HH:mm:ss")));
                            }
                            else if (field.GetValue(UpdateItem).GetType() == typeof(TimeSpan))
                            {
                                Params += string.Format("{0}='{1}'", field.Name, field.GetValue(UpdateItem).ToString());
                            }
                            else
                            {
                                Params += string.Format("{0}={1}", field.Name, field.GetValue(UpdateItem));
                            }
                        }
                        catch (NullReferenceException)
                        {
                            Params += string.Format("{0}={1}", field.Name, "null");
                        }
                        Params += ',';
                    }
                    Params = Params.TrimEnd(',');

                    SqlCommand command = new SqlCommand(string.Format("UPDATE {0} SET {1} WHERE {2};", TableName, Params, Where), connection);
                    return (new string[] { "true", command.ExecuteNonQuery().ToString() });
                }
                catch (SqlException ex)
                {
                    return (new string[] { "false", ex.Message });
                }
            }
        }

        /// <summary>
        /// 刪除
        /// </summary>
        /// <param name="SqlServer">主機位址</param>
        /// <param name="SqlName">主機名稱</param>
        /// <param name="SqlUid">登入帳號</param>
        /// <param name="SqlPwd">登入密碼</param>
        /// <param name="TableName">資料表名稱</param>
        /// <param name="Where">條件約束語句</param>
        /// <param name="Timeout">連線超時時間，單位為秒(s)，預設為 10 秒</param>
        /// <param name="Trusted_Connection">信任連接，預設為 True</param>
        /// <returns>傳回長度為 2 之字串陣列，[0] 表示刪除結果，[1] 表示受影響之資料數或錯誤訊息</returns>
        public static string[] SqlDelete(string SqlServer, string SqlName, string SqlUid, string SqlPwd, string TableName, string Where, int Timeout = 10, bool Trusted_Connection = true)
        {// Create a Connection object
            using (SqlConnection connection = new SqlConnection(string.Format("Data Source={0}; Database={1}; User ID={2}; Password={3}; Connection Timeout={4}; Trusted_Connection={5};",
                                                                              SqlServer, SqlName, SqlUid, SqlPwd, Timeout.ToString(), Trusted_Connection.ToString())))
            {
                try
                {
                    // Open the connection
                    connection.Open();
                }
                catch (SqlException ex)
                {
                    return (new string[] { "false", ex.Message });
                }

                // Do work here; connection closed on following line.
                try
                {
                    SqlCommand command = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1}", TableName, Where), connection);
                    return (new string[] { "true", command.ExecuteNonQuery().ToString() });
                }
                catch (SqlException ex)
                {
                    return (new string[] { "false", ex.Message });
                }
            }
        }
    }

    public class OrderByPair
    {
        private string key = "";
        private OrderMode mode = OrderMode.ASC;

        public OrderByPair(string key, OrderMode mode)
        {
            this.key = key;
            this.mode = mode;
        }

        public string GetKey()
        {
            return this.key;
        }

        public void SetKey(string key)
        {
            this.key = key;
        }

        public OrderMode GetMode()
        {
            return this.mode;
        }

        public void SetMode(OrderMode mode)
        {
            this.mode = mode;
        }

        public string GetModeStr()
        {
            return this.mode.ToString();
        }
    }

    public enum OrderMode
    {
        ASC,
        DESC
    }
}