概要
vs2022中,微软推荐程序员采用Entity Framework core 访问数据库,更有利于初学者掌握,并对各种数据库访问进行了封装,统一使用LInq 语句进行访问,程序设计者不必关心数据管理的底层设计。但对于已熟悉数据库管理的人员来说,不能发挥直接操作数据库的高效特性。这时介绍一种通过适配器,直接操作数据库的方法。
整体架构流程
主要有以下几个步骤:
- 建立数据访问接口;
- 建立接口实现,即provider;
- 在控制器中直接调用接口;
- 在 Program.cs中注入
实现代码
- 1、数据接口
using System.Data;
namespace WebMvc4.Models
{
public interface IWzbmb
{
public string GetWzbmb(ulong wzbm);
public List<Km> GetKms(string commandString);
public DataView GetDataView(string commandString);
public DataView GetDV(string commandString);
public string getKmmc(string kmbm);
}
}
- 2、接口的实现,注意要继承自接口和IDisposeable
namespace WebMvc4.Models
{
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.Data;
using System.Data.Common;
using System.Security.AccessControl;
using System.Security.Cryptography;
public class Provider:IDisposable,IWzbmb
{
private System.Data.IDbDataAdapter? adapter;
public Microsoft.Data.SqlClient.SqlConnection sqlConnection;
public Provider(string ConnectionString)
{
sqlConnection = new(ConnectionString);
// sqlConnection.Open();
}
public DataView GetDV(string Cmd)
{
sqlConnection.Close();
SqlDataAdapter sda = new(Cmd, sqlConnection);
DataSet ds = new();
sda.Fill(ds);
DataView dv = ds.Tables[0].DefaultView;
return dv;
}
public DataView GetDataView(string commandString)
{
DataView dv = new DataView();
DataTable dt= new DataTable("km");
dt.Columns.Add("kmdm");
dt.Columns.Add("kmmc");
dv.AllowNew=true;
dv.AllowEdit=true;
SqlCommand cmd= sqlConnection.CreateCommand();
cmd.CommandText = commandString;
sqlConnection.Open();
SqlDataReader reader = cmd.ExecuteReader();
int k = 0;
while (reader.Read())
{
dt.Rows.Add();
for (int i=0;i<reader.FieldCount;i++)
{
dt.Rows[k][i]=reader.GetValue(i);
}
k++;
}
sqlConnection.Close();
dv.Table = dt;
return dv;
}
public List<Km> GetKms(string commandString)
{
string kmdm, kmmc;
List<Km> kms= new List<Km>();
adapter = new SqlDataAdapter(commandString, sqlConnection);
DataSet kms1=new DataSet();
adapter.Fill(kms1);
DataView dv = kms1.Tables[0].DefaultView;
if (dv != null)
{
for (int i = 0; i < dv.Count; i++)
{
Km km = new Km();
kmdm =dv[i][0].ToString();
kmmc = dv[i][1].ToString();
km.Kmdm = kmdm;
km.Kmmc = kmmc;
kms.Add(km);
}
}
return kms;
}
public void Dispose()
{
sqlConnection.Close();
GC.SuppressFinalize(this);
//throw new NotImplementedException();
}
public string GetWzbmb(ulong wzbm)
{
return "代码为:"+wzbm.ToString();
}
public string getKmmc(string kmdm)
{
return kmdm.ToString();
}
}
[Keyless]
public class Km
{
private string kmdm;
private string kmmc;
public string Kmdm { get => kmdm; set => kmdm = value; }
public string Kmmc { get => kmmc; set => kmmc = value; }
public Km()
{
}
}
}
- 注入
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllersWithViews();
builder.Services.AddScoped<IWzbmb, WzbmbReposistory>();
var app = builder.Build();
小结
通过以上架构,能很方便的移植老的软件,并能灵活操作各种数据库。缺点是,不能使用微软提供的新的功能。