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

}

}