C# SqlConnection.ExecuteReader方法代码示例

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

 /// <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();
        }

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

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

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