using System;
using System.Data;
using System.Data.SqlClient;
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;
/// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
{
public SqlHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 获取数据库连接字符串
/// </summary>
/// <returns></returns>
private static string sqlConnectionString
{
get
{
//直接在这里获取数据库联接
//return "Data Source = (local); Initial Catalog=window;Integrated Security=SSPI;";
//VS.NET2003下的使用方式
//return System.Configuration.ConfigurationSettings.AppSettings["ConnectDataBase"];
//VS.NET2005下的使用方式
return System.Configuration.ConfigurationManager.AppSettings["ConnectDataBase"];
}
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns>Conn</returns>
private static SqlConnection sqlConn
{
get
{
//VS.NET2003下的使用方式
//return System.Configuration.ConfigurationSettings.AppSettings["ConnectDataBase"];
//VS.NET2005下的使用方式
//ConnStrings = System.Configuration.ConfigurationManager.AppSettings["ConnectDataBase"];
string ConnStrings = "Data Source = (local); Initial Catalog=window;Integrated Security=SSPI;";
SqlConnection Conn = new SqlConnection();
Conn.ConnectionString = ConnStrings;
return Conn;
}
}
/// <summary>
/// 执行sql语句，返回DataSet
/// </summary>
/// <param name="sqlString">sql语句参数</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string sqlString)
{
DataSet dsSet = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(sqlString, sqlConnectionString);
try
{
adp.Fill(dsSet);
}
catch (Exception e)
{
throw (e);
}
finally
{
adp.Dispose();
}
return dsSet;
}
/// <summary>
/// 执行sql语句，返回DataTable
/// </summary>
/// <param name="sqlString">sql语句参数</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(string sqlString)
{
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(sqlString, sqlConnectionString);
try
{
adp.Fill(dt);
}
catch (Exception e)
{
throw (e);
}
finally
{
adp.Dispose();
}
return dt;
}
/// <summary>
/// 执行存储过程返回DataSet
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="commandParameters">存储过程参数</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string spName, SqlParameter[] commandParameters)
{
SqlConnection conn = sqlConn;
conn.Open();
SqlCommand sqlcommand = new SqlCommand();
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet dataSet = new DataSet();
sqlcommand.Connection = conn;
sqlcommand.CommandText = spName;
sqlcommand.CommandType = CommandType.StoredProcedure;
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
sqlcommand.Parameters.Add(p);
}
}
adapter.SelectCommand = sqlcommand;
try
{
adapter.Fill(dataSet);
}
catch (Exception e)
{
throw (e);
}
finally
{
sqlcommand.Parameters.Clear();
sqlcommand.Dispose();
adapter.Dispose();
conn.Close();
}
return dataSet;
}
/// <summary>
/// 执行存储过程返回DataTable
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="commandParameters">存储过程参数</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(string spName, SqlParameter[] commandParameters)
{
SqlConnection conn = sqlConn;
conn.Open();
SqlCommand sqlcommand = new SqlCommand();
SqlDataAdapter adapter = new SqlDataAdapter();
DataTable dataTable = new DataTable();
sqlcommand.Connection = conn;
sqlcommand.CommandText = spName;
sqlcommand.CommandType = CommandType.StoredProcedure;
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
sqlcommand.Parameters.Add(p);
}
}
adapter.SelectCommand = sqlcommand;
try
{
adapter.Fill(dataTable);
}
catch (Exception e)
{
throw (e);
}
finally
{
sqlcommand.Parameters.Clear();
sqlcommand.Dispose();
adapter.Dispose();
conn.Close();
}
return dataTable;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="commandParameters">存储过程参数</param>
/// <returns>true or false</returns>
public static bool ExecuteProcedure(string spName, SqlParameter[] commandParameters)
{
bool result = false;
SqlConnection conn = sqlConn;
conn.Open();
SqlCommand sqlcommand = new SqlCommand();
sqlcommand.Connection = conn;
sqlcommand.CommandText = spName;
sqlcommand.CommandType = CommandType.StoredProcedure;
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
sqlcommand.Parameters.Add(p);
}
}
try
{
sqlcommand.ExecuteNonQuery();
result = true;
}
catch (Exception e)
{
throw (e);
}
finally
{
sqlcommand.Parameters.Clear();
sqlcommand.Dispose();
conn.Close();
}
return result;
}
/// <summary>
/// 执行存储过程返回一个object对象
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="commandParameters">存储过程参数</param>
/// <returns>object</returns>
public static object ExecuteProcedures(string spName, SqlParameter[] commandParameters)
{
object ret = new object();
ret = DBNull.Value;
SqlConnection conn = sqlConn;
conn.Open();
SqlCommand sqlcommand = new SqlCommand();
sqlcommand.Connection = conn;//2009/2/27丢掉的Connection连接
sqlcommand.CommandText = spName;
sqlcommand.CommandType = CommandType.StoredProcedure;
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
sqlcommand.Parameters.Add(p);
}
}
try
{
ret = sqlcommand.ExecuteScalar();
}
catch (Exception e)
{
throw (e);
}
finally
{
sqlcommand.Parameters.Clear();
sqlcommand.Dispose();
conn.Close();
}
return ret;
}
/// <summary>
/// 执行sql语句，返回一个object对象
/// </summary>
/// <param name="sqlString">自定义sql语句</param>
/// <returns>object</returns>
public static object ExecuteScalar(string sqlString)
{
object ret = new object();
ret = DBNull.Value;
SqlConnection conn = sqlConn;
SqlCommand sqlcommand = new SqlCommand(sqlString, conn);
try
{
ret = sqlcommand.ExecuteScalar();
}
catch (Exception e)
{
throw (e);
}
finally
{
sqlcommand.Dispose();
conn.Close();
}
return ret;
}
/// <summary>
/// 执行自定义sql语句
/// </summary>
/// <param name="sqlString">自定sql语句</param>
/// <returns>true or false</returns>
public static bool ExecuteNoQueryString(string sqlString)
{
bool result = false;
SqlCommand sqlcommand = new SqlCommand();
SqlConnection conn = new SqlConnection();
conn.Open();
sqlcommand.Connection = conn;
try
{
sqlcommand.ExecuteScalar();
result = true;
}
catch
{
result = false;
}
finally
{
sqlcommand.Dispose();
conn.Close();
}
return result;
}
}
这个文件在网上应该是很流行的。不做解释，记录在案以后使用。
下边是调用 存储过程 的方法 带参数的：
public bool InsertUsers(Users user)
{
SqlParameter[] parms = {
new SqlParameter("@UserName",user.userName),
new SqlParameter("@UserPass",user.userPass),
new SqlParameter("@UserRole",user.userRole),
new SqlParameter("@UserEmail",user.userEmail),
new SqlParameter("@Remark",user.remark)
};
return SQLHelper.ExecuteProcedure("tfwk_InsertUser", parms);
}