/// <summary>
/// 获取权限类别的列表
/// </summary>
public object GetCategoryList()
{
using (var conn = new System.Data.SqlClient.SqlConnection(ConnString))
{
return conn.Query<AccountsPermissionCategories>("sp_Accounts_GetPermissionCategories", null, null, true, null, CommandType.StoredProcedure);
}
}
public static void Main(string[] args)
{
try
{
using (var connection = new System.Data.SqlClient.SqlConnection(ConnectionString))
{
connection.Open();
var sql = "select * FROM People";
var people = connection.Query<People>(sql);
foreach (var person in people)
{
Console.WriteLine(JsonConvert.SerializeObject(person));
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + ex.StackTrace);
}
Console.ReadLine();
}
public IEnumerable<EventModel> GetEvents()
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
var event_ = sqlConnection.Query<EventModel>("SELECT * FROM Event");
return event_;
}
}
public IEnumerable<EventModel> GetRelatedEvents(int eventKey)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
var animal = sqlConnection.Query<EventModel>("SELECT * FROM Event WHERE _Event_key IN (SELECT _RelatedEvent_key FROM EventMap WHERE _Event_key={0})", eventKey);
return animal;
}
}
/// <summary>
/// 获取指定类别下的权限列表
/// </summary>
public object GetPermissionsInCategory(int categoryId)
{
DynamicParameters p = new DynamicParameters();
p.Add("@CategoryID", categoryId, DbType.Int32);
using (var conn = new System.Data.SqlClient.SqlConnection(ConnString))
{
return conn.Query<AccountsPermissionCategories>("sp_Accounts_GetPermissionsInCategory", p, null, true, null, CommandType.StoredProcedure);
}
}
/// <summary>
/// 获取权限类别信息
/// </summary>
public object Retrieve(int categoryId)
{
using (var conn = new System.Data.SqlClient.SqlConnection(ConnString))
{
return conn.Query<AccountsPermissionCategories>("sp_Accounts_GetPermissionCategoryDetails", new { CategoryID = categoryId }, null, true, null, CommandType.StoredProcedure).SingleOrDefault();
}
}
public IEnumerable<Worker> GetWorkers(int usersID = 0)
{
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring);
sqlConnection.Open();
var worker = sqlConnection.Query<Worker>("dbo.spUsersGet "+usersID.ToString());
sqlConnection.Close();
return worker;
}
public Customer GetCustomerById(int customerId)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
string sqlQuery = string.Format("SELECT * FROM Customer WHERE CustomerId = @Id");
var customer = sqlConnection.Query<Customer>(sqlQuery, new {Id = customerId}).SingleOrDefault();
return customer;
}
}
public IEnumerable<Customer> GetCustomers()
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
var customer = sqlConnection.Query<Customer>("sELECT * FROM Customer");
return customer;
}
}
public IEnumerable<MaterialTypeModel> GetMaterialTypes()
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
var materialTypes = sqlConnection.Query<MaterialTypeModel>("SELECT * FROM cv_MaterialType");
return materialTypes;
}
}
/// <summary>
/// 根据角色ID获取角色的信息
/// </summary>
public object Retrieve(int roleId)
{
using (var conn = new System.Data.SqlClient.SqlConnection(ConnString))
{
return conn.Query<AccountsRoles>("sp_Accounts_GetRoleDetails", new { RoleID = roleId }, null, true, null, CommandType.StoredProcedure).SingleOrDefault();
}
}
public MaterialModel GetEventMaterials(int eventKey)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
string strQuery = string.Format("SELECT * FROM Material WHERE _Material_key IN (SELECT _Material_key FROM EventMaterial WHERE _Event_key={0}", eventKey);
var animal = sqlConnection.Query<MaterialModel>(strQuery).Single<MaterialModel>();
return animal;
}
}
//This is the Dapper Version
public TagCount GetdynPostsByTag(string tagname)
{
string connection = ConfigurationManager.ConnectionStrings["SoFConnStr"].ToString();
int counter;
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connection))
{
sqlConnection.Open();
counter = sqlConnection.Query<int>("SELECT Count(0) as PostCount FROM dbo.Posts WHERE FREETEXT(tags, @tagname)", new { tagname = tagname }).Single();
}
TagCount taggy = new TagCount { TagName = tagname, CountTag = counter };
return taggy;
}
public game AddGame(game myGame)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
myGame.game_id = sqlConnection.Query<int>(@"insert into game (A1, A2, A3, B1, B2 , B3 , C1, C2, C3 ,status , winner)
values(@A1, @A2,@A3, @B1, @B2, @B3, @C1, @C2, @C3,@status, @winner);
SELECT CAST(SCOPE_IDENTITY() AS INT)",
new
{
A1 = myGame.A1,
A2 = myGame.A2,
A3 = myGame.A3,
B1 = myGame.B1,
B2 = myGame.B2,
B3 = myGame.B3,
C1 = myGame.C1,
C2 = myGame.C2,
C3 = myGame.C3,
status = myGame.status,
winner = myGame.winner,
gameid = myGame.game_id,
}).Single();
sqlConnection.Close();
}
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
sqlConnection.Query<int>(@"insert into players (game_id,user_one_id,user_two_id)
values(@gameid,@userOneId,@userTwoId);",
new
{
gameid = myGame.game_id,
userOneId = myGame.user_one_id,
userTwoId = myGame.user_two_id
}).Single();
sqlConnection.Close();
}
return myGame;
}
public void UpdateMovie(Movie movie)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("MyMediaDB")))
{
connection.Query <Movie>("dbo.spUpdate_Movie_Selected @Title, " +
"@id, " +
"@Runtime, " +
"@Year, " +
"@FirstName," +
"@LastName",
new
{
movie.Title,
movie.id,
movie.Runtime,
movie.Year,
movie.Director.FirstName,
movie.Director.LastName
});
}
}
/// <summary>
/// 销售订单
/// </summary>
/// <returns></returns>
public List<Order> GetOrderList()
{
List<Order> OrderList;
try
{
using (var conn = new System.Data.SqlClient.SqlConnection(connString))
{
conn.Open();
//exchno='S001170330171356' and
OrderList = conn.Query<Order>("select * from CsmMaster as a where exchno='S001170323183514' and a.OptTime>='" + this.DateSelect.Text + "' and a.opttime<='" + this.DateSelect.Text + " 23:59:59' order by a.OptTime desc").ToList();
conn.Close();
}
return OrderList.ToList();
}
catch (Exception ex)
{
MessageBox.Show("获取数据失败:" + ex.Message);
return null;
}
}
public PersonModel UpdatePerson(PersonModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(database)))
{
var p = new DynamicParameters();
p.Add("@id", model.Id);
p.Add("@UniqueCode", model.UniqueCode);
p.Add("@FirstName", model.FirstName);
p.Add("@LastName", model.LastName);
p.Add("@Password", model.Password);
p.Add("@EmailAddress", model.EmailAddress);
p.Add("@Money", model.Money);
p.Add("@PhoneNumber", model.PhoneNumber);
p.Add("@Photo", model.Photo);
p.Add("@RegistrationDate", model.RegistrationDate);
connection.Query("dbo.spPerson_UpdatePerson", p, commandType: CommandType.StoredProcedure);
return(model);
}
}
private bool checkAddTrip(Trip trip)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConnValue("bus_station")))
{
string query = $"select * from Trip t where id_bus = 1 " +
$"and( " +
$"(convert(datetime,'{trip.DateArrival.ToString("yyyy-MM-dd HH:mm")}',120) < t.datestart and convert(datetime,'{trip.DateArrival.ToString("yyyy-MM-dd HH:mm")}',120) < t.datestart) " +
$"or " +
$"(convert(datetime,'{trip.DateDeparture.ToString("yyyy-MM-dd HH:mm")}',120) > t.dateend and convert(datetime,'{trip.DateDeparture.ToString("yyyy-MM-dd HH:mm")}',120) > t.dateend) " +
$") ";
int all = this.GetAll().Count;
int selectDate = connection.Query <int>(query).ToList().Count;
if (all == selectDate)
{
return(true);
}
return(false);
}
}
public List <AuthorModel> GetAllAuthors()
{
List <AuthorModel> authors = new List <AuthorModel>();
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(database)))
{
authors = connection.Query <AuthorModel>("dbo.spAuthors_GetAll", commandType: CommandType.StoredProcedure).ToList();
foreach (AuthorModel author in authors)
{
author.Books = GetAuthorsBooks(author);
foreach (BookModel book in author.Books)
{
book.Category = GetCategory_ByBook(book);
}
}
}
return(authors);
}
public List <OrderItem> GetOrderItemList()
{
List <OrderItem> ItemList;
try
{
using (var conn = new System.Data.SqlClient.SqlConnection(connString))
{
conn.Open();
ItemList = conn.Query <OrderItem>("select * from CsmDetail as a where a.OptTime>='" + this.DateSelect.Text + "' and a.opttime<='" + this.DateSelect.Text + " 23:59:59'").ToList();
conn.Close();
}
return(ItemList.ToList());
}
catch (Exception ex)
{
MessageBox.Show("获取数据失败:" + ex.Message);
return(null);
}
}
/// <summary>
/// 退回订单
/// </summary>
/// <returns></returns>
public List <Order> GetRejectOrderList()
{
List <Order> RejectOrderList;
try
{
using (var conn = new System.Data.SqlClient.SqlConnection(connString))
{
conn.Open();
RejectOrderList = conn.Query <Order>("select * from RfdMaster as a where a.OptTime>='" + this.DateSelect.Text + "' and a.opttime<='" + this.DateSelect.Text + " 23:59:59' order by a.OptTime desc").ToList();
conn.Close();
}
return(RejectOrderList.ToList());
}
catch (Exception ex)
{
MessageBox.Show("获取数据失败:" + ex.Message);
return(null);
}
}
public Order GetRejectOrder(string id)
{
Order order;
try
{
using (var conn = new System.Data.SqlClient.SqlConnection(connString))
{
conn.Open();
order = conn.Query <Order>("select * from CsmMaster as a where a.exchno='" + id + "'").FirstOrDefault();
conn.Close();
}
return(order);
}
catch (Exception ex)
{
MessageBox.Show("获取数据失败:" + ex.Message);
return(null);
}
}
public List <DivisionModel> TeamDivisionEntries_GetByID(List <DivisionModel> divisions)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfiguration.ConnectionString(Db)))
{
foreach (DivisionModel dm in divisions)
{
var p = new DynamicParameters();
p.Add("@DivisionId", dm.Id);
var teams = connection.Query <TeamModel>("dbo.spTeamDivisionEntries_GetByID", p, commandType: CommandType.StoredProcedure).ToList();
foreach (TeamModel tm in teams)
{
TeamModel teamId = new TeamModel();
teamId.Id = tm.TeamId;
teamId = GetTeamById(teamId.Id);
dm.TeamsEntered.Add(teamId);
}
}
}
return(divisions);
}
public List <T> Query <T>(string sql, object param) where T : class
{
List <T> lResult = new List <T>();
try
{
//using (var cn = new System.Data.SqlClient.SqlConnection(ConnStr))
//{
// return cn.Query<T>(sql, param).ToList<T>();
//}
if (_ProviderName == "System.Data.SqlClient")
{
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(ConnStr))
{
lResult = cn.Query <T>(sql, param).ToList <T>();
}
}
else if (_ProviderName == "MySql.Data.MySqlClient")
{
using (MySql.Data.MySqlClient.MySqlConnection cn = new MySql.Data.MySqlClient.MySqlConnection(ConnStr))
{
lResult = cn.Query <T>(sql, param).ToList <T>();
}
}
else
{
using (System.Data.SqlClient.SqlConnection cn = SqlClient)
{
lResult = cn.Query <T>(sql, param).ToList <T>();
}
}
}
catch (Exception)
{
return(null);
}
return(lResult);
}
public int QueryTotalCount(string sql, object param)
{
int i = 0;
try
{
//using (var cn = new System.Data.SqlClient.SqlConnection(ConnStr))
//{
// i = cn.Query(sql, param).Count();
//}
if (_ProviderName == "System.Data.SqlClient")
{
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(ConnStr))
{
i = cn.Query(sql, param).Count();
}
}
else if (_ProviderName == "MySql.Data.MySqlClient")
{
using (MySql.Data.MySqlClient.MySqlConnection cn = new MySql.Data.MySqlClient.MySqlConnection(ConnStr))
{
i = cn.Query(sql, param).Count();
}
}
else
{
using (System.Data.SqlClient.SqlConnection cn = SqlClient)
{
i = cn.Query(sql, param).Count();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(i);
}
public List <ProductModel> producName(string leng, string prod, Boolean check)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("AdventureWorks2016")))
{
string sql = "";
if (check == true)
{
sql = "SELECT " +
$"Production.Product.Name, Production.ProductDescription.Description, Production.Product.Name " +
"FROM " +
"Production.Product " +
"INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID " +
"INNER JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID " +
"INNER JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID " +
"INNER JOIN Production.ProductModelProductDescriptionCulture ON Production.ProductModel.ProductModelID = Production.ProductModelProductDescriptionCulture.ProductModelID " +
"INNER JOIN Production.ProductDescription ON Production.ProductModelProductDescriptionCulture.ProductDescriptionID = Production.ProductDescription.ProductDescriptionID " +
"WHERE " +
$"Production.Product.Name like '%{prod}%' AND ProductModelProductDescriptionCulture.CultureID = '{ leng }' AND Product.ProductModelID IS NOT NULL AND Product.SellEndDate IS NOT NULL";
}
else
{
sql = "SELECT " +
$"Production.Product.Name, Production.ProductDescription.Description, Production.Product.Name, Production.Product.SellStartDate, Production.Product.SellEndDate " +
"FROM " +
"Production.Product " +
"INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID " +
"INNER JOIN Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID " +
"INNER JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID " +
"INNER JOIN Production.ProductModelProductDescriptionCulture ON Production.ProductModel.ProductModelID = Production.ProductModelProductDescriptionCulture.ProductModelID " +
"INNER JOIN Production.ProductDescription ON Production.ProductModelProductDescriptionCulture.ProductDescriptionID = Production.ProductDescription.ProductDescriptionID " +
"WHERE " +
$"Production.Product.Name like '%{prod}%' AND ProductModelProductDescriptionCulture.CultureID = '{ leng }' AND Product.ProductModelID IS NOT NULL";
}
var output = connection.Query <ProductModel>(sql).ToList();
return(output);
}
}
/// <summary>
/// Match the investments with Each Owner From the database
/// </summary>
/// <param name="owners"></param>
/// <param name="investments"></param>
/// <param name="db"></param>
/// <returns></returns>
public static List <OwnerModel> SetTheInvestmentsForEachOwnerFromTheDatabase(List <OwnerModel> owners, List <InvestmentModel> investments, string db)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnVal(db)))
{
foreach (OwnerModel owner in owners)
{
List <int> investmentsIds = new List <int>();
var p = new DynamicParameters();
p.Add("@OwnerId", owner.Id);
investmentsIds = connection.Query <int>("dbo.spOwner_GetInstallmentIdByOwnerId", p, commandType: CommandType.StoredProcedure).ToList();
foreach (int id in investmentsIds)
{
owner.Investments.Add(new InvestmentModel {
Id = id
});
}
}
}
foreach (OwnerModel ownerModel in owners)
{
List <int> investmentIds = new List <int>();
foreach (InvestmentModel investmentModel in ownerModel.Investments)
{
investmentIds.Add(investmentModel.Id);
}
ownerModel.Investments = new List <InvestmentModel>();
foreach (int id in investmentIds)
{
ownerModel.Investments.Add(investments.Find(x => x.Id == id));
}
}
return(owners);
}
/// <summary>
/// match OrderProducts foreach order From the database
/// </summary>
/// <param name="orders"></param>
/// <param name="staffs"></param>
/// <param name="db"></param>
/// <returns></returns>
public static List <OrderModel> SetTheOrderProductsForEachOrderFromTheDatabase(List <OrderModel> orders, List <OrderProductModel> orderProducts, string db)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnVal(db)))
{
foreach (OrderModel order in orders)
{
List <int> OrderProductsId = new List <int>();
var p = new DynamicParameters();
p.Add("@OrderId", order.Id);
OrderProductsId = connection.Query <int>("dbo.spOrders_GetOrderProductIdByOrderId", p, commandType: CommandType.StoredProcedure).ToList();
foreach (int id in OrderProductsId)
{
order.OrderProducts.Add(new OrderProductModel {
Id = id
});
}
}
}
foreach (OrderModel orderModel in orders)
{
List <int> OrderProductsId = new List <int>();
foreach (OrderProductModel orderProductModel in orderModel.OrderProducts)
{
OrderProductsId.Add(orderProductModel.Id);
}
orderModel.OrderProducts = new List <OrderProductModel>();
foreach (int id in OrderProductsId)
{
orderModel.OrderProducts.Add(orderProducts.Find(x => x.Id == id));
}
}
return(orders);
}
/// <summary>
/// Match the revenues with Each Owner From the database
/// </summary>
/// <param name="owners"></param>
/// <param name="revenues"></param>
/// <param name="db"></param>
/// <returns></returns>
public static List <OwnerModel> SetTheRevenuesForEachOwnerFromTheDatabase(List <OwnerModel> owners, List <RevenueModel> revenues, string db)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnVal(db)))
{
foreach (OwnerModel owner in owners)
{
List <int> revenuesIds = new List <int>();
var p = new DynamicParameters();
p.Add("@OwnerId", owner.Id);
revenuesIds = connection.Query <int>("dbo.spOwner_GetRevenueIdByOwner", p, commandType: CommandType.StoredProcedure).ToList();
foreach (int id in revenuesIds)
{
owner.Revenues.Add(new RevenueModel {
Id = id
});
}
}
}
foreach (OwnerModel ownerModel in owners)
{
List <int> revenuesIds = new List <int>();
foreach (RevenueModel revenue in ownerModel.Revenues)
{
revenuesIds.Add(revenue.Id);
}
ownerModel.Revenues = new List <RevenueModel>();
foreach (int id in revenuesIds)
{
ownerModel.Revenues.Add(revenues.Find(x => x.Id == id));
}
}
return(owners);
}
public List <Person> UpdatePerson(string cnp, string lastName, string firstName, string emailAddress, string phoneNumber)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("FormUI.Properties.Settings.DatabaseConnectionString")))
{
Person newPerson = new Person {
CNP = cnp, FirstName = firstName, LastName = lastName, EmailAddress = emailAddress, PhoneNumber = phoneNumber
};
List <Person> people = new List <Person>();
people = connection.Query <Person>($"select * from People where CNP='{cnp}'").ToList();
foreach (Person i in people)
{
i.LastName = lastName;
i.FirstName = firstName;
i.EmailAddress = emailAddress;
i.PhoneNumber = phoneNumber;
}
connection.Execute("dbo.Procedure_Update2 @cnp,@lastName,@firstName, @emailAddress, @phoneNumber", people);
return(people);
}
}
/// <summary>
/// -OLD- get all stockes from the database
/// - set the product for each store
/// - set the store for each sotre
/// </summary>
/// <param name="db"></param>
/// <param name="allProducts"> get the product list from the database </param>
/// <param name="allStores"> get the stores list from the database </param>
/// <returns></returns>
public static List <StockModel> GetStocks(string db, List <ProductModel> allProducts, List <StoreModel> allStores)
{
List <StockModel> stocks = new List <StockModel>();
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnVal(db)))
{
stocks = connection.Query <StockModel>("dbo.spStock_GetAll").ToList();
// set the product and the store foreach stock
foreach (StockModel stock in stocks)
{
// set the product id
var p = new DynamicParameters();
p.Add("@StockId", stock.Id);
stock.Product = connection.QuerySingle <ProductModel>("spStock_GetProductIdByStockId", p, commandType: CommandType.StoredProcedure);
// set the product from the product list
foreach (ProductModel product in allProducts)
{
if (product.Id == stock.Product.Id)
{
stock.Product = product;
break;
}
}
stock.Store = connection.QuerySingle <StoreModel>("spStock_GetStoreIdByStockId", p, commandType: CommandType.StoredProcedure);
// set the store from the store list
foreach (StoreModel store in allStores)
{
if (store.Id == stock.Store.Id)
{
stock.Store = store;
break;
}
}
}
}
return(stocks);
}
/// <summary>
/// Sets The store Models for each staff in the list
/// -Open the connection
/// - get the IDs of a staff Foreach ID Add store to the staff with this id
/// -Close the connection
/// -match the IDs to the publicVariables.Stores AND set the Store models for each staffModel
/// </summary>
/// <param name="staffs"></param>
/// <param name="stores"></param>
/// <param name="db"></param>
/// <returns></returns>
public static List <StaffModel> SetTheStorsForEachStaffFromTheDatabase(List <StaffModel> staffs, List <StoreModel> stores, string db)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnVal(db)))
{
foreach (StaffModel staff in staffs)
{
List <int> storesId = new List <int>();
var o = new DynamicParameters();
o.Add("@StaffId", staff.Id);
storesId = connection.Query <int>("dbo.spStaffStore_GetStoreIdByStaffId", o, commandType: CommandType.StoredProcedure).ToList();
foreach (int id in storesId)
{
staff.Stores.Add(new StoreModel {
Id = id
});
}
}
}
foreach (StaffModel staffModel in staffs)
{
// Get the Staff's Stores IDs
List <int> staffStoreIds = new List <int>();
foreach (StoreModel store in staffModel.Stores)
{
staffStoreIds.Add(store.Id);
}
staffModel.Stores = new List <StoreModel>();
foreach (int id in staffStoreIds)
{
staffModel.Stores.Add(stores.Find(x => x.Id == id));
}
}
return(staffs);
}
public List <Classes.LotTask> GetProductionTaskList()
{
using (System.Data.IDbConnection connection = new System.Data.SqlClient.SqlConnection(Classes.Helper.CnnVal("TracerDB")))
{ //Responsible for returning all 4 Engineering Task types and compiling them under a lotTask list
//Crazy SQL Call...
return(connection.Query <Classes.LotTask>(
//Get Pre-Bid Review Requests
$"SELECT ActiveQuotes.QuoteWOR AS JobWOR " +
$", Lot = '0' " +
$", ActiveQuotes.PartID " +
$", JobStatus = 'Pre-Bid Review Requested' " +
$", QuoteDueDate AS DueDate " +
$", SuperHot = '0' " +
$"FROM ActiveQuotes INNER JOIN QuoteStatus " +
$"ON ActiveQuotes.QuoteWOR = QuoteStatus.QuoteWOR " +
$"WHERE QuoteStatus.PreBidRequest = 'True' " +
$"AND QuoteStatus.PreBidInProgress = 'False' " +
$"AND ActiveQuotes.QuoteInactive = 'False' " +
$"AND ActiveQuotes.POReceived = 'False' " +
$"UNION " +
//Get Pre-Bid Review In Progress
$"SELECT ActiveQuotes.QuoteWOR AS JobWOR " +
$", Lot = '0' " +
$", ActiveQuotes.PartID " +
$", JobStatus = 'Pre-Bid Review In Progress' " +
$", QuoteDueDate AS DueDate " +
$", SuperHot = '0' " +
$"FROM ActiveQuotes INNER JOIN QuoteStatus " +
$"ON ActiveQuotes.QuoteWOR = QuoteStatus.QuoteWOR " +
$"WHERE QuoteStatus.PreBidRequest = 'True' " +
$"AND QuoteStatus.PreBidInProgress = 'True' " +
$"AND ActiveQuotes.QuoteInactive = 'False' " +
$"AND ActiveQuotes.POReceived = 'False' " +
$"ORDER BY SuperHot DESC, DueDate, JobWOR, Lot").ToList());
}
}
public int calculpg()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("AdventureWorks2016")))
{
string sql = "SELECT COUNT(*) " +
"FROM " +
"Production.Product " +
"INNER JOIN Production.ProductModel ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID " +
"INNER JOIN Production.ProductModelProductDescriptionCulture ON Production.ProductModel.ProductModelID = Production.ProductModelProductDescriptionCulture.ProductModelID " +
"INNER JOIN Production.ProductDescription ON Production.ProductModelProductDescriptionCulture.ProductDescriptionID = Production.ProductDescription.ProductDescriptionID " +
$"WHERE ProductModelProductDescriptionCulture.CultureID = '{ lenguageComboBox.Text }' ";
DataAcces db = new DataAcces();
int totalNumeroDeProductes = connection.Query <int>(sql).FirstOrDefault();
int numtotalPg = totalNumeroDeProductes / int.Parse(numFilasComboBox.Text) + 1;
return(numtotalPg);
}
}
public bool IsValidUser(LoginModel user)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConString("DefaultConnection")))
{
var data = connection.Query <UserMaster>("Select * from UserMaster where Email = @Email", new { user.Email }).ToList();
if (data.Count == 0)
{
return(false);
}
else
{
if (user.Password == data[0].Password)
{
return(true);
}
else
{
return(false);
}
}
}
}
static void Main(string[] args)
{
string connectionString = "Data Source=localhost; Initial Catalog=WebsiteProjectSkeleton_vNext; User ID=justin;password=DemoP@ssword;";
using (var conn = new System.Data.SqlClient.SqlConnection(connectionString))
{
conn.Open();
var results = conn.Query <dynamic>("SELECT UserID, EmailAddress FROM dbo.[User] ORDER BY [EmailAddress];");
Console.WriteLine("Listing of users in webskel:");
Console.WriteLine("UserID\tEmail Address");
foreach (var result in results)
{
Console.WriteLine($"{result.UserID}\t{result.EmailAddress}");
}
}
Console.WriteLine();
Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}
public DynamicListDTO GellAllData(string _connectionString)
{
DynamicListDTO listJobOrder = new DynamicListDTO();
try
{
using (System.Data.SqlClient.SqlConnection activeConn = new System.Data.SqlClient.SqlConnection(_connectionString))
{
activeConn.Open();
//var p = new Dapper.DynamicParameters();
//p.Add("@Name", Name, dbType: DbType.String);
listJobOrder.thisList = activeConn.Query <dynamic>("TestProcInitial", commandType: System.Data.CommandType.StoredProcedure).ToList();
}
return(listJobOrder);
}
catch (Exception ex)
{
listJobOrder.errorMessage = Convert.ToString(ex.Message);
return(listJobOrder);
}
}
public List <Movie> SearchMovies(MovieSearch movieSearch)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("MyMediaDB")))
{
return(connection.Query <Movie>("dbo.spSearch_Movies_By_All @Title, " +
"@Runtime, " +
"@Year, " +
"@ActorFirstName, " +
"@ActorLastName, " +
"@DirectorFirstName," +
"@DirectorLastName",
new
{
movieSearch.Title,
movieSearch.Runtime,
movieSearch.Year,
movieSearch.ActorFirstName,
movieSearch.ActorLastName,
movieSearch.DirectorFirstName,
movieSearch.DirectorLastName
}).ToList());
}
}
public ResponseModel <List <TeacherModel> > GetTeachers()
{
ResponseModel <List <TeacherModel> > responseModel = new ResponseModel <List <TeacherModel> >();
List <TeacherModel> teachers = new List <TeacherModel>();
try
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(userAccess)))
{
teachers = connection.Query <TeacherModel>("dbo.spTeachers_GetAll", commandType: CommandType.StoredProcedure).ToList();
responseModel.Model = teachers;
responseModel.IsSuccess = true;
}
}
catch (Exception e)
{
responseModel.OutputMessage = e.Message;
}
return(responseModel);
}
public int CreateFood(string foodName, string foodDesc, string brand, int cals, int?macC, int?macF, int?macP, int creatorID, string APIFoodID, string ImageURL)
{
using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB")))
{
string procedureName = "dbo.CreateFood";
var param = new DynamicParameters();
param.Add("@FoodName", foodName, DbType.String);
param.Add("@FoodDesc", foodDesc, DbType.String);
param.Add("@Brand", brand, DbType.String);
param.Add("@Cals", cals, DbType.Int32);
param.Add("@MacC", macC, DbType.Int32);
param.Add("@MacP", macP, DbType.Int32);
param.Add("@MacF", macF, DbType.Int32);
param.Add("@CreatorID", creatorID, DbType.Int32);
param.Add("@APIFoodID", APIFoodID, DbType.String);
param.Add("@ImageURL", ImageURL, DbType.String);
return(connection.Query <int>(
procedureName,
param,
commandType: CommandType.StoredProcedure).First());
}
}
public bool CheckLoginCredentials(int brugerID, string brugernavn, string passw)
{
bool validUser = false;
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("Dating")))
{
var bruger = connection.Query <Bruger>("Bruger_CheckLoginCredentials @BrugerID, @Brugernavn, @Passw", new { BrugerID = brugerID, Brugernavn = brugernavn, Passw = passw }).ToList().First();
if (bruger != null)
{
if (bruger.Brugernavn == brugernavn && bruger.Passw == passw)
{
validUser = true;
}
else
{
validUser = false;
}
}
return(validUser);
}
}
public List <Course> GetStudentCourses(int studentid)
{
List <Course> course = new List <Course>();
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.Cnnval("App1DB")))
{
string sql = "dbo.spStudentCourse_GeetStudentCourses";
course = connection.Query <Course>(sql,
new
{
StudentID = studentid
}, commandType: CommandType.StoredProcedure).ToList();
if (course == null)
{
return(null);
}
else
{
return(course);
}
}
}
public void izhod(string id)
{
DateTime prihod = new DateTime();
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("registratorBaza2")))
{
prihod = connection.Query <DateTime>($"SELECT prihod from prihod WHERE id_kartice=@Id", new { Id = id }).FirstOrDefault();
}
TimeSpan skupaj = new TimeSpan();
skupaj = DateTime.Now - prihod;
double ure = skupaj.TotalHours;
int min = skupaj.Minutes;
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("registratorBaza2")))
{
//doda izhod v bazo izhodov
connection.Execute("dbo.izhod @Id_kartice, @prihod, @izhod, @ure, @minute", new { Id_kartice = id, prihod = prihod, izhod = DateTime.Now, ure = ure, minute = min });
//izbriše iz baze prihodov
connection.Execute("dbo.izbriši_prihod @Id_kartice", new { Id_kartice = id });
}
}
public ResponseModel <long> GetNewAccessToken(string refreshToken, string accessToken)
{
ResponseModel <long> responseModel = new ResponseModel <long>();
Authenticator authenticator = new Authenticator();
Encryptor encryptor = new Encryptor();
string hashedRefreshToken = string.Empty;
string accessTokenUserID = authenticator.GetUserIDFromExpiredToken(accessToken);
if (int.TryParse(accessTokenUserID, out int userID))
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(userAccess)))
{
var p = new DynamicParameters();
p.Add("@UserID", userID);
hashedRefreshToken = connection.Query <string>("dbo.spRefreshToken_Get", p, commandType:
CommandType.StoredProcedure).FirstOrDefault();
}
}
if (!string.IsNullOrEmpty(hashedRefreshToken) && encryptor.IsHashValid(refreshToken, hashedRefreshToken))
{
responseModel.OutputMessage = encryptor.CreateAccessToken(userID.ToString());
responseModel.Model = GlobalConfig.GetAccessTokenExpDate();
responseModel.IsSuccess = true;
}
else // refresh token invalid -> logout
{
responseModel.OutputMessage = "Session has expired";
responseModel.ErrorAction = "[LogOut]";
}
return(responseModel);
}
public async Task RunAsync()
{
// {{ parameters:
string repository = "uwp";
DateTime start = DateTime.Parse("2015-9-1");
DateTime end = DateTime.Parse("2015-10-1");
// }}
var distance = new LevenshteinDistance();
string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
IConnectionStringProvider mongoDBDataProvider =
ConnectionStringProvider.CreateConnectionStringProvider(ConnectionStringProvider.ConnectionStringProviderType.MongoDBConnectionStringProvider);
var client = new MongoClient(mongoDBDataProvider.GetConnectionString(repository));
var database = client.GetDatabase(repository);
var dup_detection = database.GetCollection<BsonDocument>("dup_detection");
using (var connection = new System.Data.SqlClient.SqlConnection(cs))
{
var threads = connection.Query(SqlQueryFactory.Instance.Get("get_thread_profile"),
new
{
repository = repository.ToUpper(),
start = start,
end = end
})
.Select(m => new { Title = m.Title, Id = m.Id })
.ToList();
for (int i = 0; i < threads.Count - 1; i++)
{
for(int j = i + 1; j < threads.Count; j++)
{
var left = (threads[i].Title as string).ToLower();
var right = (threads[j].Title as string).ToLower();
var percentage = distance.LevenshteinDistancePercent(left, right) * 100;
// list all the percentage >= 50%
if(percentage >= 50m)
{
var md5 = Utils.ComputeStringPairMD5Hash(left, right);
var count = await dup_detection.Find("{_id: '" + md5 + "'}").CountAsync();
if (count == 0)
{
var dict = new Dictionary<string, object>()
{
{ "_id", md5 },
{ "left", new Dictionary<string, string> {
{ "thread_id", threads[i].Id as string },
{ "text", left as string}
}},
{ "right", new Dictionary<string, string> {
{ "thread_id", threads[j].Id as string},
{ "text", right as string}
}},
{ "percentage", (int)percentage }
};
var document = new BsonDocument(dict);
await dup_detection.InsertOneAsync(document);
}
}
}
Console.Write(".");
}
}
}
/// <summary>
/// 获取指定角色没有的权限列表
/// </summary>
public object GetNoPermissionList(int roleId)
{
DynamicParameters p = new DynamicParameters();
p.Add("@RoleID", roleId, DbType.Int32);
using (var conn = new System.Data.SqlClient.SqlConnection(ConnString))
{
return conn.Query<AccountsPermissions>("sp_Accounts_GetNoPermissionList", p, null, true, null, CommandType.StoredProcedure);
}
}
public List<game> GetAllGamesForUser(int user_id)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
List<game> games = sqlConnection.Query<game>(@"SELECT g.*, p.user_one_id, p.user_two_id
FROM game g
INNER JOIN players p ON p.game_id = g.game_id
WHERE p.user_one_id = @userid OR p.user_two_id = @userid", new { userid = user_id }).ToList();
sqlConnection.Close();
return games;
}
}
public async Task RunAsync(string[] args)
{
// {{ parameters:
string repository = args[0];
DateTime start = DateTime.Parse(args[1]);
DateTime end = DateTime.Parse(args[2]);
string targetCollection = args[3];
// }}
var distance = new LevenshteinDistance();
var client = new MongoClient(_mongoconnectionStringProvider.GetConnectionString(repository));
var database = client.GetDatabase(repository);
var dup_detection = database.GetCollection<BsonDocument>(targetCollection);
using (var connection = new System.Data.SqlClient.SqlConnection(_mssqlconnectionStringProvider.GetConnectionString()))
{
var threads = connection.Query(SqlQueryFactory.Instance.Get(QUERY_GET_THREAD_PROFILE),
new
{
repository = repository.ToUpper(),
start = start,
end = end
})
.Select(m => new { Title = m.Title, Id = m.Id })
.ToList();
for (int i = 0; i < threads.Count - 1; i++)
{
for (int j = i + 1; j < threads.Count; j++)
{
var left = (threads[i].Title as string).ToLower();
var right = (threads[j].Title as string).ToLower();
var percentage = distance.LevenshteinDistancePercent(left, right) * 100;
// list all the percentage >= 50%
if (percentage >= 50m)
{
var md5 = Utils.ComputeStringPairMD5Hash(left, right);
var count = await dup_detection.Find("{_id: '" + md5 + "'}").CountAsync();
if (count == 0)
{
var dict = new Dictionary<string, object>()
{
{ "_id", md5 },
{ "left", new Dictionary<string, string> {
{ "thread_id", threads[i].Id as string },
{ "text", left as string}
}},
{ "right", new Dictionary<string, string> {
{ "thread_id", threads[j].Id as string},
{ "text", right as string}
}},
{ "percentage", (int)percentage }
};
var document = new BsonDocument(dict);
await dup_detection.InsertOneAsync(document);
}
}
}
}
}
}
public IEnumerable<MaterialModel> GetMaterials()
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
var animal = sqlConnection.Query<MaterialModel>("SELECT * FROM Material");
return animal;
}
}
public user AddUser(user myUser)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
myUser.user_id = sqlConnection.Query<int>(@"insert into user (email,password)
values(@myEmail,@myPassword);
SELECT CAST(SCOPE_IDENTITY() AS INT)",
new
{
myEmail = myUser.email,
myPassword = myUser.password
}).Single();
sqlConnection.Close();
}
return myUser;
}
public user GetUserByEmailPassword(string email, string password)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
user usr = sqlConnection.Query<user>(@"select * from user where email = @email and password = @password", new { email = email, password = password }).First();
sqlConnection.Close();
return usr;
}
}
public user GetUserById(int id)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
user usr = sqlConnection.Query<user>(@"select * from user where user_id = @userid", new { userid = id }).First();
sqlConnection.Close();
return usr;
}
}
public game GetGameByID(int game_id)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
game games = sqlConnection.Query<game>(@"SELECT g.*, p.user_one_id, p.user_two_id
FROM game g
INNER JOIN players p ON p.game_id = g.game_id
WHERE g.game_id = @gameid", new { gameid = game_id }).First();
sqlConnection.Close();
return games;
}
}
public List<user> GetAllUsers(int current_user_id)
{
using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
{
sqlConnection.Open();
List<user> usr = sqlConnection.Query<user>(@"select * from user where user_id != @userid", new { userid = current_user_id }).ToList();
sqlConnection.Close();
return usr;
}
}
public void Run()
{
// {{ parameters:
string repository = "uwp";
DateTime start = DateTime.Parse("2015-9-1");
DateTime end = DateTime.Parse("2015-10-1");
// }}
string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
var connection = new System.Data.SqlClient.SqlConnection(cs);
Framework.ConnectionStringProviders.IConnectionStringProvider mongoDBDataProvider =
Framework.ConnectionStringProviders.ConnectionStringProvider.CreateConnectionStringProvider(Framework.ConnectionStringProviders.ConnectionStringProvider.ConnectionStringProviderType.MongoDBConnectionStringProvider);
var client = new MongoClient(mongoDBDataProvider.GetConnectionString(repository));
var database = client.GetDatabase(repository);
var threadProfiles = database.GetCollection<BsonDocument>("thread_profiles");
var threads = database.GetCollection<BsonDocument>("sep_threads");
var prifiles = connection.Query(SqlQueryFactory.Instance.Get("get_thread_profile"),
new
{
repository = repository.ToUpper(),
start = start,
end = end
});
foreach (dynamic profile in prifiles)
{
var tags = connection.Query<string>(SqlQueryFactory.Instance.Get("get_thread_tags"), new { ThreadId = profile.Id });
var key = Builders<BsonDocument>.Filter.Eq("id", (profile.Id as string).Trim());
var task = threads.Find(key).Project("{url: 1, messages: 1, _id: 0}").SingleOrDefaultAsync();
task.Wait();
var match = task.Result;
var updateAction = Builders<BsonDocument>.Update
.Set("create_on", (DateTime)profile.CreateOn)
.Set("category", profile.Category as string)
.Set("title", profile.Title as string)
.Set("type", profile.Type as string);
if (match != null)
{
var html = match.GetValue("messages")
.AsBsonArray
.FirstOrDefault()
.AsBsonDocument
.GetValue("body").AsString;
var document = new HtmlAgilityPack.HtmlDocument();
document.LoadHtml(html);
var text = document.DocumentNode.InnerText;
var excerpt = text.Substring(0, Math.Min(256, text.Length));
updateAction = updateAction.Set("url", match.GetValue("url").AsString)
.Set("excerpt", excerpt);
}
if (tags != null)
{
var tagArray = new BsonArray(tags.Select(m => m.ToLower()).ToList());
updateAction = updateAction.Set("tags", tagArray);
}
threadProfiles.UpdateOneAsync("{'_id': '" + (profile.Id as string).Trim() + "'}",
updateAction,
new UpdateOptions { IsUpsert = true });
Console.Write(".");
}
}
/// <summary>
/// 获取权限列表
/// </summary>
public object GetPermissionList()
{
//string sqlCommandText = "select * from Accounts_PermissionCategories as a join Accounts_Permissions as p on a.CategoryID=p.CategoryID";
//List<AccountsPermissionCategories> userList = new List<AccountsPermissionCategories>();
//using (var conn = new System.Data.SqlClient.SqlConnection(ConnString))
//{
// var lookUp = new Dictionary<int, AccountsPermissionCategories>();
// userList = conn.Query<AccountsPermissionCategories, AccountsPermissions, AccountsPermissionCategories>(sqlCommandText,
// (user, role) =>
// {
// AccountsPermissionCategories u;
// if (!lookUp.TryGetValue(user.CategoryID, out u))
// {
// lookUp.Add(user.CategoryID, u = user);
// }
// u.AccountsPermissionsList.Add(role);
// return user;
// }, null, null, true, "PermissionID", null, null).ToList();
// var result = lookUp.Values;
//}
//return userList;
using (var conn = new System.Data.SqlClient.SqlConnection(ConnString))
{
return conn.Query<AccountsPermissions>("sp_Accounts_GetPermissionList", null, null, true, null, CommandType.StoredProcedure);
}
}
public static List<string> DatabaseList()
{
var cs = ConfigurationManager.ConnectionStrings["BlogData"].ConnectionString;
var cn = new System.Data.SqlClient.SqlConnection(cs);
cn.Open();
var list = cn.Query<string>("select substring(name, 5, 40) from Databases order by name").ToList();
return list;
}