databaselayer.cs

C#编写的应用程序

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;

namespace Function_Ontology
{
    public class DatabaseLayer
    {
        /// <summary>
        /// 保护变量，数据库连接。
        /// </summary>
        protected SqlConnection _Connection;

        /// <summary>
        /// 保护变量，数据库连接串。
        /// </summary>
        protected String _ConnectionString;

        #region 属性
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        public SqlConnection Connection
        {
            set
            {
                this._Connection = value;
            }
            get
            {
                return this._Connection;
            }
        }

        /// <summary>
        /// 获取数据库连接串
        /// </summary>
        public string ConnectionString
        {

            set
            {
                this._ConnectionString = value;
            }
            get
            {
                return this._ConnectionString;
            }
        }
        #endregion

        /// <summary>
        ///  重载构造函数，便于实例化。
        /// </summary>
        public DatabaseLayer()
        {
            ConnectionString = global::Function_Ontology.Properties.Settings.Default.ProductConnectionString;
        }

       /// <summary>
        /// 重载构造函数，从配置文件中读取连接字符串名称。
       /// </summary>
       /// <param name="ConfigConnectionString">配置文件中连接字符串名称</param>
       /// <param name="i">无意义</param>
        public DatabaseLayer(string ConfigConnectionString,int i)
        {
            //获取连接字符串。
            this._ConnectionString = ConfigurationManager.ConnectionStrings[ConfigConnectionString].ConnectionString;

        }

        /// <summary>
        /// 重载构造函数，传入一个连接字符串参数。
        /// </summary>
        /// <param name="connstr">连接字符串</param>
        public DatabaseLayer(string connstr)
        {
            //获取连接字符串。
            this._ConnectionString=connstr;
        }

        /// <summary>
        /// 析构函数，释放非托管资源
        /// </summary>
        ~DatabaseLayer()
        {
            try
            {
                if (this._Connection != null)
                    this._Connection.Close();
            }
            catch { }
            try
            {
                Dispose();
            }
            catch { }
        }

        /// <summary>
        /// 公有方法，打开数据库连接。
        /// </summary>
        public void Open()
        {
            if (this._Connection == null)
            {
                this._Connection = new SqlConnection(this._ConnectionString);
            }
            if (this._Connection.State.Equals(ConnectionState.Closed))
            {
                this._Connection.Open();
            }
        }

        /// <summary>
        /// 公有方法，关闭数据库连接。
        /// </summary>
        public void Close()
        {
            if (this._Connection != null)
                this._Connection.Close();
        }

        /// <summary>
        /// 公有方法，释放资源。
        /// </summary>
        public void Dispose()
        {
            // 确保连接被关闭
            if (this._Connection != null)
            {
                this._Connection.Dispose();
                this._Connection = null;
            }
        }

