ado.net SqlHelp类
2024-10-06 23:43:25
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient; namespace Reservation.DAL
{
public class SqlHelper
{
/// <summary>
/// 自定义数据库连接字符串
/// </summary>
public static string ConnString = @"Data Source=.;Initial Catalog=DB_Date_Plan; User ID=sa;Pwd=111111";
/// <summary>
/// 应用程序下获取连接字符串
/// </summary>
//public static string ConnString
//{
// get { // return System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ToString();
// }
//} /// <summary>
/// 执行一个Sql语句返回受影响的行数
/// </summary>
/// <param name="sql">insert,update,delete或相关的存储过程</param>
/// <param name="type">命令类型：SQL语句还是存储过程</param>
/// <param name="pars">SQL语句所需要的参数</param>
public static int ExcuteSQLReturnInt(string sql, CommandType type, params SqlParameter[] pars)
{
//定义连接对象
SqlConnection conn = new SqlConnection(ConnString);
//判断连接对象的状态，并且打开
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}
try
{
//实例化命令对象
SqlCommand cmd = new SqlCommand(sql, conn);
//判断CommandType类型是否是SQL语句还是存储过程
cmd.CommandType = type;
if (pars != null && pars.Length > )
{
foreach (SqlParameter p in pars)
{
cmd.Parameters.Add(p);
}
}
//调用方法执行SQL语句或存储过程
int count = cmd.ExecuteNonQuery();
return count;
}
catch (Exception ex)
{
return ;
}
finally
{
//记得要关闭连接
conn.Close();
}
} /// <summary>
/// 执行一个Sql语句或存储过程，返回一条记录，sqldataReader
/// </summary>
/// <param name="sql">select语句，或相关的存储过程</param>
/// <param name="type">指定命令类型，sql语句还是存储过程，默认的是sql语句</param>
/// <param name="pars">参数的集合</param>
/// <returns></returns>
public static SqlDataReader ExcuteSqlReturnReader(string sql, CommandType type, SqlParameter[] pars)
{
SqlConnection conn = new SqlConnection(ConnString);
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(sql, conn);
if (pars != null && pars.Length > )
{
foreach (SqlParameter p in pars)
{
cmd.Parameters.Add(p);
}
}
cmd.CommandType = type;
//当reader.close(),也就是关闭了datareader时，CommandBehavior.CloseConnection 也会关闭连接！
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
} /// <summary>
/// 执行一个sql语句或存储过程,不带参数的!返回一天记录 sqldataReader
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <returns></returns>
public static SqlDataReader ExcuteSqlReturnReader(string sql, CommandType type)
{
SqlConnection conn = new SqlConnection(ConnString);
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = type;
//当reader.close(),也就是关闭了datareader时，CommandBehavior.CloseConnection 也会关闭连接！
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
} /// <summary>
/// 执行一个Sql语句或存储过程，返回一个数据集，dataset
/// </summary>
/// <param name="sql">select语句或相关的存储过程</param>
/// <param name="type">命令类型</param>
/// <param name="pars">命令类型</param>
/// <returns>DataSet</returns>
public static DataSet SelectSqlReturnDataset(string sql, CommandType type, SqlParameter[] pars)
{
SqlConnection conn = new SqlConnection(ConnString);
//数据集识别器对象SqlDataAdapter 会自动打开数据库链接！
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
if (pars != null && pars.Length > )
{
foreach (SqlParameter p in pars)
{
sda.SelectCommand.Parameters.Add(p);
}
}
sda.SelectCommand.CommandType = type;
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
} /// <summary>
/// 执行一个sql语句返回一个数据表对象
/// </summary>
/// <param name="sql">select</param>
/// <param name="type">命令类型</param>
/// <param name="pars">参数集合</param>
/// <returns>DataTable</returns>
public static DataTable SelectSqlReturnDataTable(string sql, CommandType type, SqlParameter[] pars)
{
SqlConnection conn = new SqlConnection(ConnString);
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
if (pars != null && pars.Length > )
{
foreach (SqlParameter p in pars)
{
sda.SelectCommand.Parameters.Add(p);
}
}
sda.SelectCommand.CommandType = type;
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
} //*******************************************************************
//SelectSqlReturnDataTable方法的重载，不传参数的情况下
/// <summary>
/// 执行一个sql语句返回一个数据表对象,不传参数
/// </summary>
/// <param name="sql">select</param>
/// <param name="type">命令类型</param>
/// <returns></returns>
public static DataTable SelectSqlReturnDataTable(string sql, CommandType type)
{
SqlConnection conn = new SqlConnection(ConnString);
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
sda.SelectCommand.CommandType = type;
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
} /// <summary>
/// 执行一个sql语句或相关的存储过程，返回一个值
/// </summary>
/// <param name="sql">select count(*) from tablename where ....</param>
/// <param name="type">命令类型</param>
/// <param name="pars">所需的参数</param>
/// <returns>object</returns>
public static object selectSqlReturnObject(string sql, CommandType type, SqlParameter[] pars)
{
SqlConnection conn = new SqlConnection(ConnString);
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = type;
if (pars != null && pars.Length > )
{
foreach (SqlParameter p in pars)
{
cmd.Parameters.Add(p);
}
}
object obj = cmd.ExecuteScalar();
return obj;
}
catch (Exception ex)
{
return null;
}
finally
{
conn.Close();
}
} /// <summary>
/// 执行一个sql语句或相关的存储过程，返回一个值，不传参数
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <returns></returns>
public static object selectSqlReturnObject(string sql, CommandType type)
{
SqlConnection conn = new SqlConnection(ConnString);
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = type;
object obj = cmd.ExecuteScalar();
return obj;
}
catch (Exception ex)
{
return null;
}
finally
{
conn.Close();
}
} }
}
最新文章
- [spring]03_装配Bean
- Android笔记：android的适配
- Android 四大组件之四(ContentProvider)
- Bootstrap系列 -- 6. 列表
- android-Okhttp初步使用
- [mondrian] 分析一个简单的schema文件
- 导入 Mysql 示例数据库 employees
- SQL Server 中添加用户
- 【LeetCode练习题】Longest Valid Parentheses
- Nhibernate refers to an unmapped class nhibernate问题的解决（初学者）
- bitmap 合并图片
- Selenium3+python几种定位元素的方法
- java ip 正则表达式
- MySQL安全策略
- visio直线交叉相交跨线修改
- Docker应用之容器
- nginx 的多域名多https转发设置方法【转】
- Redis 连接命令
- 使用百度地图API实现轨迹回放
- 【转】打开linux-tcp端口快速回收
热门文章
- 20165207 Exp8 Web基础
- 没有安装zip引发的一系列安装
- 如何实现一个串行promise
- ORA-00904: "B"."METHOD": 标识符无效，00904. 00000 - "%s: invalid identifier"
- 2019.11.29 SAP SMTP郵件服務器配置 發送端 QQ郵箱
- POI XSSF与HSSF区别
- python3.4 + pycharm安装与使用
- 【AMAD】django-filer -- 一个管理文件和图片的django app
- 如何为根分区扩容（centos7为例）
- TensorFlow实战第八课（卷积神经网络CNN）