NET数据库基本操作类

永久禁言 · 发表于 2013-2-21 11:35:15

NET数据库基本操作类。欢迎大家指正

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Tools.Data
{
/// <summary>
/// 数据库连接类
/// 创建时间：2013-02-20
/// </summary>
public class SqlHelper
{
#region 基本变量
private static SqlConnection conn = null;
private static string _connectionString = "";
/// <summary>
/// 数据库连接字符串
/// </summary>
public static string ConnectionString
{
get { return _connectionString; }
set { _connectionString = value; }
}
#endregion
#region 私有方法
/// <summary>
/// 创建数据库连接
/// </summary>
private static void CreateConnection()
{
//判断连接是否创建，没创建的话创建一个连接
if (conn == null)
{
conn = new SqlConnection(ConnectionString);
}
}
/// <summary>
/// 打开数据库连接
/// </summary>
private static void Open()
{
//判断连接是否关闭
if (conn.State == ConnectionState.Closed)
{
try
{
conn.Open();
}
catch (Exception ex)
{
//添加错误日志 ex
}
finally
{ }
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private static void Close()
{
//判断连接是否创建
if (conn != null)
{
//判断连接的状态是否打开
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
/// <summary>
/// 释放资源
/// </summary>
private static void Dispose()
{
//判断连接是否创建
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
/// <summary>
/// 将SqlParameter参数数组(参数值)分配给SqlCommand命令.
/// 这个方法将给任何一个参数分配DBNull.Value;
/// 该操作将阻止默认值的使用.
/// </summary>
/// <param name="command">命令名</param>
/// <param name="commandParameters">SqlParameters数组</param>
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if (p != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
/// <summary>
/// 创建SqlCommand对象
/// </summary>
/// <param name="comm">要处理的SqlParameter</param>
/// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
/// <param name="commandText">存储过程名或都SQL命令文本</param>
/// <param name="commandParameters">和命令相关联的SqlParameter参数数组,如果没有参数为'null'</param>
/// <param name="mustCloseConnection">如果连接是打开的,则为true,其它情况下为false.</param>
private static void Createcommand(SqlCommand comm, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
{
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
if (conn == null)
CreateConnection();
if (conn.State == ConnectionState.Closed)
{
Open();
mustCloseConnection = true;
}
else
{
mustCloseConnection = false;
}
comm.Connection = conn;
comm.CommandType = commandType;
comm.CommandText = commandText;
if (commandParameters != null)
AttachParameters(comm, commandParameters);
}
#endregion
#region ExecuteNonQuery
/// <summary>
/// 执行SqlCommand
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(string commandText)
{
int outID = 0;
return ExecuteNonQuery(out outID, CommandType.Text, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行SqlCommand
/// </summary>
/// <param name="id">影响的行数</param>
/// <param name="commandText">SQL语句</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(out int outID, string commandText)
{
return ExecuteNonQuery(out outID, CommandType.Text, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行SqlCommand
/// </summary>
/// <param name="id">影响的行数</param>
/// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(out int outID, CommandType commandType, string commandText)
{
return ExecuteNonQuery(out outID, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行SqlCommand
/// </summary>
/// <param name="id">影响的行数</param>
/// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(out int outID, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
// 创建SqlCommand命令,并进行预处理
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
Createcommand(cmd, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令
int retval = cmd.ExecuteNonQuery();
// 清除参数,以便再次使用.
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT @@identity";
outID = int.Parse(cmd.ExecuteScalar().ToString());
if (mustCloseConnection)
{
Close();
}
return retval;
}
#endregion
#region ExecuteScalar
/// <summary>
/// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object ExecuteScalar(string commandText)
{
return ExecuteScalar(CommandType.Text, commandText);
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
/// </summary>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object ExecuteScalar(CommandType commandType, string commandText)
{
return ExecuteScalar(commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
/// </summary>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
// 创建SqlCommand命令,并进行预处理
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
Createcommand(cmd, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行SqlCommand命令,并返回结果.
object retval = cmd.ExecuteScalar();
// 清除参数,以便再次使用.
cmd.Parameters.Clear();
if (mustCloseConnection)
Close();
return retval;
}
#endregion
#region ExecuteDataset
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns>返回一个包含结果集的DataSet</returns>
public static DataSet ExecuteDataset(string commandText)
{
return ExecuteDataset(CommandType.Text, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
/// </summary>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>返回一个包含结果集的DataSet</returns>
public static DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
// 预处理
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
Createcommand(cmd, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建DbDataAdapter和DataSet.
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = cmd;
DataSet ds = new DataSet();
// 填充DataSet.
da.Fill(ds);
cmd.Parameters.Clear();
if (mustCloseConnection)
Close();
return ds;
}
}
#endregion
#region ExecuteReader
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <param name="commandText">存储过程名或SQL语句</param>
/// <returns>返回包含结果集的SqlDataReader</returns>
private static SqlDataReader ExecuteReader(string commandText)
{
return ExecuteReader(CommandType.Text, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或SQL语句</param>
/// <returns>返回包含结果集的SqlDataReader</returns>
private static SqlDataReader ExecuteReader(CommandType commandType, string commandText)
{
return ExecuteReader(commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或SQL语句</param>
/// <param name="commandParameters">SqlParameters参数数组,如果没有参数则为'null'</param>
/// <returns>返回包含结果集的SqlDataReader</returns>
private static SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
bool mustCloseConnection = false;
// 创建命令
SqlCommand cmd = new SqlCommand();
try
{
Createcommand(cmd, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据阅读器
SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// _queries++;
// 清除参数,以便再次使用..
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can磘 set its values.
// When this happen, the parameters can磘 be used again in other command.
bool canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
//cmd.Dispose();
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
Close();
throw;
}
}
#endregion
#region 生成参数
/// <summary>
/// 生成参数
/// </summary>
/// <param name="ParamName">参数名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数类型值大小</param>
/// <param name="Value">参数值</param>
/// <returns></returns>
public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
/// 生成参数
/// </summary>
/// <param name="ParamName">参数名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Value">参数值</param>
/// <returns></returns>
public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, object Value)
{
return MakeParam(ParamName, DbType, 0, ParameterDirection.Input, Value);
}
/// <summary>
/// 生成参数
/// </summary>
public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
param = MakeParam(ParamName, DbType, Size);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
/// <summary>
/// 生成参数
/// </summary>
public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size)
{
SqlParameter param;
if (Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType);
return param;
}
#endregion
}
}

复制代码

王豆豆 · 发表于 2013-3-3 19:44:04

沙发啊，嘿嘿

		自动登录	找回密码
密码			注册

[图文教程] NET数据库基本操作类