数据库操作类

//============================================//
//数据库操作类
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;        }    }}

}
}