sqlservercommand.cs

Discuz集成.net源代码

using System;

namespace Motion.DZ.DB
{
    public class SqlServerCommand : IDbProvider
    {
        private string _connectionString = String.Empty, 
            _errorString = String.Empty;

        /// <summary>
        /// 异常错误信息
        /// </summary>
        public string vErrorResultString
        {
            get { return _errorString; }
        }

        /// <summary>
        /// 数据库连接语句
        /// </summary>
        public string vDataBaseConnectionString
        {
            set { _connectionString = value; }
            get { return _connectionString; }
        }

        /// <summary>
        /// MS-SQL数据库类
        /// </summary>
        /// <param name="vDataBaseConnectionString">SQL数据库连接语句</param>
        public SqlServerCommand(string vDBaseConnectionString)
        {
            this._connectionString = vDBaseConnectionString;
        }
        /// <summary>
        /// MS-SQL数据库类
        /// </summary>
        public SqlServerCommand()
        {

        }

        /// <summary>
        /// 执行SqlCommand参数并返回内存中数据的一个表
        /// </summary>
        /// <param name="_vParameters">SqlCommand的Parameters值，用一个X行2列的二维数组表示。[0, X]为Parameter名，[1, X]为Parameter的值</param>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <param name="_vStoredProcedure">是否为存储过程</param>
        /// <returns>System.Data.DataTable</returns>
        public System.Data.DataTable vDataTable(object[,] _vParameters, string _vCommandText, bool _vStoredProcedure)
        {
            System.Data.DataSet vmyDS = this.vDataSet(_vParameters, _vCommandText, _vStoredProcedure);
            return ((!Object.Equals(vmyDS, null)) && (vmyDS.Tables.Count > 0)) ? vmyDS.Tables[0].Copy() : null;
        }

        /// <summary>
        /// 执行SqlCommand参数并返回内存中数据的一个表
        /// </summary>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <returns>System.Data.DataTable</returns>
        public System.Data.DataTable vDataTable(string _vCommandText)
        {
            return this.vDataTable(null, _vCommandText, false);
        }

        /// <summary>
        /// 执行SqlCommand参数并返回数据在内存中的缓存
        /// </summary>
        /// <param name="_vParameters">SqlCommand的Parameters值，用一个X行2列的二维数组表示。[0, X]为Parameter名，[1, X]为Parameter的值</param>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <param name="_vStoredProcedure">是否为存储过程</param>
        /// <returns>System.Data.DataSet</returns>
        public System.Data.DataSet vDataSet(object[,] _vParameters, string _vCommandText, bool _vStoredProcedure)
        {
            System.Data.DataSet vmyDS = new System.Data.DataSet();
            try
            {
                System.Data.SqlClient.SqlCommand vComm = (System.Data.SqlClient.SqlCommand)this.vCommand(_vParameters, _vCommandText, _vStoredProcedure);
                vComm.Connection.Open();
                try
                {
                    new System.Data.SqlClient.SqlDataAdapter(vComm).Fill(vmyDS);
                    this._errorString = String.Empty;
                }
                catch (Exception E)
                {
                    vmyDS = null;
                    this._errorString = E.Message;
                }
                try
                {
                    vComm.Connection.Close();
                    vComm.Parameters.Clear();
                }
                catch { }
            }
            catch (Exception E)
            {
                vmyDS = null;
                this._errorString = E.Message;
            }
            return vmyDS;
        }
        /// <summary>
        /// 执行SqlCommand参数并返回数据在内存中的缓存
        /// </summary>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <returns>System.Data.DataSet</returns>
        public System.Data.DataSet vDataSet(string _vCommandText)
        {
            return this.vDataSet(null, _vCommandText, false);
        }

        /// <summary>
        /// 执行SqlCommand参数并返回结果集中的第一行第一列。忽略其他行或列。
        /// </summary>
        /// <param name="_vParameters">SqlCommand的Parameters值，用一个X行2列的二维数组表示。[0, X]为Parameter名，[1, X]为Parameter的值</param>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <param name="_vStoredProcedure">是否为存储过程</param>
        /// <returns>System.Data.SqlClient.SqlCommand.ExecuteScalar()</returns>
        public object vExecuteScalar(object[,] _vParameters, string _vCommandText, bool _vStoredProcedure)
        {
            object vmyRd;
            try
            {
                System.Data.SqlClient.SqlCommand vComm = (System.Data.SqlClient.SqlCommand)this.vCommand(_vParameters, _vCommandText, _vStoredProcedure);
                vComm.Connection.Open();
                try
                {
                    vmyRd = vComm.ExecuteScalar();
                    this._errorString = String.Empty;
                }
                catch (Exception E)
                {
                    vmyRd = null;
                    this._errorString = E.Message;
                }
                try
                {
                    vComm.Connection.Close();
                    vComm.Parameters.Clear();
                }
                catch { }
            }
            catch (Exception E)
            {
                vmyRd = null;
                this._errorString = E.Message;
            }
            return vmyRd;
        }
        /// <summary>
        /// 执行SqlCommand参数并返回结果集中的第一行第一列。忽略其他行或列。
        /// </summary>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <returns>System.Data.SqlClient.SqlCommand.ExecuteScalar()</returns>
        public object vExecuteScalar(string _vCommandText)
        {
            return this.vExecuteScalar(null, _vCommandText, false);
        }

