数据库操作类

//============================================//
//数据库操作类
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace HYCRM.Common.CommDB
{
///
/// 数据库操作类
///
public class DbOperation
{
//从配置文件(App.config)中获取连接字符串
public static string connectionString = ConfigurationManager.AppSettings[“HYCRM_DB_CONNECTION”].ToString();
//定义SqlConnection
private static SqlConnection objSqlConn = null;

/// <summary>
/// 连接数据库
/// </summary>
private static void GetConnection()
{
    try
    {
        if(objSqlConn == null)
        {
            objSqlConn = new SqlConnection(connectionString);
            objSqlConn.Open();
        }
    }
    catch (Exception ex)  
    {
        throw ex;
    } 
}

/// <summary>
/// 根据查询SQL字符串，获取DataTable
/// </summary>
///<param name = "strSelectSql">查询SQL字符串</param>
///<returns>返回DataSet</returns>
public static DataTable GetDataTable(string strSelectSql)
{
    try
    {
        GetConnection();
        DataTable dtTarget = new DataTable();
        using (SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(strSelectSql, objSqlConn)) 
        {
            objSqlDataAdapter.Fill(dtTarget);
        }
        return dtTarget;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (objSqlConn != null)
        {
            objSqlConn.Close();
            objSqlConn.Dispose();
            objSqlConn = null;
        }
    }
}

/// <summary>
/// 根据查询SQL字符串数组，获取DataSet
/// </summary>
///<param name = "strSelectSqlSet">查询SQL字符串数组</param>
///<returns>返回DataSet</returns>
public static DataSet GetDataSet(string[] strSelectSqlSet)
{
    try
    {
        GetConnection();
        DataSet dsTarget = new DataSet();
        if (strSelectSqlSet.Length > 0)
        {
            for (int i = 0; i < strSelectSqlSet.Length; i++)
            {
                dsTarget.Tables.Add(GetDataTable(strSelectSqlSet[i]));
                dsTarget.Tables[i].TableName = "DT" + i.ToString();
            }
        }
        return dsTarget;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (objSqlConn != null)
        {
            objSqlConn.Close();
            objSqlConn.Dispose();
            objSqlConn = null;
        }
    }
}

/// <summary>
/// 将DataGridView的数据源更新到数据库中
/// </summary>
/// <param name="dtResult">数据源</param>
/// <returns>执行状态</returns>
public static bool UpdateDataSource(DataTable dtResult, string strDestinationTableName)
{
    try
    {
        StringBuilder sbSQL = new StringBuilder();
        sbSQL.AppendLine("TRUNCATE TABLE [dbo].[" + strDestinationTableName + "]");
        ExecuteNonQuery(sbSQL.ToString());
        ExecuteSqlBulkCopy(dtResult, strDestinationTableName);
        return true;
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

/// <summary>
/// 执行非查询SQL字符串，如Insert，Update，Delete
/// </summary>
///<param name = "strSql">非查询SQL字符串</param>
///<returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string strSql)
{
    try
    {
        GetConnection();
        int intResult = 0;
        using (SqlCommand objSqlCmd = new SqlCommand(strSql, objSqlConn))
        {
            intResult = objSqlCmd.ExecuteNonQuery();
        }
        return intResult;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (objSqlConn != null)
        {
            objSqlConn.Close();
            objSqlConn.Dispose();
            objSqlConn = null;
        }
    }
}

/// <summary>
/// 执行数据快速插入或复制,要求数据源DataTable的表结构与目标表完全一致
/// </summary>
///<param name = "dtSource">数据源DataTable</param>
///<param name = "strDestinationTableName">目标表名称</param>
///<returns>返回执行状态</returns>
public static bool ExecuteSqlBulkCopy(DataTable dtSource, string strDestinationTableName)
{
    try
    {
        GetConnection();
        using (SqlBulkCopy objSqlBulkCopy = new SqlBulkCopy(objSqlConn))
        {
            objSqlBulkCopy.DestinationTableName = strDestinationTableName;
            for (int i = 0; i < dtSource.Columns.Count; i++)
            {
                objSqlBulkCopy.ColumnMappings.Add(i, i);
            }
            objSqlBulkCopy.WriteToServer(dtSource);
        }
        return true;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (objSqlConn != null)
        {
            objSqlConn.Close();
            objSqlConn.Dispose();
            objSqlConn = null;
        }
    }
}

/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="strStoreProcName">存储过程名称</param>
/// <param name="strParametersName">参数名集合</param>
/// <param name="strParameterValue">参数值集合</param>
/// <returns>执行状态</returns>
public static bool ExecuteStoreProcedure(string strStoreProcName, string[] strParametersName, string[] strParameterValue)
{
    try
    {
        GetConnection();
        using (SqlCommand objSqlCmd = new SqlCommand(strStoreProcName, objSqlConn))
        {
            objSqlCmd.CommandType = CommandType.StoredProcedure;
            int intParaCount = strParametersName.Length;
            for (int i = 0; i < intParaCount; i++)
            {
                objSqlCmd.Parameters.Add(strParametersName[i], SqlDbType.NVarChar);
                objSqlCmd.Parameters[strParametersName[i]].Value = strParameterValue[i];
            }
            objSqlCmd.ExecuteNonQuery();
        }
        return true;
    }                                
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (objSqlConn != null)
        {
            objSqlConn.Close();
            objSqlConn.Dispose();
            objSqlConn = null;
        }
    }
}

/// <summary>
/// 执行SQL脚本文件,必须引用Microsoft.SqlServer.ConnectionInfo.dll， Microsoft.SqlServer.Management.Sdk.Sfc.dll， Microsoft.SqlServer.Smo.dll缺一不可
/// </summary>
/// <param name="strFileFullPath">SQL脚本文件的完整路径</param>
public static void ExecuteSqlScriptFile(string strFileFullPath)
{
    try
    {
        GetConnection();
        FileInfo objFileInfo = new FileInfo(strFileFullPath);
        string SqlScript = objFileInfo.OpenText().ReadToEnd();
        Server objServer = new Server(new ServerConnection(objSqlConn));
        objServer.ConnectionContext.ExecuteNonQuery(SqlScript);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (objSqlConn != null)
        {
            objSqlConn.Close();
            objSqlConn.Dispose();
            objSqlConn = null;
        }
    }
}

}
}