实例介绍
【实例简介】
【实例截图】
【实例截图】
【核心代码】
using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Configuration;
using System.Data.OleDb;
using System.Web;
namespace DB
{
/// <summary>
/// 通用的数据库处理类，通过ado.net与数据库连接
/// </summary>
///
[Obsolete]
public class Database : IDisposable
{
// 连接数据源
//public static string dbName=""; //数据库名
private static OleDbConnection con=null;
public static OleDbDataAdapter oda;
public string GetDefaultValue(string key)
{
OleDbDataReader dr = RunQuery("select statename from userstate where statetype='" key "'");
if (dr.Read()) return dr[0].ToString();
return null;
}
public OleDbDataAdapter getAdapter() {
Open();
OleDbDataAdapter adapter = new OleDbDataAdapter();
return adapter;
}
public static OleDbDataAdapter getAdapter(string query)
{
Open();
OleDbCommand selectCmd = new OleDbCommand(query, con);
selectCmd.Transaction = null;
OleDbDataAdapter adapter = new OleDbDataAdapter(selectCmd);
OleDbCommandBuilder scb = new OleDbCommandBuilder(adapter);//自动产生各种命令
return adapter;
}
public static OleDbDataAdapter getAdapter(string query, OleDbParameter[] prams)
{
Open();
OleDbCommand selectCmd = new OleDbCommand(query, con);
OleDbDataAdapter adapter = new OleDbDataAdapter(selectCmd);
OleDbCommandBuilder scb = new OleDbCommandBuilder(adapter);//自动产生各种命令
return adapter;
}
public static DataTable getDataTable(string query, OleDbParameter[] prams)
{
OleDbDataAdapter ad = getAdapter(query);
oda = ad;
if (prams != null)
{
foreach (OleDbParameter pra in prams)
{
ad.SelectCommand.CommandType = CommandType.StoredProcedure;
ad.SelectCommand.Parameters.Add(pra);
}
}
DataTable dt = new DataTable();
ad.Fill(dt);
return dt;
}
public static DataTable getDataTable(string query)
{
OleDbDataAdapter ad = getAdapter(query);
oda = ad;
DataTable dt = new DataTable();
//log.Info(dt.Rows[0][0].ToString());
ad.Fill(dt);
return dt;
}
public static OleDbDataReader getDataReader(string query)
{
OleDbCommand cd = getCommand(query);
OleDbDataReader dr = cd.ExecuteReader();
return dr;
}
public void updateDataTable(DataSet dataset, string tablename)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = CreateCommand("select * from " tablename);
adapter.Update(dataset, tablename);
}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="procName">查询语句</param>
/// <param name="dataReader">返回存储过程返回值</param>
public static OleDbDataReader RunQuery(string OleDb)
{
return RunQuery(OleDb, null);
}
public static OleDbDataReader RunQuery(string OleDb, OleDbParameter[] prams)
{
OleDbCommand cmd;
if (prams == null)
cmd = CreateCommand(OleDb);
else cmd = CreateCommand(OleDb, prams);
//cmd.CommandType=CommandType.Text;
return cmd.ExecuteReader();
//return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); //连接随reader关闭而关闭
//return (int)cmd.Parameters["ReturnValue"].Value;
}
/// <summary>
/// 执行返回表的存储过程，不带参数
/// </summary>
/// <param name="OleDb"></param>
/// <returns></returns>
public static DataTable RunTableProc(string OleDb)
{
return RunTableProc(OleDb, null);
}
/// <summary>
/// 执行返回表的存储过程，带参数
/// </summary>
/// <param name="OleDb"></param>
/// <param name="prams"></param>
/// <returns></returns>
public static DataTable RunTableProc(string OleDb, OleDbParameter[] prams)
{
OleDbDataAdapter oda=getAdapter(OleDb);
if(prams!=null)
{
foreach (OleDbParameter pram in prams)
{
oda.SelectCommand.Parameters.Add(pram);
}
}
oda.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable table = new DataTable();
oda.Fill(table);
return table;
}
/// <summary>
/// 执行返回值的存储过程
/// </summary>
/// <param name="OleDb"></param>
/// <param name="prams"></param>
/// <returns></returns>
///
public static object RunObjProc(string OleDb)
{
return RunObjProc(OleDb, null);
}
/// <summary>
/// 执行返回值的存储过程
/// </summary>
/// <param name="OleDb"></param>
/// <param name="prams"></param>
/// <returns></returns>
///
public static object RunObjProc(string OleDb, OleDbParameter[] prams)
{
OleDbCommand oda = getCommand(OleDb);
OleDbCommand cmd;
if (prams == null)
cmd = CreateCommand(OleDb);
else
cmd = CreateCommand(OleDb, prams);
cmd.ExecuteNonQuery();
object obj = cmd.Parameters["ReturnValue"].Value;
return obj;
}
/// <summary>
/// 执行非查询语句
/// </summary>
/// <param name="procName">非查询语句</param>
/// <param name="dataReader">返回非查询语句返回值</param>
public static int RunNoneQuery(string OleDb)
{
return RunNoneQuery(OleDb, null);
/*
OleDbCommand cmd = CreateCommand(OleDb);
return cmd.ExecuteNonQuery();*/
}
public static int RunNoneQuery(string OleDb, OleDbParameter[] prams)
{
OleDbCommand cmd;
if (prams != null) cmd = CreateCommand(OleDb, prams);
else cmd = CreateCommand(OleDb);
int ret = cmd.ExecuteNonQuery();
return ret;
}
public static OleDbCommand getCommand(string query)
{
Open();
OleDbCommand cmd = new OleDbCommand(query, con);
return cmd;
}
/// <summary>
/// 创建一个OleDbCommand对象以此来执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回OleDbCommand对象</returns>
private static OleDbCommand CreateCommand(string procName, OleDbParameter[] prams)
{
OleDbCommand cmd = CreateCommand();
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
// 依次把参数传入存储过程
if (prams != null)
{
foreach (OleDbParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 加入返回参数
//cmd.Parameters.Add(
// new OleDbParameter("ReturnValue", OleDbType.Integer, 4,
// ParameterDirection.ReturnValue, false, 0, 0,
// string.Empty, DataRowVersion.Default, null));
return cmd;
}
private static OleDbCommand CreateCommand()
{
// 确认打开连接
Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
return cmd;
}
private static OleDbCommand CreateCommand(String OleDb)
{
OleDbCommand cmd = CreateCommand();
cmd.CommandText = OleDb;
return cmd;
}
/// <summary>
/// 打开数据库连接.
/// </summary>
public static void Open()
{
// 打开数据库连接
if (con == null)
{
//if (dbName == "")
// return;
string dbName = System.Configuration.ConfigurationSettings.AppSettings["DBName"].ToString();
string s = System.Configuration.ConfigurationSettings.AppSettings["constring"].ToString();
s = s.Replace("####", dbName); //动态赋值数据库名
//string s = "Provider=SQLOLEDB;server=127.0.0.1;database=Test;User Id=lwl;pwd=890914;";
con = new OleDbConnection(s);
//加入延迟时间
}
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
}
public static OleDbConnection getConnection()
{
Open();
return con;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public static void Close()
{
try
{
if (con != null)
con.Close();
}
catch
{
}
}
/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确认连接是否已经关闭
if (con != null)
{
con.Dispose();
con = null;
}
}
/// <summary>
/// 传入输入参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param></param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public static OleDbParameter MakeInParam(string ParamName, OleDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的 parameter 对象</returns>
public static OleDbParameter MakeOutParam(string ParamName, OleDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
/// <summary>
/// 生成存储过程参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方向</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public static OleDbParameter MakeParam(string ParamName, OleDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
OleDbParameter param;
if (Size > 0)
param = new OleDbParameter(ParamName, DbType, Size);
else
param = new OleDbParameter(ParamName, DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
/// <summary>
/// 事件处理多条Sql语句
/// </summary>
/// <param name="strs"></param>
public static int ExecTrans(string[] strs)
{
Open();
int OK = -1;
OleDbCommand cmd = CreateCommand();
OleDbTransaction tran=con.BeginTransaction();
cmd.Transaction = tran;
try {
foreach(string sql in strs)
{
OK ;
cmd.CommandText=sql;
cmd.ExecuteNonQuery();
}
tran.Commit();
}catch(Exception e){ //执行失败返回有问题的sql索引
tran.Rollback();
return OK;
}finally{
con.Close();
}
if (OK == strs.Length - 1) //执行成功的话返回-1;
{
OK = -1;
}
return OK;
}
}
}
标签： 数据访问层封装
好例子网口号：伸出你的我的手 — 分享！
小贴士
感谢您为本站写下的评论，您的评论对其它用户来说具有重要的参考价值，所以请认真填写。
- 类似“顶”、“沙发”之类没有营养的文字，对勤劳贡献的楼主来说是令人沮丧的反馈信息。
- 相信您也不想看到一排文字/表情墙，所以请不要反馈意义不大的重复字符，也请尽量不要纯表情的回复。
- 提问之前请再仔细看一遍楼主的说明，或许是您遗漏了。
- 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦，又没人会搭理，于人于己都无利。
关于好例子网
本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享，对搜索内容的合法性不具有预见性、识别性、控制性，仅供学习研究，请务必在下载后24小时内给予删除，不得用于其他任何用途，否则后果自负。基于互联网的特殊性，平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查；无论平台是否已进行审查，用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场，基于网友分享，根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定，若资源存在侵权或相关问题请联系本站客服人员，点此联系我们。关于更多版权及免责申明参见 版权及免责申明
网友评论
我要评论