C#.net操作数据库通用类

C#.NET操作数据库通用类（MS SQL Server篇）

下面给出了一个C#操作MS SQL Server 数据库的通用类，通过该类可以对数据库进行任何操作，包括执行SQL语句、执行存储过程。以下是其详细实现过程，希望大家共同修改优化之。稍后将介绍如何使用它实现N层的程序设计。

配置web.config文件的链接参数

C#代码

using System;

using System.Data;

using System.Data.SqlClient;

namespace Com.LXJ.Database

{

///

/// ConnDB的摘要说明。

///

public class ConnDB

{

protected SqlConnection Connection;

private string connectionString;

///

/// 默认构造函数

///

public ConnDB()

{

string connStr;

connStr = System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString(); connectionString = connStr;

Connection = new SqlConnection(connectionString);

}

///

/// 带参数的构造函数

///

/// 数据库联接字符串

public ConnDB(string newConnectionString)

connectionString = newConnectionString;

Connection = new SqlConnection(connectionString);

}

///

/// 完成SqlCommand对象的实例化

///

///

///

///

private SqlCommandBuildCommand(string storedProcName,IDataParameter[] parameters) {

SqlCommand command = BuildQueryCommand(storedProcName,parameters);

command.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Em pty,DataRowVersion.Default,null));

return command;

}

///

/// 创建新的SQL命令对象(存储过程)

///

///

///

///

private SqlCommandBuildQueryCommand(string storedProcName,IDataParameter[] parameters)

{

SqlCommand command = new SqlCommand(storedProcName,Connection);

https://www.360docs.net/doc/003053365.html,mandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)

{

command.Parameters.Add(parameter);

}

return command;

}

///

/// 执行存储过程,无返回值

///

///

///

public void ExecuteProcedure(string storedProcName,IDataParameter[] parameters)

Connection.Open();

SqlCommand command;

command=BuildQueryCommand(storedProcName,parameters);

command.ExecuteNonQuery();

Connection.Close();

}

///

/// 执行存储过程，返回执行操作影响的行数目

///

///

///

///

///

public intRunProcedure(string storedProcName,IDataParameter[] parameters,outintrowsAffected)

{

int result;

Connection.Open();

SqlCommand command = BuildCommand(storedProcName,parameters);

rowsAffected = command.ExecuteNonQuery();

result = (int)command.Parameters["ReturnValue"].Value;

Connection.Close();

return result;

}

///

/// 重载RunProcedure把执行存储过程的结果放在SqlDataReader中

///

///

///

///

public SqlDataReaderRunProcedure(string storedProcName,IDataParameter[] parameters) {

SqlDataReaderreturnReader;

Connection.Open();

SqlCommand command = BuildQueryCommand(storedProcName,parameters); https://www.360docs.net/doc/003053365.html,mandType = CommandType.StoredProcedure;

returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

return returnReader;

}

///

/// 重载RunProcedure把执行存储过程的结果存储在DataSet中和表tableName为可选参数

///

///

///

///

///

public DataSetRunProcedure(string storedProcName,IDataParameter[] parameters,params string[] tableName)

{

DataSetdataSet = new DataSet();

Connection.Open();

SqlDataAdaptersqlDA = new SqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand(storedProcName,parameters);

string flag;

flag = "";

for(int i=0;i

flag = tableName[i];

if (flag!="")

sqlDA.Fill(dataSet,tableName[0]);

else

sqlDA.Fill(dataSet);

Connection.Close();

return dataSet;

}

///

/// 执行SQL语句，返回数据到DataSet中

///

///

///

public DataSetReturnDataSet(string sql)

{

DataSetdataSet=new DataSet();

Connection.Open();

SqlDataAdaptersqlDA=new SqlDataAdapter(sql,Connection);

sqlDA.Fill(dataSet,"objDataSet");

Connection.Close();

return dataSet;

}

///

/// 执行SQL语句，返回DataReader

///

///

///

public SqlDataReaderReturnDataReader(String sql)

{

Connection.Open();

SqlCommand command = new SqlCommand(sql,Connection); SqlDataReaderdataReader = command.ExecuteReader(); returndataReader;

}

///

/// 执行SQL语句，返回记录数

///

///

///

public intReturnRecordCount(string sql)

{

intrecordCount = 0;

Connection.Open();

SqlCommand command = new SqlCommand(sql,Connection); SqlDataReaderdataReader = command.ExecuteReader();

while(dataReader.Read())

{

recordCount++;

}

dataReader.Close();

Connection.Close();

returnrecordCount;

}

///

/// 执行SQL语句

///

///

///

public boolEditDatabase(string sql)

{

boolsuccessState = false;

Connection.Open();

SqlTransactionmyTrans = Connection.BeginTransaction(); SqlCommand command = new SqlCommand(sql,Connection,myTrans); try

{

command.ExecuteNonQuery();

https://www.360docs.net/doc/003053365.html,mit();

successState = true; }

catch

{

myTrans.Rollback(); }

finally

{

Connection.Close(); } returnsuccessState; }

///

/// 关闭数据库联接///

public void Close() {

Connection.Close(); }

}//end class

}//end namespace