---
title: "sqlhelper"
author: "mywebcode"
date: "2012-11-29T20:55:00Z"
tags: []
slug: "sqlhelper"
draft: false
lastmod: "2013-05-08T10:26:28Z"
id: 1862863
url: "https://mywebcode.iteye.com/blog/1862863"
---

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
using System.Configuration;  
using System.Data.SqlClient;  
using System.Data;

namespace sqlHealpler  
{  
public static class SqlHealper  
{  
private static readonly string constr = ConfigurationManager.ConnectionStrings\["sqlcon"].ConnectionString;  
/// &lt;summary&gt;  
/// 执行insert，delete，update的方法  
/// &lt;/summary&gt;  
/// &lt;param name="sql"&gt;请输入参数1，要执行的sql语句&lt;/param&gt;  
/// &lt;param name="pms"&gt;sql语句中的参数&lt;/param&gt;  
/// &lt;returns&gt;&lt;/returns&gt;  
public static int ExectueNonQuery(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);//将参数（课能是数组）添加到cmd对象中  
}  
con.Open();  
return cmd.ExecuteNonQuery();//返回执行返回的值

}  
}

}

public static object ExecuteScalar(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();

}  
}  
}  
/// &lt;summary&gt;  
/// 当返回dataReader的时候，connection不能关闭，DataReader不能关闭。  
/// 3给command对象一个参数 SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);  
/// 这是 在关闭reader的时候就把 connection 关闭了 。  
///  
/// &lt;/summary&gt;  
/// &lt;param name="sql"&gt;&lt;/param&gt;  
/// &lt;param name="pms"&gt;&lt;/param&gt;  
/// &lt;returns&gt;&lt;/returns&gt;  
public static SqlDataReader Executereader(sql,params SqlParameter\[] par)  
{  
SqlConnection conn=new SqlConnection(constr);

using(SqlCommand cmd=new SqlCommand())  
{  
if(par!=null)  
{  
cmd.Parameters.AddRange(par);  
}  
conn.Open();  
SqlDataReader reader=cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);  
return reader;  
}

}

public static DataTable ExecuteDataTable(string sql, params SqlParameter\[] pms)  
{  
SqlDataAdapter adapter=new SqlDataAdapter(sql,constr);  
DataSet ds=new DataSet("MyStudents");

adapter.selectcommand.parameters.addrange(par);  
adapter.Fill(ds);

return ds.Tables\[0];  
}  
}  
}