public List<BootcampTechnology> GetAllBootcampTechnologies()
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         return connection.Query<BootcampTechnology>("BootcampTechnologyGetAll", commandType: CommandType.StoredProcedure).ToList();
     }
 }
Ejemplo n.º 2
0
        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;
                }
            }
        }
Ejemplo n.º 3
0
        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();
            }
        }
Ejemplo n.º 4
0
        public Tool GetInfo(string tool)
        {
            // making sure our connection is cut cleanly
            // Helper.CnnVal("LLOdatabase") returns the connection string
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("LLOdatabase")))
            {
                Tool       tempTool = new Tool();
                List <int> tempStock;

                // requestion hammer description from database
                List <string> output  = connection.Query <string>($"select Description from dbo.Tools where Name = '{ tool }'").ToList();
                string        combine = string.Join("", output); //converting the list of the description to one string
                tempTool.Description = combine;

                // Get tool name
                output        = connection.Query <string>($"select Name from dbo.Tools where Name = '{ tool }'").ToList();
                combine       = string.Join("", output); //converting the list of the description to one string
                tempTool.Name = combine;

                // Get tool price
                List <float> outputPrice = connection.Query <float>($"select Price from dbo.Tools where Name = '{ tool }'").ToList();
                tempTool.Price = outputPrice.ElementAt(0);

                // Get tool Stock amount
                tempStock         = connection.Query <int>($"select Quantity from dbo.Tools where Name = '{ tool }'").ToList();
                tempTool.Name     = combine;
                tempTool.Quantity = tempStock.ElementAt(0);

                return(tempTool); // return List<string> to temp variable "combine" to convert List<string> to string
            }
        }
Ejemplo n.º 5
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;
            }
        }
Ejemplo n.º 6
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

                });

            }
        }
Ejemplo n.º 7
0
        /// <summary>
        /// Get all training bookings of curren employee
        /// </summary>
        /// <param name="employeeId">Id of current employee</param>
        /// <returns>A list with all bookings of current employee</returns>
        public Dictionary <string, TrainingStatus> GetTrainingBooking(int employeeId)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING)))
            {
                Dictionary <string, TrainingStatus> bookings = new Dictionary <string, TrainingStatus>();

                // ----------- Get all bookings  -----------


                var p = new DynamicParameters();
                p.Add("@Employee", employeeId);

                List <TrainingBookingEntry> trainings = connection.Query <TrainingBookingEntry>("dbo.spTrainingBooking_GetAllBookingsOfEmployee", p, commandType: CommandType.StoredProcedure).ToList();

                foreach (var tmp_entry in trainings)
                {
                    p = new DynamicParameters();
                    p.Add("@id", tmp_entry.Training);

                    List <Training> tmp_trainings = connection.Query <Training>("dbo.spTraining_GetById", p, commandType: CommandType.StoredProcedure).ToList();

                    if (tmp_trainings != null)
                    {
                        bookings.Add(tmp_trainings.First().Title, tmp_entry.TrainingStatus);
                    }
                }

                return(bookings);
            }
        }
Ejemplo n.º 8
0
        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);
                }
            }
        }
        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
                };
            }
        }
Ejemplo n.º 10
0
 /// <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();
     }
 }
Ejemplo n.º 11
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);
            }
        }
Ejemplo n.º 12
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 });

            }
        }
Ejemplo n.º 13
0
        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));

            }
        }
Ejemplo n.º 14
0
        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);
        }
Ejemplo n.º 15
0
        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"));

            }
        }
Ejemplo n.º 16
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();
            }

        }
        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 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;
            }
        }
Ejemplo n.º 20
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 });
     }
 }
