Select list of all employees is just a simple call as below.
It is returning strongly typed object provided we match the Employee class to
database Employee table.
Find query is very simple and it is not returning any
exception if I do not have matching record in database.
Complex queries where data is coming from two different
tables with some join, example:-
Dapper allows you to process multiple result grids in a
single query.
Dapper fully supports stored procs.
Dapper allow you to pass in IEnumerable and will
automatically parameterize your query. E.g.
Dapper.net allows us to execute a command multiple times
like below.
This works for any parameter that implements IEnumerable for
some T.
Dapper.net has simplified the bulk insert queries. Look at
below sample:-
Using Execute extension for Remove operation, dapper.net has
provided the delete functionality. Example is as below.
Dapper.net has not provided create table functionality.
Dapper also supports transactional operations. Syntax for
transaction start and commit is as below. Execute the dapper.net queries in
after starting transaction and have commit on successful execution.
A key feature of Dapper is performance. The metrics shows statistics taken from github website provided by dapper.net
people itself.
https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper.Tests/PerformanceTests.cs
Sample
public interface IEmployeeDashBoard
{
List
GetAll();
List
Find(string findText);
Employee Add(Employee employee);
Employee Update(Employee employee);
void Remove(int id);
IEnumerable
FindWithDynamicSupport(string findString);
}
public class EmployeeDashBoard : IEmployeeDashBoard
{
private IDbConnection _db; // = new SqlConnection("Data Source=pallavi-kul-LAP\\MSSQL2008; Database= test; Integrated Security=True;");
public EmployeeDashBoard(string connectionString)
{
_db = new SqlConnection(connectionString);
}
public List
GetAll()
{
List
empList = this._db.Query("SELECT * FROM Employee").ToList();
return empList;
}
public List
Find(string findString)
{
string query = "SELECT * FROM Employee WHERE EmpName like '%" + findString + "%'";
return this._db.Query
(query).ToList();
}
public IEnumerable
FindWithDynamicSupport(string findString)
{
string query = "SELECT EmpId,EmpName FROM Employee WHERE EmpName like '%" + findString + "%'";
return this._db.Query
(query);
}
public Employee Add(Employee employee)
{
var sqlQuery = "INSERT INTO Employee (EmpName, EmpScore) VALUES (@EmpName, @EmpScore); " + "SELECT CAST(SCOPE_IDENTITY() as int)";
var employeeId = this._db.Query
(sqlQuery, employee).Single();
employee.EmpID = employeeId;
return employee;
}
public void BulkInsert()
{
List
list = new List() { new Employee() { EmpName = "Name1", EmpScore = 12 }, new Employee() { EmpName = "Name2", EmpScore = 13 } };
var sqlQuery = "INSERT INTO Employee (EmpName, EmpScore) VALUES (@EmpName, @EmpScore); ";
var result = this._db.Execute(sqlQuery, list);
}
public string AddUsingSPN(Employee employee)
{
var p = new DynamicParameters();
p.Add("EmpName", employee.EmpName, null, ParameterDirection.Input, 50);
p.Add("EmpScore", employee.EmpScore, null, ParameterDirection.Input);
p.Add("EmpUpdatedName", null, DbType.AnsiString, ParameterDirection.Output, 50);
p.Add("EmpJoiningDate", null, DbType.DateTime, ParameterDirection.Output);
this._db.Query
("CreateEmployeeRecord", p, commandType: CommandType.StoredProcedure);
string name = p.Get
("EmpUpdatedName");
DateTime date = p.Get
("EmpJoiningDate");
return name;
}
public string QueryMultiplePOC(int i = 4)
{
var p = new DynamicParameters();
p.Add("a", i);
p.Add("r", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
var grid = _db.QueryMultiple("spEcho", p, commandType: CommandType.StoredProcedure);
var result1 = grid.Read
>(
(a, b) => Tuple.Create((object)a, (object)b)).ToList();
var result2 = grid.Read
>(
(a, b) => Tuple.Create((object)a, (object)b)).ToList();
return ((int)(result1[0].Item1.Id)) + " | " + ((int)(result1[0].Item2.Id));
}
public Employee Update(Employee employee)
{
var sqlQuery =
"UPDATE Employee " +
"SET EmpName = @EmpName, " +
" EmpScore = @EmpScore " +
"WHERE EmpID = @EmpID";
this._db.Execute(sqlQuery, employee);
return employee;
}
public void Remove(int id)
{
var sqlQuery = ("Delete From Employee Where EmpID = " + id + "");
this._db.Execute(sqlQuery);
}
}
//use
EmployeeDashBoard _board = new EmployeeDashBoard(textBoxConnectionstring.Text);
private void RefreshListBox()
{
this.listBox1.DataSource = _board.GetAll();
}
private void buttonRemove_Click(object sender, EventArgs e)
{
_board.Remove(((Employee)(listBox1.SelectedItem)).EmpID);
RefreshListBox();
}
private void buttonAddUsingSP_Click(object sender, EventArgs e)
{
var msg = _board.AddUsingSPN(new Employee(textBox1.Text, double.Parse(textBox2.Text)));
MessageBox.Show(msg);
}
private void buttonQueryMultiplePOC_Click(object sender, EventArgs e)
{
/* ***************** below is SP ***********
* create proc spEcho
@a int
as
begin,
select @a Id, 'ping' Name, 1 Id, 'pong1' Name
select @a Id, 'ping' Name, 2 Id, 'pong2' Name
return @a
end
* */
/* ***************** Result ******************
*
*
(1 row(s) affected)
Id Name Id Name
----------- ---- ----------- -----
3 ping 2 pong2
(1 row(s) affected)
Return Value
------------
3
(1 row(s) affected)
* */
MessageBox.Show(_board.QueryMultiplePOC());
}
private void buttonFind_Click(object sender, EventArgs e)
{
string textToFind = textBoxFind.Text;
List
empList = _board.Find(textToFind);
MessageBox.Show(empList.Count.ToString());
}
private void buttonReEstablishConnection_Click(object sender, EventArgs e)
{
_board = new EmployeeDashBoard(textBoxConnectionstring.Text);
}
private void buttonFindWithDynamicSupport_Click(object sender, EventArgs e)
{
string displayResult = "";
var results = _board.FindWithDynamicSupport(textBoxFind.Text);
foreach (var row in results)
{
displayResult += row.EmpId + " " + row.EmpName + "\n";
}
MessageBox.Show(displayResult);
}
private void buttonBulkInsert_Click(object sender, EventArgs e)
{
_board.BulkInsert();
RefreshListBox();
}
