<pre class="css" name="code"><pre name="code" class="html">
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace SqlHelp类优化代码
{
public static class sqlhelp
{
private static readonly string constr = ConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString;
/// <summary>
/// 执行insert,delete,update的方法
/// </summary>
/// <param name="sql">请输入参数1,要执行的sql语句</param>
/// <param name="pms">sql语句中的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)//说明还传了参数
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery
();
}
}
}
public static object ExecuteSclar(string sql, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)//说明还传了参数
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 当返回dataReader的时候,connection不能关闭,DataReader不能关闭。
/// 3给command对象一个参数 SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
/// 这是 在关闭reader的时候就把 connection 关闭了 。
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql,params SqlParameter[] pms )
{
SqlConnection con = new SqlConnection(constr);//不使用using,不关闭连接
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)//说明还传了参数
{
cmd.Parameters.AddRange(pms);
}
con.Open();
SqlDataReader reader = cmd.ExecuteReader
(System.Data.CommandBehavior.CloseConnection);//不使用using,不关闭DataReader
return reader;
}
}
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
{
DataSet dataset = new DataSet();//在内存中开辟空间给 dataset
SqlDataAdapter adapeter = new SqlDataAdapter(sql, constr);//把数据库服务器中的查询结果给转接器 adapter
if (pms != null)
{
adapeter.SelectCommand.Parameters.AddRange(pms);
}
adapeter.Fill(dataset);//adapter把查询结果交给dataset
return dataset.Tables[0];
}
public static DataTable ExecuteDataTable(string sql, CommandType commandtype, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
SqlDataAdapter adapeter = new SqlDataAdapter(sql, constr2);//把数据库服务器中的查询结果给转接器 adapter
adapeter.SelectCommand.CommandType = commandtype;
if (pms != null)
{
adapeter.SelectCommand.Parameters.AddRange(pms);
}
adapeter.Fill(dt);
//adapter把查询结果交给dataset
return dt;
}
}
}
loner_li Asp.net 中常用的sqlhelp.cs 类文件
最新推荐文章于 2020-01-10 17:17:19 发布