Ejemplo n.º 21
0
        private static void DapperExample()
        {
            // select Name, SecName, Position, BirthDay, IQ from UserNames;
            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(GLOBAL_CONNECTION_STRING))
            {
                conn.Open();

                // Чтение данных с помощью Dapper
                var userColl = conn.Query <UserInfo>("select Name, SecName, Position, BirthDay, IQ from UserNames");
                foreach (var usin in userColl)
                {
                    Log($"\t{usin.Name} {usin.SecName}, {usin.Position}, BD: {usin.BirthDay:yyyy-MM-dd}, IQ: {usin.IQ}");
                }

                // Вставка данных
                // Для вставки данных используется команда Execute
                // Параметры @par заменяются соответствующими полями объекта, переданного в качестве параметра
                conn.Execute(@"insert into UserNames (Name, SecName, Position, BirthDay, iq)
                            values (@par1, @par2, @par3, @par4, @par5)",
                             new object[]
                {
                    new { par1 = "Вячеслав", par2 = "Кудрявцев", par3 = "Специалист", par4 = new DateTime(1999, 5, 25), par5 = 120 },
                    new { par1 = "Вячеслав2", par2 = "Кудрявцев2", par3 = "Специалист2", par4 = new DateTime(1999, 5, 25), par5 = 120 }
                });

                //UserInfo UserInfoObj = null;
                //conn.Execute(@"insert into UserNames (Name, SecName, Position, BirthDay, iq)
                //            values (Name, SecName, Position, BirthDay, Iq)",
                //            UserInfoObj);

                // Если мы имеем объект типа UserInfo, то мы можем назвать параметры в соответствии с именами полей/свойств
                // и передавать в качестве параметра конкретный объект с данными
            }
        }
        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;
            }
        }
Ejemplo n.º 23
0
 public List<Link> GetAllLinks()
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         return connection.Query<Link>("LinksGetAll", commandType: CommandType.StoredProcedure).ToList();
     }
 }
Ejemplo n.º 24
0
        public void Edit(Cars car)
        {
            using (var sqlConnection = new System.Data.SqlClient.SqlConnection())
            {
                sqlConnection.ConnectionString = ConfigurationManager
                                                 .ConnectionStrings["DefaultConnection"]
                                                 .ConnectionString;
                DynamicParameters dynamicParameters = new DynamicParameters();
                dynamicParameters.Add("@CarId", car.CarId);
                dynamicParameters.Add("@Sold", car.Sold);
                dynamicParameters.Add("@CarModelId", car.CarModelId);
                dynamicParameters.Add("@Vin", car.Vin);
                dynamicParameters.Add("@SalesPrice", car.SalesPrice);
                dynamicParameters.Add("@MSRP", car.MSRP);
                dynamicParameters.Add("@CarYear", car.CarYear);
                dynamicParameters.Add("@Mileage", car.Mileage);
                dynamicParameters.Add("@Special", car.Special);
                dynamicParameters.Add("@Photo", car.Photo);
                dynamicParameters.Add("@Discription", car.Discription);
                dynamicParameters.Add("@BodyStyleId", car.BodyStyleId);
                dynamicParameters.Add("@ColorId", car.ColorId);
                dynamicParameters.Add("@CarTypeId", car.CarTypeId);
                dynamicParameters.Add("@TransmissionId", car.TransmissionId);
                dynamicParameters.Add("@InteriorColorId", car.InteriorColorId);
                dynamicParameters.Add("@CarModelId", car.CarModelId);
                dynamicParameters.Add("AspNetUserId", car.AspNetUserId);

                sqlConnection.Query <Cars>("EditCars", dynamicParameters,
                                           commandType: CommandType.StoredProcedure);
            }
        }
Ejemplo n.º 25
0
 public static T GetItem <T>(string tableName, long id) where T : new()
 {
     using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(WebInfo.Conn)) {
         string sql = "Select * From " + tableName + " Where ID = " + id;
         return(conn.Query <T>(sql).SingleOrDefault());
     }
 }
