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

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

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);
}
}
}

上网搜一下：Microsoft.ApplicationBlocks.Data

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

我这儿有个不用存储过程的，需要自已定义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;
}
}
}
}
}

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

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);
}

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

我这里有个简单的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: 在此处添加构造函数逻辑
//
}
}
}

自己写的,也不知道能不能用上
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();
        }
    }
}

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

调试易

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

解决方案 »