我将连接数据库的语句都放在一个类库的项目中,连接数据库以及操作数据库的放在一个类文件中(都是静态方法),另外的一个asp.net项目引用此项目。但是要求类库连接两个数据库,我不想在asp.net项目中设置连接,而是,在类库的项目中创建针对这两个连接的方法,如果针对两个连接创建两个类好不好?还是有更好的方法?请指教!目前代码如下:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;public class DataSource
{
private static SqlConnection conn =new SqlConnection("Data Source=10.9.162.24;UID=sa;PWD=0;DataBase=uu");//目前没用上
public static SqlConnection getConnection(string strConnection)
{
try
{
conn = new SqlConnection();
conn.ConnectionString = strConnection;
conn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.StackTrace);
}
return conn;
} #region SQL语句 /// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlCommand cmd = new SqlCommand(SQLString, conn))
{
try
{
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
conn.Close();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// 执行查询语句,返回DataTable
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataTable execQuery(string SQLString)
{
DataTable dt = new DataTable();
try
{ SqlDataAdapter command = new SqlDataAdapter(SQLString,conn);
command.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
#endregion}
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;public class DataSource
{
private static SqlConnection conn =new SqlConnection("Data Source=10.9.162.24;UID=sa;PWD=0;DataBase=uu");//目前没用上
public static SqlConnection getConnection(string strConnection)
{
try
{
conn = new SqlConnection();
conn.ConnectionString = strConnection;
conn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.StackTrace);
}
return conn;
} #region SQL语句 /// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlCommand cmd = new SqlCommand(SQLString, conn))
{
try
{
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
conn.Close();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// 执行查询语句,返回DataTable
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataTable execQuery(string SQLString)
{
DataTable dt = new DataTable();
try
{ SqlDataAdapter command = new SqlDataAdapter(SQLString,conn);
command.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
#endregion}
using System;
using System.Data;namespace FactoryModel
{
/// <summary>
/// Description of IConnection.
/// </summary>
public interface IConnection
{
void getConnection(string strConnection);
DataTable execQuery(string SQLString);
}
}实现以上接口
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace FactoryModel
{
/// <summary>
/// Description of MSConnection.
/// </summary>
public class MSConnection : IConnection
{
private static SqlConnection conn = null; //new SqlConnection("Data Source=10.9.162.24;UID=sa;PWD=0;DataBase=uu");
public MSConnection()
{
}
public void getConnection(string strConnection)
{
try
{
conn = new SqlConnection();
conn.ConnectionString = strConnection;
conn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.StackTrace);
}
//return conn;
}
public DataTable execQuery(string SQLString){
DataTable dt = new DataTable();
try
{ SqlDataAdapter command = new SqlDataAdapter(SQLString,conn);
command.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
}
}
简单工厂模式,通过setType()方法改变取得某种数据库连接。
你的SQL执行方法也可以通过这样来写。
namespace FactoryModel
{ enum SQLType{
SERVER = 1,
ORACLE
}
/// <summary>
/// Description of ConnFactory.
/// </summary>
public class ConnFaction
{ int type ;
static ConnFaction connFact = null;
private ConnFaction(){}
public static ConnFaction getInstance(){
connFact = new ConnFaction();
return connFact;
}
public int setType{
set{
type = value;
}
get{
return this.type;
}
}
public IConnection doConn(){
IConnection ic = null;
switch(type){
case (int)SQLType.SERVER :
ic = new MSConnection();
break;
case (int)SQLType.ORACLE:
ic = new 另外的一个实现(); break;
default:
ic = new 默认的实现方法(); break;
}
return ic;
}
}
}不知道是不是你想要的。
1、晚打开,早关闭
2、要用数据库的连接池
3、要有缓存机制。