In this article, I would like to explain CRUD operations in Windows applications using C#. In today's internet world, there are many companies/organizations using Windows applications for their intranet work.
First of all, I created a database InventoryDB and run the following script:
CREATE TABLE [dbo].[Customer](
[CustomerID] [bigint] IDENTITY(1,1) NOT NULL,
[CustomerFirstName] [varchar](50) NULL,
[CustomerLastName] [varchar](50) NULL,
[CustomerAddress1] [varchar](100) NULL,
[CustomerAddress2] [varchar](100) NULL,
[CustomerCity] [varchar](50) NULL,
[CustomerPin] [varchar](20) NULL,
[CustomerMobile1] [bigint] NULL,
[CustomerMobile2] [bigint] NULL,
[CustomerOffPhone] [varchar](20) NULL,
[CustomerWebSite] [varchar](50) NULL,
[CustomerCompanyName] [varchar](100) NULL,
[IsActive] [bit] NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_IsActive] DEFAULT ((1)) FOR [IsActive]
GO
CREATE PROCEDURE [dbo].[Customer_AddUpdate] (@CustomerID BIGINT=0,
@CustomerFirstName VARCHAR(50)=NULL,
@CustomerLastName VARCHAR(50)=NULL,
@CustomerAddress1 VARCHAR(100)=NULL,
@CustomerAddress2 VARCHAR(100)=NULL,
@CustomerCity VARCHAR(50)=NULL,
@CustomerPin VARCHAR(20)=NULL,
@CustomerMobile1 BIGINT=0,
@CustomerMobile2 BIGINT=0,
@CustomerOffPhone VARCHAR(20),
@CustomerWebSite VARCHAR(50)=NULL,
@CustomerCompanyName VARCHAR(100)=NULL,
@Result BIGINT output)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT CustomerID
FROM dbo.Customer (nolock)
WHERE CustomerID = @CustomerID)
BEGIN
UPDATE Customer
SET CustomerFirstName = @CustomerFirstName,
CustomerLastName = @CustomerLastName,
CustomerAddress1 = @CustomerAddress1,
CustomerAddress2 = @CustomerAddress2,
CustomerCity = @CustomerCity,
CustomerPin = @CustomerPin,
CustomerMobile1 = @CustomerMobile1,
CustomerMobile2 = @CustomerMobile2,
CustomerOffPhone = @CustomerOffPhone,
CustomerWebSite = @CustomerWebSite,
CustomerCompanyName = @CustomerCompanyName
WHERE CustomerID = @CustomerID
END
ELSE
BEGIN
INSERT INTO Customer
(CustomerFirstName,
CustomerLastName,
CustomerAddress1,
CustomerAddress2,
CustomerCity,
CustomerPin,
CustomerMobile1,
CustomerMobile2,
CustomerOffPhone,
CustomerWebSite,
CustomerCompanyName)
VALUES ( @CustomerFirstName,
@CustomerLastName,
@CustomerAddress1,
@CustomerAddress2,
@CustomerCity,
@CustomerPin,
@CustomerMobile1,
@CustomerMobile2,
@CustomerOffPhone,
@CustomerWebSite,
@CustomerCompanyName )
END
IF @@ERROR <> 0
BEGIN
SET @Result = 0
END
ELSE
BEGIN
IF @CustomerID = 0
BEGIN
SET @Result = @@IDENTITY
END
ELSE
BEGIN
SET @Result = 1
END
END
SET NOCOUNT OFF;
END
GO
CREATE PROCEDURE [dbo].[Customer_DeleteCustomerByID] (@CustomerId BIGINT,
@Result BIGINT output)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Customer
WHERE CustomerId = @CustomerId
IF @@ERROR <> 0
BEGIN
SET @Result = 0
END
ELSE
BEGIN
SET @Result = 1
END
SET NOCOUNT OFF;
END
GO
CREATE PROCEDURE [dbo].[Customer_GetAll]
AS
BEGIN
SET NOCOUNT ON;
Select
CustomerID
,CustomerFirstName
,CustomerLastName
,CustomerAddress1
,CustomerAddress2
,CustomerCity
,CustomerPin
,CustomerMobile1
,CustomerMobile2
,CustomerOffPhone
,CustomerWebSite
,CustomerCompanyName
FROM dbo.Customer (nolock) order by customerid desc
SET NOCOUNT OFF;
End
GO
CREATE PROCEDURE [dbo].[Customer_GetCustomerById] (@CustomerId BIGINT)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID,
CustomerFirstName,
CustomerLastName,
CustomerAddress1,
CustomerAddress2,
CustomerCity,
CustomerPin,
CustomerMobile1,
CustomerMobile2,
CustomerOffPhone,
CustomerWebSite,
CustomerCompanyName
FROM Customer
WHERE CustomerID = @CustomerId
AND IsActive = 1
SET NOCOUNT OFF;
END
CREATE PROCEDURE [dbo].[Customer_GetCustomerId]
(
@Result bigint output
)
AS
SET NOCOUNT ON;
SET @Result = (Select top 1 CustomerID from Customer where IsActive=1 Order By CustomerID Desc)
Select @Result
return;
SET NOCOUNT OFF;
GO
CREATE PROCEDURE [dbo].[Customer_SearchByNameCity]
(
@searchText varchar(100),
@type int=0
)
AS
BEGIN
SET NOCOUNT ON;
if @type=0
begin
Select * from Customer Where CustomerFirstName like '' + @searchText + '%'
end
if @type=1
begin
Select * from Customer Where CustomerLastName like '' + @searchText + '%'
end
if @type=2
begin
Select * from Customer Where CustomerCity like '' + @searchText + '%'
end
SET NOCOUNT OFF;
END
GO
Then , I created a solution using Visual Studio 2012 as in the following:
Right-click on the solution and add a new project as in the following:
Right-click on the solution and add another project as in the following:
Now, we have 4 projects in our solution. Build the WindowsFormsEntity class type library project and add a reference of this into the WindowsFormsDAL class type library project. Build the WindowsFormsDAL class and add a reference of the WindowsFormsEntity and WindowsFormsDAL classes into the WindowsFormsBAL project. Add a reference of WindowsFormsEntity, WindowsFormsDAL and WindowsFormsBAL class type library projects to our main Windows application WindowsFormsApp and build the solution.
Add the class Customer.cs in WindowsFormsEntity as in the following:
public class Customer
{
public Int64 CustomerID { get; set; }
public string CustomerFirstName { get; set; }
public string CustomerLastName { get; set; }
public string CustomerAddress1 { get; set; }
public string CustomerAddress2 { get; set; }
public string CustomerCity { get; set; }
public string CustomerPin { get; set; }
public string CustomerMobile1 { get; set; }
public string CustomerMobile2 { get; set; }
public string CustomerOffPhone { get; set; }
public string CustomerWebSite { get; set; }
public string CustomerCompanyName { get; set; }
public Boolean IsActive { get; set; }
}
I added a reference of the Microsoft.Practices.EnterpriseLibrary to the project WindowsFormsDAL to work with the database layer.
Add a new class EventLog into the project WindowsFormsDAL to log any type of error occuring during the transaction. Also added a static class ConfigurationDAL to get the value of the ConnectionString from the app.config file of the WindowsFormsApp project. In the class CustomerDAL, there are 5 methods added to the add/update/retrieve/delete operations. Similarly, 5 methods are added to the project WindowsFormsBAL.
Added a new Windows form Form1.cs to the project WindowsFormsApp.
Drag and drop controls like Labels, TextBoxes, Buttons, ComboBox and DataGridView.
Added 2 datasets to work with Crystal Reports.
Create a TableAdapter and create a new connectionstring and the values are stored in the app.config file.
Click on TableAdapter; the next screen will ask about Choose Your Data Connection as follows.
To create a new connection, click on New Connection and the next screen will come as follows.
Enter server name, user name, password and database. Click on Test Connection to test it.
After a successful connection, the next screen will ask Choose a Command Type, Select Use existing procedures.
The next screen will be as follows.
Click on "Next" to choose the method to generate.
Click on Finish.
Finally our dataset is created for the report.
Added a new Crystal Reports report as follows.
Create a new Crystal Reports document as in the following:
Click on "Next" and verify the username and password to connect with the SQL Server and then the verification screen will look as follows:
Click on Finish and the report is ready to display with fields.
Add a new form ReportForm.cs to the display report in a separate window.
Download the file and change the app.config as per your requirements.
Kapi ShivharePosted Sep 28, 2016, 12:52 PM
hiiiii
amsbarrosPosted Nov 25, 2014, 10:48 AM
Thank you for this useful job. I tried this, build the solution, but when I run the application, I receive the following error: Microsoft.Practices.ServiceLocation.ActivationException was unhandled HResult=-2146233088 Message=Activation error occured while trying to get instance of type LogWriter, key "" Source=Microsoft.Practices.ServiceLocation StackTrace: em Microsoft.Practices.ServiceLocation.ServiceLocatorImplBase.GetInstance(Type serviceType, String key) em Microsoft.Practices.ServiceLocation.ServiceLocatorImplBase.GetInstance[TService]() em Microsoft.Practices.EnterpriseLibrary.Logging.Logger.get_Writer() em Microsoft.Practices.EnterpriseLibrary.Logging.Logger.Write(LogEntry log) em WindowsFormsDAL.EventLog.LogData(String vstrMessage, TraceEventType enmEventType) em c:\TestesVC#\CRUD Applications\WindowsAppInventory\WindowsAppInventory\WindowsFormsDAL\EventLog.cs:line 54 em WindowsFormsDAL.EventLog.LogError(Exception vobjException, String vstrAdditionalInfo) em c:\TestesVC#\CRUD Applications\WindowsAppInventory\WindowsAppInventory\WindowsFormsDAL\EventLog.cs:line 33 em WindowsFormsDAL.EventLog.LogError(Exception vobjException) em c:\TestesVC#\CRUD Applications\WindowsAppInventory\WindowsAppInventory\WindowsFormsDAL\EventLog.cs:line 17 em WindowsFormsDAL.CustomerDAL.GetCustomerId() em c:\TestesVC#\CRUD Applications\WindowsAppInventory\WindowsAppInventory\WindowsFormsDAL\CustomerDAL.cs:line 74 em WindowsFormsBAL.CustomerBAL.GetCustomerId() em c:\TestesVC#\CRUD Applications\WindowsAppInventory\WindowsAppInventory\WindowsFormsBAL\CustomerBAL.cs:line 41 em WindowsFormsApp.Form1.Form1_Load(Object sender, EventArgs e) em c:\TestesVC#\CRUD Applications\WindowsAppInventory\WindowsAppInventory\WindowsFormsApp\Form1.cs:line 105 em System.Windows.Forms.Form.OnLoad(EventArgs e) em System.Windows.Forms.Form.OnCreateControl() em System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible) em System.Windows.Forms.Control.CreateControl() em System.Windows.Forms.Control.WmShowWindow(Message& m) em System.Windows.Forms.Control.WndProc(Message& m) em System.Windows.Forms.ScrollableControl.WndProc(Message& m) em System.Windows.Forms.ContainerControl.WndProc(Message& m) em System.Windows.Forms.Form.WmShowWindow(Message& m) em System.Windows.Forms.Form.WndProc(Message& m) em System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) em System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) em System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) em System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, Int32 wParam, Int32 lParam) em System.Windows.Forms.Form.SetVisibleCore(Boolean value) em System.Windows.Forms.Control.set_Visible(Boolean value) em System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) em System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) em System.Windows.Forms.Application.Run(Form mainForm) em WindowsFormsApp.Program.Main() em c:\TestesVC#\CRUD Applications\WindowsAppInventory\WindowsAppInventory\WindowsFormsApp\Program.cs:line 19 em System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) em System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) em Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() em System.Threading.ThreadHelper.ThreadStart_Context(Object state) em System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) em System.Threading.ThreadHelper.ThreadStart() InnerException: Microsoft.Practices.Unity.ResolutionFailedException HResult=-2146233088 Message=Resolution of the dependency failed, type = "Microsoft.Practices.EnterpriseLibrary.Logging.LogWriter", name = "(none)". Exception occurred while: while resolving. Exception is: InvalidOperationException - The type LogWriter cannot be constructed. You must configure the container to supply this value. ----------------------------------------------- At the time of the exception, the container was: Resolving Microsoft.Practices.EnterpriseLibrary.Logging.LogWriter,(none) Source=Microsoft.Practices.Unity TypeRequested=LogWriter StackTrace: em Microsoft.Practices.Unity.UnityContainer.DoBuildUp(Type t, Object existing, String name, IEnumerable`1 resolverOverrides) em Microsoft.Practices.Unity.UnityContainer.DoBuildUp(Type t, String name, IEnumerable`1 resolverOverrides) em Microsoft.Practices.Unity.UnityContainer.Resolve(Type t, String name, ResolverOverride[] resolverOverrides) em Microsoft.Practices.Unity.UnityServiceLocator.DoGetInstance(Type serviceType, String key) em Microsoft.Practices.ServiceLocation.ServiceLocatorImplBase.GetInstance(Type serviceType, String key) InnerException: System.InvalidOperationException HResult=-2146233079 Message=The type LogWriter cannot be constructed. You must configure the container to supply this value. Source=Microsoft.Practices.Unity StackTrace: em Microsoft.Practices.ObjectBuilder2.DynamicMethodConstructorStrategy.GuardTypeIsNonPrimitive(IBuilderContext context, SelectedConstructor selectedConstructor) em Microsoft.Practices.ObjectBuilder2.DynamicMethodConstructorStrategy.PreBuildUp(IBuilderContext context) em Microsoft.Practices.ObjectBuilder2.StrategyChain.ExecuteBuildUp(IBuilderContext context) em Microsoft.Practices.ObjectBuilder2.DynamicMethodBuildPlanCreatorPolicy.CreatePlan(IBuilderContext context, NamedTypeBuildKey buildKey) em Microsoft.Practices.ObjectBuilder2.BuildPlanStrategy.PreBuildUp(IBuilderContext context) em Microsoft.Practices.ObjectBuilder2.StrategyChain.ExecuteBuildUp(IBuilderContext context) em Microsoft.Practices.Unity.UnityContainer.DoBuildUp(Type t, Object existing, String name, IEnumerable`1 resolverOverrides) InnerException: