C#数据库操作类
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
namespace QfSoft.DB.MSSQL
{
///
/// 数据库操作基础类
///
public class DataBase
{
#region 数据连接字符串
protected static string ConnectionStr = ConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;
#endregion
#region SQL操作
///
/// 执行SQL语句，返回影响的记录数
///
///
///
public static int ExecuteSQL(string SQLString)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
SqlCommand Cmd = new SqlCommand(SQLString, Conn);
try
{
Conn.Open();
int rows = Cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
}
///
/// 执行两条SQL语句，实现数据库事务
///
///
///
public static void ExecuteSqlTran(string SQLString1, string SQLString2)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
SqlCommand Cmd = new SqlCommand();
Cmd.Connection = Conn;
SqlTransaction tx = Conn.BeginTransaction();
Cmd.Transaction = tx;
try
{
Cmd.CommandText = SQLString1;
Cmd.ExecuteNonQuery();
Cmd.CommandText = SQLString2;
Cmd.ExecuteNonQuery();
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
///
/// 执行多条SQL语句,实现数据库事务，每条语句以";"分割
///
///
public static void ExecuteSqlTran(string SQLStringList)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
Conn.Open();
SqlCommand Cmd = new SqlCommand();
SqlTransaction tx = Conn.BeginTransaction();
Cmd.Transaction = tx;
try
{
string[] split = SQLStringList.Split(new Char[] { ';' });
foreach (string strsql in split)
{
if (strsql.Trim() != "")
{
Cmd.CommandText = strsql;
Cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
///
/// 执行带一个存储过程参数的SQL语句
///
///
///
///
public static int ExecuteSQL(string SQLString, string Content)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
SqlCommand Cmd = new SqlCommand(SQLString, Conn);
SqlParameter MyParameter = new SqlParameter("@Content", SqlDbType.NText);
MyParameter.Value = Content;
Cmd.Parameters.Add(MyParameter);
try
{
Conn.Open();
int rows = Cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
///
/// 向数据库中插入图像格式的字段
///
///
///
///
public static int ExecuteSqlInsertImg(string SQLString, byte[] fs)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
SqlCommand Cmd = new SqlCommand(SQLString, Conn);
SqlParameter MyParameter = new SqlParameter("@fs", SqlDbType.Image);
MyParameter.Value = fs;
Cmd.Parameters.Add(MyParameter);
try
{
Conn.Open();
int rows = Cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
///
/// 执行一条计算结果语句，返回查询结果（整数）
///
///
///
public static int GetCount(string SQLstring)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
SqlCommand Cmd = new SqlCommand(SQLstring, Conn);
try
{
Conn.Open();
SqlDataReader Result = Cmd.ExecuteReader();
int i = 1;
while (Result.Read())
{
i = Result.GetInt32(0);
}
Result.Close();
return i;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
///
/// 执行一条计算结果语句，返回查询结果（object）
///
///
///
public static object GetSingle(string SQLString)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
SqlCommand Cmd = new SqlCommand(SQLString, Conn);
try
{
Conn.Open();
object obj = Cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
///
/// 执行查询语句，返回SqlDataReader
///
///
///
public static SqlDataReader ExecuteReader(string SQLString)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
SqlCommand Cmd = new SqlCommand(SQLString, Conn);
SqlDataReader MyReader;
try
{
Conn.Open();
MyReader = Cmd.ExecuteReader();
return MyReader;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
Cmd.Dispose();
Conn.Close();
}
}
///
/// 执行查询语句，返回DataSet
///
///
///
public static DataSet Query(string SQLString)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
DataSet DS = new DataSet();
try
{
Conn.Open();
SqlDataAdapter DA = new SqlDataAdapter(SQLString, Conn);
DA.Fill(DS, "ds");
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
return DS;
}
#endregion
#region 构建存储过程执行对象
///
/// 构建一个SqlCommand对象以此执行存储过程
///
///
///
///
///
private static SqlCommand BuildQueryCommand(SqlConnection Conn, string storedProcName, IDataParameter[] parameters)
{
SqlCommand Cmd = new SqlCommand(storedProcName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
//添加存储过程参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
Cmd.Parameters.Add(parameter);
}
}
return Cmd;
}
///
/// 构建一个SqlDataAdapter对象以此执行存储过程
///
///
///
///
///
private static SqlDataAdapter BuildQueryDataAdapter(SqlConnection Conn, string storedProcName, IDataParameter[] parameters)
{
SqlDataAdapter DA = new SqlDataAdapter(storedProcName, Conn);
DA.SelectCommand.CommandType = CommandType.StoredProcedure;
//添加存储过程参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
DA.SelectCommand.Parameters.Add(parameter);
}
}
return DA;
}
#endregion
#region 构建存储过程参数
///
/// 生成存储过程参数
///
/// 存储过程名称
/// 参数类型
/// 参数大小
/// 参数方向
/// 参数值
/// 新的 Parameter 对象
public static SqlParameter BuildParameter(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
//当参数大小为0时，不使用该参数大小值
if (Size > 0)
{
param = new SqlParameter(ParamName, DbType, Size);
}
else
{
param = new SqlParameter(ParamName, DbType);
}
//创建输出类型的参数
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
param.Value = Value;
}
//返回创建的参数
return param;
}
///
/// 传入输入参数
///
/// 存储过程名称
/// 参数类型
/// 参数大小
/// 参数值
/// 新的 Parameter 对象
public static SqlParameter BuildInParameter(string ParamName, SqlDbType DbType, int Size, object Value)
{
return BuildParameter(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
///
/// 传入返回值参数
///
/// 存储过程名称
/// 参数类型
/// 参数大小
/// 参数值
/// 新的 Parameter 对象
public static SqlParameter BuildOutParameter(string ParamName, SqlDbType DbType, int Size)
{
return BuildParameter(ParamName, DbType, Size, ParameterDirection.Output, null);
}
///
/// 传入返回值参数
///
/// 存储过程名称
/// 参数类型
/// 参数大小
/// 参数值
/// 新的 Parameter 对象
public static SqlParameter BuildReturnParameter(string ParamName, SqlDbType DbType, int Size)
{
return BuildParameter(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
}
#endregion
#region 执行存储过程并返回不同的值
///
/// 执行一个存储过程，返回影响的行数
///
///
///
///
public static int RunPro(string storedProcName)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
Conn.Open();
SqlCommand Cmd = BuildQueryCommand(Conn, storedProcName, null);
return Cmd.ExecuteNonQuery();
}
///
/// 执行一个带参数的存储过程，返回影响的行数
///
///
///
///
public static int RunPro(string storedProcName, SqlParameter[] parameters)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
Conn.Open();
SqlCommand Cmd = BuildQueryCommand(Conn, storedProcName, parameters);
return Cmd.ExecuteNonQuery();
}
///
/// 执行一个存储过程，返回DataSet类型
///
///
///
public static void RunPro(string storedProcName,ref DataSet DS)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
DS = new DataSet();
Conn.Open();
//SqlDataAdapter DA = new SqlDataAdapter();
//DA.SelectCommand = BuildQueryCommand(Conn, storedProcName, null);
SqlDataAdapter DA = BuildQueryDataAdapter(Conn, storedProcName, null);
DA.Fill(DS);
Conn.Close();
}
///
/// 执行一个带参数的存储过程，返回DataSet类型
///
///
///
///
public static void RunPro(string storedProcName, SqlParameter[] parameters, ref DataSet DS)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
DS = new DataSet();
Conn.Open();
SqlDataAdapter DA = BuildQueryDataAdapter(Conn, storedProcName, parameters);
DA.Fill(DS);
Conn.Close();
}
///
/// 执行一个存储过程，返回SqlDataReader类型
///
///
///
public static void RunPro(string storedProcName, out SqlDataReader DR)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
Conn.Open();
SqlCommand Cmd = BuildQueryCommand(Conn, storedProcName, null);
//DR = Cmd.EndExecuteReader(CommandBehavior.CloseConnection);
DR = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
///
/// 执行一个带参数的存储过程，返回SqlDataReader类型
///
///
///
///
public static void RunPro(string storedProcName, SqlParameter[] parameters, out SqlDataReader DR)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
Conn.Open();
SqlCommand Cmd = BuildQueryCommand(Conn, storedProcName, parameters);
DR = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
///
/// 执行一个带参数存储过程，返回一个SqlDataAdapter类型
///
///
///
///
public static void RunPro(string storedProcName, SqlParameter[] parameters,out SqlDataAdapter DA)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
DA = new SqlDataAdapter();
Conn.Open();
SqlCommand Cmd = BuildQueryCommand(Conn, storedProcName, parameters);
DA.SelectCommand = Cmd;
//Conn.Close();
}
///
/// 执行一个带参数存储过程，并设置一个表名，返回一个SqlDataAdapter类型
///
///
///
///
///
public static void RunPro(string storedProcName, SqlParameter[] parameters, string tableName,out SqlDataAdapter DA)
{
SqlConnection Conn = new SqlConnection(ConnectionStr);
DataSet DS = new DataSet();
Conn.Open();DA = new SqlDataAdapter();
DA.SelectCommand = BuildQueryCommand(Conn, storedProcName, parameters);
DA.Fill(DS, tableName);
Conn.Close();
}
#endregion
#region 数据转换
public static DataTable ConvertdrToDatatable(SqlDataReader DR)
{
//定于DataTable
DataTable datatable = new DataTable();
//动态添加表的数据列
for(int i=0;i {
DataColumn mydc = new DataColumn();
mydc.DataType = DR.GetFieldType(i);
mydc.ColumnName = DR.GetName(i);
datatable.Columns.Add(mydc);
}
//添加表的数据
while (DR.Read())
{
DataRow mydr = datatable.NewRow();
for (int i = 0; i < DR.FieldCount; i++)
{
mydr = DR.ToString();
}
datatable.Rows.Add(mydr);
mydr = null;
}
//关闭数据读取器
DR.Close();
return datatable;
}
#endregion
}
}