英文:
How to add data of one table to another table using Id and display it using .net api
问题
我在我的数据库中有2个表。一个是国家表,另一个是州表。
国家表有Id、名称和states[]字段。
州表有Id、stateName和countryId字段。
我需要根据Id在.NET Core中获取国家的详细信息。
每当我使用国家Id进行国家的获取操作时,我应该获得包含该国家Id的Id、名称和所有州详细信息的数组格式。
例如,我正在获取Id = 1的国家,它有2个州,那么输出应该是:
Id: 1,
Name: abc,
States: [
{
Id: 1,
stateName: st1,
countryId: 1
},
{
Id: 2,
stateName: st2,
countryId: 1
}]
我需要使用连接吗?还是其他什么东西?我需要在DAL中编写什么代码来获得这样的输出?请帮助我!我是.NET新手。
英文:
I have 2 tables in my db. One is country table and other is state table.
Country table has Id, name ,states[].
State table has Id, stateName, countryId.
I need to fetch a country details based on Id using .net core.
Whenever I use get operation for country using country Id - I should get the Id, name, and all state details with that country Id in an array format.
Ex. I am fetching country with Id = 1 and it has 2 states then, output should be,
Id: 1,
Name: abc,
States: [
{
Id:1,
stateName: st1,
countryId: 1
},
{
Id:2,
stateName: st2,
countryId:1
}]
Do I need to use join? Or something else. What code I need to write in DAL to get output like this? Please help!! I am new to .Net
答案1
得分: 1
你有两种方法来获取数据。1. EF 和 2. Ado.Net
1. EF
你可以使用 EF。
首先,你必须添加两个类,一个类是 Country,另一个类是 Status,因为 Country 有很多状态,你必须使用 "ICollection<states>"。
类
public class Country
{
public int Id { set; get; }
public string name { set; get; }
public virtual ICollection<states> states { set; get; }
}
public class states
{
public int Id { set; get; }
public string stateName { set; get; }
public int countryId { set; get; }
public virtual Country country { set; get; }
}
这是 DbContext 中的代码(必须添加这些类)
public DbSet<Country> Country { get; set; }
public DbSet<states> states { get; set; }
这是插入数据(Country、Status)的代码,在保存更改后,CountryId 将获得一个值
var Country = new Country();
Country.name = "test2";
Country.states = new Collection<states>();
var state = new states();
state.stateName = "st1";
Country.states.Add(state);
state = new states();
state.stateName = "st2";
Country.states.Add(state);
context.Country.Add(Country);
context.SaveChanges();
这是获取数据(Country、Status)的代码,使用 Include 来获取带有 Country 的状态
var d = context.Country.Include(d => d.states).FirstOrDefault(d=>d.Id==2);
2. 使用 Ado.net
DataBaseCommon _GetData = new DataBaseCommon();
var _list = _GetData.GetData(ConntionStringSource);
var _result = _GetData.getCountry(_list);
public class DataBaseCommon
{
public DataTable GetData(string connectionString)
{
DataTable dtPerson = new DataTable();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
var _SqlCommand = "SELECT b.*,a.stateName,a.countryId\r\n FROM [TestDB7].[dbo].[Country] b\r\n inner join [TestDB7].[dbo].[states] a on a.countryId=b.Id";
SqlCommand objSqlCommand = new SqlCommand(_SqlCommand, con);
SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
try
{
objSqlDataAdapter.Fill(dtPerson);
}
catch (Exception ex)
{
con.Close();
}
}
return dtPerson;
}
public List<Country> getCountry( DataTable table)
{
var _listCountry =
from p in table.AsEnumerable()
group p by p.Field<int>("Id") into g
select new Country
{
Id = g.Key,
name = g.Select(i => i.Field<string>("Name")).FirstOrDefault(),
states = table.AsEnumerable()
.Where(row => g.Any(p => g.Key == row.Field<int>("CountryId")))
.Select(d => new states
{
stateName = d.Field<string>("stateName"),
countryId = d.Field<int>("countryId")
}).ToList()
};
return _listCountry.ToList();
}
}
(Note: The code contains some HTML-encoded characters, like ", which are used for formatting. These characters may need to be replaced with their actual values when implementing the code.)
英文:
You have two ways to fetch data.1. EF and 2.adoNet
1.EF
You can use EF
First you must add Classes (two classes) ,a class is country and other class is status ,because country has a lot of status you must use "ICollection<states>"
Classes
public class Country
{
public int Id { set; get; }
public string name { set; get; }
public virtual ICollection<states> states { set; get; }
}
public class states
{
public int Id { set; get; }
public string stateName { set; get; }
public int countryId { set; get; }
public virtual Country country { set; get; }
}
This is Code in DbContext(must add classes)
public DbSet<Country> Country { get; set; }
public DbSet<states> states { get; set; }
This is Code to insert Data(Country,Status) that Countryid after savechange It takes a value
var Country = new Country();
Country.name = "test2";
Country.states = new Collection<states>();
var state = new states();
state.stateName = "st1";
Country.states.Add(state);
state = new states();
state.stateName = "st2";
Country.states.Add(state);
context.Country.Add(Country);
context.SaveChanges();
This is Code to fetch Data(Country,Status) that use Include for fetch states with Country
var d = context.Country.Include(d => d.states).FirstOrDefault(d=>d.Id==2);
2.with Ado.net
DataBaseCommon _GetData = new DataBaseCommon();
var _list = _GetData.GetData(ConntionStringSource);
var _result = _GetData.getCountry(_list);
public class DataBaseCommon
{
public DataTable GetData(string connectionString)
{
DataTable dtPerson = new DataTable();
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
var _SqlCommand = "SELECT b.*,a.stateName,a.countryId\r\n FROM [TestDB7].[dbo].[Country] b\r\n inner join [TestDB7].[dbo].[states] a on a.countryId=b.Id";
SqlCommand objSqlCommand = new SqlCommand(_SqlCommand, con);
SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
try
{
objSqlDataAdapter.Fill(dtPerson);
}
catch (Exception ex)
{
con.Close();
}
}
return dtPerson;
}
public List<Country> getCountry( DataTable table)
{
var _listCountry =
from p in table.AsEnumerable()
group p by p.Field<int>("Id") into g
select new Country
{
Id = g.Key,
name = g.Select(i => i.Field<string>("Name")).FirstOrDefault(),
states = table.AsEnumerable()
.Where(row => g.Any(p => g.Key == row.Field<int>("CountryId")))
.Select(d => new states
{
stateName = d.Field<string>("stateName"),
countryId = d.Field<int>("countryId")
}).ToList()
};
return _listCountry.ToList();
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论