        /// <summary>
        /// 执行SqlCommand参数并返回受影响的行数
        /// </summary>
        /// <param name="_vParameters">SqlCommand的Parameters值，用一个X行2列的二维数组表示。[0, X]为Parameter名，[1, X]为Parameter的值</param>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <param name="_vStoredProcedure">是否为存储过程</param>
        /// <returns>受影响的行数</returns>
        public int vExecuteNonQuery(object[,] _vParameters, string _vCommandText, bool _vStoredProcedure)
        {
            int vX;
            try
            {
                System.Data.SqlClient.SqlCommand vComm = (System.Data.SqlClient.SqlCommand)this.vCommand(_vParameters, _vCommandText, _vStoredProcedure);
                vComm.Connection.Open();
                try
                {
                    vX = vComm.ExecuteNonQuery();
                    this._errorString = String.Empty;
                }
                catch (Exception E)
                {
                    vX = -1;
                    this._errorString = E.Message;
                }
                try
                {
                    vComm.Connection.Close();
                    vComm.Parameters.Clear();
                }
                catch { }
            }
            catch (Exception E)
            {
                vX = -1;
                this._errorString = E.Message;
            }
            return vX;
        }
        /// <summary>
        /// 执行SqlCommand参数并返回受影响的行数
        /// </summary>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <returns>受影响的行数</returns>
        public int vExecuteNonQuery(string _vCommandText)
        {
            return this.vExecuteNonQuery(null, _vCommandText, false);
        }

        /// <summary>
        /// 执行SqlCommand参数并返回受影响的行数
        /// </summary>
        /// <param name="_vParameters">SqlCommand的Parameters值，用一个X行2列的二维数组表示。[0, X]为Parameter名，[1, X]为Parameter的值</param>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <param name="_vStoredProcedure">是否为存储过程</param>
        /// <param name="id">返回最后的id</param>
        /// <returns>受影响的行数</returns>
        public int vExecuteNonQuery(object[,] _vParameters, string _vCommandText, bool _vStoredProcedure, out int id)
        {
            int vX;
            try
            {
                System.Data.SqlClient.SqlCommand vComm = (System.Data.SqlClient.SqlCommand)this.vCommand(_vParameters, _vCommandText, _vStoredProcedure);
                vComm.Connection.Open();
                try
                {
                    this._errorString = String.Empty;
                    vX = vComm.ExecuteNonQuery();
                    vComm.Parameters.Clear();
                    vComm.CommandType = System.Data.CommandType.Text;
                    vComm.CommandText = "SELECT SCOPE_IDENTITY();";
                    Int32.TryParse(vComm.ExecuteScalar().ToString(), out id);
                }
                catch (Exception E)
                {
                    vX = id = -1;
                    this._errorString = E.Message;
                }
                vComm.Connection.Close();
            }
            catch (Exception E)
            {
                vX = id = -1;
                this._errorString = E.Message;
            }
            return vX;
        }

        /// <summary>
        /// 执行SqlCommand参数并返回受影响的行数
        /// </summary>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <param name="id">返回最后的id</param>
        /// <returns>受影响的行数</returns>
        public int vExecuteNonQuery(string _vCommandText, out int id)
        {
            return this.vExecuteNonQuery(null, _vCommandText, false, out id);
        }

        /// <summary>
        /// SqlCommand
        /// </summary>
        /// <param name="_vParameters">SqlCommand的Parameters值，用一个2行X列的二维数组表示。[0, X]为Parameter名，[1, X]为Parameter的值</param>
        /// <param name="_vCommandText">要执行的Sql语句</param>
        /// <param name="_vStoredProcedure">是否为存储过程</param>
        /// <returns>System.Data.SqlClient.SqlCommand</returns>
        public object vCommand(object[,] _vParameters, string _vCommandText, bool _vStoredProcedure)
        {
            System.Data.SqlClient.SqlCommand vComm = new System.Data.SqlClient.SqlCommand();
            vComm.Connection = new System.Data.SqlClient.SqlConnection(this.vDataBaseConnectionString);
            vComm.CommandText = _vCommandText;
            vComm.CommandType = _vStoredProcedure ? System.Data.CommandType.StoredProcedure : System.Data.CommandType.Text;
            if ((!Object.Equals(_vParameters, null)) && (_vParameters.GetUpperBound(0) == 1) && (_vParameters.Rank == 2))
                for (int i = 0; i <= _vParameters.GetUpperBound(1); i++)
                    if (!Object.Equals(_vParameters[0, i], null)) vComm.Parameters.AddWithValue(_vParameters[0, i].ToString(), _vParameters[1, i]);
            return vComm;
        }
    }
}