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"); }