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

/// <summary>
///SqlCon 的摘要说明
/// </summary>
public class SqlHelper
{
private static SqlConnection con;
private static SqlConnection Con
{

get
{
string conString = ConfigurationManager.ConnectionStrings["XjhDemo"].ConnectionString;
if (con == null)
{
con = new SqlConnection(conString);

}

if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
{
con.Open();
}
return con;
}
}
/// <summary>
/// 调用命令对象的ExecuteNonQuery的重载之执行存储过程
/// </summary>
/// <param name="safeSql">存储过程名</param>
/// <param name="values">存储过程需要的参数</param>
/// <returns>受影响的行数</returns>
///
public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql, Con);
cmd.CommandType = CommandType.StoredProcedure;
if (values != null)
{
cmd.Parameters.AddRange(values);
}
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return result;
}
public static int ExecuteCommand(string safeSql,out SqlCommand command, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql, Con);
cmd.CommandType = CommandType.StoredProcedure;
if (values != null)
{
cmd.Parameters.AddRange(values);
}
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
command = cmd;
return result;
}
public static SqlDataReader GetReader(string safeSql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql,Con);
cmd.CommandType = CommandType.StoredProcedure;
if (values != null)
{
cmd.Parameters.AddRange(values);
}
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
public static DataTable GetDatatable(string safeSql, params SqlParameter[] values)
{
DataTable table = new DataTable();
SqlDataReader reader = GetReader(safeSql, values);
table.Load(reader);
reader.Close();
return table;
}
public static DataTable GetDataTable(string safeSql, out SqlCommand command, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql, Con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = safeSql;
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
reader.Close();
cmd.Connection.Close();
command = cmd;
return table;
}

// <summary>
/// 调用命令对象的ExecuteScalar的重载之执行存储过程(标量查询)
/// </summary>
/// <param name="safeSql">存储过程名</param>
/// <returns>查询结果</returns>
public static object GetScalar(string safeSql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql, Con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
object result = cmd.ExecuteScalar();
cmd.Connection.Close();
return result;
}
public static string GetXReader(string safeSql, out SqlCommand command, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(safeSql, Con);
cmd.CommandType = CommandType.StoredProcedure;
if (values != null)
{
cmd.Parameters.AddRange(values);
}
XmlReader xreader = cmd.ExecuteXmlReader();
xreader.Read();
string xml = xreader.ReadOuterXml();
xreader.Close();
con.Close();
command = cmd;
return xml;
}

}