asp.net 数据库连接类代码(SQL)_实用技巧

复制代码 代码如下:

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;
}
}

时间： 2024-07-31 10:10:10

asp.net 数据库连接类代码(SQL)_实用技巧的相关文章

Asp.Net类型转换类(通用类)代码分享_实用技巧

废话不多说了,直接给大家贴代码了,具体代码如下所述: /// <summary> /// 类型转换类 /// 处理数据库获取字段为空的情况 /// </summary> public static class DBConvert { #region------------------ToInt32类型转换------------------ /// <summary> /// 读取数据库中字符串并转换成Int32 /// 为空时返回0 /// </summary&

ASP.NET XmlDocument类详解_实用技巧

XmlDocument类是.NET框架的DOC解析器.XmlDocument将XML视为树状结构,它装载XML文档,并在内存中构建该文档的树状结构.下面来看下XmlDocument提供了哪些功能. 一.属性: Attributes 获取一个 XmlAttributeCollection,它包含该节点的属性. (继承自 XmlNode.) BaseURI     获取当前节点的基 URI. (重写 XmlNode..::.BaseURI.) ChildNodes 获取节点的所有子节点. (继承自

ASP.net 验证码实现代码(C#)_实用技巧

public class ValidateCode : System.Web.UI.Page {   private void Page_Load(object sender, System.EventArgs e)   {    this.CreateCheckCodeImage(GenerateCheckCode());   }   #region Web 窗体设计器生成的代码   override protected void OnInit(EventArgs e)   {    //  

Win 2000下ASP.NET开发环境的配置_实用技巧

Win 2000下ASP.NET的配置 Win 2000(包括Professional,Server和Advanced Server)在默认情况下是不支持ASP.NET的.必须对它进行一个环境的配置. 客户端 SQL Server .NET 数据提供程序 Microsoft 数据访问组件 (MDAC) 2.6 或更高版本 对系统管理信息的访问 Windows Management Instrumentation (WMI)(在 Windows 2000操作系统一起安装)COM+ 服务 Windo

ASP.NET程序中常用代码汇总_实用技巧

1. 打开新的窗口并传送参数: //传送参数: response.write("<script>window.open('*.aspx?id="+this.DropDownList1.SelectIndex+"&id1="++"')</script>") //接收参数: string a = Request.QueryString("id"); string b = Request.QueryS

用Fine Uploader+ASP.NET MVC实现ajax文件上传[代码示例]_实用技巧

This project attempts to achieve a user-friendly file-uploading experience over the web. It's built as a Javascript plugin for developers looking to incorporate file-uploading into their website. Fine Uploader 不依赖于 jQuery,也就是说不引用jquery.js,也可以正常使用.同时,

ASP.NET用户控件技术_实用技巧

我们发现,利用代码绑定技术我们可以容易的将我们的代码和内容分离开来,利用它可以建立可重用的代码,只是这种技术本身也存在着一些局限性.在本文中,我们将会一同探讨另外一种新的ASP.NET技术:用户控件.    什么是用户控件(User Controls)?  为了能更好的理解用户控件的重要性,我们先来看看一段小小的"历史".在以前的ASP当中,可重用的技术实现选择是相当受限制的.许多的开发者一般都是借助将公共的常用的子过程放到那些包含文件当中的做法来实现一定的所谓代码重用的.比如,如我们

asp.net页面SqlCacheDependency缓存实例_实用技巧

缓存技术是asp.net程序设计中非常实用的技术,也是大型web程序设计中比较常见的技术.本文就以实例形式对此加以说明.具体如下: asp.net页面的SqlCacheDependency Sql缓存: Cahce/SqlCachePage.aspx页面代码如下: <%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile=&qu

微信公众平台开发之地理位置.Net代码解析_实用技巧

微信公共平台中涉及到地理位置的有两种情况:         第一.我发送一个自选的地理位置给微信,然后微信可以自动反馈响应的信息.         第二.让微信获取我们GPS定位地址位置,反馈响应的信息.         首先我们先来看第一种,在微信中除了可以发文本,图片,语音等还有一个信息就是地理位置,按照微信接受地理信息的XML信息,我们需要改造一下之前的wxmessage类加上几个属性:  class wxmessage { public string FromUserName { get