首页 / 软件开发 / .NET编程技术 / 一个支持SqlClient和OleDb的ADO.NET常用操作通用类

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Data.OleDb;/***************************************** **作者:GhostBear **博客:http://blog.csdn.net/ghostbear* * *****************************************/namespace AdoNetOmnipotence{/// <summary>/// 操纵Ado.net的通用类（sql和oledb）/// </summary>public class Omnipotence{bool isSqlClient = true;//判断调用sqlclient还是oledbstring connstr;//连接字符串SqlConnection sconn = null;OleDbConnection oconn = null;/// <summary>/// 初始化字符串/// </summary>/// <param name="connectionstring">连接字符串</param>public Omnipotence（string connectionstring）{this.connstr = connectionstring;}/// <summary>/// 指定连接字符串和操纵的目标/// </summary>/// <param name="connectionstring">连接字符串</param>/// <param name="issqlClient">操纵的目标</param>public Omnipotence（string connectionstring, bool issqlClient）{this.connstr = connectionstring;this.isSqlClient = issqlClient;}/// <summary>/// 打开连接/// </summary>private void Open（）{if （isSqlClient）{if （sconn == null）sconn = new SqlConnection（connstr）;if （sconn.State == ConnectionState.Closed）sconn.Open（）;}else{if （oconn == null）oconn = new OleDbConnection（connstr）;if （oconn.State == ConnectionState.Closed）oconn.Open（）;}}/// <summary>/// 关闭连接/// </summary>private void Close（）{if （isSqlClient）{if （sconn ！= null）if （sconn.State == ConnectionState.Open）sconn.Close（）;}else{if （oconn ！= null）if （oconn.State == ConnectionState.Open）oconn.Close（）;}}/***************************************** **作者:GhostBear **博客:http://blog.csdn.net/ghostbear* * *****************************************//// <summary>/// 返回command对象，可以是sqlcommand或oledbcommand/// </summary>/// <param name="sqlcmd">命令</param>/// <param name="isStored">是否存储过程</param>/// <param name="param">参数</param>/// <returns>一个装箱的command对象</returns>private object command（string sqlcmd, bool isStored, IDataParameter[] param）{if （isSqlClient）{SqlCommand cmd = new SqlCommand（sqlcmd）;//初始化commandcmd.Connection = sconn; //赋予连接对象if （isStored）cmd.CommandType = CommandType.StoredProcedure;//赋予命令类型elsecmd.CommandType = CommandType.Text;if （param ！= null）{foreach （SqlParameter t in param）{cmd.Parameters.Add（t）;//参数数据库不为空则添加参数}}return cmd; //返回sqlcommand}else{OleDbCommand cmd = new OleDbCommand（sqlcmd）;//初始化commandcmd.Connection = oconn; //赋予连接对象if （isStored）cmd.CommandType = CommandType.StoredProcedure;//赋予命令类型elsecmd.CommandType = CommandType.Text;if （param ！= null）{foreach （OleDbParameter t in param）{cmd.Parameters.Add（t）;//参数数据库不为空则添加参数}}return cmd; //返回Oledbcommand}}/// <summary>/// 返回dataadapt对象/// </summary>/// <param name="command">command对象</param>/// <returns>一个装箱的dataadapter对象</returns>private object adapter（object command）//返回DataAdpter类型的对象{object adpt;if （isSqlClient）{adpt = new SqlDataAdapter（（SqlCommand）command）;}else{adpt = new OleDbDataAdapter（（OleDbCommand）command）;}return adpt;}/// <summary>/// 执行操作。返回影响的行数/// </summary>/// <param name="sqlcmd">sql语句或存储过程名</param>/// <param name="isStored">是否为存储过程</param>/// <returns>语句执行影响的行数</returns>/***************************************** **作者:GhostBear **博客:http://blog.csdn.net/ghostbear* * *****************************************/public void RunProc（string sqlcmd, bool isStored,out int infectionRows）{SqlCommand scmd;OleDbCommand ocmd;Open（）;if （isSqlClient）{scmd = （SqlCommand）command（sqlcmd, isStored, null）;infectionRows = scmd.ExecuteNonQuery（）;Close（）;}else{ocmd = （OleDbCommand）command（sqlcmd, isStored, null）;infectionRows = ocmd.ExecuteNonQuery（）;Close（）;}return;}/// <summary>/// 执行操作。返回影响的行数/// </summary>/// <param name="sqlcmd">sql语句或存储过程名</param>/// <param name="isStored">是否为存储过程</param>/// <param name="param">执行所需要的参数</param>/// <returns>操作影响的行数</returns>public void RunProc（string sqlcmd, bool isStored, IDataParameter[] param,out int infectionRows）{SqlCommand scmd;OleDbCommand ocmd;Open（）;if （isSqlClient）{scmd = （SqlCommand）command（sqlcmd, isStored, param）; infectionRows=scmd.ExecuteNonQuery（）;Close（）;}else{ocmd = （OleDbCommand）command（sqlcmd, isStored, param）; infectionRows=ocmd.ExecuteNonQuery（）;Close（）;}return;}/***************************************** **作者:GhostBear **博客:http://blog.csdn.net/ghostbear* * *****************************************//// <summary>/// 返回数据集合中第一行第一列中的值/// </summary>/// <param name="sqlcmd">sql语句或存储过程名</param>/// <param name="isStored">是否存储过程</param>/// <param name="isReturnContetn">返回值</param>/// <returns></returns>public void RunProc（string sqlcmd, bool isStored,out object isReturnContent）{SqlCommand scmd;OleDbCommand ocmd;Open（）;if （isSqlClient）{scmd = （SqlCommand）command（sqlcmd, isStored, null）; isReturnContent = scmd.ExecuteScalar（）;Close（）;}else{ocmd = （OleDbCommand）command（sqlcmd, isStored, null）;isReturnContent = ocmd.ExecuteScalar（）;Close（）;}return;}/// <summary>/// 返回数据集合中第一行第一列中的值/// </summary>/// <param name="sqlcmd">sql语句或存储过程名</param>/// <param name="isStored">是否存储过程</param>/// <param name="param">参数集合</param>/// <param name="isReturnContent">返回值</param>/// <returns></returns>public void RunProc（string sqlcmd, bool isStored, IDataParameter[] param, out object isReturnContent）{SqlCommand scmd;OleDbCommand ocmd;Open（）;if （isSqlClient）{scmd = （SqlCommand）command（sqlcmd,isStored,param）; isReturnContent = scmd.ExecuteScalar（）;Close（）;}else{ocmd = （OleDbCommand）command（sqlcmd, isStored, param）; isReturnContent = ocmd.ExecuteScalar（）;Close（）;}return;}/***************************************** **作者:GhostBear **博客:http://blog.csdn.net/ghostbear* * *****************************************//// <summary>/// 返回sqldatareader对象/// </summary>/// <param name="sqlcmd">sql语句或存储过程</param>/// <param name="isStored">是否存储过程</param>/// <param name="reader">输出sqldatareader的 out参数</param>public void RunProc（string sqlcmd, bool isStored,out SqlDataReader reader）{SqlCommand scmd; Open（）;scmd = （SqlCommand）command（sqlcmd, isStored, null）; reader = scmd.ExecuteReader（CommandBehavior.CloseConnection）;return;}/// <summary>/// 返回oledbdatareader对象/// </summary>/// <param name="sqlcmd">sql语句或存储过程</param>/// <param name="isStored">是否存储过程</param>/// <param name="reader">输出oledbdatareader的 out参数</param>public void RunProc（string sqlcmd, bool isStored, out OleDbDataReader reader）{OleDbCommand ocmd;Open（）;ocmd = （OleDbCommand）command（sqlcmd, isStored, null）;reader = ocmd.ExecuteReader（CommandBehavior.CloseConnection）;return;}/// <summary>/// 返回oledbdatareader对象/// </summary>/// <param name="sqlcmd">sql语句或存储过程</param>/// <param name="isStored">是否存储过程</param>/// <param name="param">命令的参数</param>/// <param name="reader">输出oledbdatareader的 out参数</param>public void RunProc（string sqlcmd, bool isStored, IDataParameter[] param, out SqlDataReader reader）{SqlCommand scmd;Open（）;scmd = （SqlCommand）command（sqlcmd, isStored,param）;reader = scmd.ExecuteReader（CommandBehavior.CloseConnection）;return;}/// <summary>/// 返回oledbdatareader对象/// </summary>/// <param name="sqlcmd">sql语句或存储过程名</param>/// <param name="isStored">是否存储过程</param>/// <param name="param">命令参数</param>/// <param name="reader">输出参数</param>public void RunProc（string sqlcmd, bool isStored, IDataParameter[] param, out OleDbDataReader reader）{OleDbCommand ocmd;Open（）;ocmd = （OleDbCommand）command（sqlcmd, isStored, param）;reader = ocmd.ExecuteReader（CommandBehavior.CloseConnection）;return;}/***************************************** **作者:GhostBear **博客:http://blog.csdn.net/ghostbear* * *****************************************//// <summary>/// 返回dataset对象/// </summary>/// <param name="sqlcmd">sql语句或命令</param>/// <param name="isStored">是否存储过程</param>/// <param name="ds">输出参数</param>public void RunProc（string sqlcmd, bool isStored, out DataSet ds）{SqlDataAdapter sadpt = null;OleDbDataAdapter oadpt = null;DataSet myds = new DataSet（）;if （isSqlClient）{Open（）;sadpt = （SqlDataAdapter）adapter（command（sqlcmd, isStored, null））;sadpt.Fill（myds）;Close（）;}else{Open（）;oadpt = （OleDbDataAdapter）adapter（command（sqlcmd, isStored, null））; oadpt.Fill（myds）;Close（）;}ds = myds;return;}/***************************************** **作者:GhostBear **博客:http://blog.csdn.net/ghostbear* * *****************************************//// <summary>/// 返回dataset对象/// </summary>/// <param name="sqlcmd">sql语句或命令</param>/// <param name="isStored">是否存储过程</param>/// <param name="param">参数</param>///<param name="ds">输出dataset</param>public void RunProc（string sqlcmd, bool isStored, IDataParameter[] param, out DataSet ds）{SqlDataAdapter sadpt = null;OleDbDataAdapter oadpt = null;DataSet myds = new DataSet（）;if （isSqlClient）{Open（）;sadpt = （SqlDataAdapter）adapter（command（sqlcmd, isStored,param））;sadpt.Fill（myds）;Close（）;}else{Open（）;oadpt = （OleDbDataAdapter）adapter（command（sqlcmd, isStored,param））;oadpt.Fill（myds）;Close（）;}ds = myds;return;}}}