建立資料庫聯結

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using InsApp.word;
using InsApp.log4;

namespace InsApp.Linkdata
{
    /// <summary>
    /// static OleDbConnection CreateConn()         建立資料庫聯結
    /// string[] GetSqlCmd_Array(string Db_Sql)     得到sql資料，返回數組
    /// bool GetSqlCmd_bool(string Db_Sql)          接收sql語句，返回bool結果
    /// string GetSqlCmd_String(string ReturnSql)   接受一個查詢語句，返回一個需要的欄位資料
    /// void GetQryString(ref String fString, String column, String val, String Or_And, String exp)查詢字串組合 Where (....) 資料
    /// </summary>
    public class DBdata
    {
        InsApp.word.CreateCode Ck_word = new CreateCode();
        Type type = System.Reflection.MethodBase.GetCurrentMethod().DeclaringType;

        #region CreateConn 建立資料庫聯結
        // 建立資料庫聯結
        public static OleDbConnection CreateConn()
        {
            string Cstr = System.Configuration.ConfigurationManager.AppSettings["strConnection"];
            string MdbPath = System.Configuration.ConfigurationManager.AppSettings["MdbPath"];
            Cstr += System.Web.HttpContext.Current.Server.MapPath(MdbPath);
            OleDbConnection Conn_Sql = new OleDbConnection(Cstr);
            return Conn_Sql;

        }
        #endregion

        #region 得到sql資料，返回數組
        /// <summary>
        /// 得到sql資料，返回數組
        /// </summary>
        /// <param name="Db_Sql">sql資料</param>
        /// <returns></returns>
        public string[] GetSqlCmd_Array(string Db_Sql)
        {
            string[] GetdataArray = null;
            ArrayList myAL = new ArrayList();

            OleDbConnection Conn = CreateConn();
            Conn.Open();
            OleDbCommand myCmd = new OleDbCommand(Db_Sql, Conn);
            OleDbDataReader myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
            try
            {
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        for (int i = 0; i < myReader.FieldCount; i++)
                        {
                            myAL.Add(myReader[i].ToString());
                        }
                    }
                    GetdataArray = (string[])myAL.ToArray(typeof(string));
                    return GetdataArray;
                }
                else
                {
                    return GetdataArray;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                myCmd.Dispose();
                myReader.Close();
                Conn.Close();
            }

        }
        #endregion

        #region   接收sql語句，返回bool結果
        /// <summary>
        /// 接收sql語句，返回bool結果
        /// </summary>
        /// <param name="Db_Sql">接收sql語句</param>
        /// <returns></returns>
        public bool GetSqlCmd_bool(string Db_Sql)
        {
            try
            {
                using (OleDbConnection Conn = CreateConn())
                {
                    Conn.Open();
                    OleDbCommand myCmd = new OleDbCommand(Db_Sql, Conn);
                    myCmd.ExecuteNonQuery();
                    myCmd.Dispose();
                    Conn.Close();
                    return true;
                }
            }
            catch (Exception ex)
            {
                LogUtil.ERROR(type, ex.Message);
                return false;
            }
        }
        #endregion

