C#工具类SqlServerHelper，基于System.Data.SqlClient封装
2024-10-19 01:07:17
源码：
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient; namespace Fly.Util.DataBase
{
/// <summary>
/// SqlServer数据库操作类
/// </summary>
public static class SqlServerHelper
{
/// <summary>
/// 执行数据库非查询操作,返回受影响的行数
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="cmdType">命令的类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前操作影响的数据行数</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
} /// <summary>
/// 执行数据库事务非查询操作,返回受影响的行数
/// </summary>
/// <param name="transaction">数据库事务对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前事务操作影响的数据行数</returns>
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
} /// <summary>
/// 执行数据库非查询操作,返回受影响的行数
/// </summary>
/// <param name="connection">SqlServer数据库连接对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前操作影响的数据行数</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
if (connection == null)
throw new ArgumentNullException("当前数据库连接不存在");
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
} /// <summary>
/// 执行数据库查询操作,返回SqlDataReader类型的内存结果集
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="cmdType">命令的类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前查询操作返回的SqlDataReader类型的内存结果集</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return reader;
}
catch
{
cmd.Dispose();
conn.Close();
throw;
}
} /// <summary>
/// 执行数据库查询操作,返回DataSet类型的结果集
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="cmdType">命令的类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前查询操作返回的DataSet类型的结果集</returns>
public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
DataSet ds = null;
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
} return ds;
} /// <summary>
/// 执行数据库查询操作,返回DataTable类型的结果集
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="cmdType">命令的类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前查询操作返回的DataTable类型的结果集</returns>
public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
DataTable dt = null; try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
dt = new DataTable();
adapter.Fill(dt);
cmd.Parameters.Clear();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
} return dt;
} /// <summary>
/// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="cmdType">命令的类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
object result = null;
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
result = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
} return result;
} /// <summary>
/// 执行数据库事务查询操作,返回结果集中位于第一行第一列的Object类型的值
/// </summary>
/// <param name="trans">一个已存在的数据库事务对象</param>
/// <param name="commandType">命令类型</param>
/// <param name="commandText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前事务查询操作返回的结果集中位于第一行第一列的Object类型的值</returns>
public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
if (trans == null)
throw new ArgumentNullException("当前数据库事务不存在");
SqlConnection conn = trans.Connection;
if (conn == null)
throw new ArgumentException("当前事务所在的数据库连接不存在"); SqlCommand cmd = new SqlCommand();
object result = null; try
{
PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
result = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch
{
throw;
}
finally
{
trans.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
} return result;
} /// <summary>
/// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值
/// </summary>
/// <param name="conn">数据库连接对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
/// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns>
public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
if (conn == null) throw new ArgumentException("当前数据库连接不存在");
SqlCommand cmd = new SqlCommand();
object result = null; try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
result = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
} return result;
} /// <summary>
/// 执行存储过程
/// </summary>
/// <param name="connection">SqlServer数据库连接对象</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader RunStoredProcedure(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlDataReader returnReader = null;
connection.Open();
SqlCommand command = BuildSqlCommand(connection, storedProcName, parameters);
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
} /// <summary>
/// 执行数据库命令前的准备工作
/// </summary>
/// <param name="cmd">Command对象</param>
/// <param name="conn">数据库连接对象</param>
/// <param name="trans">事务对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">SqlServer存储过程名称或PL/SQL命令</param>
/// <param name="cmdParms">命令参数集合</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open(); cmd.Connection = conn;
cmd.CommandText = cmdText; if (trans != null)
cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
} /// <summary>
/// 构建SqlCommand对象
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildSqlCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
}
}
最新文章
- Bean生命周期及BeanFactory
- 使用 Object.create 创建对象，super 关键字，class 关键字
- python中raw_input() 与 input()
- Positive-definite matrix
- Java Struts2读取Excel 2003/2007/2010例子
- [Solution] Microsoft Windows 服务(2) 使用Topshelf创建Windows服务
- openssl mac中使用终端生成RSA私钥和公钥文件
- POJ 1773 Parity game 带权并查集
- Codeforces 543B Destroying Roads(最短路)
- 使用bootstrapvalidator的remote验证经验
- android样式布局---&gt;ListView(附上源代码)
- ASP.NET Core教程【一】关于Razor Page的知识
- 网络编程之套接字（tcp）
- SpringBoot文件上传（MVC情况和webFlux情况）
- [LeetCode] Loud and Rich 聒噪与富有
- 初探LaTeX
- C# 栈 、队列的概念
- java之双缓冲的代码粘贴
- windebug 与 office Home
- Keras的泰坦尼克号的生存率的数据分析