求一性能较好的c#数据库操作类-

[qqhmitzk]

前一段时间在网上收集关于数据库操作类的,总觉得性能不怎么样,而微软的那个SQLHelper 有点让人摸不着头脑,也没有找着专门的使用文档.
请哪位高手做过相关的研究没?望赐教

[SassyBoy]

还是SQLHelper~~好好学习一下吧~~

[SassyBoy]

里面的英文注释就是使用方法~~

[lovesnow1573]

namespace Utilities
{

/// <summary>
/// DbAccess类,即进行数据库访问时需要调用的类
/// </summary>
public class DbaseBS
{
//取出数据库连接字符串
public static readonly string conStr = ConfigurationSettings.AppSettings["ConnectionString"];

/// <summary>
/// 得到数据库连接对象
/// </summary>
/// <returns>数据库连接对象</returns>
public static SqlConnection GetConObject()
{
return new SqlConnection(conStr);
}

/// <summary>
/// 执行操作数据库的存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <returns>存储过程执行后所影响的行数</returns>
public static int ExecuteNonQuery(string procName)
{
SqlCommand cmd = new SqlCommand();

using (SqlConnection con = GetConObject())
{
CreateCommand(cmd, con, null, procName,null);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
return val;
}
}

/// <summary>
/// 执行操作数据库的存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数</param>
/// <returns>存储过程执行后所影响的行数</returns>
public static int ExecuteNonQuery(string procName, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();

using (SqlConnection con = GetConObject())
{
CreateCommand(cmd, con, null, procName, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
return val;
}
}

/// <summary>
/// 执行操作数据库的存储过程(事务控制)
/// </summary>
/// <param name="trans">数据库连接所关联的事务对象</param>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数</param>
/// <returns>存储过程执行后所影响的行数</returns>
public static int ExecuteNonQuery(SqlTransaction trans, string procName, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
CreateCommand(cmd, trans.Connection, trans, procName, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
return val;
}

/// <summary>
/// 执行读数据集操作，以DataReader的形式返回
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数</param>
/// <returns>DataReader对象</returns>
public static SqlDataReader ExecuteReader(string procName, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = GetConObject();

try
{
CreateCommand(cmd, con, null, procName, cmdParms);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch(Exception ex)
{
cmd.Dispose();
Close(con);
throw ex;
}
}

/// <summary>
/// 执行读数据集操作，以DataReader的形式返回
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <returns>DataReader对象</returns>
public static SqlDataReader ExecuteReader(string procName)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = GetConObject();

try
{
CreateCommand(cmd, con, null, procName, null);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch(Exception ex)
{
cmd.Dispose();
Close(con);
throw ex;
}
}

/// <summary>
/// 执行读数据集操作，以DataSet的形式返回
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="cmdParms">存储过程所使用的参数</param>
/// <returns>DataSet对象</returns>
public static DataSet ExecuteDataSet(string procName, SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = GetConObject();
DataSet ds = new DataSet();

try
{
CreateCommand(cmd, con, null, procName, cmdParms);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch(Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
Close(con);
}
}


/// <summary>
/// 执行读数据集操作，以DataSet的形式返回
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <returns>DataReader对象</returns>
public static DataSet ExecuteDataSet(string procName)
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = GetConObject();
DataSet ds = new DataSet();

try
{
CreateCommand(cmd, con, null, procName, null);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch(Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
Close(con);
}
}
}

[bumm]

上网搜一下：Microsoft.ApplicationBlocks.Data

[qqhmitzk]

SQLHElper执行的都是什么存储过程,有执行SQL语句的没有,比如删除,更新等SQL单一的语句,而不是存储过程

[antoniusguo]

MS最近的几个例子好象都是没存储过程的

[zh-wall-e]

我这儿有个不用存储过程的，需要自已定义SQl语句

using System;
using System.Data;
using System.Data.SqlClient;

namespace Aptech.Student.Common
{

public class DataBaseOperate:IDisposable
{
// 保存数据库连接字符串
private string m_sConnectionString;

// 数据库连接标志
private bool m_bConnected;

// 存储过程开启标志
private bool m_bTransaction;


//数据库连接对象
private SqlConnection m_sqlConnection;
private SqlCommand m_sqlCommand;
private SqlTransaction m_sqlTransaction;
private SqlDataAdapter m_sqlDataAdapter;

/// <summary>
/// 构造函数，初始化内部变量
/// </summary>
public DataBaseOperate()
:this("user id=sa;password=510902;data source=\"YAOYANG\\YAOYANG\";initial catalog=Student")
{
//
// TODO: 在此处添加构造函数逻辑
//
}

public DataBaseOperate(
string sConnectionString)
{
if (sConnectionString != "")
{
m_sConnectionString = sConnectionString;
m_bConnected = false;
}
m_bTransaction = false;
}

/// <summary>
/// 连接数据库，并打开数据库连接
/// </summary>
/// <returns>成功返回true</returns>
private bool ConnectDataBase()
{
if (!m_bConnected)
{
try
{
if (m_sqlConnection == null)
{
m_sqlConnection = new SqlConnection(m_sConnectionString);
m_sqlConnection.Open();
}

if (m_sqlCommand == null)
{
m_sqlCommand = new SqlCommand();
}
m_bConnected = true;
m_sqlCommand.Connection = m_sqlConnection;
}
catch(SqlException e)
{
throw e;
}
}
return true;
}

/// <summary>
/// 关闭数据库，释放数据库资源
/// </summary>
/// <returns>成功返回true</returns>
public bool CloseDataBase()
{
Dispose();
return true;
}

/// <summary>
/// 执行数据库查询操作
/// </summary>
/// <param name="sQuery">查询的Sql语句</param>
/// <param name="sTableName">返回数据集的表名</param>
/// <returns>返回数据集</returns>
public DataSet Search(
string sQuery,
string sTableName )
{

//若连接数据库失败抛出错误
if (!ConnectDataBase())
{
throw(new ApplicationException("没有建立数据库连接。"));
}

DataSet dataSet = new DataSet();
m_sqlCommand.CommandType = System.Data.CommandType.Text;
m_sqlCommand.CommandText = sQuery;
m_sqlDataAdapter = new SqlDataAdapter();
m_sqlDataAdapter.SelectCommand = m_sqlCommand;
try
{
m_sqlDataAdapter.Fill(
dataSet,
sTableName);
}
catch(SqlException e)
{
//如果正在执行事务，回滚
if(m_bTransaction)
{
m_sqlTransaction.Rollback();
}
throw e;
}
return dataSet;
}


/// <summary>
/// 执行修改数据库操作，修改、删除等无返回值的操作
/// </summary>
/// <param name="sSql">执行的SQL语句</param>
/// <returns>成功执行返回True</returns>
public bool Execute(
string sSql)
{

if (!ConnectDataBase())
{
throw(new ApplicationException("没有建立数据库连接"));
}

m_sqlCommand.CommandType = System.Data.CommandType.Text;
m_sqlCommand.CommandText = sSql;
try
{
m_sqlCommand.ExecuteNonQuery();
}
catch(SqlException e)
{
//如果正在执行事务，回滚
if(m_bTransaction)
{
m_sqlTransaction.Rollback();
}
throw e;
}
return true;
}

/// <summary>
/// 开始事务处理功能，之后执行的全部数据库操作语句需要调用提交函数（commit）生效
/// </summary>
public void StartTransation()
{
//若连接数据库失败抛出错误
if (!ConnectDataBase())
{
throw(new ApplicationException("没有建立数据库连接。"));
}

m_bTransaction = true;
m_sqlTransaction = m_sqlConnection.BeginTransaction(
IsolationLevel.ReadCommitted);
m_sqlCommand.Transaction = m_sqlTransaction;

}

/// <summary>
/// 当前待处理事务提交，失败全部回滚
/// <returns>成功提交返回true</returns>
public bool Commit()
{
//如果没有开启事务处理功能，不做任何操作，直接返回成功
if (!m_bTransaction)
{
return true;
}

try
{
m_sqlTransaction.Commit();
}
catch(SqlException e)
{
m_sqlTransaction.Rollback();
throw e;
}
return true;
}

/// <summary>
/// 除去对象资源.
/// </summary>
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true);
}

/// <summary>
/// 释放对象实例变量.
/// </summary>
protected virtual void Dispose(
bool bDispose)
{
if (!bDispose)
return;

if (m_bConnected)
{
if (m_sqlConnection.State != ConnectionState.Closed )
{
m_sqlConnection.Dispose();
m_sqlConnection.Close();

m_sqlCommand = null;
m_sqlConnection = null;
m_sqlTransaction = null;

m_bConnected = false;
}
}
}
}
}

[qqhmitzk]

zh_li_(我爸的儿子) ( )
你能给个使用样例不?

[YUAN168]

太简单了吧!

把你要用的操作都做成静态的方法就OK了啊...

传递几个参数过去嘛

[SassyBoy]

SQLHElper执行的都是什么存储过程,有执行SQL语句的没有,比如删除,更新等SQL单一的语句,而不是存储过程
---------------------------
谁说没有?这个不是吗?!!!

public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
}

[SassyBoy]

int rowEffect=SqlHelper.ExecuteNonQuery("constr",CommandType.Text,"sql");
就可以了

[zmj1563]

我这里有个简单的

using System;
using System.Data.SqlClient;
using System.Data;

namespace 数据报表模块
{
/// <summary>
/// DBUtil 的摘要说明。
/// </summary>
public class DBUtil
{
private static string strConnect =
System.Configuration.ConfigurationSettings.AppSettings["connStr"];


public static int SqlExecuteNonQuery(SqlCommand objCommand)
{
SqlConnection objConnection=new SqlConnection(strConnect);
objCommand.Connection=objConnection;
try
{
if(objConnection.State==ConnectionState.Closed)
{
objConnection.Open();
}
return objCommand.ExecuteNonQuery();
}
catch(Exception e)
{
throw e;
}
finally
{
if(objConnection.State==ConnectionState.Open)
{
objConnection.Close();
}
}
}
//重写SqlExecuteNonQuery,这样写以后调用类时只用写SQL命令
public static int SqlExecuteNonQuery(string strCmd)
{
SqlCommand objCommand=new SqlCommand(strCmd);
//调用已定义的方法
return SqlExecuteNonQuery(objCommand);
}


//带返回结果的SqlExecuteReader,不采用重写的方法
public static SqlDataReader SqlExecutrReader(string strCmd)
{
SqlConnection objConnection=new SqlConnection(strConnect);
SqlCommand objCommand=new SqlCommand(strCmd,objConnection);
try
{
if(objConnection.State==ConnectionState.Closed)
{
objConnection.Open();
}
return objCommand.ExecuteReader();
}
catch(Exception e)
{
throw e;
}
finally
{
if(objConnection.State==ConnectionState.Open)
{
objConnection.Close();
}
}
}


//返回查询结果的SqlExecuteScalar
public static object SqlExecuteScalar(SqlCommand objCommand)
{
SqlConnection objConnection=new SqlConnection(strConnect);
objCommand.Connection=objConnection;
try
{
if(objConnection.State==ConnectionState.Closed)
{
objConnection.Open();
}
return objCommand.ExecuteScalar();
}
catch(Exception e)
{
throw e;
}
finally
{
if(objConnection.State==ConnectionState.Open)
{
objConnection.Close();
}
}
}
//重写SqlExecuteScalar
public static object SqlExecuteScalar(string strCmd)
{
SqlCommand objCommand=new SqlCommand();
objCommand.CommandText=strCmd;
return SqlExecuteScalar(objCommand);
}

//返回DataSet
public static DataSet GetDataSet(string strCmd,string TableName)
{
SqlConnection objConnection=new SqlConnection(strConnect);
SqlCommand objCommand=new SqlCommand(strCmd,objConnection);
try
{
if(objConnection.State==ConnectionState.Closed)
{
objConnection.Open();
}
DataSet objDs=new DataSet();
SqlDataAdapter objDa=new SqlDataAdapter(objCommand);
objDa.Fill(objDs,TableName);
return objDs;
}
catch(Exception e)
{
throw e;
}
finally
{
if(objConnection.State==ConnectionState.Open)
{
objConnection.Close();
}
}

}
public DBUtil()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
}
}

[cmHua]

其实微软的企业库是一个不错的选择,如果楼主真的要学习但又不想看它的英文注释的话可以去书店找一下,上次我有看到一本专门写企业库的书,不过不记得书名了,只能帮你这么多了:)

[blankfang]

个人感觉用在不同的地方
就是效果不一样！！

[qqhmitzk]

我的有好友用的类有十八种方法，性能还不错，但是封装了的，无发去研究他,各位仁兄有更好的吗？

[fscnt]

自己写的,也不知道能不能用上
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace LY
{
class C_Public
{
private string strSQL;
private SqlConnection myConnection = null;
private SqlCommand cmd;
private SqlCommandBuilder sqlCmdBld;
private DataSet ds = new DataSet();
private SqlDataAdapter da;
private static SqlTransaction Transaction = null; //数据库事务
private static bool IsInTransaction = false; //是否在事务中
//程序用到的存储数据变量
public class AppNeddInfo
{
public static string psWorker;
}
public C_Public()
{
}
private void openLink()
{
string ConnectionText; //Store The Connection Sentence for Database
if ((myConnection == null) || (myConnection.State == ConnectionState.Closed))
{
ConnectionText = ReadConn();
myConnection = new SqlConnection(ConnectionText);
try
{
myConnection.Open();//Open Database
}
catch
{
myConnection = null;
}
}
}
public string ReadConn()
{
string str = "server=FYGS-X;database=text;uid=sa;pwd=sa";
return str;
}
//数据库连接参数
public SqlConnection DataConn()
{
return myConnection;
}
//开始一个事务
public void BeginTransaction()
{
if ((myConnection == null) || (myConnection.State == ConnectionState.Closed))
{
return;
}
else
{
Transaction = myConnection.BeginTransaction();
IsInTransaction = true;
}
}
//提交一个事务
public void CommitTransaction()
{
if (Transaction != null)
{
Transaction.Commit();
IsInTransaction = false;
Transaction = null;
myConnection.Close();
}
else
{
return;
}
}
//回滚一个事务
public void RollbackTransaction()
{
if (Transaction != null)
{
Transaction.Rollback();
IsInTransaction = false;
Transaction = null;
myConnection.Close();
}
else
{
return;
}
}

public DataSet SelectDataBase(string tempStrSQL, string tempTableName)
{
openLink();
this.strSQL = tempStrSQL;
this.da = new SqlDataAdapter(this.strSQL, this.myConnection);
this.ds.Clear();
this.da.Fill(ds, tempTableName);
myConnection.Close();
return ds;//返回填充了数据的DataSet，其中数据表以tempTableName给出的字符串命名
}
//数据库数据更新(传DataSet和DataTable的对象)
public DataSet UpdateDataBase(DataSet changedDataSet, string tableName)
{
openLink();
this.da = new SqlDataAdapter(this.strSQL, this.myConnection);
this.sqlCmdBld = new SqlCommandBuilder(da);
this.da.Update(changedDataSet, tableName);
myConnection.Close();
return changedDataSet;//返回更新了的数据库表
}
//检索数据库数据(传字符串,直接操作数据库)
public DataTable SelectDataBase(string tempStrSQL)
{
openLink();
DataSet tempDataSet = new DataSet();
this.da = new SqlDataAdapter(tempStrSQL, this.myConnection);
this.da.Fill(tempDataSet);
myConnection.Close();
return tempDataSet.Tables[0];
}
//返回单个字符
public string GetDataChar(string tempStrSQl, string tempReturnChar)
{
openLink();
cmd = new SqlCommand(tempStrSQl, myConnection);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
string returnChar = dr[tempReturnChar].ToString();
myConnection.Close();
return returnChar;
}
myConnection.Close();
return "";
}
//数据库数据更新(传字符串，直接操作数据库)
public int UpdateDataBase(string tempStrSQL)
{
openLink();
//使用Command之前一定要先打开连接,后关闭连接,而DataAdapter则会自动打开关闭连接
myConnection.Open();
SqlCommand tempSqlCommand = new SqlCommand(tempStrSQL, this.myConnection);
int intNumber = tempSqlCommand.ExecuteNonQuery();//返回数据库中影响的行数
myConnection.Close();
return intNumber;
}
//检索数据库数据(返回记录数)
public int SelectData(string tempSQL)
{
openLink();
DataSet tempDataSet = new DataSet();
this.da = new SqlDataAdapter(tempSQL, this.myConnection);
this.da.Fill(tempDataSet);
myConnection.Close();
return tempDataSet.Tables[0].Rows.Count;
}
//执行数据库操作
public void DoCommand(string sqlStr)
{
openLink();
cmd = new SqlCommand("", myConnection);
if (IsInTransaction) cmd.Transaction = Transaction;
cmd.CommandText = sqlStr;
cmd.ExecuteNonQuery();
if (IsInTransaction == false) myConnection.Close();
}
}
}

[qqhmitzk]

最近朋友公司的数据处理类，性能也不错．有十几中调用方法,就是被封装了，无法研究它，各位仁兄有更好的数据苦操作类吗？

[qqhmitzk]

我先试一试,谢谢 fscnt(走丢了)

[panzi667]

呵呵,收下咯