using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
//using System.Data.OracleClient; //由于Oracle没有安装,在此舍去Oracle的调试
namespace MyWeb
{
/// <summary>
/// 用于判断数据库类型的名称枚举
/// </summary>
public enum DataBaseType
{
SqlServer,
Access,
Oracle
}
/// <summary>
/// 访问数据库
/// </summary>
public class MyDBHelper
{
#region 创建Connection,Command,DataAdapter实例对象
/// <summary>
/// 创建并初始化Connection连接对象实例
/// </summary>
/// <param name="dbType">数据提供程序类型</param>
/// <returns>返回一个已经创建好的数据库连接对象实例</returns>
public static IDbConnection CreateConnection(DataBaseType dbType)
{
//声明一个IDbConnection对象,用以返回不同的连接对象实例
IDbConnection con;
//根据枚举值来判断创建何种类型的数据库连接实例
switch (dbType)
{
case DataBaseType.Access:
con = new OleDbConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString);
break;
//case DataBaseType.Oracle:
// con = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString);
// break;
default:
con = new SqlConnection(ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString);
break;
}
return con;
}
/// <summary>
/// 创建并初始化Command对象实例
/// </summary>
/// <param name="cmdText">SQL查询字符串或存储过程名臣</param>
/// <param name="dbType">数据提供程序类型</param>
/// <param name="con">数据库连接实例</param>
/// <returns>返回一个已经创建好的Command实例对象</returns>
public static IDbCommand CreateCommand(string cmdText, DataBaseType dbType, IDbConnection con)
{
//声明一个IDbCommand对象,用以返回不同的连接对象实例
IDbCommand cmd;
//根据枚举值来判断创建何种类型的数据库命令对象实例
switch (dbType)
{
case DataBaseType.Access:
cmd = new OleDbCommand(cmdText, (OleDbConnection)con);
break;
//case DataBaseType.Oracle:
// cmd = new OracleConnection(cmdText, (OracleConnection)con);
// break;
default:
cmd = new SqlCommand(cmdText, (SqlConnection)con);
break;
}
return cmd;
}
/// <summary>
/// 创建并初始化一个DataAdapter对象实例
/// </summary>
/// <param name="cmd">Command实例对象</param>
/// <param name="dbType">数据提供程序类型</param>
/// <returns>返回一个已经创建好的IDbDataAdapter对象</returns>
public static IDbDataAdapter CreateDataAdapter(IDbCommand cmd, DataBaseType dbType)
{
//声明一个IDbDataAdapter对象,用以返回不同的DataAdapter对象实例
IDbDataAdapter Adapter;
//根据枚举值来判断创建何种类型的DataAdapter对象实例
switch (dbType)
{
case DataBaseType.Access:
Adapter = new OleDbDataAdapter((OleDbCommand)cmd);
break;
//case DataBaseType.Oracle:
// Adapter = new OracleDataAdapter((OracleCommand)cmd);
// break;
default:
Adapter = new SqlDataAdapter((SqlCommand)cmd);
break;
}
return Adapter;
}
#endregion
#region 私有成员变量
private IDbConnection con;
private IDbCommand cmd;
private DataSet ds;
private IDbDataAdapter Adapter;
private DataBaseType dbType = (DataBaseType)Enum.Parse(typeof(DataBaseType), ConfigurationManager.AppSettings["dbType"].ToString());
#endregion
#region 执行数据库操作语句
public MyDBHelper()
{
con = CreateConnection(dbType);
ds = new DataSet();
}
/// <summary>
/// 执行增删改操作,UID是CUID中的
/// </summary>
/// <param name="cmdText">执行操作的SQL字符串</param>
public void ExecuteUID(string cmdText)
{
try
{
cmd = CreateCommand(cmdText, dbType, con);
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
/// <summary>
/// 执行增删改操作,UID是CUID中的
/// </summary>
/// <param name="cmdText">执行操作的SQL字符串</param>
/// <param name="parameter">参数</param>
public void ExecuteUID(string cmdText,IDbDataParameter[] parameter)
{
try
{
cmd = CreateCommand(cmdText, dbType, con);
foreach (IDbDataParameter p in parameter)
{
cmd.Parameters.Add(p);
}
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
/// <summary>
/// 执行查询操作,C代表CUID中的C
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns>返回DataSet对象</returns>
public DataSet ExecuteC(string cmdText)
{
cmd = CreateCommand(cmdText, dbType, con);
Adapter = CreateDataAdapter(cmd, dbType);
Adapter.Fill(ds);
return ds;
}
#endregion
}
}
注:<appSettings>
<add key="dbType" value="SqlServer"/>
<!--这里可选值必须是SqlServer或者Access在或者是Oracle,大小写不能改变-->
</appSettings>
<connectionStrings>
<add name="AccessConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=你的Access数据库文件路径; User Id=用户名;Password=用户密码"/>
<add name="SqlServerConnectionString" connectionString="Server=IP地址;DataBase=数据库文件名称;UID=数据库登录名;Pwd=数据库登录密码" />
</connectionStrings>