IMEX.mes.wcf/DK.Basics/DataAccess/DataManager.cs

/*******************************************************************************
 * Copyright(c) 2014 dongke All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：DataManager.cs
 *      2.功能描述：本系统的数据库访问封装类，所有数据访问都调用
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      欧阳涛          2013/11/21          1.00            新建
 *******************************************************************************/

using System;
using System.Collections.Generic;
using System.Data;
//using System.Data.OracleClient;
using System.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Basics.DataAccess
{
    public sealed class DataManager
    {
        //public static string ConnectionString = "Data Source=ORCL;User Id=dongkeTest;Password=dongke;Integrated Security =no;";// 数据库连接串
        //public static string ConnectionString = "Provider=OraOLEDB.Oracle;Persist Security Info=False;Data Source=ORCL;User ID=dongkeTest;Password=dongke";
        
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.19)(PORT=1521))(CONNECT_DATA=(SID=DEVIBOSSPRD)));User Id=deviboss;Password=dongke;";

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnectionStringFormat = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};";

        /// <summary>
        /// 日志文件保存地址
        /// </summary>
        public static string LogFileStartPath = System.Windows.Forms.Application.StartupPath;

        ///// <summary>
        ///// 日志文件名称
        ///// </summary>
        //public static string LogFileName = "AppLog.log";

        ///// <summary>
        ///// 日志文件控制类型 True 表示可以写入 False 表示不需要写入
        ///// </summary>
        //public static bool LogFileControl = true;

        public static string LicString = null;
        public static DataSet LicDataSet = null;
        public static object LicLock = new object();

        #region ExecuteNonQuery
        /// <summary>   
        /// 执行SQL语句，返回影响的记录数
        /// </summary>   
        /// <param name="sqlString">SQL语句</param>
        /// <returns>
        ///     0:SQL语句执行影响的行数为0
        ///     大于0:SQL语句执行成功
        ///     小于0:SQL语句执行失败
        /// </returns>   
        public static int ExecuteNonQuery(string sqlString)
        {
            try
            {
                return OracleHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sqlString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行带有参数的SQL语句
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <param name="cmdParms">SQL语句的参数</param>
        /// <returns>
        ///     0:SQL语句执行影响的行数为0
        ///     大于0:SQL语句执行成功
        ///     小于0:SQL语句执行失败
        /// </returns>
        public static int ExecuteNonQuery(string sqlString, params OracleParameter[] commandParms)
        {
            try
            {
                return OracleHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sqlString, commandParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 用一个已经存在的数据库连接执行一个SQL命令
        /// 例如：int result = ExecuteNonQuery("PublishOrders",
        ///                     CommandType.StoredProcedure,
        ///                     new OracleParameter(":prodid", 24));
        /// </summary>
        /// <param name="commandType">执行命令类型(存储过程、SQL语句等)</param>
        /// <param name="commandText">存储过程名称或者SQL语句</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>执行命令对数据库影响的行数</returns>
        public static int ExecuteNonQuery(string commandText, CommandType commandType,
                                                                    params OracleParameter[] commandParameters)
        {
            try
            {
                return OracleHelper.ExecuteNonQuery(ConnectionString, commandType, commandText, commandParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">多条SQL语句</param>
        /// <returns>
        /// -1：执行sql语句失败
        /// 其他：执行sql语句成功，并返回影响行数
        /// </returns>
        public static int ExecuteNonQuery(List<string> sqlStringList)
        {
            using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
            {
                // 打开数据库连接
                sqlConnection.Open();
                // 为执行命令对象赋值
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
                sqlCommand.Transaction = sqlTransaction;

                int affectedRowCount = 0;
                int affectedRowCountTemp = 0;
                try
                {
                    // 循环遍历SQL文数据集
                    foreach (string sqlString in sqlStringList)
                    {
                        if (!string.IsNullOrEmpty(sqlString))
                        {
                            sqlCommand.CommandText = sqlString;
                            affectedRowCountTemp = sqlCommand.ExecuteNonQuery();
                            if (0 > affectedRowCountTemp)
                            {
                                sqlTransaction.Rollback();
                                return -1;
                            }
                            affectedRowCount += affectedRowCountTemp;
                        }
                    }
                    sqlTransaction.Commit();

                    // 清除sql执行对象的属性值
                    sqlCommand.Parameters.Clear();
                    sqlCommand.Dispose();
                }
                catch (SqlException ex)
                {
                    sqlTransaction.Rollback();
                    throw ex;
                }
                finally
                {
                    // 释放资源
                    if (sqlConnection.State != ConnectionState.Closed && sqlConnection != null)
                    {
                        sqlConnection.Close();
                    }
                }
                return affectedRowCount;
            }
        }

        /// <summary>
        /// 执行带参数的多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">SQL语句列表</param>
        /// <param name="commandParmsList">对应的参数列表</param>
        /// <returns>
        /// -1：执行sql语句失败
        /// 其他：执行sql语句成功，并返回影响行数
        /// </returns>
        public static int ExecuteNonQuery(List<string> sqlStringList, List<OracleParameter[]> commandParmsList)
        {
            // sql语句的个数与参数个数不相同时，直接返回-1
            if (sqlStringList.Count != commandParmsList.Count)
            {
                return -1;
            }
            using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
            {
                // 打开数据库连接
                sqlConnection.Open();
                // 为执行命令对象赋值
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
                sqlCommand.Transaction = sqlTransaction;

                int affectedRowCount = 0;
                int affectedRowCountTemp = 0;
                try
                {
                    // 循环遍历文本和参数
                    for (int i = 0; i < sqlStringList.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(sqlStringList[i]) && commandParmsList[i] != null)
                        {
                            // 执行命令的参数设定
                            foreach (OracleParameter commandParms in commandParmsList[i])
                            {
                                if ((commandParms.Direction == ParameterDirection.InputOutput
                                    || commandParms.Direction == ParameterDirection.Input)
                                    && (commandParms.Value == null))
                                {
                                    commandParms.Value = DBNull.Value;
                                }
                                sqlCommand.Parameters.Add(commandParms);
                            }

                            // SQL命令的执行语句设定
                            sqlCommand.CommandText = sqlStringList[i];
                            // 执行命令
                            affectedRowCountTemp = sqlCommand.ExecuteNonQuery();
                            if (0 > affectedRowCountTemp)
                            {
                                sqlTransaction.Rollback();
                                return -1;
                            }
                            affectedRowCount += affectedRowCountTemp;
                            sqlCommand.Parameters.Clear();
                        }
                    }
                    sqlTransaction.Commit();

                    // 清除sql执行对象的属性值
                    sqlCommand.Parameters.Clear();
                    sqlCommand.Dispose();
                }
                catch (SqlException ex)
                {
                    sqlTransaction.Rollback();
                    throw ex;
                }
                finally
                {
                    // 释放资源
                    if (sqlConnection.State != ConnectionState.Closed && sqlConnection != null)
                    {
                        sqlConnection.Close();
                    }
                }
                return affectedRowCount;
            }
        }
        #endregion

        #region ExecuteDataset
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>   
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet数据集</returns>
        public static DataSet ExecuteDataset(string sqlString)
        {
            try
            {
                return OracleHelper.Query(ConnectionString, sqlString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行带有参数的查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <param name="cmdParms">查询语句的参数</param>
        /// <returns>DataSet数据集</returns>
        public static DataSet ExecuteDataset(string sqlString, params OracleParameter[] commandParms)
        {
            try
            {
                return OracleHelper.Query(ConnectionString, sqlString, commandParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}