Shubham Gupta

WPF DataGrid Example Please Help Me For This

Aug 27 2016 3:16 PM

I have two tables :-

1- UnitOfMeasurment ( Id, Units)

2- Items (Name,Units,Price)

DataGrid have 3 Columns textcolumn , comboboxcolumn , and textcolumn

i want to display data from Items to DataGrid and also add,edit,delete items form DataGrid to Items

and in DataGridComboBox items Fecth From UnitOfMeasurment

i try this-

------------XAML CODE---------

----------------------------.CS CODE--------------------------

using System;
using System.Collections.Generic;
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.Shapes;
using System.Data;
using System.Data.SqlServerCe;
using Gupta.Shubham.Billing.Classes;
using System.Media;

namespace Gupta.Shubham.Billing
{
/// <summary>
/// Interaction logic for AddItemWindow.xaml
/// </summary>
public partial class AddItemWindow : Window
{

private DataTable unitsTable;
private bool toCloseForm = false;
private int lastUoMAssigned = 0;
public AddItemWindow()
{
InitializeComponent();
}

private void Window_Loaded(object sender, RoutedEventArgs e)
{
Uri iconuri = new Uri("pack://application:,,,/Resources/invoiceicon.ico", UriKind.RelativeOrAbsolute);
this.Icon = BitmapFrame.Create(iconuri);

if (!loadData())
{
return;
}
ConfigureGridColumns();
}

private void ConfigureGridColumns()
{
UoMID.ItemsSource = unitsTable.DefaultView;

}
private bool loadData()
{
string errorText;
SqlCeConnection connection = Global.getDatabaseConnection(out errorText);

if (errorText != null)
{
Global.DisplayConnectionErrorMessage();
return false;
}

return loadData(connection);
}

private bool loadData(SqlCeConnection connection)
{
try
{
unitsTable = loadUnits(connection);
if (unitsTable.Rows.Count == 0)
{
string message = "No unit of measurement (UoM) has been defined so far. " +
"\nThe items can only be defined after 1 or more UoM has been defined.";
System.Media.SystemSounds.Exclamation.Play();
MessageBox.Show(message, "UoM Not Defined", MessageBoxButton.OK,
MessageBoxImage.Exclamation);
toCloseForm = true;
return false;
}

using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "Items";
command.CommandType = CommandType.TableDirect;

using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(command))
{
DataTable table = new DataTable("Items");
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.FillSchema(table, SchemaType.Source);

adapter.Fill(table);
itemsdata.ItemsSource = table.DefaultView;
table.ColumnChanged += new DataColumnChangeEventHandler(table_ColumnChanged);
}
}
}
catch (Exception ex)
{
string message = "An error occurred in fetching the data from the database." +
"\nThe error text is as follows:\n" + Global.getExceptionText(ex);
SystemSounds.Exclamation.Play();
MessageBox.Show(message, "Error in Fetching Data", MessageBoxButton.OK, MessageBoxImage.Error);
ErrorLogger.LogError(ex);
toCloseForm = true;
return false;
}

return true;
}

private void table_ColumnChanged(object sender, DataColumnChangeEventArgs e)
{
if (e.Column.ColumnName != "UoMID")
{
return;
}

lastUoMAssigned = (int)e.ProposedValue;
System.Diagnostics.Debug.WriteLine("value = " + lastUoMAssigned);
}

private DataTable loadUnits(SqlCeConnection connection)
{
DataTable table = new DataTable("UnitOfMeasurement");

using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "UnitOfMeasurement";
command.CommandType = CommandType.TableDirect;

using (SqlCeDataReader reader = command.ExecuteReader())
{
table.Load(reader);
}
}

return table;
}

private void itemsdata_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
dltbtn.IsEnabled = (itemsdata.Items.Count > 0) ? true : false;
}

private void filtertxt_TextChanged(object sender, TextChangedEventArgs e)
{
string text = filtertxt.Text.Trim();
DataTable table = ((DataView)itemsdata.ItemsSource).ToTable();

if (text.Length == 0)
{
table.DefaultView.RowFilter = string.Empty;
}
else
{
table.DefaultView.RowFilter = "Name Like '*" + text + "*'";
}
}

private void Window_ContentRendered(object sender, EventArgs e)
{

if (toCloseForm)
{
this.Close();
}
}

private void okbtn_Click(object sender, RoutedEventArgs e)
{
Mouse.OverrideCursor = Cursors.Wait;

if (!saveData())
{
return;
}

Mouse.OverrideCursor = null;
this.Close();
}

