public List<BootcampTechnology> GetAllBootcampTechnologies()
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         return connection.Query<BootcampTechnology>("BootcampTechnologyGetAll", commandType: CommandType.StoredProcedure).ToList();
     }
 }
Example #2
0
        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 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();
            }
        }
Example #7
0
        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();
     }
 }
Example #9
0
        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 });

            }
        }
Example #10
0
        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"));

            }
        }
Example #14
0
        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 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();
            }

        }
Example #16
0
        public List<ListingImage> GetListingsSharingImageHash(string stateCode, ulong sharedHash)
        {
            var result = new List<ListingImage>();

            var sql = @"select
                            mpr_id as MprId,
                            master_listing_id as MlId,
                            listing_id as ListingId,
                            image_url as ImageUrl,
                            image_hash as ImageHash,
                            address_line as AddressLine,
                            city as City,
                            state as State,
                            zip as Zip
                        from [MasterPropertyRecord].[dbo].[zzz_hackathon_0115_image_hashes_try2]
                        where image_hash = @imageHash";

            string connectionString = _mprRedirect.GetConnectionStringByStateCode(stateCode, "MasterPropertyRecord");
            using (var dbConnection = new SqlConnection(connectionString))
            {
                dbConnection.Open();
                var items = dbConnection.Query<ListingImage>(sql, new { imageHash = (decimal)sharedHash }, commandTimeout: 9800);
                foreach (var item in items)
                    result.Add(item);
            }

            return result;
        }
 public bool CheckIndividualCustomerExist(string customerID)
 {
     using (var conn = new SqlConnection(DataProvider.ConnectionString))
     {
         return conn.Query<bool>("SELECT 1 FROM BCUSTOMER_INFO WHERE CustomerID= @CustomerID", new { CustomerID = customerID }).Any();
     }                                                                                      //tao tham so:CustomerID gan vao @CustomerID o cac cau SQL
 }
        public List<FailedFaxList> GetFailedFaxList()
        {
            List<FailedFaxList> failedFaxListList = new List<FailedFaxList>();

            try
            {
                using (IDbConnection db = new SqlConnection(ConfigurationValues.WaldenFaxConnection))
                {
                    const string query = "SELECT [SendID],[AccountID],[UserID],[FaxName],[FaxPath],[FaxNumber],[RecipientName]"
                        + " ,[Notes],[PageCount],[FaxSent],[InUse],[ToTif],[CallWait],[TimesCalled],[Status]"
                        + " ,[ShowFax],[CreateTime],[CompletionTime],[DateStamp]"
                        + " FROM [FaxesSendServer]"
                        + " where userId = 'faxAutomation'"
                        + " and status <> 'Completed'"
                        + " and CallWait > 2"
                        + " order by sendid";

                    failedFaxListList = db.Query<FailedFaxList>(query).ToList();
                    return failedFaxListList;
                }
            }
            catch (Exception er)
            {
                Utility.LogErrors(ConfigurationValues.ErrorLogPath, er.ToString());
                return failedFaxListList;
            }
        }
