来源忘了,每次接口加挺烦
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PushRisReportServicePro;
namespace DAL
{
public class DBaseHelper
{
private string connectiostring = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnString"].ToString();//MyConnString
//private string connectiostring = System.Configuration.ConfigurationSettings.AppSettings["MyConnString"].ToString();//MyConnString
private SqlConnection connection = null;
public SqlConnection Connection
{
get
{
if (connection == null)
{
connection = new SqlConnection(connectiostring);
connection.Open();
}
else if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == ConnectionState.Broken)
{
connection.Close();
connection.Dispose();
connection.Open();
}
return connection;
}
}
public Boolean Exists(string strSql)
{
SqlDataAdapter sda = new SqlDataAdapter(strSql, Connection);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt != null)
{
return true;
}
else
{
return false;
}
}
public Boolean Exists(string strSql,SqlParameter[] paramter)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paramter);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count>0)
{
return true;
}
else
{
return false;
}
}
public SqlDataReader GetReader(string strSql)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
//connection.Dispose();
return reader;
}
public SqlDataReader GetReader(string strSql, CommandType type, params SqlParameter[] paramter)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
cmd.CommandType = type;
cmd.Parameters.AddRange(paramter);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
//connection.Dispose();
return reader;
}
public SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
connection.Close();
//connection.Dispose();
return reader;
}
public int ExecuteCommand(string strSql)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
int count = cmd.ExecuteNonQuery();
connection.Close();
//connection.Dispose();
return count;
}
//返回第一行第一列
public object ExecuteScalar(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
object result = cmd.ExecuteScalar();
connection.Close();
//connection.Dispose();
return result;
}
public int ExecuteNonQuery(string strSql, params SqlParameter[] paramter)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paramter);
int count = Convert.ToInt32(cmd.ExecuteNonQuery());
connection.Close();
//connection.Dispose();
return count;
}
public int ExecuteCommand(string strSql, CommandType type, params SqlParameter[] paramter)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
cmd.CommandType = type;
cmd.Parameters.AddRange(paramter);
int count = cmd.ExecuteNonQuery();
connection.Close();
//connection.Dispose();
return count;
}
public int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
connection.Close();
//connection.Dispose();
return cmd.ExecuteNonQuery();
}
public DataTable GetDataTable(string strSql)
{
try
{
SqlDataAdapter sda = new SqlDataAdapter(strSql, Connection);
DataTable dt = new DataTable();
sda.Fill(dt);
connection.Close();
//connection.Dispose();
return dt;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public DataTable GetDataTable(string sql, params SqlParameter[] values)
{
LogFiles log = new LogFiles();
try
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
connection.Close();
//connection.Dispose();
return ds.Tables[0];
}
catch (Exception ex)
{
log.WriteLine(ex.Message+"");
return null;
}
}
public DataTable getTable(string sql)
{
try
{
SqlDataAdapter sda = new SqlDataAdapter(sql, Connection);
DataSet ds = new DataSet();
sda.Fill(ds);
connection.Close();
return ds.Tables[0];
}
catch (Exception ex)
{
return null;
}
}
public DataTable GetDataTableNoPara(string sql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
connection.Close();
//connection.Dispose();
return ds.Tables[0];
}
public DataTable GetDataTablePro(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
connection.Close();
return ds.Tables[0];
}
public DataSet GetDataSet(string sql, params SqlParameter[] paramter)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(paramter);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
connection.Close();
//connection.Dispose();
return ds;
}
public DataSet GetDataSet(string strSql)
{
try
{
SqlDataAdapter sda = new SqlDataAdapter(strSql, Connection);
DataSet ds = new DataSet();
sda.Fill(ds);
connection.Close();
//connection.Dispose();
return ds;
}catch(Exception ex){
return null;
}
}
public DataTable GetDataSet(string strSql, CommandType type, params SqlParameter[] paramter)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
cmd.CommandType = type;
cmd.Parameters.AddRange(paramter);
DataSet ds = new DataSet();
sda.Fill(ds);
connection.Close();
//connection.Dispose();
return ds.Tables[0];
}
public int GetScalar(string strSql)
{
SqlCommand cmd = new SqlCommand(strSql, Connection);
int count = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
//connection.Dispose();
return count;
}
internal SqlDataReader GetReader()
{
throw new Exception("The method or operation is not implemented.");
}
}
}