How to execute inserting and updating using Dapper?

c# dapper orm

Question

I'm interested in utilizing Dapper, but it only seems to support Query and Execute from what I've seen. Dapper does not seem to provide a method for inserting and updating objects.

What is the ideal technique for executing Inserts and Updates with dapper, given that our project (most projects?) need them?

We'd want not to have to use the ADO.NET technique for parameter creation, etc.

For inserts and updates, the best solution I can think of right now is to utilize LinqToSQL. Is there a more appropriate response?

1
187
5/10/2011 11:54:29 PM

Accepted Answer

We're thinking of making a few helpers, but we're currently considering on APIs and whether or not this should move into core. For progress, see https://code.google.com/archive/p/dapper-dot-net/issues/6.

You may do the following in the meanwhile.

val = "my value";
cnn.Execute("insert into Table(val) values (@val)", new {val});

cnn.Execute("update Table set val = @val where Id = @id", new {val, id = 1});

etcetera

See also That vexing INSERT issue on my blog.

Update

As mentioned in the comments, the project now has various expansions accessible in the form of these. Dapper.ContribIDbConnection techniques of extension:

T Get<T>(id);
IEnumerable<T> GetAll<T>();
int Insert<T>(T obj);
int Insert<T>(Enumerable<T> list);
bool Update<T>(T obj);
bool Update<T>(Enumerable<T> list);
bool Delete<T>(T obj);
bool Delete<T>(Enumerable<T> list);
bool DeleteAll<T>();
195
5/25/2018 5:35:08 PM

Popular Answer

Using Dapper to do CRUD activities is simple. I have mentioned the below examples that should help you in CRUD operations.

RUD's code is: C

When entering data from distinct entities, this approach is utilized. Method #1:

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string insertQuery = @"INSERT INTO [dbo].[Customer]([FirstName], [LastName], [State], [City], [IsActive], [CreatedOn]) VALUES (@FirstName, @LastName, @State, @City, @IsActive, @CreatedOn)";

    var result = db.Execute(insertQuery, new
    {
        customerModel.FirstName,
        customerModel.LastName,
        StateModel.State,
        CityModel.City,
        isActive,
        CreatedOn = DateTime.Now
    });
}

When your entity properties and SQL columns have the identical names, this technique is employed. Because Dapper is an ORM, it converts entity properties to SQL columns. Method #2:

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string insertQuery = @"INSERT INTO [dbo].[Customer]([FirstName], [LastName], [State], [City], [IsActive], [CreatedOn]) VALUES (@FirstName, @LastName, @State, @City, @IsActive, @CreatedOn)";

    var result = db.Execute(insertQuery, customerViewModel);
}

CUD's code is: R

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string selectQuery = @"SELECT * FROM [dbo].[Customer] WHERE FirstName = @FirstName";

    var result = db.Query(selectQuery, new
    {
        customerModel.FirstName
    });
}

CRD's code is: U

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string updateQuery = @"UPDATE [dbo].[Customer] SET IsActive = @IsActive WHERE FirstName = @FirstName AND LastName = @LastName";

    var result = db.Execute(updateQuery, new
    {
        isActive,
        customerModel.FirstName,
        customerModel.LastName
    });
}

CRU's code is: D

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string deleteQuery = @"DELETE FROM [dbo].[Customer] WHERE FirstName = @FirstName AND LastName = @LastName";

    var result = db.Execute(deleteQuery, new
    {
        customerModel.FirstName,
        customerModel.LastName
    });
}


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow