大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
这篇文章主要介绍C#中Sql数据库SQLHelper类的示例代码,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!
让客户满意是我们工作的目标,不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户,将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴,公司提供的服务项目有:空间域名、网络空间、营销软件、网站建设、如东网站维护、网站推广。
using System;using System.Collections.Generic;using System.Text;using System.Collections;using System.Data.SqlClient;using System.Data;using System.Configuration; public class SQLHelper { //取得数据库连接web.config 中配置 public static readonly string ConnectString = ConfigurationManager.ConnectionStrings["DBString"].ConnectionString; ////// 无事务,数据查询 /// /// 存储过程或Sql语句 /// 存储过程名或Sql语句内容 /// 参数列表 ///public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } catch { throw; } finally { conn.Close(); } } /// /// 有事务,数据操作类 /// /// 事务 /// 操作类别 (stored procedure,sql) /// 存储过程名或Sql语句 /// 参数 ///返回影响的数据行数 public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); /*if (cmdType == CommandType.StoredProcedure) { cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); val = (int)cmd.Parameters["@RETURN_VALUE"].Value; } else if (cmdType==CommandType.Text) { val = cmd.ExecuteNonQuery(); }*/ int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } ////// 返回数据集 DataReader /// /// /// /// ///public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch (Exception ex) { conn.Close(); // throw new Exception("操作失败!"); throw new Exception(ex.Message); } } /// /// 有事务的取数据 /// /// /// /// /// ///public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } catch { throw; } finally { conn.Close(); } } public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// /// 根据Sql语句取得表 /// /// /// /// ///public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { DataTable temptable = new DataTable(); SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder scb = new SqlCommandBuilder(da); da.Fill(temptable); } finally { conn.Close(); } return temptable; } public static DataTable ExecuteTable(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { DataTable temptable = new DataTable(); SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder scb = new SqlCommandBuilder(da); da.Fill(temptable); cmd.Parameters.Clear(); return temptable; } /// /// 根据Sql语句或存储过程取得数据 /// /// /// /// ///public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlConnection conn = new SqlConnection(ConnectString); SqlCommand cmd = new SqlCommand(); DataSet TempDataSet = new DataSet(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(TempDataSet); cmd.Parameters.Clear(); return TempDataSet; } finally { conn.Close(); } } public static DataSet ExecuteDataSet(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); DataSet TempDataSet = new DataSet(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(TempDataSet); cmd.Parameters.Clear(); return TempDataSet; } /// /// 生成Sql语句或准备 /// /// /// /// /// /// /// private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } public static object ToDBValue(object value) { return value == null ? DBNull.Value : value; } public static object FromDBValue(object dbValue) { return dbValue == DBNull.Value ? null : dbValue; } }
以上是“C#中Sql数据库SQLHelper类的示例代码”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注创新互联行业资讯频道!