数据库基本操作类库

DataBaseHelper类。

1、执行sql语句

2、执行事物

数据库操作类

 public class DBOperator
    {
        private SqlConnection _cn;   //数据库连接

        private string _strConfigPath;

        public string StrConfigPath
        {
            get { return _strConfigPath; }
            set { _strConfigPath = value; }
        }

        public SqlConnection Connection
        {
            get { return _cn; }
            set { _cn = value; }
        }

        public DBOperator(string connectionString)
        {
            _cn = new SqlConnection(connectionString);
        }

        public DBOperator()
        {
            ConnectionString connectionString = XMLReader.ReadXml(_strConfigPath);
            Common.ConnectionString = string.Format(Common.ConnectionString, connectionString.DbServerPath, connectionString.DbName,
                connectionString.DbUser, connectionString.DbPwd);
            _cn = new SqlConnection(Common.ConnectionString);
        }

        /// <summary>
        /// 执行Sql语句,返回Sql语句影响的函数
        /// </summary>
        /// <param name="cmdText">待执行的Sql语句</param>
        /// <returns>Sql语句影响的函数</returns>
        public int ExecuteNonQuery(string cmdText)
        {
            int row = 0;
            try
            {
                if (_cn != null && _cn.State != ConnectionState.Open)
                {
                    _cn.Open();
                }               
                SqlCommand cmd = new SqlCommand(cmdText, _cn);
                cmd.CommandTimeout = 10000;
                row = cmd.ExecuteNonQuery();
            }
            finally
            {
                if (_cn != null && _cn.State == ConnectionState.Open)
                {
                    _cn.Close();
                }
            }

            return row;

        }

        /// <summary>
        /// 返回查询得到的结果及的第一行,第一列.
        /// </summary>
        /// <param name="cmdText">待执行的Sql语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText)
        {
            object obj = null;

            try
            {
                if (_cn != null && _cn.State != ConnectionState.Open)
                {
                    _cn.Open();
                }
                SqlCommand cmd = new SqlCommand(cmdText, _cn);
                cmd.CommandTimeout = 10000;
                obj = cmd.ExecuteScalar();
            }
            finally
            {
                if (_cn != null && _cn.State == ConnectionState.Open)
                {
                    _cn.Close();
                }
            }

            return obj;

        }

        /// <summary>
        /// 返回查询得到的结果集
        /// </summary>
        /// <param name="cmdText">sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string cmdText)
        {
            DataSet returnDs = null;

            try
            {
                if (_cn != null && _cn.State != ConnectionState.Open)
                {
                    _cn.Open();
                }
                SqlDataAdapter da = new SqlDataAdapter(cmdText, _cn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                returnDs = ds;
            }
            finally
            {
                if (_cn != null && _cn.State == ConnectionState.Open)
                {
                    _cn.Close();
                }
            }
            return returnDs;
        }

        /// <summary>
        /// 返回查询到的结果集
        /// </summary>
        /// <param name="cmdText">sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string cmdText)
        {
            DataTable returnDt = null;

           try
           {
               if (_cn != null && _cn.State != ConnectionState.Open)
               {
                   _cn.Open();
               }
               SqlDataAdapter da = new SqlDataAdapter(cmdText, _cn);
               DataSet ds = new DataSet();
               da.Fill(ds);
               returnDt = ds.Tables[0];
           }
           finally
           {
               if (_cn != null && _cn.State == ConnectionState.Open)
               {
                   _cn.Close();
               }
           }

           return returnDt;
        }


        public int ExecuteNonQuery(string cmdText,SqlTransaction transaction)
        {
            int row = 0;
            //try
            //{
                //if (_cn != null && _cn.State != ConnectionState.Open)
                //{
                //    _cn.Open();
                //}
                SqlCommand cmd = new SqlCommand(cmdText, _cn, transaction);
                cmd.CommandTimeout = 10000;
                row = cmd.ExecuteNonQuery();
            //}
            //finally
            //{
            //    if (_cn != null && _cn.State == ConnectionState.Open)
            //    {
            //        _cn.Close();
            //    }
            //}

            return row;

        }


        /// <summary>
        /// 返回查询得到的结果集
        /// </summary>
        /// <param name="cmdText">sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string cmdText,SqlTransaction transaction)
        {
            DataSet returnDs = null;

            //try
            //{
            //    if (_cn != null && _cn.State != ConnectionState.Open)
            //    {
            //        _cn.Open();
            //    }
                SqlCommand cmd = new SqlCommand(cmdText, _cn, transaction);
                cmd.CommandTimeout = 10000;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                returnDs = ds;
            //}
            //finally
            //{
            //    if (_cn != null && _cn.State == ConnectionState.Open)
            //    {
            //        _cn.Close();
            //    }
            //}
            return returnDs;
        }

 

公共类

     class Common
    {
        public static string ConnectionString = "Data Source={0};Initial Catalog={1};User Id={2};Password={3}";
    }

 

读取配置文件

 

  class XMLReader
    {
        public static ConnectionString ReadXml(string inStrXmlPath)
        {
            ConnectionString returnConnectionString = new ConnectionString();
            #region 读取XML
            XmlDocument xmlDoc = new XmlDocument();

            xmlDoc.Load(AppDomain.CurrentDomain.BaseDirectory + @"\Config.xml");
            XmlNodeList nodeList = xmlDoc.SelectSingleNode("Connection").ChildNodes;
            foreach (XmlNode xn in nodeList)
            {
                XmlElement xe = (XmlElement)xn;
                if (xe.Name == "ConnectionString")
                {
                    XmlNodeList nls = xe.ChildNodes;
                    foreach (XmlNode xn1 in nls)
                    {
                        XmlElement xe2 = (XmlElement)xn1;
                        switch (xe2.Name)
                        {
                            case "ServerPath":
                                returnConnectionString.DbServerPath = xe2.InnerText;
                                break;
                            case "User":
                                returnConnectionString.DbUser = xe2.InnerText;
                                break;
                            case "Pwd":
                                returnConnectionString.DbPwd = xe2.InnerText;
                                break;
                            case "DBName":
                                returnConnectionString.DbName = xe2.InnerText;
                                break;
                        }
                    }
                }
            }
            #endregion

            return returnConnectionString;
            
        }
    }

 

 连接串实体类

 

     class ConnectionString
    {
        private string _dbServerPath;

        public string DbServerPath
        {
            get { return _dbServerPath; }
            set { _dbServerPath = value; }
        }
        private string _dbUser;

        public string DbUser
        {
            get { return _dbUser; }
            set { _dbUser = value; }
        }
        private string _dbPwd;

        public string DbPwd
        {
            get { return _dbPwd; }
            set { _dbPwd = value; }
        }
        private string _dbName;

        public string DbName
        {
            get { return _dbName; }
            set { _dbName = value; }
        }
    }

 

配置文件

 

<?xml version="1.0" encoding="utf-8" ?>
<Connection>
  <ConnectionString genre="电信">
    <ServerPath>192.168.1.66</ServerPath>
    <User>sa</User>
    <Pwd>kingdee</Pwd>
    <DBName>AIS20071113082248</DBName>
  </ConnectionString>
</Connection>