首页 > 代码库 > DotNerCore的ADO.NET操作实例
DotNerCore的ADO.NET操作实例
依赖环境
.Net Core 1.0.0-preview2-003121
VSCode
一、创建.Net Core项目
1、打开命令行选择好工作目录后执行 dotnet new，生成2个文件（Program.cs、project.json）
2、执行 dotnet restore 还原整个项目的依赖库 生成相应文件
3、执行 dotnet build 编译程序
4、执行 dotnet run 默认输出 helloworld
此时默认的.net core console程序已经完成创建 解析依赖 。
二、引用System.Data.SqlClient
打开project.json 讲dependencies修改如下
"dependencies": { "System.Data.Common": "4.1.0-*", "System.Data.SqlClient" : "4.1.0-*", "System.Runtime": "4.1.0-*" },
然后执行 dotnet restore ，执行完成后就可以在程序中使用ado.net对象了
经编码发现 SqlConnection和SqlCommand都在，查询数据库SqlDataReader保留下来了，但是SqlDataAdapter没有了，所以需要自己创建。
另外Datable和DataSet目前仍然不可用所以需要自己创建。因此需要创建三个大类和一些小类（DataRow，DataColumn）
搜索了下资料在园友处找到了先造轮子的人孙凯旋 于是拷贝了他的扩展代码整合 DataTableExtensions文件，代码如下
using System; using System.Collections; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Threading.Tasks; namespace SqlSugar { /// <summary> /// 作者：sunkaixuan /// 创建时间：2016/7/31 /// 修改时间：- /// 说明：让.netCore支持DataSet /// </summary> public class DataSet { /// <summary> /// 数据表 /// </summary> public List<DataTable> Tables = new List<DataTable>(); } /// <summary> /// 作者：sunkaixuan /// 创建时间：2016/7/31 /// 修改时间：- /// 说明：让.netCore支持DataTable /// </summary> public class DataTable { /// <summary> /// 列信息 /// </summary> public DataColumnCollection Columns = new DataColumnCollection(); /// <summary> /// 行信息 /// </summary> public DataRowCollection Rows = new DataRowCollection(); } /// <summary> /// 数据列 /// </summary> public class DataColumn { /// <summary> /// Data Column /// </summary> public DataColumn() { } /// <summary> /// Column Name /// </summary> /// <param name="columnName"></param> public DataColumn(string columnName) { this.ColumnName = columnName; } /// <summary> /// Data Column /// </summary> /// <param name="columnName"></param> /// <param name="dataType"></param> public DataColumn(string columnName, Type dataType) { this.ColumnName = columnName; this.DataType = dataType; } /// <summary> /// Column Name /// </summary> public string ColumnName { get; internal set; } /// <summary> /// Data Type /// </summary> public Type DataType { get; internal set; } } /// <summary> /// 数据列集合 /// </summary> public class DataColumnCollection : IEnumerable, ICollection, IEnumerator { /// <summary> /// Get Item By Index /// </summary> /// <param name="thisIndex"></param> /// <returns></returns> public DataColumn this[int thisIndex] { get { return cols[thisIndex]; } } private int index = -1; private List<DataColumn> cols; /// <summary> /// Count /// </summary> public int Count { get { if (this.cols == null) { this.cols = new List<DataColumn>(); } return this.cols.Count; } } /// <summary> /// Add /// </summary> /// <param name="col"></param> public void Add(DataColumn col) { if (this.cols == null) { this.cols = new List<DataColumn>(); } this.cols.Add(col); } /// <summary> /// Is Synchronized /// </summary> public bool IsSynchronized { get { return true; } } /// <summary> /// Sync Root /// </summary> public object SyncRoot { get { return null; } } /// <summary> /// Current /// </summary> public object Current { get { return cols[index]; } } /// <summary> /// Copy To /// </summary> /// <param name="array"></param> /// <param name="index"></param> public void CopyTo(Array array, int index) { throw new NotImplementedException(); } /// <summary> /// 获取该集合的 System.Collections.IEnumerator /// </summary> /// <returns></returns> public IEnumerator GetEnumerator() { return (IEnumerator)this; ; } /// <summary> /// Move Next /// </summary> /// <returns></returns> public bool MoveNext() { index++; var isNext = index < cols.Count; if (!isNext) Reset(); return isNext; } /// <summary> /// Reset /// </summary> public void Reset() { index = -1; } /// <summary> /// Contains Key /// </summary> /// <param name="name"></param> /// <returns></returns> public bool ContainsKey(string name) { if (this.cols == null) return false; return (this.cols.Any(it => it.ColumnName == name)); } } /// <summary> /// 数据行集合 /// </summary> public class DataRowCollection : IEnumerable, ICollection, IEnumerator { /// <summary> /// Get item by index /// </summary> /// <param name="thisIndex"></param> /// <returns></returns> public DataRow this[int thisIndex] { get { return Rows[thisIndex]; } } private int index = -1; private List<DataRow> Rows = null; /// <summary> /// Count /// </summary> public int Count { get { if (this.Rows == null) { this.Rows = new List<DataRow>(); } return Rows.Count; } } /// <summary> /// Current /// </summary> public object Current { get { if (this.Rows == null) { this.Rows = new List<DataRow>(); } return Rows[index]; } } /// <summary> /// Is Synchronized /// </summary> public bool IsSynchronized { get { return true; } } /// <summary> /// Sync Root /// </summary> public object SyncRoot { get { return null; } } /// <summary> /// Copy To /// </summary> /// <param name="array"></param> /// <param name="index"></param> public void CopyTo(Array array, int index) { throw new NotImplementedException(); } /// <summary> /// 获取该集合的 System.Collections.IEnumerator。 /// </summary> /// <returns></returns> public IEnumerator GetEnumerator() { return (IEnumerator)this; ; } /// <summary> /// Move Next /// </summary> /// <returns></returns> public bool MoveNext() { index++; var isNext = index < (Rows==null?0:Rows.Count); if (!isNext) Reset(); return isNext; } /// <summary> /// Reset /// </summary> public void Reset() { index = -1; } /// <summary> /// Add /// </summary> /// <param name="daRow"></param> internal void Add(DataRow daRow) { if (Rows == null) { Rows = new List<DataRow>(); } Rows.Add(daRow); } } /// <summary> /// 数据行 /// </summary> public class DataRow { private Dictionary<string, object> obj = new Dictionary<string, object>(); /// <summary> /// Add /// </summary> /// <param name="key"></param> /// <param name="value"></param> public void Add(string key, object value) { obj.Add(key, value); } /// <summary> /// Get Item By Name /// </summary> /// <param name="name"></param> /// <returns></returns> public object this[string name] { get { return obj[name]; } } /// <summary> /// Get Item By Index /// </summary> /// <param name="index"></param> /// <returns></returns> public object this[int index] { get { int i = 0; object reval = null; foreach (var item in obj) { if (i == index) { reval = item.Value; break; } i++; } return reval; } } /// <summary> /// Contains Key /// </summary> /// <param name="columnName"></param> /// <returns></returns> public bool ContainsKey(string columnName) { if (this.obj == null) return false; return (this.obj.ContainsKey(columnName)); } } /// <summary> /// 数据填充器 /// </summary> public class SqlDataAdapter { private SqlCommand command; private string sql; private SqlConnection _sqlConnection; /// <summary> /// SqlDataAdapter /// </summary> /// <param name="command"></param> public SqlDataAdapter(SqlCommand command) { this.command = command; } /// <summary> /// SqlDataAdapter /// </summary> /// <param name="sql"></param> /// <param name="_sqlConnection"></param> public SqlDataAdapter(string sql, SqlConnection _sqlConnection) { this.sql = sql; this._sqlConnection = _sqlConnection; } /// <summary> /// SelectCommand /// </summary> public SqlCommand SelectCommand { get { if (this.command == null) { this.command = new SqlCommand(this.sql, this._sqlConnection); } return this.command; } } /// <summary> /// Fill /// </summary> /// <param name="dt"></param> public void Fill(DataTable dt) { if (dt == null) { dt = new DataTable(); } var columns = dt.Columns; var rows = dt.Rows; using (SqlDataReader dr = command.ExecuteReader()) { for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i).Trim(); if (!columns.ContainsKey(name)) columns.Add(new DataColumn(name, dr.GetFieldType(i))); } while (dr.Read()) { DataRow daRow = new DataRow(); for (int i = 0; i < columns.Count; i++) { if (!daRow.ContainsKey(columns[i].ColumnName)) daRow.Add(columns[i].ColumnName, dr.GetValue(i)); } dt.Rows.Add(daRow); } } } /// <summary> /// Fill /// </summary> /// <param name="ds"></param> public void Fill(DataSet ds) { if (ds == null) { ds = new DataSet(); } using (SqlDataReader dr = command.ExecuteReader()) { do { var dt = new DataTable(); var columns = dt.Columns; var rows = dt.Rows; for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i).Trim(); if (!columns.ContainsKey(name)) columns.Add(new DataColumn(name, dr.GetFieldType(i))); } while (dr.Read()) { DataRow daRow = new DataRow(); for (int i = 0; i < columns.Count; i++) { if (!daRow.ContainsKey(columns[i].ColumnName)) daRow.Add(columns[i].ColumnName, dr.GetValue(i)); } dt.Rows.Add(daRow); } ds.Tables.Add(dt); } while (dr.NextResult()); } } } }
因为要早同个命名空间下才能使用所以需要修改命名空间一致
于是简单的.Net Core ADO.NET查询方法就可以执行了，修改program.cs文件代码如下
using System; using System.Data; using System.Data.SqlClient; namespace ConsoleApplication { public class Program { public static void Main(string[] args) { string ConStr = "Data Source=.;Initial Catalog=ForTest;Persist Security Info=False;User ID=sa;Password=sa;"; string CmdStr = "select * from IisLog"; DataTable dt = new DataTable(); using(SqlConnection con = new SqlConnection(ConStr)) { using(SqlCommand command = new SqlCommand(CmdStr,con)) { if (con.State != ConnectionState.Open) con.Open(); SqlDataAdapter sda = new SqlDataAdapter(command); sda.Fill(dt); } } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { Console.Write(dt.Rows[i][j] +" "); } Console.WriteLine(); } Console.ReadKey(); } } }
控制台输出了结果
DotNerCore的ADO.NET操作实例
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。