Ejemplo n.º 26
0
        /// <summary>
        /// Get all role names
        /// </summary>
        /// <returns>List with role names</returns>
        public List <Role> GetAllRoleNames()
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING)))
            {
                List <Role> roles = new List <Role>();


                List <string> roleNames = connection.Query <string>("dbo.spRole_GetAllNames", commandType: CommandType.StoredProcedure).ToList();


                foreach (var name in roleNames)
                {
                    if (name.Equals(Role.ROLE_ADMIN.ToString()))
                    {
                        roles.Add(Role.ROLE_ADMIN);
                    }
                    if (name.Equals(Role.ROLE_EMPLOYEE.ToString()))
                    {
                        roles.Add(Role.ROLE_EMPLOYEE);
                    }
                }

                return(roles);
            }
        }
Ejemplo n.º 27
0
        public void AddInvoice(Invoice invoice)
        {
            using (var sqlConnection = new System.Data.SqlClient.SqlConnection())
            {
                sqlConnection.ConnectionString = ConfigurationManager
                                                 .ConnectionStrings["DefaultConnection"]
                                                 .ConnectionString;
                DynamicParameters dynamicParameters = new DynamicParameters();
                dynamicParameters.Add("@InvoiceId", dbType: DbType.Int32, direction: ParameterDirection.Output);
                dynamicParameters.Add("@InvoiceName", invoice.InvoiceName);
                dynamicParameters.Add("@Phone", invoice.Phone);
                dynamicParameters.Add("@Email", invoice.Email);
                dynamicParameters.Add("@StreetOne", invoice.StreetOne);
                dynamicParameters.Add("@StreetTwo", invoice.StreetTwo);
                dynamicParameters.Add("@City", invoice.City);
                dynamicParameters.Add("@InvoiceState", invoice.InvoiceState);
                dynamicParameters.Add("@ZipCode", invoice.ZipCode);
                dynamicParameters.Add("@Price", invoice.Price);
                dynamicParameters.Add("@PerchaseType", invoice.PerchaseType);


                sqlConnection.Query <Contact>("Purchase", dynamicParameters,
                                              commandType: CommandType.StoredProcedure).AsList();
            }
        }
Ejemplo n.º 28
0
        /// <summary>
        /// Get permission out of permission assignment where employe id is the same as in assignment entry
        /// </summary>
        /// <param name="employeeId">Id of employee</param>
        /// <param name="permissionId">Id of permission</param>
        /// <returns></returns>
        public Permission GetRolePermissionById(int roleId, int permissionId)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING)))
            {
                string permissionName = "";


                var p = new DynamicParameters();
                p.Add("RoleId", roleId);
                p.Add("@PermissionId", permissionId);


                List <string> permissions = connection.Query <string>("dbo.spRolePermission_GetPermission", p, commandType: CommandType.StoredProcedure).ToList();

                if (permissions.Count > 0)
                {
                    permissionName = permissions.First();
                }

                foreach (Permission tmp_permission in Enum.GetValues(typeof(Permission)))
                {
                    if (tmp_permission.ToString().Equals(permissionName))
                    {
                        return(tmp_permission);
                    }
                }

                return(Permission.NO_PERMISSION);
            }
        }
Ejemplo n.º 29
0
 /*
  * Method: GetParkingLot
  * Input: [string] lotName, [string] lotNum
  * Output: [List] of Parking Lot
  * Purpose: Used to get specific parking lot information defined by parameter
  */
 public List <ParkingLot> GetParkingLot(string lotName, string lotNum)
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(SQLConnection.ConnString("ParkingLotDB")))
     {
         return(connection.Query <ParkingLot>($"SELECT * FROM viewParkingLotInfo WHERE LotName = '" + lotName + "' AND (LotNumber = '" + lotNum + "' OR LotNumber IS NULL)").ToList());
     }
 }
