public List<BootcampTechnology> GetAllBootcampTechnologies() { using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString())) { return connection.Query<BootcampTechnology>("BootcampTechnologyGetAll", commandType: CommandType.StoredProcedure).ToList(); } }
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 int Add(LogisticsCompany c, string currentUser) { using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) { conn.Open(); var result = conn.Query<int>(@" insert into invoice.Company(CompanyName,ContactPerson, AddressLine1,AddressLine2,City,State,Zip,Email, MobileNumber,OfficeNumber,FaxNumber,ComplimentaryWeight,WeightRate, BasePickupCharge,CreatedBy,CreatedAt) values(@CompanyName,@ContactPerson, @AddressLine1,@AddressLine2,@City,@State,@Zip,@Email, @MobileNumber,@OfficeNumber,@FaxNumber,@ComplimentaryWeight,@WeightRate, @BasePickupCharge,@CreatedBy,getdate()); SELECT SCOPE_IDENTITY() ", c); var result1 = conn.Query<int>(@" insert into invoice.UserCompany(UserId, CompanyId, CreatedBy, CreatedAt) values(@CurrentUser, @CompanyId, @EnvUser, getdate()); ", new { CurrentUser = currentUser , CompanyId = result.FirstOrDefault(), EnvUser = Environment.UserName }); return result.FirstOrDefault(); } }
public 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 } }
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 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> /// 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); } }
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 }; } }
/// <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(); } }
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 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 }); } }
public void BasicSPROCs() { using (IDbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Chinook"].ConnectionString)) { conn.Open(); var albums = conn.Query<AlbumResult>("spGetAlbumsWithArtist", commandType: CommandType.StoredProcedure); Assert.That(albums.Count(), Is.EqualTo(347)); var albumsFiltered = conn.Query<AlbumResult>("spGetAlbumsWithArtist", new { Artist = "AC/DC" }, commandType: CommandType.StoredProcedure); Assert.That(albumsFiltered.Count(), Is.EqualTo(2)); } }
public ActionResult Details(SearchModel model) { if (ModelState.IsValid) { using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString)) { var searchString = model.SearchString; var searchTables = model.SearchTables; var searchColumns = model.SearchColumns; var searchComments = model.SearchComments; var selectedServerNames = model.SelectedServerNames; //This what's populated when a user selects (a) linked server(s). var selectedServerNamesString = ""; if (selectedServerNames != null && selectedServerNames.Any()) { selectedServerNamesString = selectedServerNames.Aggregate(selectedServerNamesString, (current, name) => current + (name + ",")); selectedServerNamesString = selectedServerNamesString.Substring(0, selectedServerNamesString.Length - 1); } conn.Open(); var loginId = User.Identity.Name; var bannerUserList = conn.Query(string.Format("SELECT LoginId FROM BannerLoginIds WHERE LoginId = '{0}'", loginId)).ToList(); var includeBannerItems = bannerUserList.Count() == 1 ? true : false; var results = conn.Query<SearchResult>( @"SELECT * FROM dbo.udf_GetTableColumnCommentsResults(@searchString, @LinkedServerNames, @SearchTables, @SearchColumns, @SearchComments, @IncludeBannerItems)", new {@searchString = searchString, @LinkedServerNames = selectedServerNamesString, @SearchTables = searchTables, @SearchColumns = searchColumns, @SearchComments = searchComments, @IncludeBannerItems = includeBannerItems }).ToList(); if (results.Count == 1) { // means we got back a single matching table => jump to table details page: var result = results.FirstOrDefault(); return RedirectToAction("Details", "Tables", new Table { LinkedServerName = result.LinkedServerName, Owner = result.Owner, TableName = result.TableName }); } return View(results); } } return View(model); }
public void BasicCRUD() { using (IDbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Chinook"].ConnectionString)) { conn.Open(); var x = conn.Query<int>("INSERT INTO Artist (Name) VALUES (@Name); SELECT cast(@@Identity as int);", new { Name = "NewArtist" }).First(); Assert.That(x, Is.GreaterThan(0)); var artist = conn.Query<Artist>("INSERT INTO Artist (Name) VALUES (@Name); SELECT * from Artist WHERE ArtistId=SCOPE_IDENTITY();", new { Name = "NewArtistX" }).First(); Assert.That(artist.ArtistId, Is.GreaterThan(0)); Assert.That(artist.Name, Is.EqualTo("NewArtistX")); } }
public 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; } }
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 }); } }
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; } }
public List<Link> GetAllLinks() { using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString())) { return connection.Query<Link>("LinksGetAll", commandType: CommandType.StoredProcedure).ToList(); } }
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); } }
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()); } }
/// <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); } }
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(); } }
/// <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); } }
/* * 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()); } }
/// <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);; } }
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(); } }
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; } }
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; } }
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; } }
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; }
/// <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(); }
//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); } }
/// <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(); } }
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; } }
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()); } }
public static List <User> GetAllUsers() { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(getConnectionString())) { return(connection.Query <User>("dbo.spTable_Users_GetAll").ToList()); } }
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); }
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(); } }
//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; } }
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 }
/// <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); }
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()); } }
/// <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); }
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); } }
// 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; }
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); } }
/// <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); } }