use dapper in asp.net mvc #
Dapper is a popular micro-ORM (Object-Relational Mapping) library for .NET that allows you to perform efficient and fast database operations with minimal overhead. Here's an example of how you can use Dapper in an ASP.NET MVC project:
Step 1: Install Dapper
First, you need to install the Dapper NuGet package. You can do this using the Package Manager Console in Visual Studio or by adding a reference to the Dapper package in your project file.
Step 2: Define a model
Create a model class that represents the data you want to retrieve from the database. For example, let's create a simple "Person" model with two properties: Id and Name.
c#
Copy code
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
Step 3: Configure database connection
In your ASP.NET MVC project, configure your database connection string in the web.config file or the appsettings.json file. Here's an example of how you can configure a connection string in the web.config file:
xml
Copy code
<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=YourDatabaseName;Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
Step 4: Use Dapper in your controller
In your controller, you can use Dapper to perform database operations such as querying, inserting, updating, and deleting data. Here's an example of how you can use Dapper to query data from the database and return a list of Person objects:
c#
Copy code
using System.Data.SqlClient;
using Dapper;
public class PersonController : Controller
{
private string _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
public ActionResult Index()
{
List<Person> people;
using (var dbConnection = new SqlConnection(_connectionString))
{
dbConnection.Open();
string sql = "SELECT Id, Name FROM People";
people = dbConnection.Query<Person>(sql).ToList();
}
return View(people);
}
}
In this example, we first create a SqlConnection object using the connection string from the web.config file. Then, we use the Query<T> method provided by Dapper to execute a SQL query and map the result to a list of Person objects. Finally, we pass the list of people to a view and return it to the client.
Step 5: Display data in a view
Create a view that displays the list of Person objects returned from the controller. You can use Razor syntax or any other view engine supported by ASP.NET MVC to render the data in HTML.
That's it! You've successfully used Dapper in your ASP.NET MVC project to perform database operations. Remember to always use best practices for handling database connections and parameterized queries to prevent SQL injection attacks.
Advance version of Dapper Repo
using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Domain.DAL.App
{
public class MyRepository : IDisposable
{
IDbConnection _connection;
public MyRepository(IDbConnection connection)
{
_connection = connection;
if (_connection.State != ConnectionState.Open)
_connection.Open();
}
public MyRepository(string ConnectionString) : this(new SqlConnection(ConnectionString)) { }
public MyRepository() : this(ConfigurationManager.ConnectionStrings["appname"].ConnectionString) { }
public void Dispose()
{
if (_connection != null)
{
if (_connection.State != ConnectionState.Closed)
_connection.Close();
_connection.Dispose();
}
}
#region lookup Data
public List<DropDownDTO> GetserviceStatus()
{
try
{
DynamicParameters prams = new DynamicParameters();
return _connection.Query<DropDownDTO>("[usp_get_service_status]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region Dashboard
public List<serviceDashboard> serviceUnitCalculations()
{
try
{ // get_service_count_by_status
DynamicParameters prams = new DynamicParameters();
//prams.Add("@user_id", user_id);
return _connection.Query<serviceDashboard>("usp_get_service_unit_calculations",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<serviceDashboard> GetserviceAmountAndUnits()
{
try
{ // get_service_count_by_status
DynamicParameters prams = new DynamicParameters();
//prams.Add("@user_id", user_id);
return _connection.Query<serviceDashboard>("usp_get_service_amount_and_Units",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<serviceDashboard> GetserviceCountByStatus()
{
try
{ // get_service_count_by_status
DynamicParameters prams = new DynamicParameters();
//prams.Add("@user_id", user_id);
return _connection.Query<serviceDashboard>("usp_get_service_count_by_status",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public DashboardDTO GetserviceCounter()
{
try
{
string spname = "";
DynamicParameters prams = new DynamicParameters();
spname = "ups_get_service_counter";
return _connection.Query<DashboardDTO>(spname,
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public DashboardDTO GetDashboardSummary(long user_id)
{
try
{
string spname = "";
DynamicParameters prams = new DynamicParameters();
if (user_id>0)
{
prams.Add("@user_id", user_id);
spname = "ups_get_dashboard_summaryv2ByuserId";
}
else
{
spname = "ups_get_dashboard_summaryv2";
}
return _connection.Query<DashboardDTO>(spname,
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region service
public user_setup_billing_shipping_details_DTO GetuserSetupBillingShippingDetails(long user_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@user_id", user_id);
return _connection.Query<user_setup_billing_shipping_details_DTO>("usp_get_user_setup_billing_shipping_details",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public List<service_detail> serviceItemDetailsV2(long service_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@service_id", service_id);
return _connection.Query<service_detail>("usp_get_service_detail_items",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<service_detail> serviceItemsDetailOnly(long service_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@service_id", service_id);
return _connection.Query<service_detail>("usp_get_service_detail_items",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<service_detail> serviceItemsDetail(long service_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@service_id", service_id);
return _connection.Query<service_detail>("[usp_get_service_detail_by_service_id]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<service_detail> servicesByStatusKeywordsSettled(int service_status, string searchkeyword)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_status", service_status);
prams.Add("searchkeyword", searchkeyword);
return _connection.Query<service_detail>("[usp_get_services_by_status_keywords_settled]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<service_detail> servicesByStatusKeywords(int service_status,string searchkeyword)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_status", service_status);
prams.Add("searchkeyword", searchkeyword);
return _connection.Query<service_detail>("[usp_get_services_by_status_keywords]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<service_detail> serviceSettlement()
{
try
{
DynamicParameters prams = new DynamicParameters();
//prams.Add("service_status", service_status);
return _connection.Query<service_detail>("usp_get_service_settlement",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<service_detail> servicesByStatus(int service_status)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_status", service_status);
return _connection.Query<service_detail>("[usp_get_services_by_status]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<PreserviceDTO> Preservices()
{
try
{
DynamicParameters prams = new DynamicParameters();
// prams.Add("@top", top);
return _connection.Query<PreserviceDTO>("[usp_get_Preservice]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public PreserviceDTO GetserviceNumber(long user_id,string nej_po,string label,string service_number)
{
PreserviceDTO o = new PreserviceDTO();
try
{
if (label=="N/A")
label = "N A"; // 865 TJ 1609 UP "Eddie Bauer" 1000373TJX-EB-1
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
prams.Add("nej_po", nej_po);
prams.Add("label", label);
prams.Add("service_number ", service_number);
o = _connection.Query<PreserviceDTO>("usp_get_global_service_number",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
o.service_number = nej_po;
}
return o;
}
public PreserviceDTO GetserviceId()
{
try
{
DynamicParameters prams = new DynamicParameters();
// prams.Add("@top", top);
return _connection.Query<PreserviceDTO>("usp_get_service_id",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public serviceFormatDTO GetserviceFormats(long user_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@user_id", @user_id);
return _connection.Query<serviceFormatDTO>("[usp_get_service_format]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public InvSumamryByCustomer GetserviceSummaryById(long ID)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@id", ID);
return _connection.Query<InvSumamryByCustomer>("[usp_get_service_summary_by_id]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public List<InvSumamryByCustomer> SearchserviceSummaryByCustomer(int OffsetValue, int PagingSize)
{
try
{
DynamicParameters prams = new DynamicParameters();
//prams.Add("user_id", user_id);
//prams.Add("PO_Num", PO_Num);
//prams.Add("PO_Status", PO_Status);
prams.Add("OffsetValue", OffsetValue);
prams.Add("PagingSize", PagingSize);
return _connection.Query<InvSumamryByCustomer>("usp_search_service_summary_by_customer",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
// [usp_search_service_summary_by_user_id]
public List<InvSumamryByCustomer> SearchserviceSummaryByuserId(long user_id, int OffsetValue, int PagingSize)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
prams.Add("OffsetValue", OffsetValue);
prams.Add("PagingSize", PagingSize);
return _connection.Query<InvSumamryByCustomer>("usp_search_service_summary_by_user_id",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<InvSumamryByCustomer> SearchserviceSummaryByCriteria(string Keyword,long user_id, int OffsetValue, int PagingSize)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
/// prams.Add("PO_Status", PO_Status);
prams.Add("Keyword", Keyword);
prams.Add("OffsetValue", OffsetValue);
prams.Add("PagingSize", PagingSize);
return _connection.Query<InvSumamryByCustomer>("usp_search_service_summary_by_criteria",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<InvSumamryByCustomer> serviceSummaryByCustomer(int top,long user_id)
{
string storeProcedureName = "usp_get_service_summary_by_customer";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@top", top);
if (user_id>0)
{
prams.Add("user_id", user_id);
storeProcedureName = "usp_get_service_summary_by_customer_user_id";
}
return _connection.Query<InvSumamryByCustomer>(storeProcedureName,
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public RequestDTO Updateservice_statusStatus(long ID)
{
RequestDTO r = new RequestDTO();
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("ID", ID);
_connection.Execute("usp_update_inv_summary_status",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
r.Message = ex.ToString();
}
return r;
}
public RequestDTO UpdateInvSumamryserviceSentStatus(long ID)
{
RequestDTO r = new RequestDTO();
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("ID", ID);
_connection.Execute("usp_update_inv_summary_status",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
r.Message = ex.ToString();
}
return r;
}
public RequestDTO CreateserviceHistory(long service_id, int is_create)
{
RequestDTO r = new RequestDTO();
//string msg = string.Empty;
//try
//{
// DynamicParameters prams = new DynamicParameters();
// prams.Add("@service_id", service_id);
// prams.Add("@is_create", is_create);
// _connection.Execute("create_service_history",
// prams,
// commandTimeout: 0,
// commandType: CommandType.StoredProcedure);
//}
//catch (Exception ex)
//{
// r.Message = ex.ToString();
//}
return r;
}
public RequestDTO UpdateserviceStatus(long service_id,int service_status)
{
RequestDTO r = new RequestDTO();
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_id", service_id);
prams.Add("service_status", service_status);
//prams.Add("creation_user", obj.creation_user);
//prams.Add("service_number", obj.service_number);
// prams.Add("label", obj.label);
_connection.Execute("[usp_update_service]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
r.Message = ex.ToString();
}
return r;
}
public RequestDTO UpdateserviceSettlement(service obj)
{
RequestDTO r = new RequestDTO();
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_id", obj.service_id);
prams.Add("nej_po", obj.nej_po);
prams.Add("buyer", obj.buyer);
prams.Add("user_po_number", obj.user_po_number);
prams.Add("service_number", obj.service_number);
prams.Add("service_amount", obj.service_amount);
prams.Add("date_service_email", obj.date_service_email);
prams.Add("date_cash_received", obj.date_cash_received);
prams.Add("amount_received", obj.amount_received);
prams.Add("service_settled", obj.service_settled);
prams.Add("payment_reference", obj.payment_reference);
prams.Add("service_approved", obj.service_approved);
prams.Add("service_date", obj.service_date);
_connection.Execute("[usp_UpdateserviceSettlement]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
r.Message = ex.ToString();
}
return r;
}
public RequestDTO Updateservices(service obj)
{
RequestDTO r = new RequestDTO();
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_id", obj.service_id);
prams.Add("service_status", obj.service_status);
prams.Add("service_number", obj.service_number);
prams.Add("label", obj.label);
prams.Add("service_date", obj.service_date);
prams.Add("buyer", obj.buyer);
prams.Add("sale_person", obj.sale_person);
prams.Add("price", obj.price);
prams.Add("service_units", obj.service_units);
prams.Add("service_amount", obj.service_amount);
prams.Add("due_date", obj.due_date);
prams.Add("ship_date", obj.ship_date);
//prams.Add("payment_terms", obj.payment_terms);
prams.Add("user_po_number", obj.user_po_number);
//prams.Add("nej_po", obj.nej_po);
prams.Add("ship_via", obj.ship_via);
prams.Add("payment_terms", obj.payment_terms);
prams.Add("service_settled", obj.service_settled);
_connection.Execute("usp_update_services",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
r.Message = ex.ToString();
}
return r;
}
public RequestDTO Createservices(service obj)
{
RequestDTO r = new RequestDTO();
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_number", obj.service_number);
prams.Add("service_date", obj.service_date);
prams.Add("service_amount", obj.service_amount);
prams.Add("service_units", obj.service_units);
prams.Add("price", obj.price);
prams.Add("due_date", obj.due_date);
prams.Add("ship_date", obj.ship_date);
prams.Add("payment_terms", obj.payment_terms);
prams.Add("user_po_number", obj.user_po_number);
prams.Add("nej_po", obj.nej_po);
prams.Add("buyer", obj.buyer);
prams.Add("sale_person", obj.sale_person);
prams.Add("ship_via", obj.ship_via);
prams.Add("label", obj.label);
prams.Add("service_status", obj.service_status);
prams.Add("service_to", obj.service_to);
prams.Add("bill_to", obj.bill_to);
prams.Add("ship_to", obj.ship_to);
prams.Add("user_id", obj.user_id);
prams.Add("processing_complete", obj.processing_complete);
prams.Add("creation_user", obj.creation_user);
// prams.Add("creation_user", obj.creation_user);
prams.Add("service_settled", obj.service_settled);
prams.Add("id", null, DbType.Int64, ParameterDirection.Output);
_connection.Execute("[usp_create_service]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
r.Id = prams.Get<long>("id");
}
catch (Exception ex)
{
r.Message = ex.ToString();
}
return r;
}
public string CreateserviceItem(service_detail obj)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_id", obj.service_id);
prams.Add("nej_po", obj.nej_po);
prams.Add("line_description", obj.line_description);
prams.Add("quantity", obj.quantity);
prams.Add("unit_price", obj.unit_price);
prams.Add("ext_price", obj.ext_price);
prams.Add("user_price_setup_id", obj.user_price_setup_id);
_connection.Execute("usp_create_service_details",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string DeleteExistingserviceItems(long service_id)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_id", service_id);
_connection.Execute("usp_delete_service_detail_items",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
#endregion
#region service Offer
public long CreateserviceOffer(service_offer obj)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_offer_date", obj.service_offer_date);
prams.Add("customer", obj.customer);
prams.Add("assigned_to_customer", obj.assigned_to_customer);
prams.Add("service_status", obj.service_status);
prams.Add("po_to_nej", obj.po_to_nej);
prams.Add("po_to_urban", obj.po_to_urban);
prams.Add("service_category", obj.service_category);
prams.Add("id", null, DbType.Int64, ParameterDirection.Output);
_connection.Execute("usp_create_service_offer",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
long id = prams.Get<long>("id");
return id;
}
catch (Exception ex)
{
msg = ex.ToString();
return 0;
}
}
public long UpdateserviceOfferAllDetails(service_offer obj)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_offer_date", obj.service_offer_date);
prams.Add("customer", obj.customer);
prams.Add("assigned_to_customer", obj.assigned_to_customer);
prams.Add("service_offer_id", obj.service_offer_id);
prams.Add("service_category", obj.service_category);
_connection.Execute("usp_update_service_offer_all_details",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
long id = prams.Get<long>("id");
return id;
}
catch (Exception ex)
{
msg = ex.ToString();
return 0;
}
}
public string DeleteserviceOfferExistingItems(long service_offer_id)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_offer_id", service_offer_id);
var res = _connection.Execute("usp_delete_service_offer_items",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
return msg;
}
catch (Exception ex)
{
msg = ex.ToString();
return null;
}
}
public string CreateserviceOfferDetail(service_offer_detail obj)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_offer_id", obj.service_offer_id);
prams.Add("po", obj.po);
prams.Add("price", obj.price);
prams.Add("extended_price", obj.extended_price);
prams.Add("units", obj.units);
prams.Add("description", obj.description);
prams.Add("label", obj.label);
prams.Add("quality", obj.quality);
_connection.Execute("usp_create_service_offer_details",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public List<service_offer_DTO> GetAllserviceOffers(int service_status)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_status", service_status);
var res = _connection.Query<service_offer_DTO>("usp_get_all_service_offers",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
return res.ToList();
}
catch (Exception ex)
{
msg = ex.ToString();
return null;
}
}
public List<service_offer_detail> GetserviceOfferDetailsById(long service_offer_id)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_offer_id", service_offer_id);
var res = _connection.Query<service_offer_detail>("usp_get_service_offer_detail_by_id",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
return res.ToList();
}
catch (Exception ex)
{
msg = ex.ToString();
return null;
}
}
public service_offer_DTO GetserviceOfferById(long service_offer_id)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_offer_id", service_offer_id);
var res = _connection.Query<service_offer_DTO>("usp_get_service_offer_by_id",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
return res;
}
catch (Exception ex)
{
msg = ex.ToString();
return null;
}
}
public string UpdateserviceOffer(long service_offer_id, decimal po_to_nej, decimal po_to_urban)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_offer_id", service_offer_id);
prams.Add("po_to_nej", po_to_nej);
prams.Add("po_to_urban", po_to_urban);
var res = _connection.Query<service_offer>("usp_update_service_offer",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
return msg;
}
catch (Exception ex)
{
msg = ex.ToString();
return null;
}
}
public string DeleteserviceOffer(long service_offer_id)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_offer_id", service_offer_id);
var res = _connection.Execute("usp_delete_service_offer",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
return msg;
}
catch (Exception ex)
{
msg = ex.ToString();
return null;
}
}
#endregion
#region Notifiction
public string CreateNotification(int notification_type, string message, string notification_to, string notification_by)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("notification_type", notification_type);
prams.Add("message", message);
prams.Add("notification_to", notification_to);
prams.Add("notification_by", notification_by);
_connection.Execute("[usp_create_notification]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public List<notification> GetAllNotifications()
{
return _connection.Query<notification>("usp_get_all_notifications",
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
#endregion
#region user Setup
public user_setup GetuserSetupDetails(long user_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@user_id", user_id);
return _connection.Query<user_setup>("usp_get_user_setup_detail",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public string UpdateTransactionFlow(user_setup obj)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", obj.user_id);
prams.Add("transaction_flow", obj.transaction_flow);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id);
_connection.Execute("usp_update_transaction_flow",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string UpdateuserSetup(user_setup obj)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_setup_id", obj.user_setup_id);
prams.Add("name", obj.name);
prams.Add("agreement", obj.agreement);
prams.Add("term_of_payments", obj.term_of_payments);
prams.Add("effective_date", obj.effective_date);
prams.Add("expiration_date", obj.expiration_date);
prams.Add("Factor", obj.Factor);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id);
prams.Add("customer_type", obj.customer_type);
_connection.Execute("usp_update_user_setup",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public List<service_document> GetserviceDocuments(long service_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@service_id", service_id);
return _connection.Query<service_document>("[usp_get_service_Documents]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public string DeleteserviceDocument(int service_document_id)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_document_id", service_document_id);
_connection.Execute("[usp_delete_service_document]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string CreateserviceDocument(service_document obj)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("service_id", obj.service_id);
prams.Add("description", obj.description);
prams.Add("Attachment", obj.Attachment);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id);
_connection.Execute("[usp_create_service_document]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string CreateuserDocument(user_document obj)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", obj.user_id);
prams.Add("description", obj.description);
prams.Add("Attachment", obj.Attachment);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id);
_connection.Execute("usp_create_user_document",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string CreateuserSetup(user_setup obj)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", obj.user_id);
prams.Add("name", obj.name);
prams.Add("agreement", obj.agreement);
prams.Add("term_of_payments", obj.term_of_payments);
prams.Add("effective_date", obj.effective_date);
prams.Add("expiration_date", obj.expiration_date);
prams.Add("Factor", obj.Factor);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id);
prams.Add("customer_type", obj.customer_type);
//
_connection.Execute("usp_create_user_setup",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string UpdateuserBusinessContact(user_contact obj)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("contact_id", obj.contact_id);
prams.Add("name", obj.name);
prams.Add("phone", obj.phone);
prams.Add("mobile", obj.mobile);
prams.Add("email", obj.email);
prams.Add("location", obj.location);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id);
_connection.Execute("[usp_update_user_business_contact]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string CreateuserServicePrice(user_price_setup obj)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", obj.user_id);
prams.Add("merchandise_type_id", obj.merchandise_type_id);
prams.Add("unit_price", obj.unit_price);
prams.Add("base_price", obj.base_price);
prams.Add("start_date", obj.start_date);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id);
_connection.Execute("usp_create_user_service_price",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string DeleteuserServicePrice(int user_price_setup_id, long user_id)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
prams.Add("user_price_setup_id", user_price_setup_id);
_connection.Execute("[usp_delete_user_service_price]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public user_price_setup GetuserServicePriceByPriceSetupId(long user_price_setup_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_price_setup_id", user_price_setup_id);
return _connection.Query<user_price_setup>("[usp_get_user_serviceprice_by_user_price_setup_id]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public List<user_price_setup> GetuserServicePrice(long user_id)
{
List<user_price_setup> feelist = new List<user_price_setup>();
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
feelist= _connection.Query<user_price_setup>("[usp_get_user_ServicePrice]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
///feelist.Insert(feelist.Count(), new user_price_setup { merchandise_type_id = -1, merchandiseName = "Adjustment" }); // Set Default value
return feelist;
}
catch (Exception ex)
{
throw ex;
}
}
public string CreateuserBusinessContact(user_contact obj)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", obj.user_id);
prams.Add("name", obj.name);
prams.Add("phone", obj.phone);
prams.Add("mobile", obj.mobile);
prams.Add("email", obj.email);
prams.Add("location", obj.location);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id);
_connection.Execute("usp_create_user_business_contact",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public List<user_document> GetuserDocuments(long user_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
return _connection.Query<user_document>("[usp_get_user_Documents]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public string DeleteserviceDocument(int document_id, long user_id)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
prams.Add("document_id", document_id);
_connection.Execute("[usp_delete_user_document]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string DeleteCustomerDocument(int document_id, long user_id)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
prams.Add("document_id", document_id);
_connection.Execute("[usp_delete_user_document]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string DeleteuserContact(int contact_id, long user_id)
{
string msg = "";
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("contact_id", contact_id);
prams.Add("user_id", user_id);
_connection.Execute("[usp_delete_user_contact]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public List<user_contact> GetuserContact(long user_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
return _connection.Query<user_contact>("[usp_get_user_Contacts]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public user_contact GetuserContactDetails(long contact_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("contact_id", contact_id);
return _connection.Query<user_contact>("[usp_get_user_contact_detail]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public List<CustomerDTO> GetCustomers()
{
try
{
DynamicParameters prams = new DynamicParameters();
return _connection.Query<CustomerDTO>("usp_Get_customers",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region users
public List<user> users()
{
try
{
DynamicParameters prams = new DynamicParameters();
return _connection.Query<user>("[usp_get_users]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region Merchandise Types
public merchandise_type GetMerchandiseTypeDetails(long merchandise_type_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@merchandise_type_id", merchandise_type_id);
return _connection.Query<merchandise_type>("usp_get_merchandise_type_detail",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public string DeleteMerchandiseType(int merchandise_type_id, long user_id)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("merchandise_type_id", merchandise_type_id);
prams.Add("user_id",user_id);
_connection.Execute("[usp_delete_merchandise]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string UpdateMerchandiseType(merchandise_type obj)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("merchandise_type_id", obj.merchandise_type_id);
prams.Add("user_id", obj.user_id);
prams.Add("name", obj.name);
prams.Add("userName", obj.creation_user);
prams.Add("@logid", obj.journal_id);
prams.Add("@label", obj.label);
prams.Add("@quality", obj.quality);
_connection.Execute("[usp_update_merchandise]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public string CreateMerchandiseType(merchandise_type obj)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", obj.user_id);
prams.Add("name", obj.name);
prams.Add("userName", obj.creation_user);
prams.Add("@logid", obj.journal_id);
prams.Add("@label", obj.label);
prams.Add("@quality", obj.quality);
_connection.Execute("usp_create_merchandise",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
public List<merchandise_type> GetMerchandiseTypes(long user_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_id", user_id);
return _connection.Query<merchandise_type>("usp_get_merchandise_type",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<DropDownDTO> GetPaymentTerms()
{
try
{
DynamicParameters prams = new DynamicParameters();
return _connection.Query<DropDownDTO>("[usp_get_payment_terms]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
public List<DropDownDTO> GetserviceCategory()
{
try
{
List<DropDownDTO> labelList = new List<DropDownDTO>();
DynamicParameters prams = new DynamicParameters();
labelList = _connection.Query<DropDownDTO>("[usp_get_service_category]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
labelList.Insert(0, new DropDownDTO { id = null, description = "--Select--" });
return labelList;
}
catch (Exception ex)
{
throw ex;
}
}
public List<DropDownDTO> GetLabels()
{
try
{
List<DropDownDTO> labelList = new List<DropDownDTO>();
DynamicParameters prams = new DynamicParameters();
labelList = _connection.Query<DropDownDTO>("[usp_get_label]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
labelList.Insert(0, new DropDownDTO { id = "N/A", description = "N/A" });
return labelList;
}
catch (Exception ex)
{
throw ex;
}
}
public List<DropDownDTO> GetQuality()
{
try
{
DynamicParameters prams = new DynamicParameters();
return _connection.Query<DropDownDTO>("[usp_get_quality]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region user Billing Shipping
public user_billing_shipping GetuserBillingShippingDetails(long user_id)
{
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("@user_id",user_id);
return _connection.Query<user_billing_shipping>("[usp_get_user_billing_shipping_detail]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
public string AddUpdateuserBillingShipping(user_billing_shipping obj)
{
string msg = string.Empty;
try
{
DynamicParameters prams = new DynamicParameters();
prams.Add("user_billing_shipping_id", obj.user_billing_shipping_id);
prams.Add("user_id", obj.user_id );
prams.Add("bill_to", obj.bill_to );
prams.Add("billing_addres1", obj.billing_addres1 );
prams.Add("billing_addres2", obj.billing_addres2 );
prams.Add("billing_addres3", obj.billing_addres3 );
prams.Add("billing_tel", obj.billing_tel );
prams.Add("billing_fax", obj.billing_fax );
prams.Add("ship_to", obj.ship_to );
prams.Add("shipping_addres1", obj.shipping_addres1 );
prams.Add("shipping_addres2", obj.shipping_addres2 );
prams.Add("shipping_addres3", obj.shipping_addres3 );
prams.Add("shipping_tel", obj.shipping_tel );
prams.Add("shipping_fax", obj.shipping_fax );
prams.Add("name", obj.name );
prams.Add("bill_to_email", obj.bill_to_email );
prams.Add("bill_to_email_cc", obj.bill_to_email_cc );
prams.Add("sale_person", obj.sale_person);
prams.Add("supplier_reference", obj.supplier_reference );
prams.Add("service_pmt_advice_id", obj.service_pmt_advice_id );
prams.Add("projected_days_to_pay", obj.projected_days_to_pay);
prams.Add("userName", obj.creation_user);
prams.Add("logid", obj.journal_id );
_connection.Execute("[usp_update_add_user_billing_shipping]",
prams,
commandTimeout: 0,
commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
msg = ex.ToString();
}
return msg;
}
#endregion
}
}
Jack Carter
2 hours agoi think that some how, we learn who we really are and then live with that decision, great post!
you can view the more detail via link https://www.youtube.com/watch?v=HpZgwHU1GcIChing xang
2 hours agoi think that some how, we learn who we really are and then live with that decision, great post!
Danial Comb
2 hours agoi think that some how, we learn who we really are and then live with that decision, great post!
Jack Carter
2 hours agoi think that some how, we learn who we really are and then live with that decision, great post!