public EpplusResult DonorTotalSummary(DonorTotalSummaryOptionsModel m)
{
var ep = new ExcelPackage();
var cn = new SqlConnection(Util.ConnectionString);
var rd = cn.ExecuteReader("dbo.DonorTotalSummary", DonorTotalSummaryParameters(m, useMedianMin: true), commandType: CommandType.StoredProcedure, commandTimeout: 1200);
ep.AddSheet(rd, "MemberNon");
rd = cn.ExecuteReader("dbo.DonorTotalSummaryBySize", DonorTotalSummaryParameters(m), commandType: CommandType.StoredProcedure, commandTimeout: 1200);
ep.AddSheet(rd, "BySize");
rd = cn.ExecuteReader("dbo.DonorTotalSummaryByAge", DonorTotalSummaryParameters(m), commandType: CommandType.StoredProcedure, commandTimeout: 1200);
ep.AddSheet(rd, "ByAge");
return new EpplusResult(ep, "DonorTotalSummary.xlsx");
}
public EpplusResult Result(Guid id, string report)
{
var cs = _db.CurrentUser.InRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
var sql = Sql(id, report);
return cn.ExecuteReader(sql).ToExcel(report + ".xlsx");
}
public DataTable GetProductss()
{
DataTable model = new DataTable();
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString("Clothes")))
{
model.Load(connection.ExecuteReader("dbo.[GetAllProduct]", null, commandType: CommandType.StoredProcedure));
}
return(model);
}
public EpplusResult PledgeFulfillment2(int fundid1, int fundid2)
{
var ep = new ExcelPackage();
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var rd = cn.ExecuteReader("dbo.PledgeFulfillment2", new { fundid1, fundid2, },
commandTimeout: 1200, commandType: CommandType.StoredProcedure);
ep.AddSheet(rd, "Pledges");
return new EpplusResult(ep, "PledgeFulfillment2.xlsx");
}
/// <summary>
/// Lấy toàn bộ Hóa đơn bán ra 1 data table
/// </summary>
/// <returns></returns>
public DataTable GetAllSellBills(DateTime start, DateTime end)
{
DataTable model = new DataTable();
var p = new DynamicParameters();
p.Add("@DateStart", start);
p.Add("@DateEnd", end);
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString("Clothes")))
{
model.Load(connection.ExecuteReader("dbo.[GetAllSaleInvoice]", p, commandType: CommandType.StoredProcedure));
}
return(model);
}
public DataTable GetAllSaleDetails(string id, out int quantity, out decimal total, out string Cusname)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString("Clothes")))
{
DataTable table = new DataTable();
var p = new DynamicParameters();
p.Add("@SaleInvoiceID", id);
p.Add("@Quantity", 0, DbType.Int32, direction: ParameterDirection.Output);
p.Add("@Total", 0, DbType.Decimal, direction: ParameterDirection.Output);
p.Add("@Name", "", DbType.String, direction: ParameterDirection.Output);
table.Load(connection.ExecuteReader("[GetAllProduct_In_SaleInvoice]", p, commandType: CommandType.StoredProcedure));
quantity = p.Get <int>("@Quantity");
total = p.Get <decimal>("@Total");
Cusname = p.Get <string>("@Name");
return(table);
}
}
/// <summary>
/// Try to match the requested URL against a configured redirect
/// </summary>
/// <param name="requestedUrl">The requested URL.</param>
/// <returns>
/// The matched redirect, or <c>null</c> if no matching redirect found
/// </returns>
/// <exception cref="System.ArgumentNullException">requestedUrl</exception>
/// <exception cref="ConfigurationErrorsException">RedirectsReader connection string not found in configuration file</exception>
public Redirect MatchRedirect(Uri requestedUrl)
{
if (requestedUrl == null) throw new ArgumentNullException("requestedUrl");
if (ConfigurationManager.ConnectionStrings["RedirectsReader"] == null || String.IsNullOrEmpty(ConfigurationManager.ConnectionStrings["RedirectsReader"].ConnectionString))
{
if (ThrowErrorOnMissingConfiguration)
{
throw new ConfigurationErrorsException("RedirectsReader connection string not found in configuration file");
}
else return null;
}
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["RedirectsReader"].ConnectionString))
{
using (var reader = connection.ExecuteReader("usp_Redirect_MatchRequest", new { request = requestedUrl.PathAndQuery.TrimStart('/') }, commandType: CommandType.StoredProcedure))
{
while (reader.Read())
{
var redirectId = Int32.Parse(reader["RedirectId"].ToString(), CultureInfo.InvariantCulture);
// Get the URL
var destinationUrl = new Uri(reader["Destination"].ToString(), UriKind.RelativeOrAbsolute);
// Get the HTTP status code
var redirectType = (RedirectType)Enum.Parse(typeof(RedirectType), reader["Type"].ToString());
var statusCode = (redirectType == RedirectType.Moved) ? 301 : 303;
// Return the redirect
return new Redirect()
{
RedirectId = redirectId,
RequestedUrl = requestedUrl,
DestinationUrl = destinationUrl,
StatusCode = statusCode
};
}
}
}
return null;
}
public IEnumerable<Row> Read(IEnumerable<Row> input) {
using (var cn = new SqlConnection(_input.Connection.GetConnectionString())) {
cn.Open();
var trans = cn.BeginTransaction();
cn.Execute(_create, null, trans);
var keys = input.Select(r => r.ToExpandoObject(_keys));
cn.Execute(_insert, keys, trans, _input.Connection.Timeout, System.Data.CommandType.Text);
using(var reader = cn.ExecuteReader(_query, null, trans, _input.Connection.Timeout, System.Data.CommandType.Text)) {
while (reader.Read()) {
yield return _rowCreator.Create(reader, _input.RowCapacity, _input.InputFields);
}
}
cn.Execute(_drop, null, trans);
trans.Commit();
}
}
public void ExecuteReader()
{
string sql = "SELECT 1 AS IntColumn, 'FizzBuzz' AS StringColumn WHERE @Fizz = 1";
var dict = new Dictionary<string, object> {{"@Fizz", 1}};
// Examples
using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString))
{
conn.Open();
using (IDataReader reader = conn.ExecuteReader(sql, dict.ToSqlParameters()))
{
reader.Read();
object result1 = reader[0];
object result2 = reader[1];
// Unit Test
Assert.AreEqual(1, result1);
Assert.AreEqual("FizzBuzz", result2);
}
}
}
private static List <SqlConnectionExtension.SysColumn> GetTableColumns(SqlConnection sourceConn, string tableName)
{
string cmdText = string.Format("select * from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);
List <SqlConnectionExtension.SysColumn> list = new List <SqlConnectionExtension.SysColumn>();
using (SqlConnection sqlConnection = (SqlConnection)((ICloneable)sourceConn).Clone())
{
sqlConnection.Open();
using (IDataReader dataReader = sqlConnection.ExecuteReader(cmdText, new DataParam[0]))
{
while (dataReader.Read())
{
list.Add(new SqlConnectionExtension.SysColumn
{
Name = dataReader.GetDbValue("name"),
ColOrder = dataReader.GetDbValue("colorder")
});
}
}
}
return(list);
}
/// <summary>
/// Reads data about all gritters including their current position
/// </summary>
/// <returns></returns>
public System.Collections.Generic.ICollection<Gritter> ReadAllGritters()
{
var gritters = new List<Gritter>();
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["GrittingReader"].ConnectionString))
{
using (var reader = connection.ExecuteReader("usp_Gritter_SelectAll", commandType: CommandType.StoredProcedure))
{
while (reader.Read())
{
gritters.Add(new Gritter()
{
GritterId = reader["GritterId"].ToString(),
GritterName = reader["GritterName"].ToString(),
Latitude = Double.Parse(reader["Latitude"].ToString(), CultureInfo.InvariantCulture),
Longitude = Double.Parse(reader["Longitude"].ToString(), CultureInfo.InvariantCulture),
Status = (GritterStatus) Enum.Parse(typeof (GritterStatus), reader["Status"].ToString())
});
}
}
}
return gritters;
}
public void RunSqlReport()
{
var content = DbUtil.Db.ContentOfTypeSql(Report);
if (content == null)
throw new Exception("no content");
if (!CanRunScript(content.Body))
throw new Exception("Not Authorized to run this script");
var hasqtag = content.Body.Contains("@qtagid");
var hascurrentorg = content.Body.Contains("@CurrentOrgId");
if (!hasqtag && !hascurrentorg)
throw new Exception("missing @qtagid or @CurrentOrgId");
var p = new DynamicParameters();
if (hasqtag)
{
var tag = DbUtil.Db.PopulateSpecialTag(Id, DbUtil.TagTypeId_Query);
p.Add("@qtagid", tag.Id);
}
if (hascurrentorg)
{
var oid = DbUtil.Db.CurrentOrgId0;
p.Add("@CurrentOrgId", oid);
if (oid > 0)
Name2 = DbUtil.Db.LoadOrganizationById(oid).FullName2;
}
var cs = HttpContext.Current.User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
using (var cn = new SqlConnection(cs))
{
cn.Open();
using (var rd = cn.ExecuteReader(content.Body, p))
Results = GridResult.Table(rd, Name2);
}
}
public Row[] Read(IEnumerable<Row> input) {
var results = new List<Row>();
using (var cn = new SqlConnection(_context.Connection.GetConnectionString())) {
cn.Open();
var trans = cn.BeginTransaction();
cn.Execute(_create, null, trans);
var keys = input.Select(r => r.ToExpandoObject(_keys));
cn.Execute(_insert, keys, trans, _context.Connection.Timeout, System.Data.CommandType.Text);
using(var reader = cn.ExecuteReader(_query, null, trans, _context.Connection.Timeout, System.Data.CommandType.Text)) {
while (reader.Read()) {
var row = _rowCreator.Create(reader, _rowCapacity, _fields);
row.TflHashCode = (int)row[_hashCode];
results.Add(row);
}
}
cn.Execute(_drop, null, trans);
trans.Commit();
}
return results.ToArray();
}
public ActionResult SqlReport(Guid id, string report)
{
var content = DbUtil.Db.ContentOfTypeSql(report);
if (content == null)
return Content("no content");
if (!content.Body.Contains("@qtagid"))
return Content("missing @qtagid");
var tag = DbUtil.Db.PopulateSpecialTag(id, DbUtil.TagTypeId_Query);
var cs = User.IsInRole("Finance")
? Util.ConnectionString
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
cn.Open();
var p = new DynamicParameters();
p.Add("@qtagid", tag.Id);
ViewBag.name = report;
var rd = cn.ExecuteReader(content.Body, p);
return View(rd);
}
public ActionResult RunScript(string name, string parameter = null, string title = null)
{
var content = DbUtil.Db.ContentOfTypeSql(name);
if (content == null)
return Content("no content");
var cs = User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
cn.Open();
var d = Request.QueryString.AllKeys.ToDictionary(key => key, key => Request.QueryString[key]);
var p = new DynamicParameters();
foreach (var kv in d)
p.Add("@" + kv.Key, kv.Value);
var script = RunScriptSql(DbUtil.Db, parameter, content, p);
if (script.StartsWith("Not Authorized"))
return Message(script);
ViewBag.Name = title ?? $"{name.SpaceCamelCase()} {parameter}";
ViewBag.Report = name;
ViewBag.Url = Request.Url?.PathAndQuery;
var rd = cn.ExecuteReader(script, p, commandTimeout: 1200);
ViewBag.ExcelUrl = Request.Url?.AbsoluteUri.Replace("RunScript/", "RunScriptExcel/");
return View(rd);
}
public ActionResult RunScript(string name, string parameter = null, string title = null)
{
var content = DbUtil.Db.ContentOfTypeSql(name);
if (content == null)
return Content("no content");
var cs = User.IsInRole("Finance")
? Util.ConnectionString
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
cn.Open();
var d = Request.QueryString.AllKeys.ToDictionary(key => key, key => Request.QueryString[key]);
var p = new DynamicParameters();
foreach (var kv in d)
p.Add("@" + kv.Key, kv.Value);
var script = RunScriptSql(DbUtil.Db, parameter, content.Body, p);
if (script.StartsWith("Not Authorized"))
return Message(script);
ViewBag.name = title ?? $"Run Script {name} {parameter}";
var rd = cn.ExecuteReader(script, p);
return View(rd);
}
public EpplusResult Result(string SavedQuery, string report)
{
var cs = _db.CurrentUser.InRole("Finance")
? Util.ConnectionString
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
var q = _db.PeopleQuery2(SavedQuery);
var sql = Sql(q, report);
return cn.ExecuteReader(sql).ToExcel(report + ".xlsx");
}
private IDataReader ExecuteReader(string report, string oids)
{
var content = DbUtil.Db.ContentOfTypeSql(report);
if (content == null)
throw new Exception("no content");
if (!SpecialReportViewModel.CanRunScript(content.Body))
throw new Exception("Not Authorized to run this script");
if (!content.Body.Contains("@OrgIds"))
throw new Exception("missing @OrgIds");
var p = new DynamicParameters();
p.Add("@OrgIds", oids);
var cs = HttpContext.Current.User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
cn.Open();
return cn.ExecuteReader(content.Body, p);
}
public static IList<GrittingDecision> ReadRouteSetDecisions(int pageSize, int pageNumber, out int totalDecisions)
{
var decisions = new List<GrittingDecision>();
var parameters = new DynamicParameters();
if (pageSize == -1)
{
parameters.Add("@pageSize", null);
}
else
{
parameters.Add("@pageSize", pageSize);
}
if (pageNumber == -1)
{
parameters.Add("@pageNumber", null);
}
else
{
parameters.Add("@pageNumber", pageNumber);
}
parameters.Add("@totalDecisions", null, DbType.Int32, ParameterDirection.Output);
// Read decisions. Important to include CommandType.StoredProcedure otherwise output parameter always null
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GrittingDecisionReader"].ConnectionString))
{
using (var reader = conn.ExecuteReader("usp_RouteSetDecision_Select", parameters, commandType: CommandType.StoredProcedure))
{
while (reader.Read())
{
var decision = new RouteSetDecision();
decision.DecisionId = Int32.Parse(reader["RouteSetDecisionId"].ToString(), CultureInfo.InvariantCulture);
decision.RouteSet.RouteSetName = reader["RouteSet_Web"].ToString();
decision.OriginalAction = reader["Action_Original"].ToString();
decision.Action = reader["Action_Web"].ToString();
decision.DecisionTime = DateTime.Parse(reader["DecisionTime"].ToString(), CultureInfo.CurrentCulture);
if (reader["ActionTime"] != DBNull.Value) decision.ActionTime = DateTime.Parse(reader["ActionTime"].ToString(), CultureInfo.CurrentCulture);
decisions.Add(decision);
}
}
}
// Wait until reader closed before getting access to output parameter
totalDecisions =parameters.Get<int>("@totalDecisions");
return decisions;
}
/// <summary>
/// Reads route set decisions from the last 26 hours.
/// </summary>
/// <returns></returns>
public static IList<GrittingDecision> ReadLatestRouteSetDecisions()
{
var decisions = new List<GrittingDecision>();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GrittingDecisionReader"].ConnectionString))
{
using (var reader = conn.ExecuteReader("usp_RouteSetDecision_SelectSummary", commandType: CommandType.StoredProcedure))
{
while (reader.Read())
{
var decision = new RouteSetDecision();
decision.DecisionId = Int32.Parse(reader["RouteSetDecisionId"].ToString(), CultureInfo.InvariantCulture);
decision.RouteSet.RouteSetName = reader["RouteSet_Web"].ToString();
decision.Action = reader["Action_Web"].ToString();
decision.DecisionTime = DateTime.Parse(reader["DecisionTime"].ToString(), CultureInfo.CurrentCulture);
if (reader["ActionTime"] != DBNull.Value) decision.ActionTime = DateTime.Parse(reader["ActionTime"].ToString(), CultureInfo.CurrentCulture);
decisions.Add(decision);
}
}
}
return decisions;
}
public EpplusResult RunSqlExcel()
{
DynamicParameters p;
var content = GetParameters(out p);
var cs = HttpContext.Current.User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
using (var cn = new SqlConnection(cs))
{
cn.Open();
return cn.ExecuteReader(content, p).ToExcel($"{Report.Replace(" ", "")}.xlsx", fromSql: true);
}
}
public ActionResult RunScriptExcel(string scriptname, string parameter = null)
{
var content = DbUtil.Db.ContentOfTypeSql(scriptname);
if (content == null)
return Message("no content");
var cs = User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
var d = Request.QueryString.AllKeys.ToDictionary(key => key, key => Request.QueryString[key]);
var p = new DynamicParameters();
foreach (var kv in d)
p.Add("@" + kv.Key, kv.Value);
var script = RunScriptSql(DbUtil.Db, parameter, content, p);
if (script.StartsWith("Not Authorized"))
return Message(script);
return cn.ExecuteReader(script, p).ToExcel("RunScript.xlsx", fromSql: true);
}
/// <summary>
/// Tests the Simple query retrievals
/// </summary>
static void QueryResultsetTest()
{
using (var con = new SqlConnection(ConString))
{
//Simple Select Query
//Imp 1
Action<DbDataReader> readDataFunc = reader =>
{
while (reader.Read())
{
Console.WriteLine("\nExpected - {0} , Got - {1}", "HR", reader.Get<string>("name"));
break;
}
};
con.ExecuteReader("Select * from Department", readDataFunc);
//Imp 2
con.ExecuteReader("Select * from Department", ReadData);
//Break the reader in between to check whether the con is closed or not
var state = con.State;
Console.WriteLine("\nExpected - {0}, Got - {1}", "Closed", state);
//Multiple Resultset
const string sql = @"Select * from Employee
Select * from Department";
con.ExecuteReader(sql, ReadDataMulti);
}
}
public void RunSqlReport()
{
DynamicParameters p;
var content = GetParameters(out p);
var cs = HttpContext.Current.User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
using (var cn = new SqlConnection(cs))
{
cn.Open();
using (var rd = cn.ExecuteReader(content, p))
Results = GridResult.Table(rd, Name2);
}
}
public string Table()
{
var cs = db.CurrentUser.InRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
var p = Parameters();
var sql = Sql();
if(sql.Contains("@userid"))
p.Add("@userid", Util.UserId);
var rd = cn.ExecuteReader(sql, p);
return GridResult.Table(rd, Name2, 2000);
}
public EpplusResult Result(string savedQuery)
{
var cs = db.CurrentUser.InRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
var p = Parameters(savedQuery);
var sql = Sql();
if(sql.Contains("@userid"))
p.Add("@userid", Util.UserId);
return cn.ExecuteReader(sql, p).ToExcel(Report + ".xlsx");
}
private IDataReader ExecuteReader(string report, string oids, DateTime? meetingDate1, DateTime? meetingDate2)
{
var content = DbUtil.Db.ContentOfTypeSql(report);
if (!content.HasValue())
throw new Exception("no content");
if (!SpecialReportViewModel.CanRunScript(content))
throw new Exception("Not Authorized to run this script");
if (!content.Contains("@OrgIds", ignoreCase: true))
throw new Exception("missing @OrgIds");
var p = new DynamicParameters();
p.Add("@OrgIds", oids);
if (content.Contains("@MeetingDate1", ignoreCase: true))
{
p.Add("@MeetingDate1", meetingDate1);
p.Add("@MeetingDate2", meetingDate2);
}
if(content.Contains("@userid", ignoreCase:true))
p.Add("@userid", Util.UserId);
var cs = HttpContext.Current.User.IsInRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
cn.Open();
return cn.ExecuteReader(content, p, commandTimeout: 1200);
}
/// <summary>
/// Read all the route sets for the county
/// </summary>
/// <returns></returns>
public static IList<GrittingRouteSet> ReadRouteSets()
{
var routeSets = new Dictionary<int, GrittingRouteSet>();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GrittingDecisionReader"].ConnectionString))
{
using (var reader = conn.ExecuteReader("usp_RouteSet_SelectAllRoutes", commandType: CommandType.StoredProcedure))
{
while (reader.Read())
{
var routeSetId = Int32.Parse(reader["RouteSetId"].ToString(), CultureInfo.InvariantCulture);
if (!routeSets.ContainsKey(routeSetId))
{
var routeSet = new GrittingRouteSet();
routeSet.RouteSetId = routeSetId;
routeSet.RouteSetName = reader["RouteSetName"].ToString();
routeSets.Add(routeSetId, routeSet);
}
var route = new GrittingRoute();
route.RouteName = reader["RouteName"].ToString();
routeSets[routeSetId].Routes.Add(route);
}
}
}
return new List<GrittingRouteSet>(routeSets.Values);
}
static void ParametrizedQueries()
{
using (var con = new SqlConnection(ConString))
{
//Insert statement with parameters
var sql = "Insert into department (departmentid, name) values (@depId, @name)";
var parameters = new DynamicParameters();
parameters.Add("@depId", 1);
parameters.Add("@name", "HR");
parameters.Add("@name", "HR");
var rowsAffected = con.Execute(sql, parameters);
Console.WriteLine("\nExpected - {0}, Got - {1}\n", 1, rowsAffected);
for (var i = 1; i < 5; i++)
{
sql = "Insert into Employee (id, name, department, salary ) values (@id, @name, @depId, @salary)";
parameters = new DynamicParameters();
parameters.Add("@id", i);
switch (i)
{
case 1:
parameters.Add("@name", "sfk shan");
break;
case 2:
parameters.Add("@name", "Hari");
break;
case 3:
parameters.Add("@name", "Jothi");
break;
case 4:
case 5:
parameters.Add("@name", "Priya");
break;
}
parameters.Add("@depId", 1);
parameters.Add("@salary", 5000 + i * 100);
rowsAffected = con.Execute(sql, parameters);
Console.WriteLine("Expected - {0}, Got - {1}", 1, rowsAffected);
}
var ctr = 0;
sql = @"select * from Employee where Name = @name";
parameters = new DynamicParameters();
parameters.Add("@name", "sfk shan");
//Select statement with params
//No need to specify if commandType: CommandType.Text coz its the default
con.ExecuteReader(sql, reader =>
{
while (reader.HasRows)
{
while (reader.Read())
{
//For test purpose only one row is taken for ctr is used
//In actual implementaion it wont be there
//For money the specified cast is decimal
if (ctr == 0)
Console.WriteLine("\nExpected - {0}, got {1}", 5100, reader.Get<int>("Salary"));
ctr++;
}
reader.NextResult();
}
ctr = 0;
}, parameters, commandType: CommandType.Text);
//Update statement with parameters
sql = "update Employee set salary = @salary where id >= 1 ";
parameters = new DynamicParameters();
parameters.Add("@salary", 5000);
var rowsAffected2 = con.Execute(sql, parameters);
Console.WriteLine("\nExpected - {0}, Got - {1}", 4, rowsAffected2);
}
}
public EpplusResult Result()
{
var cs = db.CurrentUser.InRole("Finance")
? Util.ConnectionStringReadOnlyFinance
: Util.ConnectionStringReadOnly;
var cn = new SqlConnection(cs);
var p = Parameters();
var sql = Sql();
return cn.ExecuteReader(sql, p).ToExcel(Report + ".xlsx");
}
