WPF CRUD With DataGrid, RepoDB ORM And C#.NET

Good day Gents,

Here's a basic tutorial on how to integrate the RepoDB ORM into your WPF desktop application. I already have written an article in ASP.NET MVC web application here using the said ORM and I realized that I have to create an example for the desktop as well. To proceed working with the code, you need to run the create table script from this post WPF CRUD With DataGrid, Entity Framework And C#.NET. Next is to add a model class that maps the information from the database table to an object or list of objects.

public class Students
{
	public int ID { get; set; }
	public string Name { get; set; }
	public int Age { get; set; }
	public string Address { get; set; }
	public string Contact { get; set; }

	public Students()
	{
		ID = 0;
		Name = string.Empty;
		Age = 0;
		Address = string.Empty;
		Contact = string.Empty;
	}
}

Add an interface to the project that has the method signatures used in the actual repository class. The interface methods are the most common CRUD (Create/Read/Update/Delete) functions such as Get(), GetAll(), Add(), Update() and Remove() which is the delete action.

public interface IStudentRepository
{
	Students Get(int id);
	List<Students> GetAll();
	void AddStudent(Students student);
	void UpdateStudent(Students student);
	void RemoveStudent(int id);
}

Then create the actual repository class that implements the methods of the interface class. This is where the RepoDB Orm's underlying CRUD methods are actually put in use. The code below is pretty easy to understand given that you have enough background with other ORMS or have worked with SQL for a couple of years.

public class StudentRepository : IStudentRepository
{
	private readonly string connectionString;

	public StudentRepository()
	{
		this.connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
	}

	public void AddStudent(Students student)
	{
		int id = 0;

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				var item = new Students()
				{
					Name = student.Name,
					Age = student.Age,
					Address = student.Address,
					Contact = student.Contact
				};

				id = (int)connection.Insert<Students>(item);
			}
		}
		catch (Exception ex)
		{                
		}
	}

	public Students Get(int id)
	{
		Students result;

		result = new Students();

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				result = connection.Query<Students>(x => x.ID == id).FirstOrDefault();
			}
		}
		catch (Exception ex)
		{
			return null;
		}

		return result;
	}

	public List<Students> GetAll()
	{
		List<Students> lstStudents;

		lstStudents = new List<Students>();

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				connection.Open();
				lstStudents = connection.QueryAll<Students>().ToList();
			}
		}
		catch (Exception ex)
		{
			return null;
		}

		return lstStudents;
	}

	public void RemoveStudent(int id)
	{
		int affectedRows = 0;

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				affectedRows = (int)connection.Delete<Students>(id);
			}
		}
		catch (Exception ex)
		{                
		}
	}

	public void UpdateStudent(Students student)
	{
		int affectedRows = 0;

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				var item = new Students()
				{
					ID = student.ID,
					Name = student.Name,
					Age = student.Age,
					Address = student.Address,
					Contact = student.Contact
				};

				affectedRows = (int)connection.Update<Students>(item);
			}
		}
		catch (Exception ex)
		{
			 
		}
	}
}

Next is to modify the XAML of MainWindow page by adding a Datagrid, TextBox and Button controls that is responsible for showing the records to the user, adding of new record, editing of record and deleting a record.

<StackPanel Orientation="Vertical">
	<GroupBox Header="Student Form" Margin="10">
		<Grid Height="150">
			<Grid.RowDefinitions>
				<RowDefinition Height="1*"/>
				<RowDefinition Height="1*"/>
				<RowDefinition Height="1*"/>
				<RowDefinition Height="1*"/>
			</Grid.RowDefinitions>
			<Grid.ColumnDefinitions>
				<ColumnDefinition Width="100"/>
				<ColumnDefinition Width="*"/>
			</Grid.ColumnDefinitions>
			<Label Content="Name" HorizontalAlignment="Left" 
				   VerticalContentAlignment="Center" Grid.Column="0" Grid.Row="0"/>
			<TextBox Grid.Row="0" Grid.Column="1" x:Name="TextBoxName" Height="27" 
				   Margin="5"  Width="300" HorizontalAlignment="Left"/>
			<Label Content="Age" HorizontalAlignment="Left" VerticalContentAlignment="Center" 
				   Grid.Row="1" Grid.Column="0"/>
			<TextBox Grid.Row="1" Grid.Column="1" x:Name="TextBoxAge" Height="27" 
				   Margin="5" Width="70" HorizontalAlignment="Left"/>
			<Label Content="Address" HorizontalAlignment="Left" VerticalContentAlignment="Center" 
				   Grid.Row="2" Grid.Column="0" />
			<TextBox Grid.Row="2" Grid.Column="1" x:Name="TextBoxAddress" Height="27" 
				   Margin="5" Width="300" HorizontalAlignment="Left"/>
			<Label Content="Contact" HorizontalAlignment="Left" VerticalContentAlignment="Center" 
				   Grid.Row="3" Grid.Column="0" />
			<TextBox Grid.Row="3" Grid.Column="1" x:Name="TextBoxContact" Height="27"
				   Margin="5" Width="300" HorizontalAlignment="Left"/>
		</Grid>
	</GroupBox>
	<StackPanel Height="40" Orientation="Horizontal" HorizontalAlignment="Right">
		<Button x:Name="ButtonSave" Content="Save" Height="30" Width="80" Click="ButtonSave_OnClick"/>
		<Button x:Name="ButtonCancel" Content="Cancel" Height="30" Width="80" Margin="5,0,10,0" Click="ButtonCancel_Click"/>
	</StackPanel>
	<StackPanel Height="210">
		<DataGrid x:Name="DataGridStudents" AutoGenerateColumns="False"
				  CanUserAddRows="False" Height="200" Margin="10">
			<DataGrid.Columns>
				<DataGridTextColumn Header="Name" Binding="{Binding Path=ID}" Visibility="Hidden"/>
				<DataGridTextColumn Header="Name" Binding="{Binding Path=Name}" Width="100"  IsReadOnly="True"/>
				<DataGridTextColumn Header="Age" Binding="{Binding Path=Age}" Width="50"  IsReadOnly="True"/>
				<DataGridTextColumn Header="Address" Binding="{Binding Path=Address}" Width="180" IsReadOnly="True"/>
				<DataGridTextColumn Header="Contact" Binding="{Binding Path=Contact}" Width="125" IsReadOnly="True"/>
				<DataGridTemplateColumn Width="50">
					<DataGridTemplateColumn.CellTemplate>
						<DataTemplate>
							<Button Content="Select" x:Name="ButtonEdit" 
									Click="ButtonEdit_OnClick"/>
						</DataTemplate>
					</DataGridTemplateColumn.CellTemplate>
				</DataGridTemplateColumn>
				<DataGridTemplateColumn Width="50">
					<DataGridTemplateColumn.CellTemplate>
						<DataTemplate>
							<Button Content="Delete" x:Name="ButtonDelete"
									Click="ButtonDelete_OnClick"/>
						</DataTemplate>
					</DataGridTemplateColumn.CellTemplate>
				</DataGridTemplateColumn>
			</DataGrid.Columns>
		</DataGrid>
	</StackPanel>
