CRUD Operations
Data is an important factor in any programming concept. Database operations are key items in any programming language. In general, all database operations can be broadly classified into four categories:
- Insert data (Create)
- Get existing data (Read)
- Modify existing data (Update)
- Delete data (Delete)
This set of tasks are called CRUD. On designing these four operations, a programmer will have a good idea on how to start his application connected with database storage.
LINQ Overview
.NET Framework 3.5 introduces one of the new concepts called LInQ (Language Integrated Query). In the current Object Oriented Programming paradigm, LINQ reduces the complexity of accessing and integrating information from data sources, by generating relational databases in the form of data classes. Language-integrated Query allows query expressions to benefit from the rich metadata, compile-time syntax checking, and static typing. On top of that, IntelliSense features are more beneficial to the developer community. Language-integrated Query also allows a single general purpose declarative query facility to be applied to all in-memory information, not just information from external sources.
In the typical 6-tier application architecture model, LINQ stays between the business logic and the actual database source as a simplified integrator for the development community. It defines a set of general purpose standard query operators that allow traversal, filter, and projection operations to be expressed in a direct yet declarative way in any .NET-based programming language.
Case Study
With the above knowledge of LINQ and CRUD operations, let us build a sample application using LINQ in a Silverlight 3.0 web based application. As a case study, we are considering database operations in a SQL Server table called 'EmployeeMaster', We are trying it using the two methodologies below:
- LINQ to SQL
- Stored Procedure
Our case study application is designed with the model below:
For our case study of all database operations, the Employee table is structured as below.
LINQ to SQL
The below sections show the step by step process to build the LINQ to SQL implementation.
Step 1: Create a new Silverlight Application
Step 2: Create Four HyperlinkButtons in MainPage.xaml
Step 3: Creation of the WCF Service
Add a new WCF service named 'DBService' from the list below by right clicking on the 'SilverlightBasicDBApp.Web' server side project.
To access the newly created service from the client/user interface layer, a web service reference is added as below.
Step 4: Creation of LINQ to SQL classes
To create a LINQ to SQL class, right click on the client solution. From the menu, select the Add New Item option to pop up the screen below to add a new LINQ to SQL class, EmpMasterDataClass.dbml.
From the Server Explorer sub window, right click on Data Connections to include a new entry. During this operation, the Add Connection window is displayed to connect to the selected data source.
On clicking the Test Connection button, we can validate the database connectivity status of the requested data source. On successful operation, the new Data Connections entry is included as in the snapshot below. On expanding the Tables tree structure, drag EmpMaster into EmpMasterDataClass.dbml.
Step 5: Read operations
In CRUD operations, let us start with the Read operation of the EmpMaster table. At first, a new WCF service needs to be added as indicated below:
Add a new service in the interface IBDService
[OperationContract]
List<EmpMaster> ReadEmpMaster(string EmpId);
Implement it in DBService
List<EmpMaster> IDBService.ReadEmpMaster(string EmpId)
{
EmpMasterDataClassDataContext dbConn = new EmpMasterDataClassDataContext();
var queryRead = from table in dbConn.EmpMasters
where table.EmpId.ToString().StartsWith(EmpId)
select table;
return queryRead.ToList();
}
User Interface
The User Interface (UI) screen is designed to fetch the result record for the given criteria. The input employee ID is read through the text box; the Read Button control calls the Web Service method to fetch the matching records from the defined data source.
<StackPanel x:Name="ContentStackPanel">
<Grid.
<Grid.ColumnDefinitions>
<ColumnDefinition Width="300">
<ColumnDefinition Width="100">
<ColumnDefinition Width="100">
</Grid.ColumnDefinitions>
<TextBlock x:Name="HeaderText"
Style="{StaticResource HeaderTextStyle}"
Text="Enter Employee ID to retrieve like">
<TextBox Grid.Column="1" x:Name="txtEmpId"
Height="25" Width="70"<
<Button Grid.Column="2" x:Name="btnRead"
Width="50" Height="25"
Click="btnRead_Click" Content="Fetch It">
</Grid>
<grid:DataGrid x:Name="gridTeam"
AlternatingRowBackground="Beige" CanUserSortColumns="True"
Width="Auto" AutoGeneratingColumn="gridTeam_AutoGeneratingColumn">
</StackPanel>
User Interface code-behind
The UI code-behind is written in the btnRead_Click delegate of the button UI control.
private void btnRead_Click(object sender, RoutedEventArgs e)
{
DBServiceRef.DBServiceClient webService =
new SilverlightBasicDBApp.DBServiceRef.DBServiceClient();
webService.ReadEmpMasterCompleted +=
new EventHandler<SilverlightBasicDBApp.DBServiceRef.ReadEmpMasterCompletedEventArgs>
(webService_ReadEmpMasterCompleted);
webService.ReadEmpMasterAsync(txtEmpId.Text.Trim());
}
void webService_ReadEmpMasterCompleted(object sender,
SilverlightBasicDBApp.DBServiceRef.ReadEmpMasterCompletedEventArgs e)
{
if (e.Result.Count > 0)
{
PagedCollectionView groupView = new PagedCollectionView(e.Result);
if (groupView.CanGroup)
{
groupView.GroupDescriptions.Add(new PropertyGroupDescription("Status"));
}
gridTeam.ItemsSource = groupView;
}
}
Executing the application for Read operations
Create Delete Update operations
In the similar line, the other three database operations are executed by repeating steps 5 6. The programming sequence would be:
- Add a new service in the interface
IBDService
- Implement it in DBService
- Update Service Reference
- User Interface
- User Interface code-behind
Stored Procedure
To execute the same Read operation using Stored Procedure methodology, the initial three steps would be the same. In step 4, the same functionality is defined in a Stored Procedure (pr_EmpMaster) which is dragged from the left side 'Data Connections' tree.
Step 5: Read operations
After dragging pr_EmpMaster, the auto-generated relevant or connected classes (like pr_EmpMasterResult) are created in the server solution. With reference to the generated classes, let us proceed to the database operations using a Stored Procedure with the steps below.
Add a new service in the interface IBDService
[OperationContract]
List<EmpMaster> prReadEmpMaster(string EmpId);
Implement it in DBService
List<pr_EmpMasterResult> IDBService.prReadEmpMaster(string EmpId)
{
EmpMasterDataClassDataContext dbConn = new EmpMasterDataClassDataContext();
return (dbConn.pr_EmpMaster(EmpId)).ToList();
}
User Interface
Reuse the previous model.
User Interface code-behind
webService.prReadEmpMasterCompleted +=
new EventHandler<SilverlightBasicDBApp.DBServiceRef.prReadEmpMasterCompletedEventArgs>
(webService_prReadEmpMasterCompleted);
webService.prReadEmpMasterAsync(txtEmpId.Text.Trim());
Step 6: Executing the application for Read Stored Procedure operations
History
- Version 1.0 - Initial version. <!------------------------------- That's it! --------------------------->
