A CRUD Form using SharpDevelop and PostgreSQL

• Download demo project - 278 KB
• Download source - 11.3 KB

Introduction

This article is a tutorial on how to create a CRUD (Create, Retrieve, Update and Delete) Form using SharpDevelop and PostgreSQL, implementing a BindingNavigator to explore the table used in the application. We will be using SharpDevelop 3.1, PostgreSQL 8.3 for Windows, and of course Npgsql so we can connect our application to the PostgreSQL Server.

Background

BindingNavigator is a very useful control, it can be used in a C# form to show and edit data from a SQL source or just a data repository (XML, text files); however, most tutorials and samples are oriented to Visual Studio and database servers as MS SQL Server. With this tutorial, you will be able to build a useful form with a BindingNavigator, fully functional, but using the exceptional IDE SharpDevelop.

What do you need?

You will need .NET Framework installed (of course), SharpDevelop 3.1 (at the time of writing this article, the stable and latest release of SharpDevelop is 3.1), PostgreSQL 8.3 for Windows, and a good database admin tool to create and edit the database schema: pgAdmin 3 is a good choice, and it comes included with the Postgres installer; you will need the Npgsql, but it's included in the Zip file linked in this article.

Creating the database

We will create a small database with a single table. Our table is called artists and will be used to store our main data set. You will need to first create the database in PgAdmin; in our example, our database will be called music.

Then, you should create the table that will be used in our form:

/* This is our table to store the artists info, 
   we define a primary key so we can edit our data in PgAdmin */
CREATE TABLE artists
(
 artistid integer NOT NULL,
 name character varying NOT NULL DEFAULT 100,
 CONSTRAINT pk_artist PRIMARY KEY (artistid)
);

Now you should insert a couple of rows in the table, so you can eventually test the ability to edit data in our form. You can insert data using the PgAdmin data editor:

Now that our database is ready, we can create our SharpDevelop project.

Create the solution

Using SharpDevelop, create a new solution, choose "Window Application" under the C# category; in our example, the solution name will be Music. As we will be using PostgreSQL as our database server, we must add the Npgsql library to our references.

1. Go to References in the solution tree.
2. Use your mouse right click button to select the Add Reference option.
3. In the .NET Assembly Browser tab, look for the Npgsql DLL files (npgsql.dll and Mono.Security.dll); you can use the files inside the Zip file included with this article, or you can download the latest Npgsql binary available at the Npgsql website.
4. You must compile your solution so the Npgsql components will be available at design time in the form designer.

Now go to the form design window. Here, we are going to add our main components that will be used in the form. Select and add two Label components, and add two TextBoxes; in our example, the name of our TextBoxes will be t_artistID and t_name.

In the Tools menu, under the Data category, select and add to the form these components:

Component	Name	Required for
BindingSource	bsource	It will be used as our link between the binding navigator and the data.
BindingNavigator	bnavigator	It will be used as our main control over the data.
DataSet	mainDS	This component will be used as our main data source.

Now, under Custom Components, you must add these components:

Component	Name	Required for
NpgsqlConnection	connection	Our connection to the PG server.
NpgsqlDataAdapter	adapter	This component will keep our controls connected to our table using the commands shown below.
NpgsqlCommand	cmdSelect	This will be our Create command.
NpgsqlCommand	cmdInsert	This will be our Retrieve command.
NpgsqlCommand	cmdDelete	This will be our Delete command.
NpgsqlCommand	cmdUpdate	This will be our Update command.

OK, after adding all this components, our form should look like this:

We must set the component properties so that our binding navigator can use our binding source to explore our data, our binding source can get the data from the dataset, the dataset can be filled using the data adapter, the adapter uses our commands to insert, update, delete, and select, and then our commands can access the database using our connection. Pretty simple, uh?, OK, let's start:

