.Net Core其實也可以使用.Net Framework時期那種ADO.Net的寫法
只是取連線字串的方式有點不同。其他步驟都大同小異
流程大致如下：
1.於建構子注入IConfiguration。之後就可以用實作GetValue取得appsettings.json裡的連線字串
2.下SQL做查詢，並用SqlDataAdapter接資料並塞進DataSet
3.把DataRow跟Model做Mapping。傳到View時就可以用強型別寫Razor語法
using System.Data.SqlClient
using System.Data //for DataSet & DataTable & 擴充方法(ex:Field<T>)
public List<ObjFgUserData> GetDetail_2(string userId)
{
string ConnectionString = _config.GetValue<string>("ConnectionStrings:DefaultConnection");
string strSQL = @"
select
....SQL Command....
and fu.UserId = @id
order by ....SQL Command....
";
List<ObjFgUserData> users = new List<ObjFgUserData>();
using (SqlConnection cn = new SqlConnection(ConnectionString))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand(strSQL, cn))
{
cmd.Parameters.Add("@id", System.Data.SqlDbType.VarChar).Value = userId;
//1.回傳DataSet or DataTable
using (SqlDataAdapter adpter = new SqlDataAdapter(cmd))
{
System.Data.DataSet ds = new System.Data.DataSet();
adpter.Fill(ds);
System.Data.DataTable dt = ds.Tables[0];
foreach (System.Data.DataRow dr in dt.Rows)
{
ObjFgUserData user = getPersonFromDataRow(dr);
users.Add(user);//DataRow Mapping To Model
}
}
}
}
return users;
}
private static ObjFgUserData getPersonFromDataRow(System.Data.DataRow row)
{
ObjFgUserData user = new ObjFgUserData();
user.CompanyId = !row.IsNull("CompanyId") ? row.Field<string>("CompanyId") : "";
user.RefCareer = !row.IsNull("RefCareer") ? row.Field<string>("RefCareer") : "";
user.CompanyName = !row.IsNull("CompanyName") ? row.Field<string>("CompanyName") : "";
user.FilingId = !row.IsNull("FilingId") ? row.Field<string>("FilingId") : "";
user.FilingStatus = !row.IsNull("FilingStatus") ? row.Field<string>("FilingStatus") : "";
user.UpdateTime = !row.IsNull("UpdateTime") ? row.Field<DateTime>("UpdateTime").ToString("yyyyMMdd HH:mm:ss") : "";
user.InspectionAgencies = !row.IsNull("InspectionAgencies") ? row.Field<string>("InspectionAgencies") : "";
user.CheckerName = !row.IsNull("CheckerName") ? row.Field<string>("CheckerName") : "";
user.CheckerDate = !row.IsNull("CheckerDate") ? row.Field<string>("CheckerDate") : "";
user.TrainingCat = !row.IsNull("CheckerName") ? row.Field<string>("CheckerName") : "";
return user;
}
public class ObjFgUserData
{
public string CompanyId { get; set; }
public string RefCareer { get; set; }
public string CompanyName { get; set; }
public string FilingId { get; set; }
public string FilingStatus { get; set; }
public string UpdateTime { get; set; }
public string InspectionAgencies { get; set; }
public string CheckerName { get; set; }
public string CheckerDate { get; set; }
public string TrainingCat { get; set; }
public string UserId { get; set; }
}取連線字串方式
1.在建構子注入IConfiguration
private readonly POSCContext _context;
private readonly Microsoft.Extensions.Configuration.IConfiguration _config;
public FgFilingUserQueryController(POSCContext context, Microsoft.Extensions.Configuration.IConfiguration config)
{
_context = context;
_config = config;
}2.使用擴充方法GetValue取得Config。要使用GetValue擴充方法要先using Microsoft.Extensions.Configuration;
string con = _config.GetValue<string>("ConnectionStrings:DefaultConnection");reference