C#中的SQL内部连接语句 [英] SQL Inner join statement in C#

问题描述

我正在做一个学校项目

我想获取乐队的所有信息，效果很好.直到我进入流派.流派有点特殊，因为一个乐队可以有多种流派，反之亦然.

I want to get all the information of the bands, which works fine. Until i get to genres. Genre is a bit special, because a band can have multiple genres and vice versa.

所以在 SQL 中我有一个表 Bands、一个表 Genre 和一个表 Band_Genre如果我在那里进行内部连接

So in SQL I have a table Bands, a table Genre and a table Band_Genre If i do the inner join there

SELECT * 
FROM Band 
INNER JOIN Band_Genre 
        ON Band.ID = Band_Genre.BandID

在 SQL 中一切正常，但是当我使用该语句尝试第二个读取器时，它返回 null.

Everything works fine in SQL but when i try a second reader with that statement it returns null.

但我现在想要做的是用流派本身而不是 ID 填充数据网格.所以我认为我需要从内部连接中获取 ID 并将其链接到 Genre.Name

But what i want to do now is fill the datagrid with the genres themselves, not the ID's. So what i think is that i need to get the ID out of the Inner join and link it to Genre.Name

也许这有点远，但是如果有人可以将 id 放入 ObservableCollection 中，我会很高兴.以便 Datagrid 显示流派:例如 1、3.

Maybe this goes a bit far but I would be happy if someone could already get the id's into the ObservableCollection. So that the Datagrid shows Genres: 1, 3 for example.

提前致谢

C#
//Property
private ObservableCollection<Genre> genres;

        public ObservableCollection<Genre> Genres
        {
            get { return genres; }
            set { genres = value; }
        }
//Method
public static ObservableCollection<Band> GetBands()
        {
            ObservableCollection<Band> list = new ObservableCollection<Band>();
            try
            {
                string provider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
                string connectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

                DbConnection con = DbProviderFactories.GetFactory(provider).CreateConnection();
                con.ConnectionString = connectionstring;
                con.Open();

                DbCommand command = DbProviderFactories.GetFactory(provider).CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.Connection = con;
                command.CommandText = "SELECT * FROM Band";

                //Bands ophalen
                DbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Band b = new Band()
                    {                       
                        ID = reader["ID"].ToString(),
                        Name = reader["Name"].ToString(),
                        Picture = reader["Picture"].ToString(),
                        Description = reader["Description"].ToString(),
                        Twitter = reader["Twitter"].ToString(),
                        Facebook = reader["Facebook"].ToString(),
                        //Genres = (Genre)reader["Genres"] lijst van genres kunnen doorgeven, probleem met meerdere genres per band. Kijken filmpje inner joint
                    };

                    if (!DBNull.Value.Equals(reader["Picture"]))
                    {
                        b.Picture = reader["Picture"].ToString();
                    }
                    else
                    {
                        b.Picture = null;
                    }
                    list.Add(b);
                }

                //Genres ophalen via INNER JOIN
                DbCommand command2 = DbProviderFactories.GetFactory(provider).CreateCommand();
                command2.CommandType = System.Data.CommandType.Text;
                command2.Connection = con;
                command2.CommandText = "SELECT * FROM Band INNER JOIN Band_Genre On Band.ID = Band_Genre.BandID";

                 //Bands ophalen
                DbDataReader reader2 = command2.ExecuteReader();
                while (reader2.Read())
                {
                    Genre g = new Genre()
                    {
                       ID = reader2["GenreID"].ToString(),
                       //Name = reader2[


                    };
                }

                reader.Close();
                reader2.Close();
                con.Close();
            }

XAML:
<DataGrid.Columns>
                <DataGridTextColumn Header="Band"  Binding="{Binding Name}"/>
                <DataGridTextColumn Header="Picture" Binding="{Binding Picture}" />
                <DataGridTextColumn Header="Description"  Binding="{Binding Description}"/>
                <DataGridTextColumn Header="Twitter" Binding="{Binding Twitter}" />
                <DataGridTextColumn Header="Facebook" Binding="{Binding Facebook}" />
                <DataGridTextColumn Header="Genres" Binding="{Binding Genres}" />
            </DataGrid.Columns>
        </DataGrid>

推荐答案

不太确定你想做什么...第二个读者不应该返回 null 除非你的表没有正确的数据 - 但它不会有流派信息.对于您想要内部加入流派的流派，不是吗?

Not quite sure what you want to do... The second reader should not be returning null unless your tables don't have the correct data in - but it won't have the genre info. For genres you want to inner join to Genre, don't you?

SELECT g.*
FROM Band b
INNER JOIN Band_Genre bg
    ON b.ID = bg.BandID
INNER JOIN Genre g on g.GenreID = bg.GenreID

这将返回您的流派信息.但是，如果您的第二个读取器为空，则看起来 Band_Genre 未填充或 ID 不正确，因此它无法加入您的 Band 表

This will return your Genre info. But if your second reader is null it looks like Band_Genre is not populated or has incorrect ID's so it can't join to your Band table

编辑

如果您希望在一个查询中全部显示:

If you want them all in one query:

SELECT b.*, g.*
FROM Band b
INNER JOIN Band_Genre bg
    ON b.ID = bg.BandID
INNER JOIN Genre g on g.GenreID = bg.GenreID

这篇关于C#中的SQL内部连接语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！