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>
/// SqlDB 的摘要说明
/// </summary>
public class SqlDB
{
//连接对象
SqlConnection conn;
//构造方法，获取web.config中的连接字符串
public SqlDB()
: this(ConfigurationManager.ConnectionStrings["sqldb"].ConnectionString)
{
}
//重写构造方法，实例
public SqlDB(string connectionString)
{
conn = new SqlConnection(connectionString);
}
/// <summary>
/// 用于执行sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>DataTable</returns>
public DataTable Search(string sql)
{
if (conn.State != ConnectionState.Open)
conn.Open();
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
try
{
sda.Fill(dt);
}
catch (SqlException se)
{
System.Diagnostics.Debug.WriteLine(se.Message);
}
finally
{
conn.Dispose();
sda.Dispose();
conn.Close();
}
return dt;
}
/// <summary>
/// 用于执行sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>bool</returns>
public bool Execute(string sql)
{
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
if (cmd.ExecuteNonQuery() > 0)
return true;
}
catch (SqlException se)
{
System.Diagnostics.Debug.WriteLine(se.Message);
}
finally
{
conn.Dispose();
cmd.Dispose();
conn.Close();
}
return false;
}
/// <summary>
/// 无参存储过程执行方法
/// </summary>
/// <param name="ProcName">存储过程名</param>
/// <returns>SqlDataReader数据</returns>
public DataTable SearchProc(string procName)
{
if (conn.State != ConnectionState.Open)
conn.Open();
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(procName, conn);
try
{
sda.Fill(dt);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
finally
{
conn.Dispose();
sda.Dispose();
conn.Close();
}
return dt;
}
/// <summary>
/// 重写带参存储过程
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="procParms">存储过程参数</param>
/// <returns>SqlDataReader数据</returns>
public DataTable SearchProc(string procName, SqlParameter[] procParams)
{
if (conn.State != ConnectionState.Open)
conn.Open();
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = new SqlCommand(procName, conn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
if (procParams != null)
{
foreach (SqlParameter procParam in procParams)
sda.SelectCommand.Parameters.Add(procParam);
}
try
{
sda.Fill(dt);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
finally
{
conn.Dispose();
sda.Dispose();
conn.Close();
}
return dt;
}
/// <summary>
/// 无返回值存储过程执行方法
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="procParms">存储过程参数数组</param>
/// <returns>bool</returns>
public bool ExecuteProc(string procName, SqlParameter[] procParams)
{
if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (procParams != null)
{
foreach (SqlParameter procParm in procParams)
cmd.Parameters.Add(procParm);
}
//try
//{
int i;
i = cmd.ExecuteNonQuery();
if (i > 0)
return true;
else
return false;
//}
//catch (Exception ex)
//{
//System.Diagnostics.Debug.WriteLine(ex.Message);
//}
//finally
//{
// conn.Dispose();
// cmd.Dispose();
// conn.Close();
//}
return false;
}
}