Ejemplo n.º 30
0
        /// <summary>
        /// Get role of employee
        /// </summary>
        /// <param name="employeeId">Employee Id</param>
        /// <returns>Role of employee</returns>
        public Role GetEmployeeRole(int employeeId)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING)))
            {
                string roleName = "";


                var p = new DynamicParameters();

                p.Add("id", employeeId);



                List <string> roles = connection.Query <string>("dbo.spEmployee_GetRole", p, commandType: CommandType.StoredProcedure).ToList();

                if (roles.Count > 0)
                {
                    roleName = roles.First();
                }

                foreach (Role tmp_role in Enum.GetValues(typeof(Role)))
                {
                    if (tmp_role.ToString().Equals(roleName))
                    {
                        return(tmp_role);
                    }
                }

                return(Role.NO_ROLE);;
            }
        }
Ejemplo n.º 31
0
 public static List <Product> GetAllProducts()
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(getConnectionString()))
     {
         return(connection.Query <Product>("spTableProduct_GetProducts").ToList());
     }
 }
        /// <summary>
        /// This is only used when the DB structure has changed.
        /// </summary>
        /// <param name="OldDBDir"></param>
        /// <param name="NewDBDir"></param>
        public static void CloneLicensedMachinesTable(string OldDB, string NewDB)
        {
            Cursor.Current = Cursors.WaitCursor;
            List <LicensedMachines> OldData = new List <LicensedMachines>();

            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValCustom(OldDB)))
            {
                for (int i = 0; i < 57; i++)
                {
                    OldData = connection.Query <LicensedMachines>("dbo.LicensedMachines_GetByLicenseID @ID", new { ID = i }).ToList();
                }
            }

            foreach (LicensedMachines _LMachines in OldData)
            {
                _LMachines.MachineName  = Utilities.CorrectApostropheForSQL(_LMachines.MachineName);
                _LMachines.MachineNotes = Utilities.CorrectApostropheForSQL(_LMachines.MachineNotes);

                // Create command for SQL server
                SqlConnection sqlConnection1 = new SqlConnection(Helper.CnnValCustom(NewDB));
                SqlDataReader reader;
                SqlCommand    command = new SqlCommand();
                command.CommandText = $"INSERT INTO LicensedMachinesTable (InstallDate, MachineName, MachineNotes, LicenseId) VALUES ('{_LMachines.InstallDate}', '{_LMachines.MachineName}', '{_LMachines.MachineNotes}', '{_LMachines.LicenseId}')";

                command.CommandType = CommandType.Text;
                command.Connection  = sqlConnection1;

                // open connection, run command, close connection.
                sqlConnection1.Open();
                reader = command.ExecuteReader();
                sqlConnection1.Close();
            }
        }
Ejemplo n.º 33
0
        public ProductModel ObtenerProductModel(int productModelId)
        {
            try
            {
                using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConexionString))
                {
                    string sql = $"SELECT DISTINCT ProductModel.ProductModelID, ProductModel.Name , ProductPhoto.LargePhoto " +
                                 $" FROM " +
                                 $" Production.ProductModel " +
                                 $" JOIN Production.Product ON ProductModel.ProductModelID = Product.ProductModelID " +
                                 $" JOIN Production.ProductProductPhoto ON Product.ProductID = ProductProductPhoto.ProductID " +
                                 $" JOIN Production.ProductPhoto ON ProductProductPhoto.ProductPhotoID = ProductPhoto.ProductPhotoID " +
                                 $" WHERE " +
                                 $" Product.ProductModelID = {productModelId}";


                    var products = connection.Query <ProductModel>(sql).FirstOrDefault();


                    return(products);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Problemas con la Obtención de Productos");
                MessageBox.Show("Problemas con la Obtención de Productos" + ex.Message);

                throw;
            }
        }
Ejemplo n.º 34
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 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;
            }
        }
Ejemplo n.º 36
0
 public List <AdultBasicInfoClass> AdultDGSearch(string search)
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("EnrollmentDB")))
     {
         return(connection.Query <AdultBasicInfoClass>($"Select * from AdultBasicInfo where firstName Like '%" + search + "%' OR lastName Like '%" + search + "%' Order By lastName").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;
            }
        }
