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.
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:
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.
- Go to References in the solution tree.
- Use your mouse right click button to select the Add Reference option.
- 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.
- 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
In the Tools menu, under the Data category, select and add to the form these components:
|It will be used as our link between the binding navigator and the data.|
|It will be used as our main control over the data.|
|This component will be used as our main data source.|
Now, under Custom Components, you must add these components:
|Our connection to the PG server.|
|This component will keep our controls connected to our table using the commands shown below.|
|This will be our Create command.|
|This will be our Retrieve command.|
|This will be our Delete command.|
|This will be our Update command.|
OK, after adding all this components, our form should look like this:
Establishing the properties
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:
- On the
bnavigator, set the
BindingSource property to
- On the
bsource, set the
DataSource property to
- 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).
- On the adapter, set the properties
UpdateCommand to their respective components,
- 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.
cmdSelect, set the
Command Text property to:
SELECT artistID,name FROM artists.
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.
cmdUpdate, set the
UPDATE artists SET name=:p_name WHERE artistID=:p_artistID.
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:
at the beginning of your Music.cs file, just after the other
Now, let's see the
void MainFormLoad(object sender, EventArgs e)
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
You can add this image:
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)
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.