using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace GameCard.DAL
{
public class DBHelper
{ private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["GameCard"].ConnectionString;
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);
int result = cmd.ExecuteNonQuery();
return result;
} public static string ReturnStringScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
string result = cmd.ExecuteScalar().ToString();
return "0";
}
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];
} }
}
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace GameCard.DAL
{
public class DBHelper
{ private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["GameCard"].ConnectionString;
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);
int result = cmd.ExecuteNonQuery();
return result;
} public static string ReturnStringScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
string result = cmd.ExecuteScalar().ToString();
return "0";
}
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];
} }
}
解决方案 »
- 怎么实现checkbox全选。
- 我的网站怎么经常出现这样的错误
- sqlhelps.cs 事务怎么用
- 虚拟主机支持asp.net, 但不支持GDI+?
- ASP.NET 导出Excel数据到SQL Server问题 (无法释放掉Excel进程,请高手指点)
- 在DataGrid中超级链接列的字体颜色能更改吗?改了怎么不起作用?
- 在一个页a.aspx中有按钮btn1可不可以执行页b.aspx中的一段代码,可不可以用__doPostBack()来做呢,我用这种写法不能执行啊(好象是自已写得
- 谁有这个网站的源代码?这个网站太酷了。
- 类似于windows用户权限的授与及收回
- to hongshun(好好) ,进来看看,Winform嵌入WebForm的问题
- 过滤非法字符~
- System.NullReferenceException: 未将对象引用设置到对象的实例
sqlhelper类详解
http://www.google.com.hk/search?hl=zh-CN&lr=&newwindow=1&safe=strict&q=sqlhelper%E7%B1%BB%E8%AF%A6%E8%A7%A3&btnG=Google+%E6%90%9C%E7%B4%A2&aq=f&aqi=&aql=&oq=&gs_rfai=
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient; //导入访问SqlServer数据库的名称空间 /// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
{
private static readonly string strConn = ConfigurationManager.ConnectionStrings["TangCompanyConn"].ConnectionString; //从配置文件获得与数据库的连接字符串
private static readonly string strName = ConfigurationManager.AppSettings["userName"].ToString(); //从配置文件中获得用户名
private static readonly string strPass = ConfigurationManager.AppSettings["userPass"].ToString(); //从配置文件中获得密码
public SqlHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static SqlDataReader ExcuteRead(string nText, CommandType nType, SqlParameter[] paras) { SqlConnection conn = new SqlConnection(strConn); //获得一个与数据库之间建立连接的连接对象
SqlCommand cmd = new SqlCommand(); //创建一个执行SQL语句的命令对象
try
{
PrepareCommand(conn, cmd, null, nType, nText, paras); //调用后面的方法,后面具体说
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //通过SQLCommand对象创建一个SqlDataReader对象
cmd.Parameters.Clear(); //清空所有参数
return dr; //返回SqlDataReader对象
}
catch(SqlException ex){ throw new Exception(ex.Message, ex);
}
}
public static int ExcuteNonQurey(string nText, CommandType nType, SqlParameter[] paras) { SqlCommand cmd = new SqlCommand(); //创建一个执行SQL语句的命令对象
//此处采用了using写法,自动释放连接对象
using (SqlConnection conn = new SqlConnection(strConn)) //括号内创建一个与数据库之间的连接对象
{ PrepareCommand(conn, cmd, null, nType, nText, paras); //调用后面的方法,后面详细说明
int rows = cmd.ExecuteNonQuery(); //执行T-SQL,并将所获得的返回行数赋给rows
cmd.Parameters.Clear(); //清空所有参数
return rows; //返回执行T-SQL所影响的行数
}
}
public static object ExcuteSclare(string nText, CommandType nType, SqlParameter[] paras) { SqlCommand cmd = new SqlCommand(); //创建一个执行SQL语句的命令对象
//此处采用了using写法,自动释放连接对象
using (SqlConnection conn = new SqlConnection(strConn))//括号内创建一个与数据库之间的连接对象
{ PrepareCommand(conn, cmd, null, nType, nText, paras); //调用后面的方法,后面详细说明
object obj = cmd.ExecuteScalar(); //返回查询中的第一行第一列,忽略其它。
cmd.Parameters.Clear(); //清空所有参数
return obj; //返回所查询到的对象
}
}
public static DataSet ExcuteReadApdater(string nText, CommandType nType, SqlParameter[] paras) { SqlConnection con = new SqlConnection(strConn); //创建一个与数据库建立连接的对象
SqlCommand cmd = new SqlCommand(); //创建一个执行SQL语句的命令对象
DataSet ds = new DataSet(); //创建一个数据集对象
try
{
PrepareCommand(con, cmd, null, nType, nText, paras); //调用后面的方法,后面详细说明
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); //创建一个数据适配器对象
sqlAdapter.Fill(ds); //填充数据集
cmd.Parameters.Clear(); //清空所有参数
return ds; //返回数据集对象 }
catch (SqlException ex) { throw new Exception(ex.Message, ex);
}
}
public static void PrepareCommand(SqlConnection con, SqlCommand cmd, SqlTransaction trans, CommandType nType, string nText, SqlParameter[] paras) { if (con.State != ConnectionState.Open) //判断与数据库之间的连接状态
con.Open(); //打开与数据库之间的连接
cmd.Connection = con; //为sqlcommand对象指定连接对象
cmd.CommandText = nText; //指定T-sql语句
if (trans != null) { //判断事务对象是否为null cmd.Transaction = trans; //为sqlcommand对象指定事务对象
}
cmd.CommandType = nType; //执行sqlcommand执行语句的类型
if (paras != null) { //判断是否有参数 foreach (SqlParameter para in paras)//循环添加参数
{ cmd.Parameters.Add(para);
}
}
}