using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MSBDAL
{
/*
* Descrption:数据库常用操作方法合集
* Author:nianyuwen
* Time:2012-03-15
* MSN:nianyuwen@live.com
* WebUrl:http://www.cnblogs.com/nianyuwen/
*/
public class SqlHelper
{
public SqlHelper()
{
//TODO:构造数据库常用操作方法集
}
private static SqlConnection connection;
private static string connectionString;
public static SqlConnection Connection
{
get
{
//string connectionString = ConfigurationManager.ConnectionStrings["stock"].ConnectionString;
//"Data Source=.;Initial Catalog=StockBank;Persist Security Info=True;User ID=sa;pwd=123";
connectionString = ConfigurationSettings.AppSettings["ConstrSQL"].ToString();
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
/// <summary>
/// 带参数的执行命令
/// </summary>
/// <param name="sql">SQL命令</param>
/// <param name="values">返回VALUE值</param>
/// <returns></returns>
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 返回影响记录数
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 获得数据集
/// </summary>
public static DataSet GetDataSet(string sql, string table)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, table);
return ds;
}
public static DataSet GetDataSet(string sql, string table, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, table);
return ds;
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MSBDAL
{
/*
* Descrption:数据库常用操作方法合集
* Author:nianyuwen
* Time:2012-03-15
* MSN:nianyuwen@live.com
* WebUrl:http://www.cnblogs.com/nianyuwen/
*/
public class SqlHelper
{
public SqlHelper()
{
//TODO:构造数据库常用操作方法集
}
private static SqlConnection connection;
private static string connectionString;
public static SqlConnection Connection
{
get
{
//string connectionString = ConfigurationManager.ConnectionStrings["stock"].ConnectionString;
//"Data Source=.;Initial Catalog=StockBank;Persist Security Info=True;User ID=sa;pwd=123";
connectionString = ConfigurationSettings.AppSettings["ConstrSQL"].ToString();
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
/// <summary>
/// 带参数的执行命令
/// </summary>
/// <param name="sql">SQL命令</param>
/// <param name="values">返回VALUE值</param>
/// <returns></returns>
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 返回影响记录数
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// 获得数据集
/// </summary>
public static DataSet GetDataSet(string sql, string table)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, table);
return ds;
}
public static DataSet GetDataSet(string sql, string table, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, table);
return ds;
}
}
}