Quickest Way to Create a .NET Database Application

1. Introduction

1.1 Summary

This article shows you the quickest way to create a C# .NET database application using .NET Framework 4.0, Visual Studio 2010 and SQL Server 2008 Express. 

1.2 Purpose

There are many approaches to creating a database application using .NET. This article concentrates on demonstrating the fastest approach available. In addition, it concentrates on utilizing the power of .NET by writing as little code as possible. For those interested in source code, you can download it from here. 

2. Programming Language and Tools

1. .NET Framework 4.0
2. C#
3. Visual Studio 2010
4. SQL Express 2008

3. Application

3.1 Functionality exposed in User Interface

The article demonstrates creating a simple application that stores customer information in a SQL Express 2008 database. To keep things simple, only the following functionality will be implemented in the User Interface.

• Search By Database Field

• First, Previous, Next, Last customer record traversal

• Add/Edit/Delete customer information

• List View and Detail View  

3.2 Step 1: Create C# .NET Solution and Project

Follow the steps below to create a .NET solution and project.

 

• Open Visual Studio 2010.

• Select File à New Project.

• Type the name of the project as CMS (short for Customer Management System).

• Click OK 

3.3 Step 2: Create the database

The following table and fields should be created in the SQL Express database. 

Table Name: Customer
ID	int	Primary Key
FirstName	varchar(100)	Not Null
LastName	varchar(100)	Not Null
Photo	image	Null

To create the SQL Express database follow the steps outlined below.

• Within Visual Studio 2010, right click on the Project "CMS".

• Select Add -> New Item.

• The Add New Item dialog is displayed.

• Select Data -> Service-based Database. 

• Type the name of the database as CMS.mdf.

• Click Add.

• Click Cancel in the Data Source Configuration Wizard dialog.

• Double click on the CMS.mdf file in the Solution Explorer.

• The database opens in the Server Explorer window as shown in the screenshot below. 

• Right-click on the Tables node and select Add New Table item.

• Enter the fields listed above and save the table as "Customer".  

3.4 Step 3: Design the User Interface

• Rename the Form1 class/file as CustomerDetails. 
• Design the UI by dragging and dropping controls from the toolbox.
• The control names are specified in the screenshot below. 

Note:

1. Remember to save changes to the form at frequent intervals while designing. This would help prevent losing work just in case something goes wrong.
2. cMSDataSet and customerTableAdapter is automatically added by .NET during the process of data binding. 

 

• After you have finished designing, drag and drop the BindingSource control from the Toolbox onto the form. 
• Select BindingSource control (displayed near OpenFileDialog control) and open Properties window by pressing the F4 function key.
• In the properties window, select DataSource and click on the down arrow.
• Select "Add Project Data Source…" hyperlink to open the "Data Source Configuration Wizard" window. 

• Select "Database" and click on the Next button.

• Select "Dataset" and click on the Next button. 

• Select "New Connection" to open the "Add Connection" dialog.

 

• Click "Browse" and select the CMS.mdf file.
• Click "Test Connection" to verify if the connection succeeds.

 

• If the connection doesn’t succeed, verify the following:

1. SQL Server is running.
1. To verify this, go to Start -> Settings -> Control Panel -> Administrative Tools -> Services. 
2. In Services window, check if "SQL Server (SQLEXPRESS)" service is started. If not start it. 
2. Database is already open in Server Explorer in Visual Studio. In this case, disconnect the connection by right clicking on the CMS.mdf node in Server Explorer and selecting "Close Connection".

• Once the connection succeeds, click OK. You should see the following information.

 

• Make sure the connection string is correct before proceeding further. If yes, and click on the Next button.
• Check the option "Yes, save the connection as:" and click on the Next button.

 

• Select the "Customer" table and click on the Finish button.

 

• On clicking finish  

1. The file CMSDataSet.xsd is added to the project.
2. The cMSDataSet (CMS.CMSDataSet) control is automatically added to the project.
3. The following connection string entry is added to the project’s app.config file.

<connectionStrings> 
<add name="LineGeneratorForWebsite.Properties.Settings.CMSConnectionString"
connectionString="DataSource=.\SQLEXPRESS;AttachDbFilename=C:\Projects\
Training\ForVideos\CMS\CMS.mdf;Integrated Security=True;Connect Timeout=30; 
User Instance=True"providerName="System.Data.SqlClient" />
</connectionStrings>

• Set the bindingNavigator’s BindingSource property to bindingSource.
• Set the below properties for the bindingSource.

DataSource	cMSDataSet
DataMember	Customer

• On setting the above properties, customerTableAdapter control gets automatically added to the project.
• Add a Label to the bindingNavigator control as shown in the below screenshot and set the text property of the label as "Save". 

 

• The end result should be as shown in the below screenshot.

 

• Set the below properties for the Search by Field combo box.

AutoCompleteMode	Append
AutoCompleteSource	ListItems
DropDownStyle	DropDownList
Items	First Name Last Name

• Set the below properties for the Search Text combo box.

AutoCompleteMode	Append
AutoCompleteSource	ListItems
DataSource	bindingSource
DropDownStyle	DropDownList

• Set the below properties for the List box.

DataSource	bindingSource
DisplayMember	FirstName

• Set the below properties for the DataGridView.

AutoSizeColumnsMode	Fill
DataSource	bindingSource
Columns	ID FirstName LastName

• For the TextBoxes (ID, First Name, Last Name) and PictureBox, set the BindingSource for the Text and Image properties respectively as shown in the below screenshot.

 

3.5 Step 4: Code the event handlers

• Form Load – CustomerDetails_Load

1. Set the below properties for the SearchByField and SearchText combo boxes.

cmbSearchByField.SelectedIndex = 0;
cmbSearchText.DisplayMember = "FirstName";

2. Fill the cMSDataSet using the customerTableAdapter

this.customerTableAdapter.Fill(this.cMSDataSet.Customer);

• Save – lblSave_Click

1. On clicking "Save", end edit.

bindingSource.EndEdit();

2. Update customer details to the database.

this.customerTableAdapter.Update(this.cMSDataSet.Customer);
this.cMSDataSet.Customer.AcceptChanges();

• Changing Search By Field –cmbSearchByField_SelectedIndexChanged

1. When the SearchByField is changed, set the DisplayMember of the cmbSearchText combo box to the selected field.

cmbSearchText.DisplayMember = 
   (cmbSearchByField.SelectedIndex == 0) ? "FirstName" : "LastName";

Note: By slightly tweaking the code, you can refrain from hard-coding the field names.

• Loading Images – btnLoadImage_Click

1. Use the below code to load images to the picture box.

if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
    pbImage.ImageLocation = openFileDialog1.FileName;
}

4. Explanation on the Data Controls used in this application

4.1 BindingNavigator

• For more information on the BindingNavigator control, visit the below page:

http://msdn.microsoft.com/en-us/library/b9y7cz6d.aspx

4.2 BindingSource

• For more information on the BindingSource component, visit the below page:

http://msdn.microsoft.com/en-us/library/h974h4y2.aspx

4.3 TableAdapter

• For more information on the TableAdapter, visit the below page:

http://msdn.microsoft.com/en-us/library/bz9tthwx%28v=vs.100%29.aspx

4.4 DataSet

• For more information on the Dataset class, visit the below page:

http://msdn.microsoft.com/en-us/library/system.data.dataset%28v=vs.100%29.aspx