完整代码:/Files/greatverve/MyFiles.rar
一个项目我一般只用一个数据库,现在有需求,需要使用多个数据库,临时改了一下,不知道好不好,
请高手指教,大家使用多数据库的时候是如何切换的。
DBHelper.cs
private
static
string
connectionString
=
"
SQLCONNECTIONSTRING
"
;
/// <summary>
/// 切换数据库
/// </summary>
/// <param name="connStr"></param>
public static void SetSqlConnection( string connStr)
{
connectionString = connStr;
}
/// <summary>
/// 返回数据库连接字符串
/// </summary>
/// <returns></returns>
public static String GetSqlConnection()
{
String conn = ConfigurationManager.AppSettings[connectionString].ToString();
return conn;
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="Sqlstr"> SQL语句 </param>
/// <param name="param"> 参数对象数组 </param>
/// <returns></returns>
public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = Sqlstr;
cmd.Parameters.AddRange(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return 1 ;
}
}
/// <summary>
/// 切换数据库
/// </summary>
/// <param name="connStr"></param>
public static void SetSqlConnection( string connStr)
{
connectionString = connStr;
}
/// <summary>
/// 返回数据库连接字符串
/// </summary>
/// <returns></returns>
public static String GetSqlConnection()
{
String conn = ConfigurationManager.AppSettings[connectionString].ToString();
return conn;
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="Sqlstr"> SQL语句 </param>
/// <param name="param"> 参数对象数组 </param>
/// <returns></returns>
public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = Sqlstr;
cmd.Parameters.AddRange(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return 1 ;
}
}
Test.cs(BLL)
using
System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Configuration;
using System.Web;
using HY.Common;
using System.Text;
using System.Data.SqlClient;
namespace HY.BLL
{
public class Test
{
/// <summary>
/// 在静态类的静态构造函数里,切换默认数据库
/// </summary>
static Test()
{
DBHelper.SetSqlConnection( " SQLCONNECTIONSTRING " );
}
/// <summary>
/// 不需要切换数据库的,直接写
/// </summary>
/// <returns></returns>
public static DataTable getAll()
{
StringBuilder sb = new StringBuilder();
sb.Append( " select * from t_Users " );
return DBHelper.ExecuteDt(sb.ToString());
}
/// <summary>
/// 需要切换的话,函数中切换
/// </summary>
/// <returns></returns>
public static DataTable getTable()
{
DBHelper.SetSqlConnection( " SQLCONNECTIONSTRING1 " ); // BLL中这样切换
StringBuilder sb = new StringBuilder();
sb.Append( " select * from t_User " );
return DBHelper.ExecuteDt(sb.ToString());
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Configuration;
using System.Web;
using HY.Common;
using System.Text;
using System.Data.SqlClient;
namespace HY.BLL
{
public class Test
{
/// <summary>
/// 在静态类的静态构造函数里,切换默认数据库
/// </summary>
static Test()
{
DBHelper.SetSqlConnection( " SQLCONNECTIONSTRING " );
}
/// <summary>
/// 不需要切换数据库的,直接写
/// </summary>
/// <returns></returns>
public static DataTable getAll()
{
StringBuilder sb = new StringBuilder();
sb.Append( " select * from t_Users " );
return DBHelper.ExecuteDt(sb.ToString());
}
/// <summary>
/// 需要切换的话,函数中切换
/// </summary>
/// <returns></returns>
public static DataTable getTable()
{
DBHelper.SetSqlConnection( " SQLCONNECTIONSTRING1 " ); // BLL中这样切换
StringBuilder sb = new StringBuilder();
sb.Append( " select * from t_User " );
return DBHelper.ExecuteDt(sb.ToString());
}
}
}
web.config
<
appSettings
>
< add key ="SQLCONNECTIONSTRING" value ="data source=(local);uid=sa;pwd=sa;database=db#test" />
< add key ="SQLCONNECTIONSTRING1" value ="data source=(local);uid=sa;pwd=sa;database=hyoa_SysInfo" />
</ appSettings >
< add key ="SQLCONNECTIONSTRING" value ="data source=(local);uid=sa;pwd=sa;database=db#test" />
< add key ="SQLCONNECTIONSTRING1" value ="data source=(local);uid=sa;pwd=sa;database=hyoa_SysInfo" />
</ appSettings >
SqlTest.aspx.cs
protected
void
Page_Load(
object
sender, EventArgs e)
{
if ( ! IsPostBack)
Bind();
}
private void Bind()
{
// 切换数据库的操作封装在BLL中
GridView1.DataSource = Test.getAll();
GridView1.DataBind();
// 切换数据库
GridView2.DataSource = Test.getTable();
GridView2.DataBind();
}
{
if ( ! IsPostBack)
Bind();
}
private void Bind()
{
// 切换数据库的操作封装在BLL中
GridView1.DataSource = Test.getAll();
GridView1.DataBind();
// 切换数据库
GridView2.DataSource = Test.getTable();
GridView2.DataBind();
}