Dapper - a simple object mapper for .Net
Install-Package Dapper -Version 1.42.0
using Dapper;
using System.Data.SqlClient;
namespace DrapperMyExample
{
public partial class Text : Form
{
SqlConnection conn = new SqlConnection(@"Data Source=DESKTOP-30T0BVB\TOWNSUITE;Initial Catalog=Dapper;Integrated Security=True");
public Text()
{
InitializeComponent();
Load();
}
public void Load() {
dataGridView1.DataSource = conn.Query<DapperUser>("select id,name,address from user2");
}
private void btnCreate_Click(object sender, EventArgs e)
{
var x = new DapperUser
{
Address = txtAddress.Text,
Name = txtName.Text
};
conn.Execute("INSERT INTO [User2] (Name, Address) VALUES (@Name, @Address)", new { Name = x.Name, Address = x.Address });
Load();
MessageBox.Show("creaet is success");
}
public int ID { get; set; }
class DapperUser {
public int ID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
}
private void btnUpdate_Click(object sender, EventArgs e)
{
var x = new DapperUser
{
ID = ID,
Address = txtAddress.Text,
Name = txtName.Text
};
conn.Execute("UPDATE[User2] SET Name = @Name, Address = @Address WHERE(ID = @ID)",new {
Name = x.Name,
Address = x.Address,
ID = x.ID
});
Load();
MessageBox.Show("update is success");
}
private void btnDelete_Click(object sender, EventArgs e)
{
var x = new DapperUser
{
ID = ID,
Address = txtAddress.Text,
Name = txtName.Text
};
conn.Execute("DELETE FROM [User2] WHERE(ID = @ID)", new
{
ID = x.ID
});
Load();
MessageBox.Show("delete is success");
}
public void WithTransaction() {
var cc = new DapperUser
{
Name = "Chamith",
Address = "Malabe"
};
using (var x = conn.BeginTransaction())
{
try
{
conn.Execute("insert into table1(name,address) values(@name,@address)", new { cc.Name, cc.Address },x);
x.Commit();
}
catch (Exception)
{
x.Rollback();
}
}
}
private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
ID = Convert.ToInt16(dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString());
txtName.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
txtAddress.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
}
}
}
//////////////////////
Execute Sp With out parameter , return parameter, in Parameter
var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);
int b = p.Get<int>("@b");
int c = p.Get<int>("@c"); Call sp with dynamic parameters
var p = new DynamicParameters();
p.Add("@Original_RegistrationItemId", value: item.ID, dbType: DbType.String, direction: ParameterDirection.Input, size: 15);
p.Add("@Email", dbType: DbType.String, direction: ParameterDirection.Output, size: 150);
p.Add("@Name", dbType: DbType.String, direction: ParameterDirection.Output, size: 150);
p.Add("@ProgramName", dbType: DbType.String, direction: ParameterDirection.Output, size: 250);
p.Add("@ParticipantName", dbType: DbType.String, direction: ParameterDirection.Output, size: 500);
p.Add("@ReturnWaitingUserID", dbType: DbType.String, direction: ParameterDirection.Output, size: 15);
// p.Add("@Original_RelationShipId", value: request.RelationShipId, dbType: DbType.Int32, direction: ParameterDirection.Input);
cn.Execute("RecreationParticipantsListUpdateFromWaitingListUCmd", p,
null, null, CommandType.StoredProcedure);
email = p.Get<string>("@Email");
Name = p.Get<string>("@Name");
ProgramName = p.Get<string>("@ProgramName");
ParticipantName = p.Get<string>("@ParticipantName");
returnWaitingUserID = p.Get<string>("@ReturnWaitingUserID");
if (returnWaitingUserID != null)
{
Action<string, string, string, string, string> caller = new Action<string, string, string, string, string>(this.NewsFeedDetailsInsert);
caller.BeginInvoke(userid, Name, ProgramName, item.ID, ParticipantName, null, null);
}
if (email != null)
{
Task.Run(() =>
{
var inst = new Email.RecreationWaitListPaymentNotification(email, Name, ProgramName, item.ID, ParticipantName);
inst.Execute();
});
}
///////////////////
MULTIPLE SELECT SP
SP
ALTER PROCEDURE [dbo].[RecreationSchduleSelectScheduleByFacility]
@FacilityIdList VARCHAR(MAX) = NULL
AS
BEGIN
DECLARE @SQL1 NVARCHAR(MAX) = NULL;
DECLARE @SQL2 NVARCHAR(MAX) = NULL;
SET @SQL1 = 'SELECT RecEvents.ProgramName, RecEventDateFacilityTimeslot.StartTime, RecEventDateFacilityTimeslot.EndTime, RecEventDateFacilityTimeslot.Date, RecEventDateFacilityTimeslot.BumpInMinutes,
RecEventDateFacilityTimeslot.BumpOutMinutes, RecEventDateFacilityTimeslot.FacilityID, RecEvents.ID AS EventID, RecEventDateFacilityTimeslot.EventDateFacilityTimeslotID AS TaskID,
RecFacility.Name as FacilityName,ScheduleColourCode as Color,RecEventDateFacilityTimeslot.IsFullDayEvent as IsAllDay
FROM RecEventDateFacilityTimeslot INNER JOIN
RecEvents ON RecEventDateFacilityTimeslot.EventID = RecEvents.ID INNER JOIN
RecFacility ON RecEventDateFacilityTimeslot.FacilityID = RecFacility.FacilityID
WHERE(RecEventDateFacilityTimeslot.FacilityID in ('+@FacilityIdList+'))';
SET @SQL2 = 'SELECT RecFacilityExclusionDays.FacilityID, RecFacilityExclusionDays.ExclutionDate, RecFacilityExclusionDays.StartTime, RecFacilityExclusionDays.EndTime,
RecFacilityExclusionDays.IsFullDayExclusion as IsAllDay, RecFacility.Name as FacilityName FROM RecFacilityExclusionDays INNER JOIN
RecFacility ON RecFacilityExclusionDays.FacilityID = RecFacility.FacilityID where RecFacilityExclusionDays.facilityID in('+@FacilityIdList+')';
EXECUTE sp_executesql @SQL1;
EXECUTE sp_executesql @SQL2;
END
----------------
DRAPER CODE
var obj = con.QueryMultiple("RecreationSchduleSelectScheduleByFacility", new { FacilityIdList = chan },commandType: CommandType.StoredProcedure);
var schedules = obj.Read<SchedulerTask>().ToList();
var exclutionDates = obj.Read<SchedulerTask>().ToList();
-------------------------
====================================
Handle Transactions
using (var con = DatabaseInfo.WebServiceConnectionFactory)
{
con.Open();
using (var transaction = con.BeginTransaction())
{
con.Query<FacilitySheduleAvailable>("RecreationValidateFacilityReservationDaysUTC", new{A = "google"},
commandType: CommandType.StoredProcedure, transaction: transaction).FirstOrDefault() ;
transaction.Commit();
}
catch (Exception ex) {
transaction.Rollback();
throw ex;
}
}
}
-------------------------
=============================================
MULTIPLE SELECT STATEMENTS
string sql = "select * from Employees ; select * from Students where id = @id";
using(var conn = new DatabaseInfo.WebServiceConnectionFactory){
var obj = conn.QueryMultiple(sql,new {@id = 1});
List<Employee> emp = obj.Read<Employee>().ToList(); // first select sql statement
List<Students> s = obj.Read<Student>().ToList();// second sql statement;
}
========================================================
Pass values to In Operator
List<int> lst = new List<int>(){2,3,5}
string sql = "select * from Employes in @EmployeeIds";
using(var conn = new DatabaseInfo.WebServiceConnectionFactory){
var obj = conn.Quary(sql,new {@EmployeeIds= lst });
List<Employee> emp = obj.Read<Employee>().ToList(); // first select sql statement
}
---------------------------------
Execute a Command multiple times
connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)", new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } } ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"
In sql handle
var lst = new List<int>(){1,2,3};
string sql = "select * from x where id in @idList";
conn.query <Entity>(sql,new {idList = lst} );
Handle Update and select query at once
const string sql = @"INSERT INTO Brands(Name)Values(@Name); SELECT CAST(SCOPE_IDENTITY() AS INT);";
using(var con = new sqlConnection(connectionString)){
int id = con.Quary<int>(sql,new{Name = "Toyota"}).Single();
}
Multiple insert
int tally = connection.Execute(@"insert #t (Name,Age) values(@Name, @Age)", new List<Student>
{
new Student{Age = 1, Name = "sam"},
new Student{Age = 2, Name = "bob"}
});
Insert and scope Identity
var templateId = con.Query<int>( @" insert into RecEventTicketTemplate(TemplateName,TemplateContent,IsEnable) values
(@TemplateName,@TemplateContent,@IsEnable);
SELECT CAST(SCOPE_IDENTITY() AS INT);", template, transaction).FirstOrDefault();
Dynamic sql return
dynamic result = con.Query<dynamic>("select gender as IsMale from User")
.FirstOrDefault();
gender= result.IsMale??false;
No comments:
Post a Comment