创建数据库联接

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

    }
}

posted @ 2007-05-28 15:41  世之云枭  阅读(178)  评论(0编辑  收藏  举报