        /// <summary>
        ///  公有方法，通过表名，获取数据，返回一个DataSet。
        /// </summary>
        /// <param name="TableName">表名</param>
        /// /// <param name="i">无意思，便于方便重载</param>
        /// <returns></returns>
        public DataSet GetDataSet(String TableName, int i)
        {
            Open();
            string SqlString = "select * from " + TableName;
            SqlDataAdapter adapter = new SqlDataAdapter(SqlString,this._Connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            Close();
            return dataset;
        }

        /// <summary>
        /// 公有方法，通过查询语句，获取数据，返回一个DataSet。
        /// </summary>
        /// <param name="SqlString">查询语句</param>        
        /// <returns></returns>
        public DataSet GetDataSet(String SqlString)
        {
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(SqlString, this._Connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            Close();
            return dataset;
        }


        /// <summary>
        /// 公有方法，通过表名，获取数据，返回一个DataTable。
        /// </summary>
        /// <param name="SqlString">Sql语句</param>
        /// <param name="i">无意思，便于方便重载</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(String TableName, int i)
        {
            DataSet dataset = GetDataSet(TableName,i);
            dataset.CaseSensitive = false;
            return dataset.Tables[0];
        }

        /// <summary>
        /// 公有方法，通过查询语句，获取数据，返回一个DataTable。
        /// </summary>
        /// <param name="SqlString">查询语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(String SqlString)
        {
            DataSet dataset = GetDataSet(SqlString);
            dataset.CaseSensitive = false;
            return dataset.Tables[0];
        }

        /// <summary>
        /// 公有方法，通过表名，获取数据，返回一个DataRow。
        /// </summary>
        /// <param name="SqlString">Sql语句</param>
        /// <returns>DataRow</returns>
        public DataRow GetDataRow(String TableName)
        {
            DataSet dataset = GetDataSet(TableName);
            dataset.CaseSensitive = false;
            if (dataset.Tables[0].Rows.Count > 0)
            {
                return dataset.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 公有方法，执行Sql语句。
        /// </summary>
        /// <param name="SqlString">Sql语句</param>
        /// <returns>对Update、Insert、Delete为影响到的行数，其他情况为-1</returns>
        public int ExecuteSQL(String SqlString)
        {
            int count = -1;
            Open();
            try
            {
                SqlCommand cmd = new SqlCommand(SqlString, this._Connection);
                count = cmd.ExecuteNonQuery();
            }
            catch
            {
                count = -1;
            }
            finally
            {
                Close();
            }
            return count;
        }

        /// <summary>
        /// 公有方法，执行一组Sql语句。
        /// </summary>
        /// <param name="SqlStrings">Sql语句组</param>
        /// <returns>是否成功</returns>
        public bool ExecuteSQL(String[] SqlStrings)
        {
            bool success = true;
            Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;
            try
            {
                foreach (String str in SqlStrings)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                Close();
            }
            return success;
        }

        /// <summary>
        /// 公有方法，执行一组Sql语句。
        /// </summary>
        /// <param name="SqlStrings">Sql语句组</param>
        /// <returns>是否成功</returns>
        public bool ExecuteSQL(ArrayList SqlStrings)
        {
            bool success = true;
            Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;
            try
            {
                foreach (String str in SqlStrings)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                Close();
            }
            return success;
        }

        /// <summary>
        /// 公有方法，在一个数据表中插入一条记录。
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="Cols">哈西表，键值为字段名，值为字段值</param>
        /// <returns>是否成功</returns>
        public bool Insert(String TableName, Hashtable Cols)
        {
            int Count = 0;

            if (Cols.Count <= 0)
            {
                return true;
            }

            String Fields = " (";
            String Values = " Values(";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                    Values += ",";
                }
                Fields += "[" + item.Key.ToString() + "]";
                Values += item.Value.ToString();
                Count++;
            }
            Fields += ")";
            Values += ")";

            String SqlString = "Insert into " + TableName + Fields + Values;

            String[] Sqls = { SqlString };
            return ExecuteSQL(Sqls);
        }

        /// <summary>
        /// 公有方法，更新一个数据表。
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="Cols">哈西表，键值为字段名，值为字段值</param>
        /// <param name="Where">Where子句</param>
        /// <returns>是否成功</returns>
        public bool Update(String TableName, Hashtable Cols, String Where)
        {
            int Count = 0;
            if (Cols.Count <= 0)
            {
                return true;
            }
            String Fields = " ";
            foreach (DictionaryEntry item in Cols)
            {
                if (Count != 0)
                {
                    Fields += ",";
                }
                Fields += "[" + item.Key.ToString() + "]";
                Fields += "=";
                Fields += item.Value.ToString();
                Count++;
            }
            Fields += " ";

            String SqlString = "Update " + TableName + " Set " + Fields + Where;

            String[] Sqls = { SqlString };
            return ExecuteSQL(Sqls);
        }


        /// <summary>
        /// 从项目名获取ID
        /// </summary>
        /// <param name="TableName">要查找的表名</param>
        /// <param name="TargetColume">要比较的列</param>
        /// <param name="sqlvalue">比较条件</param>
        /// <returns></returns>
        public int GetID(string TableName, string TargetColume, string sqlvalue)
        {
            int ID = -1;
            string SqlString = "select * from " + TableName + " where " + TargetColume + " like " + "'" + sqlvalue + "'";
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(SqlString,this._Connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            Close();
            ID = int.Parse(dataset.Tables[0].Rows[0].ItemArray[0].ToString());
            return ID;
        }

        /// <summary>
        /// 由ID获取项目列。
        /// </summary>
        /// <param name="TableName">要查找的表名</param>
        /// <param name="TargetID">目标ID列</param>
        /// <param name="ColuneIndex">需要返回的列</param>
        /// <param name="sqlvalue">比较条件</param>
        /// <returns></returns>
        public string GetColume(string TableName, string TargetID, int ColuneIndex, int sqlvalue)
        {
            string Item = "";
            string inquiry = "select * from " + TableName + " where " + TargetID + " = " + sqlvalue;
            DataSet ds = new DataSet();
            ds = this.GetDataSet(inquiry);
            if (ds.Tables[0].Rows.Count != 0)
            {
                Item = ds.Tables[0].Rows[0].ItemArray[ColuneIndex].ToString();
            }           
            return Item;
        }        
        
    }
}