如何使用 Id 将一个表的数据添加到另一个表,并在 .net API 中显示它。

huangapple go评论63阅读模式
英文:

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&lt;states&gt; 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&lt;Country&gt; Country { get; set; }
public DbSet&lt;states&gt; states { get; set; }

这是插入数据(Country、Status)的代码,在保存更改后,CountryId 将获得一个值

var Country = new Country();
Country.name = &quot;test2&quot;;
Country.states = new Collection&lt;states&gt;();
           
var state = new states();
state.stateName = &quot;st1&quot;;
Country.states.Add(state);
state = new states();
state.stateName = &quot;st2&quot;;
           
Country.states.Add(state);
context.Country.Add(Country);
context.SaveChanges();

这是获取数据(Country、Status)的代码,使用 Include 来获取带有 Country 的状态

var d = context.Country.Include(d =&gt; d.states).FirstOrDefault(d=&gt;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 = &quot;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&quot;;
                SqlCommand objSqlCommand = new SqlCommand(_SqlCommand, con);
                SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                try
                {
                    objSqlDataAdapter.Fill(dtPerson);

                }
                catch (Exception ex)
                {
                    con.Close();
                }
            }

            return dtPerson;

        }

        public  List&lt;Country&gt; getCountry( DataTable table)
        {


            var _listCountry =
               from p in table.AsEnumerable()
               group p by p.Field&lt;int&gt;(&quot;Id&quot;) into g
               select new Country
               {
                   Id = g.Key,
                   name = g.Select(i =&gt; i.Field&lt;string&gt;(&quot;Name&quot;)).FirstOrDefault(),
                   states = table.AsEnumerable()
                           .Where(row =&gt; g.Any(p =&gt; g.Key == row.Field&lt;int&gt;(&quot;CountryId&quot;)))
                           .Select(d =&gt; new states
                           {
                               stateName = d.Field&lt;string&gt;(&quot;stateName&quot;),
                               countryId = d.Field&lt;int&gt;(&quot;countryId&quot;)
                           }).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&lt;states&gt; 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&lt;Country&gt; Country { get; set; }
public DbSet&lt;states&gt; 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 = &quot;test2&quot;;
            Country.states = new Collection&lt;states&gt;();
           
            var state = new states();
            state.stateName = &quot;st1&quot;;
            Country.states.Add(state);
            state = new states();
            state.stateName = &quot;st2&quot;;
           
            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 =&gt; d.states).FirstOrDefault(d=&gt;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 = &quot;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&quot;;
                SqlCommand objSqlCommand = new SqlCommand(_SqlCommand, con);
                SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
                try
                {
                    objSqlDataAdapter.Fill(dtPerson);

                }
                catch (Exception ex)
                {
                    con.Close();
                }
            }

            return dtPerson;

        }

        public  List&lt;Country&gt; getCountry( DataTable table)
        {


            var _listCountry =
               from p in table.AsEnumerable()
               group p by p.Field&lt;int&gt;(&quot;Id&quot;) into g
               select new Country
               {
                   Id = g.Key,
                   name = g.Select(i =&gt; i.Field&lt;string&gt;(&quot;Name&quot;)).FirstOrDefault(),
                   states = table.AsEnumerable()
                           .Where(row =&gt; g.Any(p =&gt; g.Key == row.Field&lt;int&gt;(&quot;CountryId&quot;)))
                           .Select(d =&gt; new states
                           {
                               stateName = d.Field&lt;string&gt;(&quot;stateName&quot;),
                               countryId = d.Field&lt;int&gt;(&quot;countryId&quot;)

                           }).ToList()
               };




            return _listCountry.ToList();

        }


    }

huangapple
  • 本文由 发表于 2023年6月18日 18:46:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/76500135.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定