SQLHELPER 帮助类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 数据库的读取
{
using System.Data;
using System.Data.SqlClient;
class SQLHelper
{

static string sqlconn = System.Configuration.ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString;

/// <summary>
/// 执行查询结果返回第一列值第一列的值
/// </summary>
public static object ExecuteScalar(string sql ,params SqlParameter[] sp)
{
using (SqlConnection conn =new SqlConnection(sqlconn))
{
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(sp);
return comm.ExecuteScalar();
}

}

/// <summary>
/// 返回表 默认
/// </summary>
/// <param name="sql"></param>
/// <param name="sp"></param>
/// <returns></returns>
public static DataTable GetTable(string sql ,SqlParameter[] sp)
{
using(SqlConnection conn =new SqlConnection(sqlconn))
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.SelectCommand.Parameters.AddRange(sp);
DataTable table = new DataTable();
da.Fill(table);
return table;
} 
}
/// <summary>
/// 返回表 
/// </summary>
/// <param name="sql"></param>
/// <param name="sp"></param>
/// <returns></returns>
public static DataTable GetTable(string sql, CommandType type ,SqlParameter[] sp)
{
//using (SqlConnection conn = new SqlConnection(sqlconn)) //可以自动释放conn 用了适配器
//{
// conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sql,new SqlConnection(sqlconn));
da.SelectCommand.CommandType = type;
da.SelectCommand.Parameters.AddRange(sp);
DataTable table = new DataTable();
da.Fill(table);

return table;
//}
}

 

/// <summary>
/// 创建读取器
/// </summary>
/// <returns></returns>
public static SqlDataReader GetSqlDataRead(string sql ,SqlParameter[] sp)
{
//创建读取器不能关闭
SqlConnection conn = new SqlConnection(sqlconn);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(sp);
return comm.ExecuteReader(CommandBehavior.CloseConnection);//当关闭读取器关闭相对的连接
}

//执行sql语句
public static int ExecuteNonQuery(string sql, SqlParameter[] sp)
{
using (SqlConnection conn =new SqlConnection(sqlconn))
{
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(sp);
return comm.ExecuteNonQuery();
}

}

}
}