sqlHelper

最新推荐文章于 2023-01-29 11:41:26 发布

转载最新推荐文章于 2023-01-29 11:41:26 发布 · 80 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/linnew/p/sqlHelper.html

本文介绍了一个SQL辅助类的设计实现，该类提供了一系列方法用于执行常见的数据库操作，包括增删改查等。通过封装SqlConnection和SqlCommand对象，简化了.NET应用程序中数据库交互的复杂性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Threading.Tasks;

namespace BookShopDAL

{

/// <summary>

/// Sql辅助类==》用于操作数据库

/// </summary>

public class SqlHelper

{

/// <summary>

/// 连接字符串

/// </summary>

//private static readonly string connectionString = ConfigurationManager.ConnectionStrings["BookShopPlus"].ConnectionString;

private static readonly string connectionString = @"server=SERVER\SQLEXPRESS;database=BookShopPlus;uid=sa;pwd=sa";

//private static readonly string connectionString = "server=.;database=BookShopPlus;uid=sa;pwd=sa";

/// <summary>

/// 获取连接

/// </summary>

private static SqlConnection _conn;

public static SqlConnection Conn

{

get

{

if (_conn == null) _conn = new SqlConnection(connectionString);

if (_conn.State == ConnectionState.Broken)

{

_conn.Close();

_conn.Open();

}

else if (_conn.State == ConnectionState.Closed)

{

_conn.Open();

}

return _conn;

}

/// <summary>

/// 关闭连接

/// </summary>

public static void CloseConnection()

{

if (Conn == null) return;

if (Conn.State == ConnectionState.Open || Conn.State == ConnectionState.Broken)

{

Conn.Close();

}

/// <summary>

/// 增删改

/// </summary>

/// <param name="sql">sql语句</param>

/// <param name="type">执行解析类型默认 sql</param>

/// <param name="param">参数</param>

/// <returns></returns>

public static int ExecuteNonQuery(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)

{

try

{

//创建执行对象

SqlCommand cmd = new SqlCommand(sql, Conn);

//判断是否存在参数

if (param != null && param.Length > 0)

{

cmd.Parameters.AddRange(param);

}

cmd.CommandType = type;//指定解析类型

return cmd.ExecuteNonQuery();//返回执行后的结果

}

catch (Exception e)

{

throw e;

}

finally

{

CloseConnection();

}

/// <summary>

///

/// </summary>

/// <param name="sql">sql语句</param>

/// <param name="type">执行解析类型默认 sql</param>

/// <param name="param">参数</param>

/// <returns>返回查询所返回的结果集中第一行的第一列</returns>

public static int ExecuteScalar(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)

{

try

{

//创建执行对象

SqlCommand cmd = new SqlCommand(sql, Conn);

//判断是否存在参数

if (param != null && param.Length > 0)

{

cmd.Parameters.AddRange(param);

}

cmd.CommandType = type;//指定解析类型

return (int)cmd.ExecuteScalar();//返回执行后的结果

}

catch (Exception e)

{

throw e;

}

finally

{

CloseConnection();

}

/// <summary>

/// 异步操作的任务查询

/// </summary>

/// <param name="sql">sql语句</param>

/// <param name="type">执行解析类型默认 sql</param>

/// <param name="param">参数</param>

/// <returns>Task<SqlDataReader></returns>

public static Task<SqlDataReader> ExecuteReaderAsync(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)

{

SqlCommand cmd = new SqlCommand(sql, Conn);

if (param != null && param.Length > 0)

{

cmd.Parameters.AddRange(param);

}

cmd.CommandType = type;

return cmd.ExecuteReaderAsync();

}

/// <summary>

/// 查询

/// </summary>

/// <param name="sql">sql语句</param>

/// <param name="type">执行解析类型默认 sql</param>

/// <param name="param">参数</param>

/// <returns>SqlDataReader</returns>

public static SqlDataReader ExecuteReader(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)

{

SqlCommand cmd = new SqlCommand(sql, Conn);

if (param != null && param.Length > 0)

{

cmd.Parameters.AddRange(param);

}

cmd.CommandType = type;

return cmd.ExecuteReader();

}

/// <summary>

/// 查询(断开式连接)

/// </summary>

/// <param name="sql">sql语句</param>

/// <param name="type">执行解析类型默认 sql</param>

/// <param name="param">参数</param>

/// <returns>DataTable</returns>

public static DataTable GetTable(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)

{

DataSet ds = new DataSet();

SqlDataAdapter sda = new SqlDataAdapter(sql, Conn);

if (param != null && param.Length > 0)

{

sda.SelectCommand.Parameters.AddRange(param);

}

sda.SelectCommand.CommandType = type;

sda.Fill(ds);//填充数据到ds

return ds.Tables[0];

}

转载于:https://www.cnblogs.com/linnew/p/sqlHelper.html