I need to update a SQL Server database from multiple textboxes. I have two questions:
What's wrong with my code (maybe there is something wrong with my ID column? (it's autoincrementing)
Is my WHERE enough to find an exact match in Employee table for update?
Employee contains 7 columns:
Id (int), Name (nvarchar(max)), LastName(nvarcharmax), Age(int), Dep_nt(nvarchar(max)), Profession (nvarchar(max)), Salary (real).
My code:
private void button0_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var sql = String.Format("UPDATE Employee SET Name = @Name, LastName = @LastName, Age = @Age, Dep_nt = @Dep_nt, Profession = @Profession, Salary = @Salary WHERE ID = @ID", connection);
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@Name", NameF.Text);
command.Parameters.AddWithValue("@LastName", LastNameF.Text);
command.Parameters.AddWithValue("@Age", Convert.ToInt32(AgeF.Text));
command.Parameters.AddWithValue("@Dep_nt", DepartmentF.Text);
command.Parameters.AddWithValue("@Profession", ProfessionF.Text);
command.Parameters.AddWithValue("@Salary", Convert.ToDouble(SalaryF.Text));
//SqlParameter param = command.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
//param = command.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
//param.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand = command;
DataTable dt = new DataTable();
adapter.Fill(dt);
Ep.ItemsSource = dt.DefaultView;
connection.Close();
}
}
Currently, my code does absolutely nothing.
Full code of my application:
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
{
Logic L = new Logic();
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);
List<string> DepartmentNames = new List<string>();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
String DepartmentName = Convert.ToString(dataTable.Rows[i]["DepartmentName"]);
DepartmentNames.Add(DepartmentName);
}
Department4.ItemsSource = DepartmentNames;
DepartmentF.ItemsSource = DepartmentNames;
DpListBox.ItemsSource = dataTable.DefaultView;
L.InitReadOnly(true, Name4, LastName4, Age4, Department4, Profession4, Salary4);
L.InitReadOnly(true, NameF, LastNameF, AgeF, DepartmentF, ProfessionF, SalaryF);
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;
connection.Close();
}
}
/// <summary>
/// Выбран новый элемент ListBox для коллекции департаментов
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DpListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
L.InitReadOnly(false, Name4, LastName4, Age4, Department4, Profession4, Salary4);
L.InitReadOnly(false, NameF, LastNameF, AgeF, DepartmentF, ProfessionF, SalaryF);
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
DataRowView dataRowView = DpListBox.SelectedItem as DataRowView;
string value = "";
if (dataRowView != null)
{
value = dataRowView.Row["DepartmentName"] as string;
}
SqlCommand command = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt='" + value + "'", connection);
adapter.SelectCommand = command;
DataTable dataTable1 = new DataTable();
adapter.Fill(dataTable1);
Ep.ItemsSource = dataTable1.DefaultView;
connection.Close();
}
private void Button1_Click(object sender, RoutedEventArgs e)
{
var sql = String.Format("INSERT INTO Employee (Name, LastName, Age, Dep_nt, Profession, Salary) " + "VALUES (N'{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", Name4.Text, LastName4.Text, Age4.Text, Department4.Text, Profession4.Text, Salary4.Text);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
SqlConnection connection1 = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
DataRowView dataRowView = DpListBox.SelectedItem as DataRowView;
string value = "";
if (dataRowView != null)
{
value = dataRowView.Row["DepartmentName"] as string;
}
SqlCommand command1 = new SqlCommand($@"SELECT * FROM Employee WHERE Dep_nt='" + value + "'", connection1);
adapter.SelectCommand = command1;
DataTable dataTable1 = new DataTable();
adapter.Fill(dataTable1);
Ep.ItemsSource = dataTable1.DefaultView;
connection.Close();
}
}
private void button0_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var sql = String.Format("UPDATE Employee SET Name = @Name, LastName = @LastName, Age = @Age, Dep_nt = @Dep_nt, Profession = @Profession, Salary = @Salary WHERE ID = @ID", connection);
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@Name", NameF.Text);
command.Parameters.AddWithValue("@LastName", LastNameF.Text);
command.Parameters.AddWithValue("@Age", Convert.ToInt32(AgeF.Text));
command.Parameters.AddWithValue("@Dep_nt", DepartmentF.Text);
command.Parameters.AddWithValue("@Profession", ProfessionF.Text);
command.Parameters.AddWithValue("@Salary", Convert.ToDouble(SalaryF.Text));
SqlParameter param = command.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
param = command.Parameters.Add("@ID", SqlDbType.Int, 0, "ID");
param.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand = command;
DataTable dt = new DataTable();
adapter.Fill(dt);
Ep.ItemsSource = dt.DefaultView;
connection.Close();
}
}
private void Ep_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
L.InitReadOnly(false, NameF, LastNameF, AgeF, DepartmentF, ProfessionF, SalaryF);
DataRowView dataRowView = Ep.SelectedItem as DataRowView;
if (dataRowView != null)
{
NameF.Text = dataRowView.Row["Name"] as string;
LastNameF.Text = dataRowView["LastName"] as string;
AgeF.Text = Convert.ToString(dataRowView["Age"]);
DepartmentF.Text = dataRowView["Dep_nt"] as string;
ProfessionF.Text = dataRowView["Profession"] as string;
SalaryF.Text = Convert.ToString(dataRowView["Salary"]);
}
else
{
NameF.Text = "";
LastNameF.Text = "";
AgeF.Text = "";
DepartmentF.Text = "";
ProfessionF.Text = "";
SalaryF.Text = "";
}
}
}
}
I use WPF. XAML code:
<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="NameF" HorizontalAlignment="Left" Margin="164,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="LastNameF" HorizontalAlignment="Left" Margin="294,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="AgeF" HorizontalAlignment="Left" Margin="424,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<ComboBox x:Name="DepartmentF" HorizontalAlignment="Left" Margin="554,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="ProfessionF" HorizontalAlignment="Left" Margin="684,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="SalaryF" HorizontalAlignment="Left" Margin="814,418,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<Button x:Name="button0" HorizontalAlignment="Left" Margin="164,448,0,0" VerticalAlignment="Top" Width="120" Height="22" Background="LightBlue"
Content="Изменить данные" Click="button0_Click"/>
<TextBox x:Name="Name1" HorizontalAlignment="Left" Margin="164,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Имя" IsReadOnly="True"/>
<TextBox x:Name="LastName1" HorizontalAlignment="Left" Margin="294,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Фамилия" IsReadOnly="True"/>
<TextBox x:Name="Age1" HorizontalAlignment="Left" Margin="424,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Возраст" IsReadOnly="True"/>
<TextBox x:Name="Department1" HorizontalAlignment="Left" Margin="554,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Департамент" IsReadOnly="True"/>
<TextBox x:Name="Profession1" HorizontalAlignment="Left" Margin="684,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Профессия" IsReadOnly="True"/>
<TextBox x:Name="Salary1" HorizontalAlignment="Left" Margin="814,386,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Заработная плата" IsReadOnly="True"/>
<TextBox x:Name="Header1" HorizontalAlignment="Left" Margin="164,39,0,0" VerticalAlignment="Top" Width="250" Height="22" Text="Добавить сотрудника" IsReadOnly="True"/>
<TextBox x:Name="Name4" HorizontalAlignment="Left" Margin="164,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="LastName4" HorizontalAlignment="Left" Margin="294,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="Age4" HorizontalAlignment="Left" Margin="424,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<ComboBox x:Name="Department4" HorizontalAlignment="Left" Margin="554,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="Profession4" HorizontalAlignment="Left" Margin="684,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="Salary4" HorizontalAlignment="Left" Margin="814,99,0,0" VerticalAlignment="Top" Width="120" Height="22" IsReadOnly="False"/>
<TextBox x:Name="Name5" HorizontalAlignment="Left" Margin="164,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Имя" IsReadOnly="True"/>
<TextBox x:Name="LastName5" HorizontalAlignment="Left" Margin="294,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Фамилия" IsReadOnly="True"/>
<TextBox x:Name="Age5" HorizontalAlignment="Left" Margin="424,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Возраст" IsReadOnly="True"/>
<TextBox x:Name="Department5" HorizontalAlignment="Left" Margin="554,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Департамент" IsReadOnly="True"/>
<TextBox x:Name="Profession5" HorizontalAlignment="Left" Margin="684,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Профессия" IsReadOnly="True"/>
<TextBox x:Name="Salary5" HorizontalAlignment="Left" Margin="814,69,0,0" VerticalAlignment="Top" Width="120" Height="22" Text="Заработная плата" IsReadOnly="True"/>
<Button x:Name="button1" HorizontalAlignment="Left" Margin="164,129,0,0" VerticalAlignment="Top" Width="120" Height="22" Background="LightBlue"
Content="Добавить" Click="Button1_Click"/>
<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 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" SelectionChanged="Ep_SelectionChanged">
<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>
Correct code for this part:
private void button0_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataRowView dataRowView = Ep.SelectedItem as DataRowView;
cmd = new SqlCommand("UPDATE Employee SET Name = @Name, LastName = @LastName, Age = @Age, Dep_nt = @Dep_nt, Profession = @Profession, Salary = @Salary WHERE ID = @ID", connection);
connection.Open();
cmd.Parameters.AddWithValue("@ID", dataRowView.Row["Id"] as Nullable<int>);
cmd.Parameters.AddWithValue("@Name", NameF.Text);
cmd.Parameters.AddWithValue("@LastName", LastNameF.Text);
cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(AgeF.Text));
cmd.Parameters.AddWithValue("@Dep_nt", DepartmentF.Text);
cmd.Parameters.AddWithValue("@Profession", ProfessionF.Text);
cmd.Parameters.AddWithValue("@Salary", Convert.ToDouble(SalaryF.Text));
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
adapter.UpdateCommand = cmd;
adapter.Fill(dt);
Ep.ItemsSource = dt.DefaultView;
connection.Close();
}
}