Ejemplo n.º 38
0
 public List <HighSchoolBasicInfoClass> HSDGSearch(string first, string second)
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("EnrollmentDB")))
     {
         return(connection.Query <HighSchoolBasicInfoClass>($"Select * from HighSchoolBasicInfo where (firstName Like '%" + first + "%' AND lastName Like '%" + second + "%') OR (firstName Like '%" + second + "%' AND lastName Like '%" + first + "%') Order By lastName").ToList());
     }
 }
		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;
		}
Ejemplo n.º 40
0
        /// <summary>
        /// Update employee
        /// </summary>
        /// <param name="employee">Object of employeef </param>
        public void UpdateEmployee(Employee employee)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING)))
            {
                // Get Department Id
                var p = new DynamicParameters();
                p.Add("@name", employee.Department.ToString());
                int departmentId = 0;

                List <int> tmp_ids = connection.Query <int>("dbo.spDepartment_GetDepartmentId", p, commandType: CommandType.StoredProcedure).ToList();

                foreach (var id in tmp_ids)
                {
                    departmentId = id;
                    break;
                }

                // Update Employee
                p = new DynamicParameters();
                p.Add("@LastName", employee.LastName);
                p.Add("@FirstName", employee.FirstName);
                p.Add("@Department", departmentId);
                p.Add("@id", employee.Id);


                connection.Execute("spEmployee_Update", p, commandType: CommandType.StoredProcedure);
            }
        }
 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();
 }
Ejemplo n.º 42
0
        //TODO - Make the CreateEmployee method actually save to the database
        /// <summary>
        /// Saves a new employee to the database.
        /// </summary>
        /// <param name="employee">The employee information.</param>
        /// <returns>The employee information, including the unique identifier.</returns>
        public Employee CreateEmployee(Employee employee)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING)))
            {
                var p = new DynamicParameters();
                p.Add("@name", employee.Department.ToString());
                int departmentId = 0;

                List <int> tmp_ids = connection.Query <int>("dbo.spDepartment_GetDepartmentId", p, commandType: CommandType.StoredProcedure).ToList();

                foreach (var id in tmp_ids)
                {
                    departmentId = id;
                    break;
                }

                p = new DynamicParameters();


                p.Add("@FirstName", employee.FirstName);
                p.Add("@LastName", employee.LastName);
                p.Add("@Department", departmentId);
                p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);

                connection.Execute("dbo.spEmployee_Insert", p, commandType: CommandType.StoredProcedure);

                employee.Id = p.Get <int>("@id");

                return(employee);
            }
        }
Ejemplo n.º 43
0
        /// <summary>
        /// This is only used when the DB structure has changed.
        /// </summary>
        /// <param name="OldDBDir"></param>
        /// <param name="NewDBDir"></param>
        public static void CloneLogsTable(string OldDB, string NewDB)
        {
            Cursor.Current = Cursors.WaitCursor;
            // GET OLD DATA
            List <LogClass> OldData;

            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValCustom(OldDB)))
            {
                OldData = connection.Query <LogClass>("dbo.GDataDB_GetAllLogs").ToList();
            }

            foreach (LogClass _log in OldData)
            {
                // CREATE NEW LOG IN DB
                SqlConnection sqlConnection1 = new SqlConnection((Helper.CnnValCustom(NewDB)));
                SqlDataReader reader;
                SqlCommand    command = new SqlCommand();
                command.CommandText = $"INSERT INTO ChangeLogTable (Date, LicenseId, Log) VALUES (@date, @licenseid, @log)";
                command.Parameters.AddWithValue("@date", $"{_log.Date}");
                command.Parameters.AddWithValue("@licenseid", $"{_log.LicenseId}");
                command.Parameters.AddWithValue("@log", $"{_log.Log}");


                command.CommandType = CommandType.Text;
                command.Connection  = sqlConnection1;

                // open connection, run command, close connection.
                sqlConnection1.Open();
                reader = command.ExecuteReader();
                sqlConnection1.Close();
            }
        }
