为罗城等地区用户提供了全套网页设计制作服务，及罗城网站建设行业解决方案。主营业务为做网站、成都做网站、罗城网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！>using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace HelloWinForm.DBUtility
{
class SQLHelper
{
#region 通用方法
// 数据连接池 private SqlConnection con;
/// /// 返回数据库连接字符串
/// /// public static String GetSqlConnection()
{
String conn= ConfigurationManager.AppSettings["connectionString"].ToString();
return conn;
}
#endregion #region 执行sql字符串
/// /// 执行不带参数的SQL语句
/// /// /// public static int ExecuteSql(String Sqlstr)
{
String ConnStr= GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd= new SqlCommand();
cmd.Connection= conn;
cmd.CommandText= Sqlstr;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return 1;
}
}
/// /// 执行带参数的SQL语句
/// /// SQL语句 /// 参数对象数组 /// public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
{
String ConnStr= GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd= new SqlCommand();
cmd.Connection= conn;
cmd.CommandText= Sqlstr;
cmd.Parameters.AddRange(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return 1;
}
}
/// /// 返回DataReader
/// /// /// public static SqlDataReader ExecuteReader(String Sqlstr)
{
String ConnStr= GetSqlConnection();
SqlConnection conn= new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的 try
{
SqlCommand cmd= new SqlCommand();
cmd.Connection= conn;
cmd.CommandText= Sqlstr;
conn.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection }
catch //(Exception ex) {
return null;
}
}
/// /// 执行SQL语句并返回数据表
/// /// SQL语句 /// public static DataTable ExecuteDt(String Sqlstr)
{
String ConnStr= GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlDataAdapter da= new SqlDataAdapter(Sqlstr, conn);
DataTable dt= new DataTable();
conn.Open();
da.Fill(dt);
conn.Close();
return dt;
}
}
/// /// 执行SQL语句并返回DataSet
/// /// SQL语句 /// public static DataSet ExecuteDs(String Sqlstr)
{
String ConnStr= GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlDataAdapter da= new SqlDataAdapter(Sqlstr, conn);
DataSet ds= new DataSet();
conn.Open();
da.Fill(ds);
conn.Close();
return ds;
}
}
#endregion #region 操作存储过程
/// /// 运行存储过程(已重载)
/// /// 存储过程的名字 /// 存储过程的返回值 public int RunProc(string procName)
{
SqlCommand cmd= CreateCommand(procName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
/// /// 运行存储过程(已重载)
/// /// 存储过程的名字 /// 存储过程的输入参数列表 /// 存储过程的返回值 public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd= CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters[0].Value;
}
/// /// 运行存储过程(已重载)
/// /// 存储过程的名字 /// 结果集 public void RunProc(string procName, out SqlDataReader dataReader)
{
SqlCommand cmd= CreateCommand(procName, null);
dataReader= cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
/// /// 运行存储过程(已重载)
/// /// 存储过程的名字 /// 存储过程的输入参数列表 /// 结果集 public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
{
SqlCommand cmd= CreateCommand(procName, prams);
dataReader= cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
/// /// 创建Command对象用于访问存储过程
/// /// 存储过程的名字 /// 存储过程的输入参数列表 /// Command对象 private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// 确定连接是打开的 Open();
//command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) ); SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType= CommandType.StoredProcedure;
// 添加存储过程的输入参数列表 if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 返回Command对象 return cmd;
}
/// /// 创建输入参数
/// /// 参数名 /// 参数类型 /// 参数大小 /// 参数值 /// 新参数对象 public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// /// 创建输出参数
/// /// 参数名 /// 参数类型 /// 参数大小 /// 新参数对象 public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
/// /// 创建存储过程参数
/// /// 参数名 /// 参数类型 /// 参数大小 /// 参数的方向(输入/输出) /// 参数值 /// 新参数对象 public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size > 0)
{
param= new SqlParameter(ParamName, DbType, Size);
}
else
{
param= new SqlParameter(ParamName, DbType);
}
param.Direction= Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
param.Value= Value;
}
return param;
}
#endregion #region 数据库连接和关闭
/// /// 打开连接池
/// private void Open()
{
// 打开连接池 if (con == null)
{
//这里不仅需要using System.Configuration;还要在引用目录里添加 con = new SqlConnection(GetSqlConnection());
con.Open();
}
}
/// /// 关闭连接池
/// public void Close()
{
if (con != null)
con.Close();
}
/// /// 释放连接池
/// public void Dispose()
{
// 确定连接已关闭 if (con != null)
{
con.Dispose();
con= null;
}
}
#endregion
}
} using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace MyBookShop.DAL
{
public static class DBHelper
{
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = "Data Source=localhost;Initial Catalog=myRole; uid=sa;Password=";
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;
}
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd= new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
public static int GetScalar(string safeSql)
{
SqlCommand cmd= new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
public static int GetScalar(params SqlParameter[] values)
{
SqlCommand cmd= new SqlCommand();
cmd.Connection= Connection;
cmd.CommandText= "Pro_InsertOrder";
cmd.CommandType= CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
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];
}
}
}
分享标题：SqlHelper-创新互联
文章出自：http://cdkjz.cn/article/pgcci.html
