62,041
社区成员
发帖
与我相关
我的任务
分享
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;
using System.Collections;
namespace Stm_DbBuild
{
public class DbBuilder
{
public DbBuilder()
{
}
//分别定义一个SqlConnection及SqlCommand对象,用于对数据库的操作
protected SqlConnection conn=new SqlConnection();
protected SqlCommand comm=new SqlCommand();
/// <summary>
/// 打开数据库连接1
/// </summary>
protected void OpenConnection()
{
CheckConnection();
}
/// <summary>
/// 检查数据库连接是否打开
/// </summary>
/// <returns>bool</returns>
public bool CheckConnection()
{
//如果数据连接是关闭的,则获取其连接字符串并打开
if (conn.State == ConnectionState.Closed)
{
try
{
//获取数据库连接字符串
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
comm.Connection = conn;
conn.Open();//打开连接
}
catch (Exception)
{
return false;
}
}
return true;
}
/// <summary>
/// 关闭当前数据库连接
/// </summary>
protected void CloseConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
comm.Dispose();
}
/// <summary>
/// 执行SQL查询语句
/// </summary>
/// <param name="sqlText">传入的SQL语句</param>
public void ExcuteSql(string sqlText)
{
try
{
OpenConnection();//打开数据库连接
comm.CommandType = CommandType.Text;
comm.CommandText = sqlText;
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);//抛出连接的异常信息
}
finally
{
CloseConnection();//关闭连接
}
}
public int ExcuteCmdSql(string sqlText)
{
try
{
OpenConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlText;
return comm.ExecuteNonQuery();//执行查询,并返回一整数,如果大于0,表示查询成功,否则表示失败
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
CloseConnection();
}
return 1;
}
/// <summary>
/// 返回指定SQL语句的DataTable
/// </summary>
/// <param name="sqlText">传入的SQL语句</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sqlText)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable datatable = new DataTable();
try
{
OpenConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlText;
da.SelectCommand = comm;
da.Fill(datatable);
}
catch (Exception)
{
}
finally
{
CloseConnection();
}
return datatable;
}
/// <summary>
/// 返回指定SQL语句的DataSet
/// </summary>
/// <param name="sqlText">传入的SQL语句</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(string sqlText)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet dataset = new DataSet();
try
{
OpenConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlText;
da.SelectCommand = comm;
da.Fill(dataset);
}
catch (Exception)
{
}
finally
{
da.Dispose();
CloseConnection();//关闭连接
}
return dataset;
}
/// <summary>
/// 建立数据库连接方法2
/// 返回一个连接的对象
/// </summary>
/// <returns>SqlConnection</returns>
public SqlConnection GetSqlConn()
{
//获取连接到数据库的字符串
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
SqlConnection conn1 = new SqlConnection(connStr);
conn1.Open();//打开数据库连接
return conn1;//返回连接对象
}
/// <summary>
/// 实例化SqlCommand对象
/// 返回一个SqlCommand对象
/// </summary>
/// <param name="sqlText">sql语句</param>
/// <returns>SqlCommand</returns>
public SqlCommand GetSqlComm(string sqlText)
{
return new SqlCommand(sqlText, GetSqlConn());//返回对象成员
}
/// <summary>
/// 执行sql语句进行数据查询
/// </summary>
/// <param name="sqlText">sql语句</param>
/// <returns></returns>
public int cmd(string sqlText)
{
comm = GetSqlComm(sqlText);//实例化一个执行对象
return comm.ExecuteNonQuery();//执行查询,并返回一整数,如果大于0,表示查询成功,否则表示失败
}
/// <summary>
/// 关闭连接2
/// </summary>
/// <param name="comm"></param>
public void CloseSqlCommand(SqlCommand comm)
{
comm.Connection.Close();
comm.Connection.Dispose();
comm.Dispose();
}
}
}