一个简短的SqlHelper 小程序必备
SqlHelper.cs:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace PROJECT
{
public class SqlHelper
{
public static readonly string ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
private static SqlCommand cmd = null;
private static SqlConnection conn = new SqlConnection(ConnStr);
private static SqlDataReader sdr = null;
private static SqlConnection Getconn()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
}
/// <summary>
/// 不带参数-增删改
/// INSERT|DELETE|UPDATE
/// </summary>
public static int ExecuteNonQuery(String sqlStr)
{
int res;
cmd = new SqlCommand(sqlStr, Getconn());
res = cmd.ExecuteNonQuery();
return res;
}
/// <summary>
/// 带参数-增删改
/// INSERT|DELETE|UPDATE
/// </summary>
public static int ExecuteNonQuery(string sqlStr, SqlParameter[] sqlparas)
{
int res;
cmd = new SqlCommand(sqlStr, Getconn());
cmd.Parameters.AddRange(sqlparas);
res = cmd.ExecuteNonQuery();
conn.Close();
return res;
}
//执行查询,并返回查询所返回的结果集中第一行的第一列。 忽略其他列或行。
//insert一条数据,返回自增的id值:insert into table([NAME])values ('张三');Select @@Identity;
public static object ExecuteScalar(string sqlStr, SqlParameter[] sqlparas)
{
object res;
cmd = new SqlCommand(sqlStr, Getconn());
cmd.Parameters.AddRange(sqlparas);
res = cmd.ExecuteScalar();
conn.Close();
return res;
}
/// <summary>
/// 不带参数-取数据
/// SELECT
/// </summary>
public static DataTable ExecuteReader(string sqlStr)
{
DataTable dt = new DataTable();
cmd = new SqlCommand(sqlStr, Getconn());
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>
/// 带参数-取数据
/// SELECT
/// </summary>
public static DataTable ExecuteReader(string sqlStr, SqlParameter[] sqlparas)
{
DataTable dt = new DataTable();
cmd = new SqlCommand(sqlStr, Getconn());
cmd.Parameters.AddRange(sqlparas);//和不带SqlParameter的方法就差这一行
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
}
}
app.config:
<connectionStrings>
<add name="ConStr" connectionString="server=.\;database=test;uid=sa;pwd=123456"/>
</connectionStrings>
使用1:
string sql = "Select * FROM [T_USER]";
var dt = SqlHelper.ExecuteNonQuery(sql);
使用2:
string sql = "Select * FROM [T_USER] WHERE [UserId]=@ID AND [UserName]=@Name";
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter("@ID",System.Data.SqlDbType.Int,123),
new SqlParameter("@Name","张三"),
};
var dt = SqlHelper.ExecuteNonQuery(sql,paras);