Simple movie database in C#

by the3factory 3/28/2008 8:12:00 PM

Introduction

For a while now I'm the member of CodeProject family, and I've never contributed any of my project. Since this site has been very helpful for me while learning C#, I've decided to share my first project in C#, it was written about one year ago when I joined here. Anyhow, this article is about simple database built in MS Access, and C#. I wanted to explore this because, end user doesn't need to install any SQL servers, he just needs executable binary and .mdb file. As I mentioned it is one of my first project, so it is not very advanced, but provided code snippets are useful, i.e. connecting to mdb file, read/write data, using dataGridView control, adding buttons to cells etc.

Using the code

First you need to create database in MS Access. The database is very simple and there for there is a lot of space for future work and improvements;

//  First table contains movies
//  where movieID is auto-increment value, also primary key, and typeID foreign key
//  movies (movieID, Title, Publisher, Previewed, Year, typeID) 
//  --------------------------------------------------------------- 
//  Second table contains movie types
//  movietypes (typeID, Type)

If you have bigger database in your mind, you can use some generator to create database, for small project like this, that kind of tools aren't necessary (also, try to keep it in 3NF).
Ok, now we have our database, MS Access allows us to add records, but we want to create our own front end. To be able to connect to database, and manipulate with records, it is necessary to use namespace System.Data.OleDb which will provide the required methods.

In the constructor of the inicial form, application connects to database using the following code:

        public Form1()
{
InitializeComponent();
// initiate DB connection
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=moviedb.mdb";
try
{
database = new OleDbConnection(connectionString);
database.Open();
//SQL query to list movies
string queryString = "SELECT movieID, Title, Publisher, Previewed, Year, Type
FROM movie,movieType WHERE movietype.typeID = movie.typeID";
loadDataGrid(queryString);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}

The method loadDataGrid loads the data from database to the dataGridView control, using the SQL query in the string variable queryString. Here is the implementation:

Collapse

public void loadDataGrid(string sqlQueryString) {
OleDbCommand SQLQuery = new OleDbCommand();
DataTable data = null;
dataGridView1.DataSource = null;
SQLQuery.Connection = null;
OleDbDataAdapter dataAdapter = null;
dataGridView1.Columns.Clear(); // <-- clear columns
SQLQuery.CommandText = sqlQueryString;
SQLQuery.Connection = database;
data = new DataTable();
dataAdapter = new OleDbDataAdapter(SQLQuery);
dataAdapter.Fill(data);
dataGridView1.DataSource = data;
dataGridView1.AllowUserToAddRows = false; // <-- remove the null line
dataGridView1.ReadOnly = true;          // <-- so the user cannot type 
dataGridView1.Columns[0].Visible = false; // following code defines column sizes
dataGridView1.Columns[1].Width = 340;
dataGridView1.Columns[3].Width = 55;
dataGridView1.Columns[4].Width = 50;
dataGridView1.Columns[5].Width = 80;
// insert edit button into datagridview
editButton = new DataGridViewButtonColumn();
editButton.HeaderText = "Edit";
editButton.Text = "Edit";
editButton.UseColumnTextForButtonValue = true;
editButton.Width = 80;
dataGridView1.Columns.Add(editButton);
// insert delete button to datagridview
deleteButton = new DataGridViewButtonColumn();
deleteButton.HeaderText = "Delete";
deleteButton.Text = "Delete";
deleteButton.UseColumnTextForButtonValue = true;
deleteButton.Width = 80;
dataGridView1.Columns.Add(deleteButton);
}

The interesting part of this code is adding buttons to dataGridView cells. Using this buttons, you can update or delete the selected row. The other way to do this is to place only two buttons outside the dataGridView control, and then select the row you want to edit/delete and press the button. Here, every row has it's own buttons.

The question remains how can I detect when the button is pressed, and where do I place my code to do some action. Well here is the way to do it:

Collapse

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
string queryString = "SELECT movieID, Title, Publisher, Previewed, Year, Type
FROM movie, movieType WHERE movietype.typeID = movie.typeID";
int currentRow = int.Parse(e.RowIndex.ToString());
try
{
string movieIDString = dataGridView1[0, currentRow].Value.ToString();
movieIDInt = int.Parse(movieIDString);
}
catch (Exception ex) { }
// edit button
if (dataGridView1.Columns[e.ColumnIndex] == editButton && currentRow >= 0)
{
string title = dataGridView1[1, currentRow].Value.ToString();
string publisher = dataGridView1[2, currentRow].Value.ToString();
string previewed = dataGridView1[3, currentRow].Value.ToString();
string year = dataGridView1[4, currentRow].Value.ToString();
string type = dataGridView1[5, currentRow].Value.ToString();
Form2 f2 = new Form2();
f2.title = title;
f2.publisher = publisher;
f2.previewed = previewed;
f2.year = year;
f2.type  = type;
f2.movieID = movieIDInt;
f2.Show();
dataGridView1.Update();
}
...

I used the CellContentClick event. So now, when the button is down, I need to know the selected row. I do this by using the e.RowIndex. Using this variable, you can fetch any column value of the selected row. As shown the first parameter is column index, and the second is row index. When the update of selected row is completed in Form2, now using the Update() method on the dataGridView1 object you can see the changes that were made.
The delete button works the same way.

            // delete button
else if (dataGridView1.Columns[e.ColumnIndex] == deleteButton && currentRow >= 0)
{
// delete sql query
string queryDeleteString = "DELETE FROM movie WHERE movieID = "+movieIDInt+"";
OleDbCommand sqlDelete = new OleDbCommand();
sqlDelete.CommandText = queryDeleteString;
sqlDelete.Connection = database;
sqlDelete.ExecuteNonQuery();
loadDataGrid(queryString);
}

Conclusion

This project shows a simple way how to use MS Access database and .NET controls to display stored data. It is shown here everything you need to start your own more advanced application for similar purpose. Project is for C# beginners, and with a little bit of imagination, it can be improved and useful. Hope you like it. Cheers!!

Tags: simple movie database