标准化SQLHelp数据库通用访问类
2022/1/20 19:15:54
本文主要是介绍标准化SQLHelp数据库通用访问类,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace SQLDB
{
public class SqlHelp
{
//链接数据库字符串
private static string connString = ConfigurationManager.ConnectionStrings[“connString”].ToString();
//定义conn
public static SqlConnection conn;
//定义全局变量
public static SqlDataAdapter sda;
//定义全局变量
public static SqlCommandBuilder scb;
//定义全局变量DataSet
public static DataSet ds;
//定义全局变量
public static SqlCommand cmd;
#region 标准化SQL语句的执行,包含连接模式和非链接模式
//===链接连接模型
///
/// 执行增删改
///
/// T-SQL语句
/// 受影响的行数
public static int GetExecuteNonQuery(string sql)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
SqlCommand cmd = new SqlCommand(sql, conn);
//打开
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex; } finally { conn.Close(); } } /// <summary> /// 查询第一行第一列 /// </summary> /// <param name="sql">T-SQL语句</param> /// <returns>返回OBJECT类型</returns> public static object GetExecuteScalar(string sql) {//实例化conn conn = new SqlConnection(connString); //实例化cmd SqlCommand cmd = new SqlCommand(sql, conn); //执行 try { conn.Open(); return cmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } /// <summary> /// 读取所有行数据 /// </summary> /// <param name="sql">T-SQL语句</param> /// <returns>返回DataRead</returns> public static SqlDataReader GetExecuteReader(string sql) { //实例化conn conn = new SqlConnection(connString); //实例化cmd SqlCommand cmd = new SqlCommand(sql, conn); //执行 try { //打开 conn.Open(); SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return sdr; } catch (Exception ex) { throw ex; } } //========================非连接模型下的方法========================= /// <summary> /// 通过键值对的方式获得DataSet的Table带有表名称和主键 /// </summary> /// <param name="TableKeyValues">Dictionary键值对《表名称,SQL语句》</param> /// <param name="TableKey">表的主键</param> /// <returns>返回带有主键和表名称的DataSet</returns> public static DataSet GetAllInfoToDataSet(Dictionary<string, string> TableKeyValues, string TableKey) { //实例化dataSet ds = new DataSet(); //实例化conn conn = new SqlConnection(connString); try { //循环遍历dic获得键值对 foreach (KeyValuePair<string, string> item in TableKeyValues) { //实例化cmd cmd = new SqlCommand(item.Value, conn); //实例化dataadapter sda = new SqlDataAdapter(cmd); //定义类型 sda.SelectCommand = cmd; //实例化SqlcommandB scb = new SqlCommandBuilder(sda); //打开 conn.Open(); //填充数据 sda.Fill(ds, item.Key); //为表添加主键 ds.Tables[item.Key].PrimaryKey = new DataColumn[] { ds.Tables[item.Key].Columns[TableKey] }; } return ds; } catch (Exception) { throw; } finally { conn.Close(); } } //更新DataSet到数据库 public static void UpdataStudentInfo(string TableName) { conn.Open(); sda.Update(ds, TableName); conn.Close(); } #endregion #region 参数化SQL语句执行 /// <summary> /// 参数化SQL语句执行增删改 /// </summary>SELECT SNO, SName, Gender, Birthday, Mobile, Email, HomeAddress, PhotoPath FROM Student where SNO LIKE @SNO AND SName Like @SName AND Mobile Like @Mobile" /// <param name="sql">T-SQL带参数的语句</param> /// <param name="para">调用参数</param> /// <returns>返回受影响的行数,</returns> public static int GetExecuteNonQuery(string sql,SqlParameter[] para) { //实例化conn conn = new SqlConnection(connString); //实例化cmd cmd = new SqlCommand(sql, conn); //执行 try { //打开conn conn.Open(); //执行 cmd.Parameters.AddRange(para); return cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } } /// <summary> /// 参数化SQL语句执行查询单个结果集,第一行第一列 /// </summary> /// <param name="sql">SQL带参数的语句</param> /// <param name="para">调用参数的数组</param> /// <returns>返回第一行第一列Object类型</returns> public static object GetExecuteScalar(string sql,SqlParameter[] para) { //实例化conn conn = new SqlConnection(connString); //实例化cmd cmd = new SqlCommand(sql, conn); //执行 try { conn.Open(); //添加参数化 cmd.Parameters.AddRange(para); //返回单个结果集 return cmd.ExecuteScalar(); } catch (Exception) { throw; } finally { conn.Close(); } } /// <summary> /// 返回多个结果集 /// </summary> /// <param name="sql">带有参数的SQL语句</param> /// <param name="para">参数的数组</param> /// <returns>返回DataReader多个结果集</returns> public static SqlDataReader GetExecuteReader(string sql,SqlParameter[]para) { //实例化conn conn = new SqlConnection(connString); //实例化comd cmd = new SqlCommand(sql, conn); //执行 try { //打开 conn.Open(); //将参数化赋值给cmd cmd.Parameters.AddRange(para); //返回 return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { throw ex; } } #endregion }
}
这篇关于标准化SQLHelp数据库通用访问类的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-26结对编程到底难不难?答案在这里
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt
- 2024-06-05做软件测试需要懂代码吗?
- 2024-06-0514-ShardingSphere的分布式主键实现
- 2024-06-03为什么以及如何要进行架构设计权衡?
- 2024-05-31全网首发第二弹!软考2024年5月《软件设计师》真题+解析+答案!(11-20题)
- 2024-05-31全网首发!软考2024年5月《软件设计师》真题+解析+答案!(21-30题)