C# SqlConnection.Query方法代码示例

 public List<BootcampTechnology> GetAllBootcampTechnologies()
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         return connection.Query<BootcampTechnology>("BootcampTechnologyGetAll", commandType: CommandType.StoredProcedure).ToList();
     }
 }

        public IEnumerable<Post> ListarComPaginacao(int pagina, int quantidadeDePosts, string termoDePesquisa)
        {
            using (var conexao = new SqlConnection(StringsDeConexao.SqlServer))
            {
                string filtro = !String.IsNullOrWhiteSpace(termoDePesquisa) ? @"WHERE Titulo LIKE @TermoDePesquisa OR Conteudo LIKE @TermoDePesquisa" : String.Empty;

                string consulta = String.Format(@"DECLARE @QuantidadeDePosts INT = {0}, @Pagina INT = {1}
                                                                    SELECT Codigo, Titulo, Conteudo, Url, Data, CaminhoDaImagemDaCapa
                                                                    FROM Post
                                                                    {2}
                                                                    ORDER BY Codigo DESC
                                                                    OFFSET(@Pagina - 1) * @QuantidadeDePosts ROWS
                                                                    FETCH NEXT @QuantidadeDePosts ROWS ONLY", quantidadeDePosts, pagina, filtro);

                var posts = Enumerable.Empty<PostBD>();

                if (String.IsNullOrWhiteSpace(filtro))
                {
                    posts = conexao.Query<PostBD>(consulta);
                }
                else
                {
                    posts = conexao.Query<PostBD>(consulta, new { TermoDePesquisa = "%" + termoDePesquisa + "%" });
                }

                foreach (var post in posts)
                {
                    post.Tags = conexao.Query<string>("SELECT Tag as Nome from TagsDoPost WHERE CodigoDoPost = @Codigo", new { post.Codigo }).ToArray();
                }

                return posts;
            }
        }

        public ActionResult Index(LinkedServer id)
        {
            if (id == null)
            {
                ViewBag.Message = "Error: Must provide a Linked Server and Database Owner";
                return RedirectToAction("Index", "Home", new {Message = "Must Select a Linked Server to continue!"});
            }
            else
            {
                var includeEmptyTables = id.IncludeEmptyTables ?? false;
                var linkedServerName = id.Name as string ?? "SIS";

                ViewBag.Message = linkedServerName + " Database Owners";

                using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString))
                {
                    conn.Open();

                    var loginId = User.Identity.Name;
                    var bannerUserList = conn.Query(string.Format("SELECT LoginId FROM BannerLoginIds WHERE LoginId = '{0}'", loginId)).ToList();
                    var includeBannerItems = bannerUserList.Count() == 1 ? true : false;

                    var databaseOwners = conn.Query<DatabaseOwner>(
                            @"EXEC usp_GetLinkedServerDatabaseOwnersAndTableCount @LinkedServerNames = @linkedServerName, @IncludeEmptyTables = @includeEmptyTables, @IncludeBannerItems = @includeBannerItems",
                            new
                                {
                                    @linkedServerName = linkedServerName,
                                    @includeEmptyTables = includeEmptyTables,
                                    @includeBannerItems = includeBannerItems
                                }).ToList();

                    return View(databaseOwners);
                }
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            using (IDbConnection db = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnStringDb"].ToString()))
            {
                try
                {
                    var household = db.Query("SELECT * FROM CensusHousehold WHERE Id = @Id", new { Id = Request.QueryString["id"] });

                    foreach (Dictionary<string, object> dict in household)
                    {
                        mainDiv.InnerHtml += dict["Address"] + "<br />";
                    }

                    mainDiv.InnerHtml += "<br />";

                    var people = db.Query("SELECT * FROM CensusHouseholdPerson WHERE CensusHouseholdId = @Id", new { Id = Request.QueryString["id"] });

                    foreach (Dictionary<string, object> dict in people)
                    {
                        mainDiv.InnerHtml += dict["Name"] + "<br />";
                    }
                }
                catch (Exception ex)
                {
                    exception.InnerText = ex.Message;
                }
            }
        }

        public Cart GetUserCart(out string message)
        {
            message = "Can't identify current user. Please login using your credential.";
            string currentUser = System.Web.HttpContext.Current.Session.SessionID;
            if(string.IsNullOrEmpty(currentUser))
            {
                return null;
            }

            bool checkedOut = false;
            DateTime dateCreated = DateTime.UtcNow;
            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                conn.Open();
                try
                {
                    var result = conn.Query<Cart>(@"
                    SELECT * 
                    FROM dbo.Cart
                    where UserName = @currentUser
                      and CheckedOut = 0
                    ", new { currentUser });

                    if (result != null &&  result.Any())
                        return result.ElementAtOrDefault(0);

                    result = conn.Query<Cart>(@"
                    INSERT INTO dbo.Cart(UserName,DateCreated,CheckedOut) VALUES (@currentUser,@dateCreated,@checkedOut)
                    

                    SELECT * 
                    FROM dbo.Cart
                    where UserName = @currentUser
                      and CheckedOut = 0
                    ", new { currentUser,dateCreated,checkedOut });

                    if (result != null && result.Any())
                        return result.ElementAtOrDefault(0);


                }
                catch (Exception ex)
                {
                    logger.Error(ex);
                }
            }
            return null;
        }

        public Models.ParentModel Execute(Guid SchoolId)
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                var result =
                    connection.
                    Query(sql: My.Resources.WeeklyReportsDelivered,
                          param: new { @SchoolId = SchoolId },
                          commandType: CommandType.Text).
                    Select(r => new
                    {
                        emailCount = r.emails,
                        emailType = (string)r.type,
                        schoolName = r.SchoolName
                    }).
                    ToList();

                var warnings =
                    connection.
                    Query(sql: My.Resources.CognitiveWeakness,
                          param: new { @SchoolId = SchoolId },
                          commandType: CommandType.Text).
                    Select(r => (int)r.trigger_count).
                    Sum();

                return new Models.ParentModel
                {
                    CurrentSchoolYearTitle = "For School Year 2015 - 2016",
                    EarlyWaringsIssued = warnings,
                    WeeklyReportsDelivered = result.Where(r => r.emailType.Equals("Weekly", StringComparison.InvariantCultureIgnoreCase)).First().emailCount,
                    SchoolName = result.First().schoolName,
                    SchoolId = SchoolId
                };
            }
        }

        public int Add(LogisticsCompany c, string currentUser)
        {
            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                conn.Open();
                var result = conn.Query<int>(@"
                    insert into invoice.Company(CompanyName,ContactPerson,
	                                            AddressLine1,AddressLine2,City,State,Zip,Email,
	                                            MobileNumber,OfficeNumber,FaxNumber,ComplimentaryWeight,WeightRate,
	                                            BasePickupCharge,CreatedBy,CreatedAt)
                                           values(@CompanyName,@ContactPerson,
	                                            @AddressLine1,@AddressLine2,@City,@State,@Zip,@Email,
	                                            @MobileNumber,@OfficeNumber,@FaxNumber,@ComplimentaryWeight,@WeightRate,
	                                            @BasePickupCharge,@CreatedBy,getdate());
                   SELECT SCOPE_IDENTITY()

                   
                                            ", c);

                var result1 = conn.Query<int>(@"
                                            insert into invoice.UserCompany(UserId, CompanyId, CreatedBy, CreatedAt)
                                            values(@CurrentUser, @CompanyId, @EnvUser, getdate());
                                            ", new { CurrentUser = currentUser , CompanyId = result.FirstOrDefault(), EnvUser = Environment.UserName });

                


                return result.FirstOrDefault();
            }
        }

        public static void SaveTags(Tag tags, int taskId)
        {
            using (var db = new SqlConnection(ConnectionString))
            {
                db.Open();
                var q = @"INSERT INTO [Collect2000].[ERCTasks].[Tags]
                        (tag)
                        VALUES (@TagDescription);SELECT SCOPE_IDENTITY();";
             var tagID=   db.Query<int>(q, new
                {
                    TagDescription = tags.tag

                });
                var q1 = @"INSERT INTO [Collect2000].[ERCTasks].[TaskTags]
                        (TagId,TaskId)
                        VALUES (@TagID,@TaskID);";
                db.Query<int>(q1, new
                {
                    TagID = tagID,
                    TaskID = taskId

                });

            }
        }

 /// <summary>
 /// Inserts a new patient into the repository
 /// </summary>
 /// <param name="entity">Entity to insert</param>
 public void Insert(Domain.Patient entity)
 {
     const string CurrentAddressSql = "INSERT INTO patient.CurrentAddress (Street, Unit, City, State, ZipCode, ZipCodeSupplement) VALUES (@Street, @Unit, @City, @State, @ZipCode, @ZipCodeSupplement); SELECT SCOPE_IDENTITY();";
     const string PatientSql = "INSERT INTO patient.Patient (FirstName, LastName, CurrentAddressId) VALUES (@FirstName, @LastName, @CurrentAddressId); SELECT SCOPE_IDENTITY();";
     using (IDbConnection connection = new SqlConnection(_connectionString))
     {
         connection.Open();
         entity.CurrentAddress.CurrentAddressId = connection.Query<int>(CurrentAddressSql, entity.CurrentAddress).First();
         entity.PatientId = connection.Query<int>(PatientSql, new { entity.FirstName, entity.LastName, entity.CurrentAddress.CurrentAddressId }).First();
     }
 }

        public static void DeleteTag(int tagId)
        {
            using (var db = new SqlConnection(ConnectionString))
            {
                db.Open();
                var q = @"DELETE FROM [collect2000].[ERCTasks].[Tags] WHERE TagId = @TagId";
                db.Query(q, new { TagId = tagId });
                var q1 = @"DELETE FROM [collect2000].[ERCTasks].[TaskTags] WHERE TagId = @TagId";
                db.Query(q1, new { TagId = tagId });

            }
        }

        static void Main(string[] args)
        {
            var connectionString = args[0];
            var key = args[1];

            using (var conn = new SqlConnection(connectionString)) {
                conn.Open();

                var usages = conn.Query<Db.Usage>("SELECT U.* FROM Usages U INNER JOIN Users ON Users.UserId = U.UserId WHERE ApiKey = @Key ORDER BY [Timestamp]", new { Key = key }, commandType: CommandType.Text).ToList();
                var references = conn.Query<Db.Reference>("SELECT R.* FROM [References] R INNER JOIN Users ON Users.UserId = R.UserId WHERE ApiKey = @Key ORDER BY [Date]", new { Key = key }, commandType: CommandType.Text).ToList();

                var startDate = usages.First().Timestamp.Date;
                var endDate = usages.Last().Timestamp.Date;

                var lastUsage = usages.First();

                var newUsages = new List<DailyUsage>();

                for (DateTime date = startDate; date <= endDate; date = date.AddDays(1)) {
                    var newDailyUsage = new DailyUsage() { Date = date };

                    var reference = references.SingleOrDefault(x => x.Date == date);
                    newDailyUsage.Reference = new Reference();
                    if (reference != null) {
                        newDailyUsage.Reference.Electricity = reference.Electricity;
                        newDailyUsage.Reference.Gas = reference.Gas;
                    }

                    var dateUsages = usages.Where(x => x.Timestamp >= date && x.Timestamp < date.AddDays(1)).OrderBy(x=> x.Timestamp).ToList();
                    newDailyUsage.Standings = new HourlyStanding[25];
                    for (int i = 0; i <= 24; i++) {
                        var hourlyUsage = dateUsages.SingleOrDefault(x => x.Timestamp == date.AddHours(i));
                        if (hourlyUsage != null)
                            lastUsage = hourlyUsage;
                        newDailyUsage.Standings[i] = new HourlyStanding() {
                            Hour = i,
                            E1 = i == 24 ? lastUsage.E1Current : lastUsage.E1Start,
                            E2 = i == 24 ? lastUsage.E2Current: lastUsage.E2Start,
                            E1Retour = i == 24 ? lastUsage.E1RetourCurrent : lastUsage.E1RetourStart,
                            E2Retour = i == 24 ? lastUsage.E2RetourCurrent : lastUsage.E2RetourStart,
                            PvProduction = 0,
                            Gas = i == 24 ? lastUsage.GasCurrent : lastUsage.GasStart
                        };
                    }

                    newUsages.Add(newDailyUsage);
                }

                string json = JsonConvert.SerializeObject(newUsages.ToArray());

                System.IO.File.WriteAllText(@"C:\temp\mongousages.txt", json);
            }
        }

        public void BasicSPROCs()
        {
            using (IDbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Chinook"].ConnectionString))
            {
                conn.Open();
                var albums = conn.Query<AlbumResult>("spGetAlbumsWithArtist", commandType: CommandType.StoredProcedure);
                Assert.That(albums.Count(), Is.EqualTo(347));

                var albumsFiltered = conn.Query<AlbumResult>("spGetAlbumsWithArtist", new { Artist = "AC/DC" }, commandType: CommandType.StoredProcedure);
                Assert.That(albumsFiltered.Count(), Is.EqualTo(2));

            }
        }

        public ActionResult Details(SearchModel model)
        {
            if (ModelState.IsValid)
            {
                using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString))
                {
                    var searchString = model.SearchString;
                    var searchTables = model.SearchTables;
                    var searchColumns = model.SearchColumns;
                    var searchComments = model.SearchComments;
                    var selectedServerNames = model.SelectedServerNames;
                        //This what's populated when a user selects (a) linked server(s).
                    var selectedServerNamesString = "";
                    if (selectedServerNames != null && selectedServerNames.Any())
                    {
                        selectedServerNamesString = selectedServerNames.Aggregate(selectedServerNamesString,
                                                                                  (current, name) =>
                                                                                  current + (name + ","));
                        selectedServerNamesString = selectedServerNamesString.Substring(0,
                                                                                        selectedServerNamesString.Length -
                                                                                        1);
                    }

                    conn.Open();

                    var loginId = User.Identity.Name;
                    var bannerUserList = conn.Query(string.Format("SELECT LoginId FROM BannerLoginIds WHERE LoginId = '{0}'", loginId)).ToList();
                    var includeBannerItems = bannerUserList.Count() == 1 ? true : false;

                    var results =
                        conn.Query<SearchResult>(
                            @"SELECT * FROM dbo.udf_GetTableColumnCommentsResults(@searchString, @LinkedServerNames,
                                @SearchTables, @SearchColumns, @SearchComments, @IncludeBannerItems)",
                            new {@searchString = searchString, @LinkedServerNames = selectedServerNamesString,
                                 @SearchTables = searchTables,
                                 @SearchColumns = searchColumns,
                                 @SearchComments = searchComments,
                                 @IncludeBannerItems = includeBannerItems
                            }).ToList();

                    if (results.Count == 1)
                    {
                        // means we got back a single matching table => jump to table details page:
                        var result = results.FirstOrDefault();
                        return RedirectToAction("Details", "Tables", new Table { LinkedServerName = result.LinkedServerName, Owner = result.Owner, TableName = result.TableName });
                    }
                    return View(results);
                }
            }
            return View(model);
        }

        public void BasicCRUD()
        {
            using (IDbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Chinook"].ConnectionString))
            {
                conn.Open();
                var x = conn.Query<int>("INSERT INTO Artist (Name) VALUES (@Name); SELECT cast(@@Identity as int);", new { Name = "NewArtist" }).First();
                Assert.That(x, Is.GreaterThan(0));

                var artist = conn.Query<Artist>("INSERT INTO Artist (Name) VALUES (@Name); SELECT * from Artist WHERE ArtistId=SCOPE_IDENTITY();", new { Name = "NewArtistX" }).First();
                Assert.That(artist.ArtistId, Is.GreaterThan(0));
                Assert.That(artist.Name, Is.EqualTo("NewArtistX"));

            }
        }

        public IList<Hop> GetAll(params string[] navigationProperties)
        {
            using (var context = new SqlConnection(SqlConnection))
            {
                var sql = @"SELECT * FROM Hops h LEFT JOIN Origins o ON h.OriginId = o.OriginId";
                var hops = context.Query<Hop, Origin, Hop>(sql, (hop, origin) =>
                {
                    hop.Origin = origin;
                    hop.Flavours = new List<HopFlavour>();
                    hop.Substituts = new List<Hop>();
                    return hop;
                }, splitOn: "OriginId");

                var hopFlavours = context.Query<HopFlavour>("SELECT * FROM HopFlavours WHERE HopId in @Ids",
                    new { Ids = hops.Select(h => h.HopId).Distinct() });

                var flavours = context.Query<Flavour>("SELECT * FROM Flavours WHERE FlavourId in @Ids",
                    new { Ids = hopFlavours.Select(m => m.FlavourId).Distinct() });

                var substitutes = context.Query<Substitute>("SELECT * FROM Substitute WHERE HopId in @Ids",
                    new { Ids = hops.Select(h => h.HopId).Distinct() });

                foreach (var substitute in substitutes)
                {
                    var hop = hops.SingleOrDefault(h => h.HopId == substitute.HopId);
                    var sub = hops.SingleOrDefault(h => h.HopId == substitute.SubstituteId);
                    if (hop == null || sub == null) break;
                    if (hop.Substituts == null)
                        hop.Substituts = new List<Hop>();
                    hop.Substituts.Add(sub);
                }

                foreach (var hopFlavour in hopFlavours)
                {
                    var flavour = flavours.SingleOrDefault(f => f.FlavourId == hopFlavour.FlavourId);
                    if (flavour != null)
                        hopFlavour.Flavour = flavour;
                    var hop = hops.SingleOrDefault(h => h.HopId == hopFlavour.HopId);
                    if (hop == null) break;
                    if (hop.Flavours == null)
                        hop.Flavours = new List<HopFlavour>();
                    hop.Flavours.Add(hopFlavour);
                }

                return hops.ToList();
            }

        }