using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
namespace BFB.DAL
{
/// <summary>
///
/// </summary>
public class SqlHelper
{
/// <summary>
///
/// </summary>
private string strConnectionString = "";
/// <summary>
/// 连库类
/// </summary>
public static SqlConnection cnn;
/// <summary>
/// 执行类库
/// </summary>
public SqlCommand com;
/// <summary>
///
/// </summary>
public SqlHelper()
{
string server = File.ReadAllText("ServerIP.txt");
strConnectionString = string.Format(@"Data Source={0};uid=aaaa;pwd=123456;database=HundredMIS", server);
}
/// <summary>
///打开数据库连接
/// </summary>
public void Open()
{
if (cnn == null)
{
cnn = new SqlConnection(strConnectionString);
}
if (cnn.State == ConnectionState.Closed)
{
try
{
cnn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void close()
{
if (cnn != null)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
}
}
/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确认连接是否已经关闭
if (cnn != null)
{
cnn.Dispose();
cnn = null;
}
}
/// <summary>
/// 返回影响行数
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int ExecuteNonQuery(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
int retval;
try
{
cnn.Open();
retval = cmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
finally
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
}
return retval;
}
/// <summary>
/// 返回影响行数
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteNonQuery(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
cnn.Open();
int retval = cmd.ExecuteNonQuery();
cnn.Close();
return retval;
}
/// <summary>
/// 查询第一行第一列
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
cnn.Open();
object retval = cmd.ExecuteScalar();
cnn.Close();
return retval;
}
public object ExecuteScalar(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
cnn.Open();
object retval = cmd.ExecuteScalar();
cnn.Close();
return retval;
}
/// <summary>
/// 使用Select查询,返回SqlDataReader类型
/// </summary>
/// <param name="query">查询命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
cnn.Open();
}
SqlDataReader dr;
try
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch (Exception ee)
{
cnn.Close();
throw ee;
}
}
/// <summary>
/// 使用存储过程查询,返回SqlDataReader类型
/// </summary>
/// <param name="query">存储过程名</param>
/// <param name="parameters"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
cnn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 使用Select查询,返回DataSet类型
/// </summary>
/// <param name="query">查询命令</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
/// <summary>
/// 使用存储过程查询,返回DataSet类型
/// </summary>
/// <param name="query">存储过程名</param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
转载于:https://www.cnblogs.com/shi-bing/archive/2013/03/07/3020794.html