时间:2021-05-28
复制代码 代码如下:
public class SqlOperation
{
#region 属性
/// <summary>
/// 保存在Web.config中的连接字符串
/// </summary>
protected static string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["hao"].ConnectionString;
/// <summary>
/// SqlConnection对象
/// </summary>
protected static SqlConnection conn = new SqlConnection();
/// <summary>
/// SqlCommand对象
/// </summary>
protected static SqlCommand comm = new SqlCommand();
#endregion
#region 内部函数
/// <summary>
/// 打开数据库连接
/// </summary>
private static void ConnectionOpen()
{
if (conn.State != ConnectionState.Open)
{
conn.Close();
conn.ConnectionString = connectionstring;
comm.Connection = conn;
try
{
conn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private static void ConnectionClose()
{
conn.Close();
conn.Dispose();
comm.Dispose();
}
#endregion
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="SqlString">要执行的SQL语句</param>
public static void ExecuteSQL(string SqlString)
{
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="ProcedureName">存储过程名称</param>
/// <param name="coll">存储过程需要的参数集合</param>
public static void ExecuteProcedure(string ProcedureName, params SqlParameter[] coll)
{
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
comm.Parameters.Clear();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// 执行Sql查询并返回第一行的第一条记录,返回object,使用时需要拆箱 -> unbox
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>返回object类型的第一行第一条记录</returns>
public static object ExecuteScalar(string SqlString)
{
object obj = new object();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
obj = comm.ExecuteScalar();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return obj;
}
/// <summary>
/// 执行SQL语句,同时进行事务处理
/// </summary>
/// <param name="sqlstr">要执行的SQL语句</param>
public static void ExecuteTransactionSQL(string SqlString)
{
SqlTransaction trans;
trans = conn.BeginTransaction();
comm.Transaction = trans;
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
comm.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// 执行指定SQL查询,返回DataSet
/// </summary>
/// <param name="sqlstr">要执行的SQL语句</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetBySQL(string SqlString)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// 通过存储过程返回DataSet
/// </summary>
/// <param name="ProcedureName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetByProcedure(string ProcedureName, params SqlParameter[] coll)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Clear();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.CommandText = ProcedureName;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// 通过存储过程返回DataSet
/// </summary>
/// <param name="ProcedureName">存储过程名称</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetByProcedure(string ProcedureName)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
comm.Parameters.Clear();
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// 返回指定sql语句的DataTable
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableBySQL(string SqlString)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
/// <summary>
/// 根据存储过程返回DataTable
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByProcedure(string ProcedureName, params SqlParameter[] coll)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
/// <summary>
/// 根据存储过程返回DataTable
/// </summary>
/// <param name="ProcedureName">存储过程名称</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByProcedure(string ProcedureName)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
}
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
asp.net操作access数据库是常见的数据库操作应用,本文就来实例讲解一下asp.net实现access数据库分页的方法。希望对大家的asp.net程序设
ASP.net连接SQL2005数据库出现错误提示:在建立与服务器的连接时出错。在连接到SQLServer2005时,在默认的设置下SQLServer不允许进行
以下内容给大家c#连接数据库的方法的相关介绍,本文非常具有参考借鉴价值,具体详情如下所示。ASP.NET连接数据库的技术叫ADO.NET,它是用来向数据库提交s
一,使数据库支持SQL高速缓存依赖性。二,使表支持SQL高速缓存依赖性。三,在ASP.NET应用程序的web.config文件中包含SQL连接字符串。四,以如下
本文实例讲述了ASP.NET连接数据库并获取数据方法。分享给大家供大家参考,具体如下:*连接对象的用法SqlConnection,SqlCommand,SqlD