C# 中 SqlHelper

最新推荐文章于 2025-08-06 16:23:50 发布

原创最新推荐文章于 2025-08-06 16:23:50 发布 · 662 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

本文介绍了一个用于简化数据库操作的SQLHelper类，该类提供了一系列实用的方法来执行SQL命令，包括执行非查询操作、获取单一结果、执行存储过程实现分页等功能。

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

namespace DAL
{
class SqlHelper
{
private static readonly string ConnStr = ConfigurationManager.ConnectionStrings["DBTestStudy"].ConnectionString;

#region ExecuteNonQuery(string sql,params SqlParameter[] parames)
/// <summary>
/// ExecuteNonQuery
/// </summary>
/// <param name="sql"></param>
/// <param name="parames"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] parames)
{

using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parames);

return cmd.ExecuteNonQuery();

}
}
}
#endregion

#region ExecuteScalar(string sql, params SqlParameter[] parames)
/// <summary>
/// ExecuteScalar(string sql, params SqlParameter[] parames)
/// </summary>
/// <param name="sql"></param>
/// <param name="parames"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params SqlParameter[] parames)
{

using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql; ;
cmd.Parameters.AddRange(parames);

return cmd.ExecuteScalar();//@@select @@identity;
}
}
}
#endregion

#region T ExecuteScalar<T>(string sql, params SqlParameter[] parames)
/// <summary>
/// T ExecuteScalar<T>(string sql, params SqlParameter[] parames)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="parames"></param>
/// <returns></returns>
public static T ExecuteScalar<T>(string sql, params SqlParameter[] parames)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql; ;
cmd.Parameters.AddRange(parames);

Type t = typeof(T);

object result = cmd.ExecuteScalar();

return (T)Convert.ChangeType(result, t);


//@@select @@identity;
}
}
}
#endregion

#region DataTable ExecuteDataTable(string sql, params SqlParameter[] parames)
/// <summary>
/// DataTable ExecuteDataTable(string sql, params SqlParameter[] parames)
/// </summary>
/// <param name="sql"></param>
/// <param name="parames"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parames)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText=sql;
cmd.Parameters.AddRange(parames);

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataTable table = new DataTable();
adapter.Fill(table);

return table;
}
}
}
#endregion

#region List<T> ExecuteList<T>(string sql, params SqlParameter[] parames)
public static List<T> ExecuteList<T>(string sql, params SqlParameter[] parames)
{
DataTable table = ExecuteDataTable(sql, parames);
if (table.Rows.Count > 0)
{
List<T> list = new List<T>();
foreach(DataRow row in table.Rows)
{
Type t = typeof(T);
T instance = (T)Activator.CreateInstance(t);
PropertyInfo[]properties = t.GetProperties();
foreach (PropertyInfo p in properties)
{
string colName = p.Name;
object value = row[colName];
p.SetValue(instance, value, null);
}
list.Add(instance);

}
return list;
}
return new List<T>(0);
}
#endregion

#region SqlDataReader ExecuteReader(string sql, params SqlParameter[] parames)
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parames)
{
SqlConnection conn = null;
try
{
conn = new SqlConnection(ConnStr);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(parames);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (SqlException e)
{
conn.Dispose();
throw e;
}
}
#endregion

#region List<T> ExecuteGetPagingPage<T>(int send, int target,out int PageCount)
/// <summary>
/// 这是数据库分页函数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="send">页面显示多少条数据，也叫种子</param>
/// <param name="target">请求第几页</param>
/// <param name="PageCount">页面总数，存在多少页面</param>
/// <returns></returns>
public static List<T> ExecuteGetPagingPage<T>(int seed, int target,out int PageCount)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetPageData"; //执行当前数据库的存储过程GetPageData
/*
* @seed int,
@target int,
@pagecount int output 要注意哦，这里的参数名称必须要个数据库端的存储过程的参数一样
*/
cmd.Parameters.Add("@seed", SqlDbType.Int);
cmd.Parameters["@seed"].Value = seed;
cmd.Parameters.Add("@target", SqlDbType.Int);
cmd.Parameters["@target"].Value = target;

//输出参数
cmd.Parameters.Add("@pagecount", SqlDbType.Int);
cmd.Parameters["@pagecount"].Direction = ParameterDirection.Output;

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);

PageCount = (int)cmd.Parameters["@pagecount"].Value;

if (table.Rows.Count > 0)
{
List<T> list = new List<T>();
foreach(DataRow row in table.Rows)
{
Type t = typeof(T);
T instance = (T)Activator.CreateInstance(t);
PropertyInfo [] properties = t.GetProperties();
foreach (PropertyInfo p in properties)
{
string colname = p.Name;
object colvalue = row[colname];
p.SetValue(instance, colvalue, null);
}
list.Add(instance);
}

return list;
}
}//end cmd
}
return new List<T>(0);
}
#endregion
}