Example #19
0
 private IList<Component> GetWebsiteComponents(int websiteId)
 {
     using (SqlConnection connection = new SqlConnection(this._connectionString))
     {
         return connection.Query<Component>("select * from Components Inner Join WebsiteComponents On Components.ComponentId = WebsiteComponents.ComponentId Where WebsiteId = @WebsiteId", new { WebsiteId = websiteId }).ToList();
     }
 }
        public List<DeletedDocument> GetDeletedDocuments()
        {
            List<DeletedDocument> deletedDocumentList = new List<DeletedDocument>();

            try
            {
                using (IDbConnection db = new SqlConnection(ConfigurationValues.PostOfficeDatabaseConnection))
                {
                    //Id
                    const string query = "SELECT [ID],[NewDocumentPath] as Id,[OldDocumentName],[NewdocumentName],[OldDocumentPath],[NewDocumentPath]"
                        + " ,[DocumentUser],[DateCreated]"
                        + " FROM [DocumentArchive]"
                        + " where datecreated > @dateCreated"
                        + " and documentuser = @documentUser";

                    deletedDocumentList = db.Query<DeletedDocument>(query, new
                    {
                        @dateCreated = DateTime.Now.AddDays(-30),
                        @documentUser = Utility.GetUserName()
                    }).ToList();
                    return deletedDocumentList;
                }
            }
            catch (Exception er)
            {
                Logging.LogErrors(ConfigurationValues.ErrorLogPath, er.ToString());
                return deletedDocumentList;
            }
        }
        public int GetDeletedItemsCount()
        {
            int count = 0;

            try
            {
                using (IDbConnection db = new SqlConnection(ConfigurationValues.PostOfficeDatabaseConnection))
                {
                    const string query = "select count(*) from DocumentArchive"
                        + " where datecreated > @dateCreated"
                        + " and documentuser = @documentUser";

                    count = db.Query<int>(query, new {
                        @dateCreated = DateTime.Now.AddDays(-30),
                        @documentUser = Utility.GetUserName()
                    }).Single();
                    return count;
                }
            }
            catch (Exception er)
            {
                Logging.LogErrors(ConfigurationValues.ErrorLogPath, er.ToString());
                return count;
            }
        }
 public virtual IQueryable<Product> GetAll()
 {
     var connection = new SqlConnection("data source=localhost;initial catalog=Northwind;integrated security=True;");
     connection.Open();
     var products = connection.Query<Product>("SELECT ProductID Id, ProductName Name, Discontinued IsDiscontinued, * FROM Products p");
     return products.AsQueryable();
 }
Example #23
0
 public IEnumerable<DemoModelo> ObtenerTodos()
 {
     using (var con = new SqlConnection(connectionString))
     {
         return con.Query<DemoModelo>("SELECT Codigo Id, Descripcion Nombre FROM Demos Where Codigo = @Id", new { Id = 1 });
     }
 }
        public List<FaxToInformation> GetFaxToInformationGreenway(string id)
        {
            List<FaxToInformation> faxToList = new List<FaxToInformation>();
            FaxToInformation faxTo;
            id = id.Replace("G", "");

            try
            {
                using (IDbConnection db = new SqlConnection(ConfigurationValues.WaldenFaxConnection))
                {
                    const string query = "SELECT [ID],[FirstName],[LastName]"
                        + " ,[Name],[FaxNumber]"
                        + " FROM [FaxGreenwayProviders]"
                        + " where ID = @SendFaxID";

                    faxToList = db.Query<FaxToInformation>(query, new { @SendFaxID = id }).ToList();
                    //faxInformationList = db.Query<SendFaxInformation>(query).ToList();
                    // return faxInformationList;
                    return faxToList;
                }
            }
            catch (Exception er)
            {
                return faxToList;
                // return faxInformationList;
            }
        }
        public void ReadBrand()
        {
            using (
                var sqlConnection =
                    new SqlConnection(
                        Burrow.BurrowEnvironment.Configuration.
                            DBConnectionString(typeof (Brand))))
            {
                sqlConnection.Open();
                var brands =
                    sqlConnection.Query<Brand>(
                        "Select * from tblBrands where [email protected]",
                        new {Id = 6});
                foreach (var brand in brands)
                {
                    Assert.IsFalse(Burrow.GetSession().Contains(brand));

                    Console.WriteLine(brand);
                    brand.Name = "Test13";
                }
                //var brand2=Burrow.GetSession().Get<Brand>(1);
                //brand2.Name = "Test12";
                Burrow.GetSession().Update(brands.First());
                //Burrow.CloseWorkSpace();
                //Burrow.InitWorkSpace();
                Burrow.GetSession().Merge(brands.FirstOrNull());
                var brand3 = Burrow.GetSession().Get<Brand>(1);
                // Assert.AreEqual(brand2.Name,brand3.Name);
                Assert.AreEqual(brands.First().Name, brand3.Name);
                Assert.IsNotNull(brand3.Manufacturer);
            }
        }
        public List<DocType> GetDocTypeNamesAdmin()
        {
            List<DocType> docTypeList = new List<DocType>();

            try
            {
                using (IDbConnection db = new SqlConnection(ConfigurationValues.GreenwayConnection))
                {
                    const string query = "select DocTypeID AS ID, DocTypeName from ClinicalDocTypes"
                        + " where enabled  = 1"
                        + " and system = 2"
                        + " and hide = 0"
                        + " and DocTypeName Not In ('Custom Note','HRA')"
                        + " order by DocTypeName";


                    docTypeList = db.Query<DocType>(query).ToList();
                    return docTypeList;
                }
            }
            catch (Exception er)
            {
                Logging.LogErrors(ConfigurationValues.ErrorLogPath, er.ToString());
                return docTypeList;
            }
        }
        // Methods
        public IEnumerable<UserInfo> QueryAll()
        {
            UserInfo user = null;
            List<UserInfo> userList = new List<UserInfo>();

            //user = new UserInfo(Guid.NewGuid());
            //user.Name = "Clark";
            ////user.IsMen = true;
            //userList.Add(user);

            //user = new UserInfo(Guid.NewGuid());
            //user.Name = "Jane";
            ////user.IsMen = false;
            //userList.Add(user);

            //var conn = System.Configuration.ConfigurationSettings.AppSettings[""].ToString();
            var connstr = "Data Source=127.0.0.1;User ID=sa;Password=summer));Initial Catalog=LabDB";
            using (var conn = new SqlConnection(connstr))
            {
                conn.Open();
                string strsql = "select * from UserInfo";
                var Users = conn.Query<UserInfo>(strsql);

                foreach (var item in Users)
                {
                    //string strID = item.Name;
                    userList.Add(item);
                }
            }

            return userList;
        }