private void dltbtn_Click(object sender, RoutedEventArgs e)
{
int selectedRowsCount = itemsdata.SelectedItems.Count;
string message = "Are you sure that you want to delete the selected ";

if (selectedRowsCount > 1)
{
message += selectedRowsCount + " rows?";
}
else
{
message += "row?";
}

MessageBoxResult result = MessageBox.Show(message, "Confirm Deletion", MessageBoxButton.YesNo,
MessageBoxImage.Question);
if (result == MessageBoxResult.No)
{
return;
}

if (itemsdata.SelectedItem != null)
{
((DataRowView)(itemsdata.SelectedItem)).Row.Delete();
}
}

private string replaceColumnNamesWithHelpfulNames(string message)
{
return message.Replace("NAME", "Item Name")
.Replace("UoMID", "Measurement Unit")
.Replace("UnitPrice", "Unit Price");
}

private bool saveData()
{
string errorText;
SqlCeConnection connection = Global.getDatabaseConnection(out errorText);

if (errorText != null)
{
Global.DisplayConnectionErrorMessage();
return false;
}

return saveData(connection);
}

private bool saveData(SqlCeConnection connection)
{
SqlCeTransaction transaction = null;

try
{
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "Items";
command.CommandType = CommandType.TableDirect;

using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(command))
{
adapter.InsertCommand = getInsertCommand(connection);
adapter.UpdateCommand = getUpdateCommand(connection);
adapter.DeleteCommand = getDeleteCommand(connection);

adapter.AcceptChangesDuringUpdate = false;

DataTable table = ((DataView)itemsdata.ItemsSource).ToTable();

transaction = connection.BeginTransaction();
adapter.InsertCommand.Transaction = transaction;
adapter.UpdateCommand.Transaction = transaction;
adapter.DeleteCommand.Transaction = transaction;

adapter.Update(table);

transaction.Commit();
table.AcceptChanges();
}
}
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
string message = "An error occurred in saving the data. \n" +
"The error text is as follows:\n" + Global.getExceptionText(ex);
SystemSounds.Exclamation.Play();
Mouse.OverrideCursor = null;
MessageBox.Show(message, "Error in Saving Data", MessageBoxButton.OK,
MessageBoxImage.Error);
ErrorLogger.LogError(ex);
return false;
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
}

return true;
}

private SqlCeCommand getInsertCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO Items (Name, UoMID, UnitPrice) "
+ "VALUES (@p1, @p2, @p3)";

SqlCeParameterCollection parameters = command.Parameters;

SqlCeParameter parameter = new SqlCeParameter();
parameter.ParameterName = "@p1";
parameter.SourceColumn = "Name";
parameter.SqlDbType = SqlDbType.NVarChar;
parameters.Add(parameter);

parameter = new SqlCeParameter();
parameter.ParameterName = "@p2";
parameter.SourceColumn = "UoMID";
parameter.SqlDbType = SqlDbType.Int;
parameters.Add(parameter);

parameter = new SqlCeParameter();
parameter.ParameterName = "@p3";
parameter.SourceColumn = "UnitPrice";
parameter.SqlDbType = SqlDbType.Money;
parameters.Add(parameter);

return command;
}

private SqlCeCommand getUpdateCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "UPDATE Items SET Name = @p1, UoMID = @p2, " +
"UnitPrice = @p3 WHERE ID = @p4";

SqlCeParameterCollection parameters = command.Parameters;

SqlCeParameter parameter = new SqlCeParameter();
parameter.ParameterName = "@p1";
parameter.SourceColumn = "Name";
parameter.SqlDbType = SqlDbType.NVarChar;
parameters.Add(parameter);

parameter = new SqlCeParameter();
parameter.ParameterName = "@p2";
parameter.SourceColumn = "UoMID";
parameter.SqlDbType = SqlDbType.Int;
parameters.Add(parameter);

parameter = new SqlCeParameter();
parameter.ParameterName = "@p3";
parameter.SourceColumn = "UnitPrice";
parameter.SqlDbType = SqlDbType.Money;
parameters.Add(parameter);

parameter = new SqlCeParameter();
parameter.ParameterName = "@p4";
parameter.SourceColumn = "ID";
parameter.SqlDbType = SqlDbType.Int;
parameter.SourceVersion = DataRowVersion.Original;
parameters.Add(parameter);

return command;
}

private SqlCeCommand getDeleteCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM ITEMS WHERE ID = @p1";

SqlCeParameterCollection parameters = command.Parameters;

SqlCeParameter parameter = new SqlCeParameter();
parameter.ParameterName = "@p1";
parameter.SourceColumn = "ID";
parameter.SqlDbType = SqlDbType.Int;
parameter.SourceVersion = DataRowVersion.Default;
parameters.Add(parameter);

return command;
}

}

}

and also i am giving XAML & .CS file.

please help me this code work well in winform application but in wpf create problem.

Attachment: WPF.zip

Answers (4)

Clickonce Deployement - Oracle database connection failure

TextBox Binding in XAML