</StackPanel>

The code behind of the MainWindow page will initilize an instance of the StudentRepository class and contains the events of the button controls and the main window itself. The constructor has an important piece of code which is to call RepoDB's SqlServerBootstrap Initialize method. Without this snippet, you won't be able to use the underlying methods for MSSQL database and it will throw an error when compiling the app.

/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
	private StudentRepository studentRepository;

	private int Id
	{
		get;
		set;
	}

	public MainWindow()
	{
		InitializeComponent();
		RepoDb.SqlServerBootstrap.Initialize();
		studentRepository = new StudentRepository();
	}

	private void Window_Loaded(object sender, RoutedEventArgs e)
	{
		Id = -1;
		PopulateGrid();
	}

	private void PopulateGrid()
	{
		DataGridStudents.ItemsSource = studentRepository.GetAll();
	}

	private void ButtonEdit_OnClick(object sender, RoutedEventArgs e)
	{
		var student = ((FrameworkElement)sender).DataContext as Students;
		if (student != null)
		{
			TextBoxName.Text = student.Name;
			TextBoxAge.Text = student.Age.ToString();
			TextBoxAddress.Text = student.Address;
			TextBoxContact.Text = student.Contact;
			this.Id = student.ID;
		}
	}

	private void ButtonCancel_Click(object sender, RoutedEventArgs e)
	{
		ResetControls();
	}

	private void ResetControls()
	{
		this.Id = -1;
		TextBoxName.Text = string.Empty;
		TextBoxAge.Text = string.Empty;
		TextBoxAddress.Text = string.Empty;
		TextBoxContact.Text = string.Empty;
	}

	private void ButtonSave_OnClick(object sender, RoutedEventArgs e)
	{
		if (!string.IsNullOrEmpty(TextBoxAddress.Text) && !string.IsNullOrEmpty(TextBoxAge.Text)
			&& !string.IsNullOrEmpty(TextBoxContact.Text) && !string.IsNullOrEmpty(TextBoxName.Text))
		{
			try
			{
				Students student = new Students()
				{
					Address = TextBoxAddress.Text,
					Age = Convert.ToInt32(TextBoxAge.Text),
					Contact = TextBoxContact.Text,
					Name = TextBoxName.Text
				};

				if (this.Id <= 0) //save
				{
					studentRepository.AddStudent(student);
					MessageBox.Show("New record successfully saved.");
				}
				else //save
				{
					student.ID = this.Id;
					studentRepository.UpdateStudent(student);
					MessageBox.Show("Record successfully updated.");
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show("An error occured unable to save record!", "", MessageBoxButton.OK, MessageBoxImage.Error);
				Debug.Print(ex.Message);
			}
			finally
			{
				ResetControls();
				PopulateGrid();
			}
		}
	}

	private void ButtonDelete_OnClick(object sender, RoutedEventArgs e)
	{
		if (MessageBox.Show("Confirm delete of this record?", "Student", MessageBoxButton.YesNo)
			== MessageBoxResult.Yes)
		{
			var student = ((FrameworkElement)sender).DataContext as Students;
			if (student != null)
			{
				try
				{
					studentRepository.RemoveStudent(student.ID);
					MessageBox.Show("Record successfully deleted");
				}
				catch (Exception)
				{
					MessageBox.Show("An error occured. Unable to delete record!", "", MessageBoxButton.OK,
						MessageBoxImage.Error);
				}
				finally
				{
					ResetControls();
					PopulateGrid();
				}
			}
		}
	}
}

Output

That's it!