Example #28
0
 public List<Link> GetAllLinks()
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         return connection.Query<Link>("LinksGetAll", commandType: CommandType.StoredProcedure).ToList();
     }
 }
        public List<FaxToInformation> GetFaxToInformation(string id)
        {
            List<FaxToInformation> faxToList = new List<FaxToInformation>();

            try
            {
                using (IDbConnection db = new SqlConnection(ConfigurationValues.WaldenFaxConnection))
                {
                    const string query = "SELECT [SendFaxID],[AccountID],[FirstName],[LastName]"
                        + " ,[Name],[FaxNumber]"
                        + " FROM [FaxSendingInformation]"
                        + " where SendFaxID = @SendFaxID";

                    faxToList = db.Query<FaxToInformation>(query, new { @SendFaxID = id }).ToList();
                    //faxInformationList = db.Query<SendFaxInformation>(query).ToList();
                    // return faxInformationList;
                    return faxToList;
                }
            }
            catch (Exception er)
            {
                EmployeeDesktop.API.Exceptions.ExceptionHandling.InsertErrorMessage(er.ToString());
                EmployeeDesktop.API.Exceptions.ExceptionHandling.SendErrorEmail(er.ToString(), ConfigurationValues.EmailFromFriendly, ConfigurationValues.EmailSendToFriendly, ConfigurationValues.EmailSubject);
                return faxToList;
            }
        }
		public List<ErrorLog> GetLogs()
		{
			var result = new List<ErrorLog>();
			IEnumerable<string> logs;

			using (IDbConnection connection = new SqlConnection(Connection))
			{
				connection.Open();

			    var query = string.Format("SELECT {0} [AllXml] FROM {1} {2} ORDER BY [Sequence] DESC", this.ResolveSelectTopClause(), this.ResolveTableName(), this.ResolveApplicationClause());

				logs = connection.Query<string>(query);
			}
			
			foreach (var log in logs)
			{
				var errorLog = _parser.Parse(log);

				if (errorLog == null)
				{
					_log.Error(string.Format("Failed to parse file: {0}", log));
					continue;
				}

				result.Add(errorLog);
			}

			return result;
		}