using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
/// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public static class SqlHelper1
{
// readonly static string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();
readonly static string constr = "data source=10.1.1.247;database=haitian_MSCRM;uid=ht_crmselect;pwd=htpass@228";
/// <summary>
/// 提交返回数据集
/// </summary>
/// <param name="commandtype">选择执行方式的枚举</param>
/// <param name="commandtext">sql语句或者存储过程名称</param>
/// <param name="sps">参数的数组</param>
/// <returns></returns>
public static DataTable ExecuteDataSet(CommandType commandtype, string commandtext, params SqlParameter[] sps)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(commandtext, con))
{
cmd.CommandType = commandtype;
if (sps != null)
{
foreach (SqlParameter var in sps)
{
cmd.Parameters.Add(var);
}
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
}
/// <summary>
/// 用于执行insert、update、delete
/// </summary>
/// <param name="commandtype"></param>
/// <param name="commandtext"></param>
/// <param name="sps"></param>
/// <returns></returns>
public static int ExecuteNonQuery(CommandType commandtype, string commandtext, params SqlParameter[] sps)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(commandtext, con))
{
cmd.CommandType = commandtype;
if (sps != null)
{
foreach (SqlParameter var in sps)
{
cmd.Parameters.Add(var);
}
}
con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
return count;
}
}
}
/// <summary>
/// 返回聚合函数值
/// </summary>
/// <param name="commandtype"></param>
/// <param name="commandtext"></param>
/// <param name="sps"></param>
/// <returns></returns>
public static object ExecuteScalar(CommandType commandtype, string commandtext, params SqlParameter[] sps)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(commandtext, con))
{
cmd.CommandType = commandtype;
if (sps != null)
{
foreach (SqlParameter var in sps)
{
cmd.Parameters.Add(var);
}
}
con.Open();
object o = cmd.ExecuteScalar(); //sql的聚合函数 Count(*)
con.Close();
return o;
}
}
}
/// <summary>
/// 用于返回一行数据
/// </summary>
/// <param name="commandtype"></param>
/// <param name="commandtext"></param>
/// <param name="sps"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(CommandType commandtype, string commandtext, params SqlParameter[] sps)
{
SqlConnection con = new SqlConnection(constr);
using (SqlCommand cmd = new SqlCommand(commandtext, con))
{
cmd.CommandType = commandtype;
if (sps != null)
{
foreach (SqlParameter var in sps)
{
cmd.Parameters.Add(var);
}
}
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //关闭读取器,将自动关闭该读取器上的连接对象
return dr;
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
/// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public static class SqlHelper1
{
// readonly static string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();
readonly static string constr = "data source=10.1.1.247;database=haitian_MSCRM;uid=ht_crmselect;pwd=htpass@228";
/// <summary>
/// 提交返回数据集
/// </summary>
/// <param name="commandtype">选择执行方式的枚举</param>
/// <param name="commandtext">sql语句或者存储过程名称</param>
/// <param name="sps">参数的数组</param>
/// <returns></returns>
public static DataTable ExecuteDataSet(CommandType commandtype, string commandtext, params SqlParameter[] sps)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(commandtext, con))
{
cmd.CommandType = commandtype;
if (sps != null)
{
foreach (SqlParameter var in sps)
{
cmd.Parameters.Add(var);
}
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
}
/// <summary>
/// 用于执行insert、update、delete
/// </summary>
/// <param name="commandtype"></param>
/// <param name="commandtext"></param>
/// <param name="sps"></param>
/// <returns></returns>
public static int ExecuteNonQuery(CommandType commandtype, string commandtext, params SqlParameter[] sps)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(commandtext, con))
{
cmd.CommandType = commandtype;
if (sps != null)
{
foreach (SqlParameter var in sps)
{
cmd.Parameters.Add(var);
}
}
con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
return count;
}
}
}
/// <summary>
/// 返回聚合函数值
/// </summary>
/// <param name="commandtype"></param>
/// <param name="commandtext"></param>
/// <param name="sps"></param>
/// <returns></returns>
public static object ExecuteScalar(CommandType commandtype, string commandtext, params SqlParameter[] sps)
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(commandtext, con))
{
cmd.CommandType = commandtype;
if (sps != null)
{
foreach (SqlParameter var in sps)
{
cmd.Parameters.Add(var);
}
}
con.Open();
object o = cmd.ExecuteScalar(); //sql的聚合函数 Count(*)
con.Close();
return o;
}
}
}
/// <summary>
/// 用于返回一行数据
/// </summary>
/// <param name="commandtype"></param>
/// <param name="commandtext"></param>
/// <param name="sps"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(CommandType commandtype, string commandtext, params SqlParameter[] sps)
{
SqlConnection con = new SqlConnection(constr);
using (SqlCommand cmd = new SqlCommand(commandtext, con))
{
cmd.CommandType = commandtype;
if (sps != null)
{
foreach (SqlParameter var in sps)
{
cmd.Parameters.Add(var);
}
}
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //关闭读取器,将自动关闭该读取器上的连接对象
return dr;
}
}
}