using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
/// <summary>
///SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
{
/// <summary>
/// sql Server数据库操作帮助类
/// </summary>
public SqlHelper()
{
//
//TODO: 在此处添加¨构造函数逻辑
//
}
/// <summary>
/// 获取数据库连接字符串
/// </summary>
public static readonly string cnnstring = ConfigurationManager.ConnectionStrings["cnnstring"].ConnectionString;
/// <summary>
/// 获取结果集的第一行第一列的结果
/// </summary>
/// <param name="sqlText">要执行的检索语句</param>
/// <param name="paramss">检索语句中的参数列表</param>
/// <returns></returns>
public static object ExecuteScalar(string sqlText,params SqlParameter[] paramss)
{
using (SqlConnection sqlCnn=new SqlConnection (cnnstring))
{
using (SqlCommand sqlCmm=sqlCnn.CreateCommand())
{
sqlCmm.CommandText = sqlText;
fillParameter(paramss, sqlCmm);
sqlCnn.Open();
return sqlCmm.ExecuteScalar();
}
}
}
/// <summary>
/// 填充Command对象的参数列表
/// </summary>
/// <param name="paramss">参数列表</param>
/// <param name="sqlCmm">Command对象</param>
private static void fillParameter(SqlParameter[] paramss, SqlCommand sqlCmm)
{
foreach (SqlParameter param in paramss)
{
sqlCmm.Parameters.Add(param);
}
}
/// <summary>
/// 获取检索结果集,返回DataTable
/// </summary>
/// <param name="sqlText">要检索的语句</param>
/// <param name="paramss">检索语句中的参数列表</param>
/// <returns></returns>
public static DataTable ExecuteTable(string sqlText,params SqlParameter[] paramss)
{
using (SqlConnection sqlCnn=new SqlConnection (cnnstring))
{
using (SqlCommand sqlCmm=sqlCnn.CreateCommand())
{
sqlCmm.CommandText = sqlText;
fillParameter(paramss,sqlCmm);
sqlCnn.Open();
using (SqlDataReader reader=sqlCmm.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
return dt;
}
}
}
}
/// <summary>
/// 执行对数据库的增、删、改操作,并返回数据库中受影响的行数
/// </summary>
/// <param name="sqlText">要执行的insert、updata、delete语句</param>
/// <param name="paramss">要执行的语句中的参数列表</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sqlText,params SqlParameter[] paramss)
{
using (SqlConnection sqlCnn=new SqlConnection (cnnstring))
{
using (SqlCommand sqlCmm=sqlCnn.CreateCommand())
{
sqlCmm.CommandText = sqlText;
fillParameter(paramss,sqlCmm);
sqlCnn.Open();
return sqlCmm.ExecuteNonQuery();
}
}
}
}