c# - System.Data.SqlClient.SqlException : "The multi-part identifier "System. Data.DataRowView“无法绑定(bind)。”

Question

我的数据库有问题。我有两个表 Employee 和 Department。

Employee 包含 7 列:

Id (int), Name (nvarchar(max)), LastName(nvarcharmax), Age(int), Dep_nt(nvarchar(max)), Profession (nvarchar(max)), Salary (real). 

Department 包含两列:

Id (int), DepartmentName(nvarchar(max)

我有一个 DpListBox (ListBox)，其中包含有关部门的信息，以及 Ep (Listview)，其中包含有关在这些部门工作的员工的信息。

我需要根据从 DpListBox 中选择的值填充 Ep。主要问题在于这行代码:

SqlCommand command = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt=" + DpListBox.SelectedValue.ToString(), connection);

我认为 DpListBox.SelectedValue = System.Data.DataRowView 因为我的数据表包含两个字段 Id 和 DepartmentName 我只需要在这里部门名称。

下面是我的代码:

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace BigCompanyinc
{
    /// <summary>
    /// Логика взаимодействия для MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Hospital;Integrated Security=True;Pooling=False";

        SqlDataAdapter adapter = new SqlDataAdapter();

        public MainWindow()
        {
            InitializeComponent();
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand("SELECT DepartmentName FROM Department ", connection);
            adapter.SelectCommand = command;
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            DpListBox.ItemsSource = dataTable.DefaultView;
            MessageBox.Show("Выберите департамент, чтобы начать работу.");
        }



        /// <summary>
        /// Добавить новый департамент
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button2_Click(object sender, RoutedEventArgs e)
        {
            var sql = String.Format("INSERT INTO Department (DepartmentName) " + "VALUES (N'{0}')",
            Name7.Text);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                command = new SqlCommand(@"UPDATE Deparment SET DepartmentName = @DepartmentName WHERE ID =@ID", connection);
                command.Parameters.Add("@DepartmentName", SqlDbType.NVarChar, -1, "DepartmentName");
                SqlParameter param = command.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
                param.SourceVersion = DataRowVersion.Original;
                adapter.UpdateCommand = command;
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                DpListBox.ItemsSource = dataTable.DefaultView;

            }
        }


        /// <summary>
        /// Выбран новый элемент ListBox для коллекции департаментов
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DpListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataAdapter adapter = new SqlDataAdapter();
            Console.WriteLine(DpListBox.SelectedValue.ToString());
            SqlCommand command = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt=" + DpListBox.SelectedValue.ToString(), connection);
            adapter.SelectCommand = command;
            DataTable dataTable1 = new DataTable();
            adapter.Fill(dataTable1);
            Ep.ItemsSource = dataTable1.DefaultView;
        }
    }
}

我使用 WPF，这里是 XAML 代码:

<Window x:Name="Staff" x:Class="BigCompanyinc.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:BigCompanyinc"
        mc:Ignorable="d"
        Title="Staff" Height="513.5" Width="991.833" ResizeMode="NoResize"
        Icon="icon1.ico">
    <Grid Height="504" VerticalAlignment="Top" Margin="10,0,-23,-19">
        <Grid.RowDefinitions>
        </Grid.RowDefinitions>
        <TextBox x:Name="Header2" HorizontalAlignment="Left" Margin="10,39,0,0" VerticalAlignment="Top" Width="144" Height="22" Text="Добавить департамент" IsReadOnly="True"/>
        <TextBox x:Name="Name6" HorizontalAlignment="Left" Margin="10,69,0,0" VerticalAlignment="Top" Width="144" Height="22" Text="Название" IsReadOnly="True"/>
        <TextBox x:Name="Name7" HorizontalAlignment="Left" Margin="10,99,0,0" VerticalAlignment="Top" Width="144" Height="22" IsReadOnly="False"/>
        <Button  x:Name="button2" HorizontalAlignment="Left" Margin="10,129,0,0" VerticalAlignment="Top" Width="70" Height="22" Background="LightBlue" 
            Content="Добавить" Click="Button2_Click"/>

        <TextBox x:Name="Department2" HorizontalAlignment="Left" Margin="10,12,0,0" VerticalAlignment="Top" Width="144" Height="22" Text="Департамент" IsReadOnly="True"/>
        <ListBox ItemsSource="{Binding Dep}" SelectedItem="DepartmentName" x:Name="DpListBox" HorizontalAlignment="Left" Margin="10,165,0,138" Width="144" SelectionChanged="DpListBox_SelectionChanged">
            <ListBox.ItemTemplate>
                <DataTemplate>
                    <TextBlock Text="{Binding DepartmentName}"/>
                </DataTemplate>
            </ListBox.ItemTemplate>
        </ListBox>
        <ListView x:Name="Ep" HorizontalAlignment="Left" Height="201" Margin="164,165,0,0" VerticalAlignment="Top" Width="791">
            <ListView.View>
                <GridView>
                    <GridViewColumn Width="130" Header="Имя" DisplayMemberBinding="{Binding Name}"/>
                    <GridViewColumn Width="130" Header="Фамилия" DisplayMemberBinding="{Binding LastName}"/>
                    <GridViewColumn Width="130" Header="Возраст" DisplayMemberBinding="{Binding Age}"/>
                    <GridViewColumn Width="130" Header="Департамент" DisplayMemberBinding="{Binding Dep_nt}"/>
                    <GridViewColumn Width="130" Header="Профессия" DisplayMemberBinding="{Binding Profession}"/>
                    <GridViewColumn Width="130" Header="Заработная плата" DisplayMemberBinding="{Binding Salary}"/>
                </GridView>
            </ListView.View>
        </ListView>
    </Grid>
</Window>

Answer 1

您需要执行以下操作才能从 DataRowView 获取列值。

DataRowView dataRowView = DpListBox.SelectedItem as DataRowView;

string value = "";

if (dataRowView != null) {
   value = dataRowView.Row["DepartmentName"] as string;
}

然后您必须将 value 而不是 DpListBox.SelectedValue.ToString() 传递给您的查询。

SqlCommand command = new SqlCommand(@"SELECT * FROM Employee WHERE Dep_nt='" + value + "'", connection);

注意:始终尝试使用准备好的语句(参数化查询)这将防止 SQL 注入(inject)。

因此参数化查询将是。

SqlCommand command = new SqlCommand(@"SELECT * FROM Employee WHERE Dep_nt=@Dep_nt", connection);
command.Parameters.AddWithValue("@Dep_nt", value);