Ejemplo n.º 44
0
 public List <Client> Logging(string Login, string Password)
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("AirTicketDB")))
     {
         return(connection.Query <Client>($"select * from Clients where Login ='******' and Password ='******'").ToList());
     }
 }
        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;
            }
        }
Ejemplo n.º 46
0
 public List <Client> GetTicketID(string ticket_id)
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("AirTicketDB")))
     {
         return(connection.Query <Client>($"select * from TicketInfo where Ticket_ID ='{ticket_id}'").ToList());
     }
 }
Ejemplo n.º 47
0
 public static List <User> GetAllUsers()
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(getConnectionString()))
     {
         return(connection.Query <User>("dbo.spTable_Users_GetAll").ToList());
     }
 }
Ejemplo n.º 48
0
        public List <TeamModel> GetTeam_All()
        {
            List <TeamModel> output;

            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
            {
                output = connection.Query <TeamModel>("dbo.spPeople_GetAll").ToList();
                foreach (TeamModel team in output)
                {
                    var p = new DynamicParameters();
                    p.Add("@TeamId", team.Id);
                    team.TeamMembers = connection.Query <PersonModel>("dbo.spTeamMembers_GetByTeam", p, commandType: CommandType.StoredProcedure).ToList();
                }
            }
            return(output);
        }
Ejemplo n.º 49
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();
     }
 }
Ejemplo n.º 50
0
        //Obtener todos los productos de una categoria especifica.

        public List <Producto> ObtenerProductosNotNull()
        {
            try
            {
                using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConexionString))
                {
                    string sql = $"SELECT DISTINCT Production.Product.ProductModelID " +
                                 $" FROM " +
                                 $" Production.Product " +
                                 $" WHERE " +
                                 $" ProductModelID IS NOT NULL ";

                    List <Producto> products = new List <Producto>();
                    products = connection.Query <Producto>(sql).ToList();


                    return(products);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Problemas con la Obtención de Productos");
                MessageBox.Show("Problemas con la Obtención de Productos" + ex.Message);

                throw;
            }
        }
Ejemplo n.º 51
0
 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
 }
Ejemplo n.º 52
0
        /// <summary>
        /// Inserts a new account
        /// </summary>
        /// <param name="myStory"></param>
        /// <returns></returns>

        public int Insert(Story myStory)
        {
            var p = new DynamicParameters();

            p.Add("@JakataID", myStory.JakataID);
            p.Add("@StoryCategorytName", 1);
            p.Add("@Title", myStory.Title);
            p.Add("@AnimalType", myStory.AnimalType);
            p.Add("@MoralType", myStory.MoralType);
            p.Add("@Comments", myStory.Comments);
            p.Add("@Stories", myStory.Stories);


            var    conString     = ConfigurationManager.ConnectionStrings["LocalStory"];
            string strConnString = conString.ConnectionString;

            int total = 0;

            using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(strConnString))
            {
                sqlConnection.Open();
                const string storedProcedure = "dbo.InsertStory";
                var          values          = sqlConnection.Query <ReceipeTotalModel>(storedProcedure, p, commandType: CommandType.StoredProcedure);
                foreach (var el in values)
                {
                    total = el.totalReceipesInt;
                }
            }

            return(total);
        }
Ejemplo n.º 53
0
 public List <HighSchoolBasicInfoClass> HSDGSearch()
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("EnrollmentDB")))
     {
         return(connection.Query <HighSchoolBasicInfoClass>($"Select * from HighSchoolBasicInfo where lastName != 'null' AND firstName != 'null' Order By lastName").ToList());
     }
 }
