using System;
using System.Data;
using System.Data.SqlClient;
namespace Sql
{
/// <summary>
/// Sql 的摘要说明
/// </summary>
public class DataClass : IDisposable
{
private SqlConnection con = null;
public void Open()
{
if (con == null)
{
con = this.getCon();
}
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
}
public SqlConnection getCon()
{
return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
}
public void Close()
{
if (con != null)
{
con.Close();
}
}
public void Dispose()
{
con.Dispose();
con = null;
GC.SuppressFinalize(true);
}
public void Sql_NonQuery(string strSql)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand(strSql, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
this.Close();
}
catch (Exception Ex)
{
throw Ex;
}
}
public void Sql_NonQuery(string strSql, SqlConnection con1)
{
try
{
con1.Open();
SqlCommand cmd = new SqlCommand(strSql, con1);
cmd.ExecuteNonQuery();
cmd.Dispose();
con1.Close();
}
catch (Exception Ex)
{
throw Ex;
}
}
public void Sql_NonQuery(string strSql, SqlParameter[] paras)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand(strSql, con);
cmd.Parameters.Clear();
foreach (SqlParameter Item in paras)
{
cmd.Parameters.Add(Item);
}
cmd.ExecuteNonQuery();
cmd.Dispose();
this.Close();
}
catch (Exception Ex)
{
throw Ex;
}
}
public void Sql_NonQuery_Pro(string strSql, SqlParameter[] paras)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strSql;
cmd.Connection = con;
cmd.Parameters.Clear();
foreach (SqlParameter Item in paras)
{
cmd.Parameters.Add(Item);
}
cmd.ExecuteNonQuery();
cmd.Dispose();
this.Close();
}
catch (Exception Ex)
{
throw Ex;
}
}
public void Sql_NonQuery_Pro(string strSql, string strRvl, SqlParameter[] paras)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strSql;
cmd.Connection = con;
cmd.Parameters.Clear();
foreach (SqlParameter Item in paras)
{
cmd.Parameters.Add(Item);
}
cmd.ExecuteNonQuery();
foreach (SqlParameter Item in paras)
{
if (Item.Direction == System.Data.ParameterDirection.Output)
{
strRvl += Item.Value.ToString() + ",";
}
}
cmd.Dispose();
this.Close();
}
catch (Exception Ex)
{
throw Ex;
}
}
public SqlDataReader Sql_Reader(string strSql)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand(strSql, con);
SqlDataReader dr = cmd.ExecuteReader();
cmd.Dispose();
return dr;
}
catch
{
return null;
}
}
public SqlDataReader Sql_Reader(string strSql, SqlConnection con1)
{
try
{
con1.Open();
SqlCommand cmd = new SqlCommand(strSql, con1);
SqlDataReader dr = cmd.ExecuteReader();
cmd.Dispose();
return dr;
}
catch
{
return null;
}
}
public SqlDataReader Sql_Reader(string strSql, SqlParameter[] paras)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand(strSql, con);
cmd.Parameters.Clear();
foreach (SqlParameter Item in paras)
{
cmd.Parameters.Add(Item);
}
SqlDataReader dr = cmd.ExecuteReader();
cmd.Dispose();
return dr;
}
catch
{
return null;
}
}
public SqlDataReader Sql_Reader_Pro(string strSql, SqlParameter[] paras)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strSql;
cmd.Connection = con;
cmd.Parameters.Clear();
foreach (SqlParameter Item in paras)
{
cmd.Parameters.Add(Item);
}
SqlDataReader dr = cmd.ExecuteReader();
cmd.Dispose();
return dr;
}
catch
{
return null;
}
}
public SqlDataReader Sql_Reader_Pro(string strSql, SqlParameter[] paras, SqlConnection con1)
{
try
{
con1.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strSql;
cmd.Connection = con1;
cmd.Parameters.Clear();
foreach (SqlParameter Item in paras)
{
cmd.Parameters.Add(Item);
}
SqlDataReader dr = cmd.ExecuteReader();
cmd.Dispose();
return dr;
}
catch
{
return null;
}
}
public object Sql_Scalar(string strSql)
{
SqlCommand cmd = null;
try
{
this.Open();
cmd = new SqlCommand(strSql, con);
return (object)cmd.ExecuteScalar().ToString();
}
catch
{
return null;
}
finally
{
cmd.Dispose();
this.Close();
}
}
public object Sql_Scalar(string strSql, SqlConnection con1)
{
SqlCommand cmd = null;
try
{
con1.Open();
cmd = new SqlCommand(strSql, con1);
return (object)cmd.ExecuteScalar().ToString();
}
catch
{
return null;
}
finally
{
cmd.Dispose();
con1.Close();
}
}
public DataSet Sql_DataSet(string strSql, string strTable)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand(strSql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, strTable);
return ds;
}
catch
{
return null;
}
finally
{
this.Close();
}
}
public DataSet Sql_DataSet(string strSql, string strTable, SqlParameter[] paras)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = strSql;
cmd.Parameters.Clear();
foreach (SqlParameter Item in paras)
{
cmd.Parameters.Add(Item);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, strTable);
return ds;
}
catch
{
return null;
}
finally
{
this.Close();
}
}
public DataSet Sql_DataSet_Pro(string strSql, string strTable, SqlParameter[] paras)
{
try
{
this.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.CommandText = strSql;
cmd.Parameters.Clear();
foreach (SqlParameter Item in paras)
{
cmd.Parameters.Add(Item);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, strTable);
return ds;
}
catch
{
return null;
}
finally
{
this.Close();
}
}
}
}