C# 实现连接数据库实例

创建一个数据库连接实例

调用代码：

//查询 1
string strSql = "SELECT COUNT(*) AS ClaimTimes, SUM(ClaimAmount) AS TotalAmount FROM BOIQClaim WHERE ContentID = @ContentID";

DataTable dtClaim = new DataAccessLibrary().GetConnection().GetDataTable(strSql, new KeyValuePair<string, object>("ContentID", boiqContent.GID));

//查询 2
string loadLogSql = sqlAllOrgFont + string.Format("select * from BOIPDownLoadLog where PolicyNo='{0}' and OrgID in (select ID from #temp1) and LoadStatus <'03'", dr["PolicyNo"]) + sqlAllOrgEnd;
DataTable dtSettlement = new DataAccessLibrary().GetConnection().GetDataTable(loadLogSql, new KeyValuePair<string, object>("OrgID", policyDto.ddlOrgID));
//插入
 String sql = "insert into BOMessage (GID,Title,Content,MessageType,SourceChannel,IsBroadcast,Tag,UserID,EntryUser,EntryTime,IsRead,IsDeleted,IsPushed,OrgID,LinkID,DeviceTypes, Priority) values (@GID,@Title,@Content,@MessageType,'01',0,null,@UserID,'U0000000',getdate(),0,0,0,@OrgID,@LinkID,null, @Priority)";
            String messageID = Guid.NewGuid().ToString();

            try
            {
                if (da == null)
                {
                    da = getConn();
                }
                da.ExecuteNonQuery(sql,
                    new KeyValuePair<String, Object>("GID", messageID),
                    new KeyValuePair<String, Object>("title", title),
                    new KeyValuePair<String, Object>("content", content),
                    new KeyValuePair<String, Object>("messageType", messageType),
                    new KeyValuePair<String, Object>("userID", userID),
                    new KeyValuePair<String, Object>("orgID", orgID),
                    new KeyValuePair<String, Object>("linkID", linkID),
                    new KeyValuePair<string, object>("Priority", priority)
                    );
            }
            catch (Exception ex)
            {
                throw ex;
            }

连接数据库类文件：

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Web;

namespace Common
{
    /// <summary>
    /// 数据库相关操作类
    /// </summary>
    public class DataAccessLibrary
    {
        /// <summary>
        /// 获取数据库连接串
        /// </summary>
        /// <returns></returns>
        public DataAccess GetConnection()
        {
            DataAccess dataAccess = new DataAccess();
            dataAccess.SetDatabase("System.Data.dll",
                "System.Data.SqlClient.SqlConnection", "System.Data.SqlClient.SqlDataAdapter");
            dataAccess.SetConnectionString(Properties.Settings.Default["DBConnectionStringMain"].ToString());
            dataAccess.CurrentParameterFlag = "@";
            return dataAccess;
        }
    }
}