Dapper CRUD Win Forms - Part One

Omar Rodriguez
7y
8.5k
0
1

Article

Create a SQL Server database with the name "DapperDB"

For purposes of the example, we can restore it from the sample file or use the code given below to create it.

USE [DapperDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
[id] [nvarchar](50) NOT NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
[status] [nvarchar](50) NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[id] 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
USE [DapperDB]
GO
/****** Object: StoredProcedure [dbo].[users_DeleteRow_By_id] Script Date: 06/02/2017 11:13:02 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[users_DeleteRow_By_id]
@id nvarchar(50)
As
Begin
Delete users
Where
[id] = @id
End
GO
USE [DapperDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[users_Insert_Update]
@id nvarchar(50),
@name nvarchar(50),
@address nvarchar(50),
@status nvarchar(50)
As
BEGIN
IF NOT EXISTS (SELECT * FROM dbo.users u WHERE u.id = @id)
BEGIN
Insert Into users
([id],[name],[address],[status])
Values
(@id,@name,@address,@status)
END
ELSE
BEGIN
Update users
Set
[id] = @id,
[name] = @name,
[address] = @address,
[status] = @status
Where [id] = @id
END
End
GO
USE [DapperDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[users_SelectAll]
As
Begin
SELECT dbo.users.id
, dbo.users.name
, dbo.users.address
, dbo.users.status FROM dbo.users
End
GO
USE [DapperDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[users_SelectRow_By_id]
@id nvarchar(50)
As
Begin
Select
dbo.users.id
, dbo.users.name
, dbo.users.address
, dbo.users.status
From users
Where
[id] = @id
End
GO
USE [DapperDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[users_SelectwithDate]
As
Begin
SELECT dbo.users.id
, dbo.users.name
, dbo.users.address
, dbo.users.status
, getdate() AS [date]
FROM dbo.users
End
GO

Create a Windows form C# Application with the name "DapperRepoWinForm"

Add Dapper package to the project.

Click on tools-> NuGet Packages Manager-> Manage NuGet Packages for Solution.

Now, click on Manage Nuget Packages option, then Windows given below will appear.

Click on Browse.
As shown in the image, type in search box “dapper”.
Select Dapper, as shown in the image.
Check the project solution.

Click on install button.

Creating folders

Now, we must create a folder in the project. We'll call it utilities. Inside it, we will create a class named "Globals".

This class will serve to create a variable "stringConn", which contains the information for the login in the database. A constant path with the address of XML file contains the login information.

using System;
using System.Collections.Generic;
using System.Data;
namespace DapperRepoWinForm.Utilities
{
public static class Globals
{
public static String stringConn = "";
public const string path = "c:\\conn.xml";
}
}

Now, we will create a class, which we will call "ConnectionDB".

This class is used to read XML file and get the data for the login. Here, you can place the code to encrypt the information. For this example, I do not put it.

using System.Xml;
namespace DapperRepoWinForm.Utilities
{
class ConnectionDB
{
public static string xml_conn(string path)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(path);
XmlNodeList nodeList = xmlDoc.DocumentElement.SelectNodes("/Table/Conexion");
string proServer = "", proDatabase = "", proUser = "", proPassword = "";
foreach (XmlNode node in nodeList)
{
proServer = node.SelectSingleNode("Server").InnerText;
proDatabase = node.SelectSingleNode("Database").InnerText;
proUser = node.SelectSingleNode("User").InnerText;
proPassword = node.SelectSingleNode("Password").InnerText;
}
return ("Server = " + proServer + "; Database =" + proDatabase + "; User Id = " + proUser + ";Password = " + proPassword + ";");
}
}
}

We create a folder in the project, which we will call it "Repository". Within this folder, we will create a class called "RepGen" At the beginning, add the code given below.

using System.Data.SqlClient;
using Dapper;
using DapperRepoWinForm.Utilities;

This class will serve to connect to the database and execute the stored procedures of ype (non query, return scalar or return numeric value).

How does it work

The void connection fills the value of the "with" variable with the connection data to the database.

private void connection()
{
con = new SqlConnection(Globals.stringConn);
}

The function executeNonQuery executes a no-query stored procedure (like, insert, update, delete).

public string executeNonQuery(string query, DynamicParameters param)
{
try
{
connection();
con.Open();
con.Execute(query, param, commandType: CommandType.StoredProcedure);
con.Close();
return "0";
}
catch (Exception ex)
{
return ex.Message;
}
}

The fuction returnNumericValue executes a stored procedure with numeric return values.

public string returnNumericValue(string query, DynamicParameters param)
{
try
{
string valor = "";
param.Add("@output", dbType: DbType.Int32 , direction: ParameterDirection.Output);
param.Add("@Returnvalue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
// Getting Return value
connection();
con.Open();
valor = con.ExecuteScalar<string>(query, param, commandType: CommandType.StoredProcedure);
con.Close();
return valor;
}
catch (Exception ex)
{
return ex.Message;
}
}

The returnScalar function executes stored procedures with return values of type scalar.

string returnScalar(string query, DynamicParameters param)
{
try
{
string valor = "";
connection();
con.Open();
valor = con.ExecuteScalar<string>(query, param, commandType: CommandType.StoredProcedure);
con.Close();
return valor;
}
catch (Exception ex)
{
return ex.Message;
}
}

We will create a next class named "RepGen"

This class will call the stored procedures that returns groups of data or a class. We will use dapper and fill a non-generic collection of the objects. I use it to fill the grids, which can be accessed individually by an index "Ilist <t>" or we can return a single class (for search purposes).

System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using Dapper;
using System.Data;
using DapperRepoWinForm.Utilities;
namespace DapperRepoWinForm.Repository
{
class RepList<T> where T : class
{
public SqlConnection con;
private void connection()
{
con = new SqlConnection(Globals.stringConn);
}
public List<T> returnListClass(string query, DynamicParameters param)
{
try
{
connection();
con.Open();
IList<T> Tlista = SqlMapper.Query<T>(con, query, param, null, true, null, commandType: CommandType.StoredProcedure).ToList();
con.Close();
return Tlista.ToList();
}
catch (Exception)
{
throw;
}
}
public T returnClass(string query, DynamicParameters param)
{
try
{
connection();
con.Open();
// return this.con.Query( query, param, null, true, null, commandType: CommandType.StoredProcedure).FirstOrDefault();
T Tlista = SqlMapper.Query<T>(con, query, param, null, true, null, commandType: CommandType.StoredProcedure).FirstOrDefault();
con.Close();
return Tlista;
}
catch (Exception)
{
throw;
}
}
}
}

In utilities folder, we will create a "Functions" class that contains a function that converts from IEnumerable to the datatable. This function serves to fill the grid controls.