Ejemplo n.º 54
0
        /// <summary>
        /// Inserts a new account
        /// </summary>
        /// <param name="myStory"></param>
        /// <returns></returns>

        public int InsertURL(youTubeModel myStory)
        {
            var p = new DynamicParameters();

            p.Add("@JakataID", myStory.JakataID);
            p.Add("@URL", myStory.URL);

            var    conString     = ConfigurationManager.ConnectionStrings["LocalStory"];
            string strConnString = conString.ConnectionString;

            int total = 0;

            using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(strConnString))
            {
                sqlConnection.Open();
                const string storedProcedure = "dbo.InsertURL";
                var          values          = sqlConnection.Query <ReceipeTotalModel>(storedProcedure, p, commandType: CommandType.StoredProcedure);
                foreach (var el in values)
                {
                    total = el.totalReceipesInt;
                }
            }

            return(total);
        }
Ejemplo n.º 55
0
        public string TehnoloskiPostopkiOPERACIJA(string input) //ne dela
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("SampleDB")))
            {
                //    var output = connection.Query($"SELECT SIFRAOPERACIJE, OPIS FROM TEHNOLOSKI_POSTOPKI_POS WHERE STEVILKA = @STEVILKA");

                var p = new
                {
                    stevilka = input
                };

                string sql = "select * from dbo.TEHNOLOSKI_POSTOPKI_POS where STEVILKA = 522";

                var people = connection.Query <TehnoloskiPostopkiPOS>(sql, p);


                string a = "";
                foreach (var person in people)
                {
                    a = person.SIFRAOPERACIJE + person.OPIS + "\n" + a;
                }

                return(a);
            }
        }
        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 Id=@Id",
                        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);
            }
        }
Ejemplo n.º 57
0
        // 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;
        }
Ejemplo n.º 58
0
        internal Account InsertData(string username, string password, string firstName, string lastName, string emailAddress)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConnectionVal(database)))
            {
                bool    exist      = false;
                Account newAccount = new Account
                {
                    Username     = username,
                    Password     = password,
                    FirstName    = firstName,
                    LastName     = lastName,
                    EmailAddress = emailAddress
                };

                var accounts = connection.Query <Account>("spAccounts_GetAuthenticationInformation @Username, @Password",
                                                          new { Username = username, Password = password }).ToList();

                if (AuthenticationVerify(accounts, username, password))
                {
                    MessageBox.Show("Account already exist !!!");
                    exist = true;
                }

                if (exist == false)
                {
                    connection.Execute("spAccounts_InsertNewAccount @Username, @Password, @FirstName, @LastName, @EmailAddress", newAccount);

                    return(newAccount);
                }

                return(null);
            }
        }
Ejemplo n.º 59
0
 /// <summary>
 /// Query database with PersonSeeking Template.
 /// </summary>
 /// <param name="name"> Connectionstring. </param>
 /// <param name="query"> FULL query text as string. </param>
 /// <returns></returns>
 public List <PersonSeeking> GetPeople(string name, string query)
 {
     using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(SqlHelper.ConVal(name)))
     {
         return(connection.Query <PersonSeeking>(query).ToList());
     }
 }
        // END LOADING DATA FOR JOB CREATION *******

        // Loading Custom Jobs based on what and where
        public List <JobModel> GetCustomJobData(string what, string where)
        {
            var headers = OperationContext.Current.IncomingMessageProperties["httpRequest"];
            var token   = ((HttpRequestMessageProperty)headers).Headers["Token"];

            using (var dbContext = new TokenDbContext())
            {
                ITokenValidator validator = new DatabaseTokenValidator(dbContext);
                if (validator.IsValid(token) == true)
                {
                    using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("GyrodataTracker")))
                    {
                        if (connection.State == ConnectionState.Closed)
                        {
                            connection.Open();
                        }

                        var p = new DynamicParameters();

                        p.Add("@SearchWhat", what);
                        p.Add("@SearchWhere", where);

                        return(connection.Query <JobModel>("spGetCustomJobData", p, commandType: CommandType.StoredProcedure).ToList());
                        // TODO - remove further
                        //return connection.Query<JobModel>("spGetCustomJobsDataANDROID", p, commandType: CommandType.StoredProcedure).ToList();
                    }
                }
                return(null);
            }
        }