这篇文章主要介绍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;
/// <summary> /// 无事务，数据查询 /// </summary> /// <param name="cmdType">存储过程或Sql语句</param> /// <param name="cmdText">存储过程名或Sql语句内容</param> /// <param name="CommandParams">参数列表</param> /// <returns></returns> 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();
}
}
/// <summary> /// 有事务，数据操作类 /// </summary> /// <param name="trans">事务</param> /// <param name="cmdType">操作类别 (stored procedure,sql)</param> /// <param name="cmdText">存储过程名或Sql语句</param> /// <param name="CommandParams">参数</param> /// <returns>返回影响的数据行数</returns> 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; }
/// <summary> /// 返回数据集 DataReader /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="CommandParams"></param> /// <returns></returns> 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); } }
/// <summary> /// 有事务的取数据 /// </summary> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="CommandParams"></param> /// <returns></returns> 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;
}
/// <summary> /// 根据Sql语句取得表 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="CommandParams"></param> /// <returns></returns> 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;
}
/// <summary> /// 根据Sql语句或存储过程取得数据 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="CommandParams"></param> /// <returns></returns> 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; }
/// <summary> /// 生成Sql语句或准备 /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> 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类的示例代码”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
