sqlHelper

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

namespace sqlHelper
{
public static class SqlHelper
{
//string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//获取数据库连接字符串
private static SqlConnection GetConn()
{
return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString);
}
//执行sql语句并返回受影响行数
public static int ExecuteNoQuery(string sql)
{

//实现方法一 捕获异常
//int rows = -1;
//SqlConnection conn= GetConn();
//try
//{
// SqlCommand cmd = new SqlCommand(sql, conn);
// conn.Open();
// rows = cmd.ExecuteNonQuery();
//}
//catch (Exception)
//{
// //正式项目自己定义一个类 对异常进行处理
// throw;
//}
//finally
//{
//conn.Close();
//conn.Dispose();
//}
//return rows;

//实现方法2 不捕获异常
//int rows = -1;
//不捕获异常的写法
//using (SqlConnection conn= GetConn())
//{
// SqlCommand cmd = new SqlCommand(sql,conn);
// conn.Open();
// rows = cmd.ExecuteNonQuery();
//}
// return rows;

//实现方法3直接调用有参数的ExecuteNoQuery 传一个空的参数
return ExecuteNoneQuery(sql,null);
}
//执行sql语句带参数
public static int ExecuteNoneQuery(string sql,params SqlParameter[] param )
{
//int rows=-1;
//using (SqlConnection conn =GetConn())
//{
// SqlCommand cmd = new SqlCommand(sql,conn);
// //判断是否有参数
// if (param!=null)
// {
// cmd.Parameters.AddRange(param);
// }
// conn.Open();
// rows = cmd.ExecuteNonQuery();

//}
//return rows;

return ExecuteNoneQuery(sql,CommandType.Text,param);

}
//执行存数过程
public static int ExecuteNoneQuery(string sql,CommandType type,params SqlParameter[] param)
{
int rows = -1;
//不捕获异常的写法
using (SqlConnection conn= GetConn())
{
SqlCommand cmd = new SqlCommand(sql,conn);
cmd.CommandType=type;
conn.Open();
rows = cmd.ExecuteNonQuery();
}
return rows;
}
//执行sql语句返回首行首列
public static object ExecuteScalar(string sql,params SqlParameter[] param)
{
object obj = null;
using (SqlConnection conn=GetConn())
{
SqlCommand cmd = new SqlCommand(sql,conn);
cmd.Parameters.AddRange(param);
conn.Open();
obj = cmd.ExecuteScalar();
}
return obj;
}
//执行sql语句，返回结果集DataTable
public static DataTable GetDataTable(string sql ,params SqlParameter [] param)
{
DataTable dt = new DataTable();
using (SqlConnection conn=GetConn())
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddRange(param);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
adapter.Fill(dt);

}
return dt;
}

}
}