数据库操作基本方法实现
时间：2010-09-05 来源：linzheng
一、获取数据库连接和基本的信息
1定义接口
namespace Com.Mycompany.Data
{
public interface IDbSetting
{
string ConnectionString
{
get;
}
DbServerType DbServerType
{
get;
}
string COID
{
get;
}
string UserNO
{
get;
}…………
}
}
2继承接口
namespace Com.Mycompany.Web.WebUI
{
public class MyDBSetting : IDbSetting
{
public MyDBSetting(string language, UserInformation user)
{
}
/// <summary>
/// 数据连接串
/// </summary>
public string ConnectionString
{
get
{
if (HttpContext.Current.Application["ConnectionString"] == null)
{
if (ConfigurationManager.ConnectionStrings["Conncetion"] == null)
{
throw new Exception(KDCommon.DataConnectionInfo);
}
else
{
HttpContext.Current.Application["ConnectionString"] = ConfigurationManager.ConnectionStrings["Conncetion"].ToString();
}
}
return HttpContext.Current.Application["ConnectionString"].ToString();
}
}
/// <summary>
/// 数据库类型
/// </summary>
public DbServerType DbServerType
{
get
{
return DbServerType.SqlServer;
}
}
public string Language
{
get
{
if (HttpContext.Current.Session["language"] == null)
{
HttpContext.Current.Session["language"] = "zh-chs";
}
return HttpContext.Current.Session["language"].ToString();
}
}
/// <summary>
/// 当前公司coid
/// </summary>
public string COID
{
get
{
SessionTimeOut("coid");
return HttpContext.Current.Session["coid"].ToString();
}
}
public string UserNO
{
get
{
SessionTimeOut("loginuserno");
return HttpContext.Current.Session["loginuserno"].ToString();
}
}
…………
}
}
3封装接口
namespace Com.Mycompany.Common
{
public class BaseInfo
{
private static IDbSetting s_dbSetting = null;
public static IDbSetting DbSetting
{
get
{
return s_dbSetting;
}
set
{
BaseInfo.s_dbSetting = value;
}
}
public BaseInfo()
{
}
}
…………
}
二、数据库基本操作
/// <summary>/// 访问数据库对象（辅助工具）
/// </summary>
/// <remarks></remarks>
public sealed class DbUtils 1、创建连接
/// <summary>
/// 数据连接串
/// </summary>
public static string ConnectionString
{
get
{
if( _connectionString == null || _connectionString.Length == 0)
{
return BaseInfo.DbSetting.ConnectionString;
}
else
{
return _connectionString;
}
}
set
{
_connectionString =value;
}
}
/// <summary>
/// 数据服务器类型
/// </summary>
public static DbServerType DbServerType
{
get
{
return BaseInfo.DbSetting.DbServerType;
}
}
/// <summary>
/// 产品id号
/// </summary>
public static string COID
{
get
{
return BaseInfo.DbSetting.COID;
}
}
/// <summary>
/// IsOverTime
/// </summary>
public static bool IsOverTime
{
get
{
return BaseInfo.DbSetting.IsOverTime;
}
}
/// <summary>
/// 用户编号
/// </summary>
public static string UserNO
{
get
{
return BaseInfo.DbSetting.UserNO;
}
}
…………
3、执行select查询语句
/// <summary>
/// 执行查询命令
/// </summary>
/// <param name="commandString">Sql命令</param>
/// <param name="datareader">输出DataReader</param>
public static void RunSelectCommand(
string commandString,
out IDataReader datareader
)
{
RunSelectCommand(null, commandString, out datareader);
}
/// <summary>
/// 执行查询命令
/// </summary>
/// <param name="commandString">Sql命令</param>
/// <param name="datareader">输出DataReader</param>
public static void RunSelectCommand(
string connectionString,
string commandString,
out IDataReader datareader
)
{
IDbCommand __command;
OleDbConnection conn = GetConnection(connectionString);
__command = new OleDbCommand(commandString, conn);
__command.CommandTimeout = 0; //设置等待命令执行的时间为无限期
if(__command.Connection.State != ConnectionState.Open)
{
__command.Connection.Open();
}
datareader = __command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
__command.Dispose();//释放__command
}
/// <summary>
/// 执行查询命令
/// </summary>
/// <param name="commandString">Sql命令</param>
/// <param name="prams">查询命令参数列表</param>
/// <param name="datareader">输出数据表</param>
public static void RunSelectCommand(
string commandString,
out IDataReader datareader,
params IDbDataParameter[] prams
)
{
RunSelectCommand(null,commandString,out datareader,prams);
}
/// <summary>
/// 处理转义字符
/// </summary>
/// <param name="sqlparam">SQL条件的条件</param>
/// <returns></returns>
public static string ReplaceSQLEscape(string sqlparam)
{
if (sqlparam == null || sqlparam == string.Empty)
return string.Empty;
sqlparam = sqlparam.Replace("[", "[[]").Replace("_", "[_]").Replace("%", "[%]").Replace("?", "[?]");
return sqlparam;
}
/// <summary>
/// 传递连接对象，用于建立临时表的操作，不能每次新建连接对象（否则临时表每次SQL执行完后会自动删除）
/// </summary>
/// <param name="commandString">Sql命令</param>
/// <param name="dataTable">输出数据表</param>
/// <param name="prams">查询命令参数列表</param>
public static void RunSelectCommand(
OleDbConnection conn,
string commandString,
out DataTable dataTable,
params IDbDataParameter[] prams
)
{
IDbDataAdapter __dataadapter;
DataSet __dataSet;
__dataadapter = new OleDbDataAdapter(commandString, conn);
__dataadapter.SelectCommand.CommandTimeout = 0;//设置等待命令执行的时间为无限期
if (prams != null)
{
foreach (IDbDataParameter pram in prams)
{
//if (pram.DbType == DbType.String)
//{
// pram.Value = ReplaceSQLEscape(pram.Value);
//}
__dataadapter.SelectCommand.Parameters.Add(pram);
}
}
__dataSet = new DataSet();
try
{
__dataadapter.Fill(__dataSet);
dataTable = __dataSet.Tables[0];
}
catch (Exception e)
{
throw new Exception(commandString, e);
}
}
/// <summary>
/// 执行查询命令
/// </summary>
/// <param name="commandString">Sql命令</param>
/// <param name="prams">查询命令参数列表</param>
/// <param name="datareader">输出数据表</param>
public static void RunSelectCommand(
string connectionString,
string commandString,
out IDataReader datareader,
params IDbDataParameter[] prams
)
{
IDbCommand __command;
OleDbConnection conn = GetConnection(connectionString);
__command = new OleDbCommand(commandString, conn);
__command.CommandTimeout = 0; //设置等待命令执行的时间为无限期
if(prams != null)
{
foreach(IDbDataParameter pram in prams)
{
__command.Parameters.Add(pram);
}
}
if(__command.Connection.State != ConnectionState.Open)
{
__command.Connection.Open();
}
datareader = __command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
__command.Dispose();
}
4、执行存储过程
#region RunStoredProcedure without out datatable
public static void RunStoredProcedure(string procName)
{
RunStoredProcedure(null,procName);
}
public static void RunStoredProcedure(
string connString,
string procName
)
{
using(OleDbConnection conn = GetConnection(connString))
{
try
{
IDbCommand command = conn.CreateCommand();
command.CommandText = procName;
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 0;//设置等待命令执行的时间为无限期
command.ExecuteNonQuery();
command.Dispose();
}
catch(Exception ex)
{
throw new Exception(procName + ":" + ex.Message);
}
finally
{
conn.Dispose();
}
}
}
public static void RunStoredProcedure(
string procName,
params IDbDataParameter[] paramsArray)
{
RunStoredProcedure(null,procName,paramsArray);
}
public static void RunStoredProcedure(
string connString,
string procName,
params IDbDataParameter[] paramsArray)
{
using(OleDbConnection conn = GetConnection(connString))
{
try
{
IDbCommand command = conn.CreateCommand();
command.CommandText = procName;
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 0;//设置等待命令执行的时间为无限期
foreach(IDbDataParameter param in paramsArray)
{
command.Parameters.Add(param);
}
command.ExecuteNonQuery();
command.Dispose();
}
catch(Exception ex)
{
throw new Exception(procName + ":" + ex.Message);
}
finally
{
conn.Dispose();
}
}
}
#endregion
#region RunStoredProcedure with out datatable
public static void RunStoredProcedure(
string procName,
out DataTable dataTable)
{
RunStoredProcedure(null,procName,out dataTable);
}
public static void RunStoredProcedure(
string connString,
string procName,
out DataTable dataTable)
{
OleDbDataAdapter adapter;
DataSet dataSet;
using(OleDbConnection conn = GetConnection(connString))
{
try
{
dataSet = new DataSet();
IDbCommand command = conn.CreateCommand();
command.CommandText = procName;
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 0;//设置等待命令执行的时间为无限期
adapter = new OleDbDataAdapter((OleDbCommand)command);
adapter.Fill(dataSet);
adapter.Dispose();
command.Dispose();
dataTable = dataSet.Tables[0];
}
catch(Exception ex)
{
throw new Exception(procName + ":" + ex.Message);
}
finally
{
conn.Dispose();
}
}
}
public static void RunStoredProcedure(
string procName,
out DataTable dataTable,
params IDbDataParameter[] paramsArray)
{
RunStoredProcedure(null,procName,out dataTable,paramsArray);
}
public static void RunStoredProcedure(
string connString,
string procName,
out DataTable dataTable,
params IDbDataParameter[] paramsArray)
{
OleDbDataAdapter adapter;
DataSet dataSet;
dataTable = null;
using(OleDbConnection conn = GetConnection(connString))
{
try
{
dataSet = new DataSet();
IDbCommand command = conn.CreateCommand();
command.CommandText = procName;
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 0;//设置等待命令执行的时间为无限期
foreach(IDbDataParameter param in paramsArray)
{
command.Parameters.Add(param);
}
adapter = new OleDbDataAdapter((OleDbCommand)command);
adapter.Fill(dataSet);
adapter.Dispose();
command.Dispose();
if (dataSet != null && dataSet.Tables.Count > 0)
{
dataTable = dataSet.Tables[0];
}
}
catch(Exception ex)
{
throw new Exception(procName + ":" + ex.Message);
}
finally
{
conn.Dispose();
}
}
}
#endregion
5、执行sql语句，返回影响数据行
public static int RunCommand(
string commandString,
params IDbDataParameter[] prams
)
{
return RunCommand("",commandString,prams);
}
//返回值表示操作影响的记录数
public static int RunCommand(
string connectionString,
string commandString,
params IDbDataParameter[] prams
)
{
IDbCommand __command;
int __rtnRowCount = -1;
using(OleDbConnection conn = GetConnection(connectionString))
{
__command = new OleDbCommand(commandString,conn);
__command.CommandTimeout = 0; //设置等待命令执行的时间为无限期
if(prams != null)
{
foreach(IDbDataParameter pram in prams)
{
__command.Parameters.Add(pram);
}
}
if(__command.Connection.State != ConnectionState.Open)
{
__command.Connection.Open();
}
__rtnRowCount = __command.ExecuteNonQuery();
__command.Dispose();
}
return __rtnRowCount;
}
/// <summary>
/// 传递连接对象，用于建立临时表的操作，不能每次新建连接对象（否则临时表每次SQL执行完后会自动删除）
/// </summary>
/// <param name="conn"></param>
/// <param name="commandString"></param>
/// <param name="prams"></param>
public static int RunCommand(
OleDbConnection conn,
string commandString,
params IDbDataParameter[] prams
)
{
IDbCommand __command;
int __rtnRowCount = -1;
__command = new OleDbCommand(commandString, conn);
__command.CommandTimeout = 0; //设置等待命令执行的时间为无限期
if (prams != null)
{
foreach (IDbDataParameter pram in prams)
{
__command.Parameters.Add(pram);
}
}
if (__command.Connection.State != ConnectionState.Open)
{
__command.Connection.Open();
}
__rtnRowCount=__command.ExecuteNonQuery();
__command.Parameters.Clear();
return __rtnRowCount;
}
相关阅读 更多 +
