SqlHelper 帮助类
来源:互联网 发布:2016年快递行业数据 编辑:程序博客网 时间:2024/06/11 16:43
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Collections;
using System.Configuration;
namespace OA.Dal
{
public class SqlHelper
{
public static readonly string ConnStr = "Data Source=.;Initial Catalog=myOffice;Persist Security Info=True;uid=sa;pwd=sa;Pooling=False";
/// <summary>
/// 为Command对象的执行做准备
/// </summary>
/// <param name="cmd">要执行的SqlCommand对象</param>
/// <param name="conn">SqlConnection对象</param>
/// <param name="trans">事物对象</param>
/// <param name="cmdType">0表示Text,1表示StoredProcedure,2表示TableDirect</param>
/// <param name="cmdText">要执行的Sql语句或存储过程名称</param>
/// <param name="cmdParms">SqlParameter参数数组</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, int cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
switch (cmdType)
{
case 0:
cmd.CommandType = CommandType.Text;
break;
case 1:
cmd.CommandType = CommandType.StoredProcedure;
break;
case 2:
cmd.CommandType = CommandType.TableDirect;
break;
default:
cmd.CommandType = CommandType.Text;
break;
}
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
/// <summary>
/// 执行带参数的存储过程
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="cmdType">0表示Text,1表示StoredProcedure,2表示TableDirect</param>
/// <param name="cmdText">存储过程名称</param>
/// <param name="commandParameters">SqlParameter参数数组</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, int cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行没有返回结果集的SQL语句
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string sql)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, conn, null, 0, sql, null);
int val = cmd.ExecuteNonQuery();
return val;
}
}
/// <summary>
/// 执行带参数的且没有返回结果集的SQL语句
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandParameters">SQL参数数组</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, conn, null, 0, sql, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行未返回结果集的存储过程
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="storedProcedure">0表示Text,1表示StoredProcedure,2表示TableDirect</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(string sql,int storedProcedure)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, conn, null, storedProcedure, sql, null);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行带参数的存储过程
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="storedProcedure">0表示Text,1表示StoredProcedure,2表示TableDirect</param>
/// <param name="commandParameters">存储过程参数数组</param>
/// <returns>返回有影响行数</returns>
public static int ExecuteNonQuery(string sql, int storedProcedure, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, conn, null, storedProcedure, sql, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行基于事物的带参数的SQL
/// </summary>
/// <param name="trans">SqlTransaction事物对象</param>
/// <param name="cmdText">要执行的SQL语句</param>
/// <param name="commandParameters">SQL语句参数</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, 0, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 执行带参数的SQL语句返回一个SqlDataReader对象
/// </summary>
/// <param name="cmdText">要执行的SQL语句</param>
/// <param name="commandParameters">SqlParameter参数数组</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConnStr);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, 0, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 执行SQL语句返回一个SqlDataReader对象
/// </summary>
/// <param name="cmdText">要执行的Sql语句</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader ExecuteReader(string cmdText)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConnStr);
try
{
PrepareCommand(cmd, conn, null, 0, cmdText, null);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 执行带参数的SQL语句,返回首行首列的值
/// </summary>
/// <param name="cmdText">要执行的SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>首行首列的值</returns>
public static object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, connection, null, 0, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行SQL语句,返回首行首列的值
/// </summary>
/// <param name="cmdText">要执行的SQL语句</param>
/// <returns>首行首列的值</returns>
public static object ExecuteScalar(string cmdText)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, connection, null, 0, cmdText, null);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行SQL语句返回一个DataSet
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>DataSet对象</returns>
public static DataSet ExecuteDataSet(string sql)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
using (SqlConnection Conn = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, Conn, null, 0, sql, null);
SqlDataAdapter dapter = new SqlDataAdapter(cmd);
dapter.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
/// <summary>
/// 执行带参数的SQL语句返回一个DataSet
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandParameters">参数数组</param>
/// <returns>DataSet对象</returns>
public static DataSet ExecuteDataSet(string sql,SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
using (SqlConnection Conn = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, Conn, null, 0, sql,commandParameters);
SqlDataAdapter dapter = new SqlDataAdapter(cmd);
dapter.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
/// <summary>
/// 执行带参数的存储过程返回一个DataSet
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="storedProcedure">0表示Text,1表示StoredProcedure,2表示TableDirect</param>
/// <param name="commandParameters">存储过程参数</param>
/// <returns>DataSet对象</returns>
public static DataSet ExecuteDataSet(string sql, int storedProcedure,SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
using (SqlConnection Conn = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, Conn, null, storedProcedure, sql, commandParameters);
SqlDataAdapter dapter = new SqlDataAdapter(cmd);
dapter.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
/// <summary>
/// 执行带参数的存储过程返回一个DataSet
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="storedProcedure">0表示Text,1表示StoredProcedure,2表示TableDirect</param>
/// <returns>DataSet对象</returns>
public static DataSet ExecuteDataSet(string sql, int storedProcedure)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
using (SqlConnection Conn = new SqlConnection(ConnStr))
{
PrepareCommand(cmd, Conn, null, storedProcedure, sql, null);
SqlDataAdapter dapter = new SqlDataAdapter(cmd);
dapter.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
}
}
- SqlHelper 帮助类
- SQLHELPER 帮助类
- c#数据库帮助类SqlHelper
- 微软SQLHelper类数据访问帮助类
- Java的一个SQLHelper帮助类
- SqlHelper帮助类------简约而不简单
- 微软SqlHelper.cs数据访问帮助类
- C# 对数据库操作的帮助类SQLHelper.cs
- SQLHelper类
- SqlHelper类
- SqlHelper类
- SqlHelper类
- sqlhelper类
- SqlHelper类
- SqlHelper类
- SqlHelper类
- SQLHelper类
- SqlHelper类
- 玩转Google开源C++单元测试框架Google Test系列(gtest)之一 - 初识gtest
- C++中const、volatile、mutable的用法
- java--new
- oracle中 connect by prior 递归算法
- 站长的痛苦:鲜为人知让网站不再被K的秘决
- SqlHelper 帮助类
- Objective-c中@class和#import
- SQL不同服务器数据库之间的数据操作整理(完整版)
- 影响搜索引擎收录网站内容的四大原因分析
- blog 分类集合
- 笔记:一个do...while的使用!~
- 玩转Google开源C++单元测试框架Google Test系列(gtest)之二 - 断言
- C#——正则表达式的使用
- opengl 不错的网站