        #region   GetSqlCmd_String接受一個查詢語句，返回一個需要的欄位資料
        /// <summary>
        /// ReturnSql接受一個查詢語句，返回一個需要的欄位資料
        /// 返回需要的欄位 2006-11-24
        /// </summary>
        /// <param name="ReturnSql"></param>
        /// <returns></returns>
        public string GetSqlCmd_String(string ReturnSql)
        {
            string GetDate = string.Empty;
            try
            {
                if (Ck_word.CheckNullstr(ReturnSql) == false)
                {
                    throw new Exception("未找到該行參數");
                }
                else
                {
                    using (OleDbConnection Conn = CreateConn())
                    {
                        OleDbCommand command = new OleDbCommand(ReturnSql, Conn);
                        Conn.Open();
                        OleDbDataReader M_reader = command.ExecuteReader();
                        if (M_reader.HasRows)
                        {
                            while (M_reader.Read())
                            {
                                GetDate += M_reader[0].ToString();
                            }
                        }
                        command.Dispose();
                        M_reader.Close();
                        Conn.Close();
                    }
                    return GetDate;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
        }
        #endregion

        #region 查詢字串組合 Where (....) 資料
        /// <summary>
        /// 類型未String的查詢參數組合
        /// 使用方法：王新 time 2006-11-25
        /// 首先需要一個完整的sql語句，例如select * from tablename
        /// ref String fString一個空的字串按照地址傳入，返回後和sql語句合并
        /// </summary>
        /// <param name="fString">需要記錄的字串 可以是String,起止日期等於終止日期的date,decimal</param>
        /// <param name="column">資料庫欄位</param>
        /// <param name="val">參數,多個用";"隔開</param>
        /// <param name="Or_And">Or || And 第一個運算式不需要</param>
        /// <param name="exp">有效運算式 例如:"like",">=d" ,"=c"等於</param>
        public void GetQryString(ref String fString, String column, String val, String Or_And, String exp)
        {
            if (val != null && !val.Equals(""))
            {
                val = val.Trim();

                if (!fString.Equals(""))
                    fString += " " + Or_And + " ";
                else
                    fString += " Where ";

                int i = 0;
                foreach (String Str in val.Split(';'))
                {
                    if (i++ > 0)
                        fString += " Or ";//裡面迴圈規定必須是或者
                    else
                        fString += " (";

                    fString += String.Format(ReturnSQL(column, exp), Str);
                    if (i == val.Split(';').Length) fString += " )";
                }
            }
        }

        protected void GetQryString(ref String fString, String column, String val, String exp)
        {
            GetQryString(ref fString, column, val, "", exp);
        }

        /// <summary>
        /// 時間段查詢，接收日期參數
        /// </summary>
        /// <param name="fString"></param>
        /// <param name="column"></param>
        /// <param name="dt_start"></param>
        /// <param name="dt_end"></param>
        /// <param name="Or_And"></param>
        public void GetQryDateTime(ref String fString, String column, string dt_start, string dt_end, String Or_And)
        {
            /*if (dt_start.StartsWith("1900-01-01 12:00:00")
                && dt_end.StartsWith("1900-01-01 12:00:00"))
                return;
             * //2006-12-11
             *            
            */
            if (dt_start.StartsWith("1900-01-01") && dt_end.StartsWith("1900-01-01"))
            return;

            if (dt_start.StartsWith(dt_end.Substring(0, 10)))   //(0,10)  10表示1900-01-01資料的長度
            {
                GetQryString(ref fString,
                    column,
                    dt_start.ToString(),
                    Or_And,
                    "date"
                    );
            }
            else
            {
                if (!fString.Equals(""))
                    fString += " " + Or_And + " ";
                else
                    fString += " Where ";

                fString += "("+ column + ">='" + dt_start + "' AND "+ column+ "<= '" + dt_end + "' )";
            }
        }

        String ReturnSQL(String column, String exp)
        {
            string tempString = "";

            exp = exp.Trim().ToLower();

            switch (exp)
            {
                case "like":
                case "not like":
                    tempString = " " + column + " " + exp + "'%{0}%'";
                    break;
                case ">d":
                case "<d":
                case ">=d":
                case "<=d":
                case "=d":
                case "<>d":
                    tempString = " " + column + " " + exp.Substring(0, exp.Length - 1) + "{0}";
                    break;
                case "=c":
                case "<>c":
                    tempString = " " + column + " " + exp.Substring(0, exp.Length - 1) + "'{0}'";
                    break;
                case "monthdate":
                    tempString = " datediff(month," + column + ",'{0}')=0 ";
                    break;
                case "date":
                    tempString = " datediff(d," + column + ",'{0}')=0 ";
                    break;
                case "in":
                case "not in":
                    tempString = " " + column + " " + exp + " ({0}) ";
                    break;
                default:
                    break;
            }
            return tempString;
        }
        #endregion

    }
}