在线客服
|
C#数据访问层主类.(SqlHelper)SQLDataAccess.cs
C#数据访问层主类.(SqlHelper)SQLDataAccess.cs
顾名思义数据访问层是直接与数据库打交通了.下面这个类封装了一些常用的方法,简单实用. using System; using System.Data; using System.Configuration; using System.Web; using System.Data.SqlClient; namespace dal { /// <summary> /// 数据访问层 by www.csframework.com /// </summary> public class SQLDataAccess { //Sql Server连接字符串 private string _ConnectionString = string.Empty; private static SQLDataAccess _Instance = null; //数据访问层实例<单件模式> public static SQLDataAccess Instance //实例 { get { if (_Instance == null) _Instance = new SQLDataAccess(); return _Instance; } } private SQLDataAccess()//私有构造器 { _ConnectionString = "server=192.168.0.3;database=mydata;uid=sa;pwd=123;"; } public SqlConnection createConnection()//创建一个连接对象 { SqlConnection conn = new SqlConnection(_ConnectionString); conn.Open(); return conn; } //获取一条记录 public DataRow getDataRow(string sql) { DataTable dt = getSQLTable(sql, "MyTable"); if (dt != null && dt.Rows.Count > 0) return dt.Rows[0]; else return null; } //获取一条记录 public DataRow getDataRow(SqlCommand command) { DataTable dt = getSQLTable(command, "MyTable"); if (dt != null && dt.Rows.Count > 0) return dt.Rows[0]; else return null; } //获取一个数据表 public DataTable getSQLTable(string selectSQL, string tableName) { SqlConnection connection = this.createConnection(); try { DataTable table = new DataTable(tableName); SqlDataAdapter adapter = new SqlDataAdapter(selectSQL, connection); adapter.Fill(table); this.CloseConnection(connection); return table; } catch (System.Exception ex) { this.CloseConnection(connection); throw ex; } } //获取一个数据表 public DataTable getSQLTable(SqlCommand command, string tableName) { SqlConnection connection = command.Connection; try { if (command.Connection == null) command.Connection = this.createConnection(); DataTable table = new DataTable(tableName); SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(table); this.CloseConnection(connection); return table; } catch (System.Exception ex) { if (connection.State != ConnectionState.Closed) connection.Close(); throw ex; } } //获取一个数据集 public DataSet getDataset(string selectSQL) { SqlConnection connection = this.createConnection(); try { DataSet ds = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(selectSQL, connection); adapter.Fill(ds); this.CloseConnection(connection); return ds; } catch (System.Exception ex) { this.CloseConnection(connection); throw ex; } } //获取一个数据集 public DataSet getDataset(SqlCommand command) { SqlConnection connection = command.Connection; try { if (command.Connection == null) command.Connection = this.createConnection(); DataSet ds = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(ds); this.CloseConnection(connection); return ds; } catch (System.Exception ex) { this.CloseConnection(connection); throw ex; } } //执行SQL语句.参数:sql语句,返回整数值为判断所影响的行数 public int executeSQL(string cmdText) { int iValue = -1; SqlConnection connection = this.createConnection(); try { SqlCommand command = new SqlCommand(cmdText, connection); iValue = command.ExecuteNonQuery(); this.CloseConnection(connection); return iValue; } catch (System.Exception ex) { this.CloseConnection(connection); throw ex; } } //执行SQL语句.参数:sql带有参数的sql语句。如:where userid=@userid //返回整数值为判断所影响的行数 public int executeSQL(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql); cmd.Parameters.AddRange(values); return executeSQL(cmd); } //执行SQL命令 public int executeSQL(SqlCommand command) { SqlConnection connection = command.Connection; try { if (command.Connection == null) command.Connection = this.createConnection(); int iValue = command.ExecuteNonQuery(); this.CloseConnection(connection); return iValue; } catch (System.Exception ex) { this.CloseConnection(connection); throw ex; } } //执行SQL命令返回第一行第一列的值 public object executeScalar(SqlCommand command) { SqlConnection connection = command.Connection; try { if (command.Connection == null) command.Connection = this.createConnection(); object ret = command.ExecuteScalar(); this.CloseConnection(connection); return ret; } catch (System.Exception ex) { this.CloseConnection(connection); throw ex; } } //执行SQL语句返回第一行第一列的值 public object executeScalar(string cmdText) { SqlConnection connection = this.createConnection(); try { SqlCommand command = new SqlCommand(cmdText, connection); object ret = command.ExecuteScalar(); this.CloseConnection(connection); return ret; } catch (System.Exception ex) { this.CloseConnection(connection); throw ex; } } //关闭SQL连接 private void CloseConnection(SqlConnection connection) { if (connection == null) return; if (connection.State != ConnectionState.Closed) connection.Close(); } } } 如何使用?通过SQLDataAccess.Instance实例访问成员方法. public class Tester { public void TestDAL() { //获取DataSet DataSet user = SQLDataAccess.Instance.getDataset("select * from users"); //执行SQL statement int i = SQLDataAccess.Instance.executeSQL("delete users where userid=’csframework'"); //返回一条记录 DataRow row = SQLDataAccess.Instance.getDataRow("select * from users where userid=’csframework’"); } } 另外提供一个Microsoft官方的SqlHepler类,不过这个类超巨大,很多功能用不了,只做参考.
参考文档：
将SQLServer数据类型转换为C#.Net类型 开发应用-观察者模式之C#实现缓存数据更新 C#多线程处理多个队列的数据(交叉线程访问及Invoke方法使用) C#调用Delphi编译的DLL函数库返回大文本数据 C#.Net授权访问远程电脑的共享目录(目录授权,用户角色扮演) 框架能否支持同时访问多个数据源？ C/S开发框架的数据访问层用LINQ吗？ C#开发实例-数据字典作为参考数据在表格中显示的两种方案 C# C/S开发框架新增数据窗体 C#.Net C/S结构开发框架中数据访问层(DAL)的作用 C# 使用设计模式构建通用数据库访问类 C#.Net对象内存模型及堆/栈数据结构详解 (一) C#.Net对象内存模型及堆/栈数据结构详解 (二) C#.Net对象内存模型及堆/栈数据结构详解 (三) C#.Net对象内存模型及堆/栈数据结构详解 (四)
其它资料：
什么是C/S结构？ | C/S框架核心组成部分 | C/S框架-WebService部署图 | C/S框架-权限管理 | C/S结构系统框架 - 5.1旗舰版介绍 | C/S结构系统框架 - 功能介绍 | C/S结构系统框架 - 产品列表 | C/S结构系统框架 - 应用展示(图) | 三层体系架构详解 | C/S架构轻量级快速开发框架 | C/S框架网客户案例 | WebApi快速开发框架 | C/S框架代码生成器 | 用户授权注册软件系统 | 版本自动升级软件 | 数据库底层应用框架 | CSFramework.CMS内容管理系统 | |
 旗舰版V5.1 (作者推荐)  轻量框架V2.1 (2021 release) 
|
在线客服