1. On the BindingNavigator bnavigator, set the BindingSource property to bsource.
2. On the BindingSource bsource, set the DataSource property to mainDS.
3. On the connection, set the ConnectionString property to something like: HOST=localhost;DATABASE=music;USER ID=postgres;PASSWORD=admin (change this according to your server host, database, and user info).
4. On the adapter, set the properties DeleteCommand, InsertCommand, SelectCommand, and UpdateCommand to their respective components, cmdDelete, cmdInsert, cmdSelect, and cmdUpdate.



5. Now we must edit the properties for the commands, each one according to the function; on the four components, we must set the Connection property to use our connection component.
• On cmdSelect, set the Command Text property to: SELECT artistID,name FROM artists.
• On cmdInsert, set the Command Text property to: INSERT INTO artists (artistID,name) VALUES (:p_artistID , :p_name). Note: The :p_xxx are the parameters used by the SQL commands; soon, we will bind these parameters to the TextBox elements in the form.
• On cmdUpdate, set the Command Text property to: UPDATE artists SET name=:p_name WHERE artistID=:p_artistID.
• On cmdDelete, set the Command Text property to: DELETE FROM artists WHERE artistID=:p_artistID.

OK, that's it, our properties are set. Now we must enter the code that will be used to bind our controls, double click over the form to open the MainFormLoad Event Handler, then we must write our small fragment of code.

You must add:

Using Npgsql;

at the beginning of your Music.cs file, just after the other using clauses.

Now, let's see the MainFormLoad code:

void MainFormLoad(object sender, EventArgs e)
{
 // We fill our DataSet and we set the table in the Dataset as "artists"

 adapter.Fill(mainDS,"artists");

 // We must set the DataMember property in the BindingSource
 // it cannot be set at the designer since our DataSet isn't populated

 bsource.DataMember="artists";

 // We add the DataBinding to the TextBox linked to the "Text" property
 // and linked to the BindingSource

 t_artistID.DataBindings.Add("Text",bsource,"artistid");
 t_name.DataBindings.Add("Text",bsource,"name");

 // Now we must set the Relation between the parameters and the fields 
 // in the Database

 cmdInsert.Parameters.Add(new NpgsqlParameter("p_artistID", 
                          NpgsqlTypes.NpgsqlDbType.Integer,0,"artistid"));
 cmdInsert.Parameters.Add(new NpgsqlParameter("p_name", 
                          NpgsqlTypes.NpgsqlDbType.Varchar,0,"name"));
 cmdDelete.Parameters.Add(new NpgsqlParameter("p_artistID", 
                          NpgsqlTypes.NpgsqlDbType.Integer,0,"artistid"));
 cmdUpdate.Parameters.Add(new NpgsqlParameter("p_artistID", 
                          NpgsqlTypes.NpgsqlDbType.Integer,0,"artistid")); 
 cmdUpdate.Parameters.Add(new NpgsqlParameter("p_name", 
                          NpgsqlTypes.NpgsqlDbType.Varchar,0,"name"));
}

Please note that on every command, we must set the parameters used by this command, and take special care of setting their respective DBTypes according to the data type in the database.

That's it, we're almost done; the only thing that we must add is a Save button, because our Insert, Delete, and Update operations won't be sent to the database if we don't save our current data.

Go to the Toolbar which represents our BindingNavigator and add a new Button:

You can add this image:

using the Image property of the newly created button. Now, double click over the new button to open the button event handler:

void ToolStripButton1Click(object sender, EventArgs e)
{
    // We call this method to officially end the editing of the Bindingsource
    bsource.EndEdit();
    
    // Now we will try to update our table
    try {
        adapter.Update(mainDS.Tables["artists"]);
    }
    
    // We must catch the Exception, because the user may cause a SQL exception 
    catch (Exception e_exception)
    {
        MessageBox.Show("Error.... "+e_exception.Message,"Error trying to commit");
    }
}

That's it, we're done. Now we can Create, Retrieve, Update, and Delete data using our new form.