/// <summary>
/// Returns a stored procedure that returns all classes from boat data db.
/// </summary>
/// <returns>List of all classes.</returns>
public static List <BoatClass> ReturnClass()
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
// Runs query.
var list = connection.Query <BoatClass>("call returnclass").ToList();
return(list);
}
}
/// <summary>
/// Get boats of a specific person.
/// </summary>
/// <param name="name">Person to search for boats.</param>
/// <returns></returns>
public static List <Boats> GetBoats(string name)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
//Runs stored procedure.
return(connection.Query <Boats>("call returnboatsspecific(@name)", new
{ name = name }).ToList());
}
}
/// <summary>
/// Get boats of a specific person.
/// </summary>
/// <param name="name">Person to search for boats.</param>
/// <returns></returns>
public static List <Boats> GetBoats(string name)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
//Runs stored procedure.
return(connection.Query <Boats>("select * from fulllist where name = @name", new
{ name = name }).ToList());
}
}
public void AddComponentToDatabase(RecipeIngredient component)
{
var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO recipe_schema.recipe_ingredient" + "({0},{1},{2},{3}) VALUES ('{4}', '{5}', '{6}', '{7}')", "RecipeId", "IngredientId", "Quantity", "QuantityUnitId", component.RecipeId, component.IngredientId, component.Quantity, component.QuantityUnitId);
conn.Query(sb.ToString());
}
public static string GetName(string id)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
return(connection.Query <string>("select name from useridname where userid = @id1", new
{
id1 = id
}).First());
}
}
public void UpdateInstructions(Recipe recipe)
{
var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("UPDATE recipe_schema.recipe\n");
sb.AppendFormat("SET instructions = '" + recipe.Instructions + "'\n");
sb.AppendFormat("WHERE id = " + recipe.Id);
conn.Query(sb.ToString());
}
public List <PersonModel> GetPerson_All()
{
List <PersonModel> output;
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(GlobalConfig.CnnString(db)))
{
output = connection.Query <PersonModel>("spPeople_GetAll").ToList();
}
return(output);
}
/*
* public void Addboat(string name)
* {
* using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("sailingDB")))
* {
* connection.Query($"update fulllist set '{ name }'");
* }
* }
*/
public Boats GetBoat(string name)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("sailingDB")))
{
//string sql = "call returnboat('" + name + "')";
//var obj = connection.Query<Boats>(sql).First();
return(connection.Query <Boats>("call returnboat(@name)", new { name = name }).First());
//return obj;
//return connection.Query<Boats>("returnboat @fullname", new { fullname = name }).First();
}
}
public Recipe GetRecipeDetails(int recipeId)
{
Recipe r = new Recipe();
IDbConnection conn;
conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
r = conn.Query <Recipe>("SELECT * FROM Recipe WHERE Id = " + recipeId).FirstOrDefault();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT * FROM recipe_ingredient AS A LEFT JOIN ingredient AS B ON A.IngredientId = B.Id " +
"LEFT JOIN quantity_unit AS C ON A.QuantityUnitId = C.Id WHERE RecipeId = {0}", recipeId);
r.RecipeIngredients = conn.Query <RecipeIngredient, Ingredient, QuantityUnit, RecipeIngredient>(sb.ToString(),
(recipeIngredient, ingredient, quantityUnit) =>
{
recipeIngredient.Ingredient = ingredient;
recipeIngredient.QuantityUnit = quantityUnit;
return(recipeIngredient);
}, splitOn: "Id").ToList();
return(r);
}
/// <summary>
/// Returns a list of all the distinct names in the fulllist db. Used a array due to
/// compatibility with javascript.
/// </summary>
/// <returns>List of names.</returns>
public static String[] GetNames()
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
// Returns a list of all the distinct names in the fulllist db.
string[] listOfNames = connection.Query <string>("call returnnames").Distinct().ToArray();
return(listOfNames);
}
}
/// <summary>
/// Returns a list of all the distinct names in the fulllist db. Used a array due to
/// compatibility with javascript.
/// </summary>
/// <returns>List of names.</returns>
public static IEnumerable <string> GetNames()
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
// Returns a list of all the distinct names in the fulllist db.
var listOfNames = connection.Query <string>("call returnnames").Distinct();
listOfNames = listOfNames.Where(x => x != Program.Globals.LastName).ToArray();
return(listOfNames);
}
}
public JsonResult OnGet(string name)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
// Returns a list of all the distinct names in the fulllist db.
var listOfNames = connection.Query <string>("call returnnames").Distinct();
listOfNames = listOfNames.Where(x => x != name).ToArray();
return(new JsonResult(listOfNames));
}
}
/*
* /// <summary>
* /// Adds boats to the race db. Runs logic for for whether they have crew or not.
* /// </summary>
* /// <param name="boat"></param>
* public static void SetBoats(Boats boat)
* {
* using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
* {
*
* // If they don't have crew add single.
* if (Program.Globals.Crew == null)
* {
*
* // Try to add them.
* try
* {
*
* InsertInto(boat, 0);
*
* }
*
* // Else add new DB for new race then add them
* catch
* {
*
* connection.Execute(CreateTable(_race));
* InsertInto(boat, 0);
* }
* }
*
* //Else remove both.
* else
* {
*
* // Try to add one person.
* try
* {
*
* InsertInto(boat,0);
*
* }
*
* // Else create db and then add person.
* catch
* {
*
* connection.Execute(CreateTable(_race));
* InsertInto(boat,0);
*
* }
*
* // In every case we will add the second without fail.
* // finally
* // {
*
* var boats1 = new Boats(Program.Globals.Crew, boat.BoatName, boat.BoatNumber, ReturnClass().FindAll(x => x.boatName == boat.BoatName).First().py);
* InsertInto(boats1,1);
*
* // }
*
* }
*
* }
*
* }
*/
/*
* /// <summary>
* /// Returns the number of crew a boat has.
* /// </summary>
* /// <param name="boatName">Class of boat</param>
* /// <returns>The number of crew a particular boat has.</returns>
* public static int GetCrew(string boatName)
* {
*
* using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
* {
*
* return connection.Query<int>("call returncrew(@boatName)", new{boatName = boatName}).FirstOrDefault();
* }
*
* }
*/
/// <summary>
/// Inserts into the db calendar a single event.
/// </summary>
/// <param name="cal">A single event.</param>
public static void Newcalendar(Calendar cal)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
connection.Query("call newcalendar(@summary, @description, @date)", new
{
summary = cal.Summary,
description = cal.Description,
date = cal.DateTime
});
}
}
public static void SQLaddnewracer(Boats personboat, int crew)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("sailingDB")))
{
connection.Query("call enterraceperson(@name, @boatName, @boatNumber, @crew)", new
{
name = personboat.name,
boatName = personboat.boatName,
boatNumber = personboat.boatNumber,
crew = crew
});
}
}
/*
* public void Addboat(string name)
* {
* using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("sailingDB")))
* {
* connection.Query($"update fulllist set '{ name }'");
* }
* }
*/
public static List <string> GetNames()
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("sailingDB")))
{
//string sql = "call returnboat('" + name + "')";
//var obj = connection.Query<Boats>(sql).First();
return(connection.Query <string>("call returnnames").Distinct <string>().ToList());
//return obj;
//return connection.Query<Boats>("returnboat @fullname", new { fullname = name }).First();
}
}
public static void SQLAddboat(string name, string boat, int boatnumber)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("sailingDB")))
{
//connection.Query("call newboat('" + name + "', '" + boat + "', '" + boatnumber + "','" + noofboats + "')");
connection.Query("call newboat(@name, @boat, @boatnumber)", new
{
name = name,
boat = boat,
boatnumber = boatnumber
});
}
}
/// <summary>
/// 获取模型
/// </summary>
/// <typeparam name="T">模型</typeparam>
/// <param name="sql">SQL语句</param>
/// <param name="obj">匿名函数new{}</param>
/// <param name="connString">链接字符串</param>
/// <returns></returns>
public static T GetModel <T>(string sql, object obj, string connString)
{
T t;
try
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
{
t = conn.Query <T>(sql, obj).FirstOrDefault();
}
}
catch (Exception e) { t = default(T); }
return(t);
}
public static void Connected()
{
MyCon _con = new MyCon();
_con.ConnectDB();
using (MySql.Data.MySqlClient.MySqlConnection connect = _con.db.connect())
{
connect.Open();
string sql = String.Format("{0}", "SELECT * FROM Customer");
List <UserModel> tested = connect.Query <UserModel>(sql).ToList();
connect.Close();
Console.WriteLine(tested);
}
}
/// <summary>
/// 获取总条数
/// </summary>
/// <typeparam name="T">模型</typeparam>
/// <param name="sql">SQL语句</param>
/// <param name="obj">匿名函数new{}</param>
/// <param name="connString">链接字符串</param>
/// <returns></returns>
public static int GetCount <T>(string sql, object obj, string connString)
{
int count = 0;
try
{
using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connString))
{
count = conn.Query <T>(sql, obj).Count();
}
}
catch { }
return(count);
}
public RecipeEditViewModel GetRecipeEditViewModel(int recipeId)
{
RecipeEditViewModel vm = new RecipeEditViewModel();
IDbConnection conn;
conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
vm.Recipe = conn.Query <Recipe>("SELECT * FROM Recipe WHERE Id = " + recipeId).ToList().FirstOrDefault();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT * FROM recipe_ingredient AS A LEFT JOIN ingredient AS B ON A.IngredientId = B.Id " +
"LEFT JOIN quantity_unit AS C ON A.QuantityUnitId = C.Id WHERE RecipeId = {0}", recipeId);
vm.Recipe.RecipeIngredients = conn.Query <RecipeIngredient, Ingredient, QuantityUnit, RecipeIngredient>(sb.ToString(),
(component, ingredient, quantityUnit) =>
{
component.Ingredient = ingredient;
component.QuantityUnit = quantityUnit;
return(component);
}, splitOn: "Id").ToList();
vm.IngredientsList = conn.Query <Ingredient>("SELECT * FROM ingredient").ToList();
vm.QuantityUnitsList = conn.Query <QuantityUnit>("SELECT * FROM quantity_unit").ToList();
return(vm);
}
/// <summary>
/// Returns a stored procedure that returns all classes from boat data db.
/// </summary>
/// <returns>List of all classes.</returns>
public static List <String> ReturnClassNames()
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
// Runs query.
var list = connection.Query <BoatClass>("call returnclass").ToList();
var stringlist = new List <String>();
foreach (var boat in list)
{
stringlist.Add(boat.BoatName);
}
return(stringlist);
}
}
/// <summary>
/// Returns the list of racers
/// </summary>
/// <returns>Returns boat data, including if the person is a crew or not.</returns>
public static List <BoatsRacing> GetRacers()
{
// Tables names cannot have spaces.
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
// Appends together the query. Stops SQL injection.
var sql1 = new StringBuilder();
sql1.Append("select * from ");
sql1.Append(_race);
sql1.Append(";");
return(connection.Query <BoatsRacing>(sql1.ToString()).ToList());
}
}
/// <summary>
/// Removes boats, knows if they are crew or not.
/// </summary>
/// <param name="boat"></param>
public static void RemoveBoats(BoatsTidy boat, Calendar race)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
// Creates query.
string str = "delete from signonlists where (name = @name and summary = @summary " +
"and dateTime = @dateTime and sailingClub = @sailingClub)";
connection.Query(str, new {
name = boat.Name,
summary = race.Summary,
dateTime = race.DateTime,
sailingClub = "Whitefriars Sailing Club"
});
}
}
public BeansResponse UpdPersonaResponse(BeansPersonaR oPersona, int nId)
{
string[] dteFecha = oPersona.FechaNacimiento.ToShortDateString().Split('/');
BeansResponse oResponse = new BeansResponse();
try
{
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
var parameter = new DynamicParameters();
connection.Open();
try
{
//parameter.Add("@Nombres", oPersona.Nombres);
//parameter.Add("@Apellido", oPersona.Apellidos);
//parameter.Add("@Edad", oPersona.Edad);
//parameter.Add("@FechaNacimiento", oPersona.FechaNacimiento);
string sql = "update Personas set nEdad =@Edad , dteFechaNacimiento =@FechaNacimiento where nId=@nId";
sql = sql.Replace("@nId", nId.ToString()).Replace("@Apellido", '"' + oPersona.Apellidos + '"').Replace("@Edad", oPersona.Edad.ToString()).Replace("@FechaNacimiento", '"' + dteFecha[2] + "-" + dteFecha[1] + "-" + dteFecha[0] + '"');
//var data = connection.Query<BeansPersona>("examen.InsPersona", param: parameter, commandType: System.Data.CommandType.StoredProcedure);
var data = connection.Query <BeansPersona>(sql, commandType: System.Data.CommandType.Text);
oResponse.bStatus = true;
oResponse.vResponse = "Se actualizo correctamente";
}
catch (Exception ex)
{
oResponse.bStatus = false;
oResponse.vResponse = "No se pudo actualizar";
throw ex;
}
finally
{
connection.Dispose();
connection.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(oResponse);
}
public static User Login(string userName, string password)
{
User u = new User();
using(var db = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString)){
var q = @"select UserId, DisplayName, UserName, EmailId, UserRole from Users where UserName=@username and Password=@password";
var user = db.Query<User>(q, new { username = userName, password = password });
foreach (var uu in user) {
u.UserId = Convert.ToInt32(uu.UserId);
u.UserName = uu.DisplayName;
u.EmailId = uu.EmailId;
u.UserName = uu.UserName;
u.UserRole = uu.UserRole;
}
}
return u;
}
/// <summary>
/// Adds a new boat/person
/// </summary>
/// <param name="boat">Boat data to add.</param>
public static void SetNewFullBoat(Boats boat)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
//Appends together the query, stops SQL injection.
var sql = new StringBuilder();
sql.Append("insert into fulllist values ('");
sql.Append(boat.Name);
sql.Append("', '");
sql.Append(boat.BoatName);
sql.Append("', ");
sql.Append(boat.BoatNumber);
sql.Append(");");
// Query.
connection.Query(sql.ToString());
}
}
public List <T> Get <T>(string connection, string sql, object parameters)
{
var result = new List <T>();
//var test = new MySql.Data.MySqlClient()
using (var c = new MySqlData.MySqlClient.MySqlConnection(connection))
{
_log.LogTrace <MySqlQuery>($"SELECT: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}");
c.Open();
result = c.Query <T>(sql, parameters).ToList();
c.Close();
}
return(result.ToList());
}
public static List <String> GetClasses()
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("sailingDB")))
{
//string sql = "call returnboat('" + name + "')";
//var obj = connection.Query<Boats>(sql).First();
try
{
return(connection.Query <String>("call returnclass").ToList());
}
catch (InvalidOperationException)
{
return(new List <String>());
}
//return obj;
//return connection.Query<Boats>("returnboat @fullname", new { fullname = name }).First();
}
}
/// <summary>
/// Inserts sailor into a named race.
/// </summary>
/// <param name="boat">Boat data of person to add.</param>
/// <param name="crew">Are they crew?</param>
private static void InsertInto(Boats boat, int crew)
{
using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal()))
{
var sql = new StringBuilder();
sql.Append("insert into ");
sql.Append(_race);
sql.Append(" values('");
sql.Append(boat.Name);
sql.Append("','");
sql.Append(boat.BoatName);
sql.Append("',");
sql.Append(boat.BoatNumber);
sql.Append(",");
sql.Append(crew);
sql.Append(");");
connection.Query(sql.ToString());
}
}
public void Install(Castle.Windsor.IWindsorContainer container, Castle.MicroKernel.SubSystems.Configuration.IConfigurationStore store)
{
List <Setting> settings;
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
{
connection.Open();
settings = connection.Query <Setting>("SELECT SettingName, SettingValue FROM settings WHERE SettingName LIKE 'facebook%'").ToList();
}
var facebookConfig = new FacebookConfig
{
FacebookAppSecret = settings.FirstOrDefault(s => s.SettingName == "FacebookAppSecret").SettingValue,
FacebookAppId = settings.FirstOrDefault(s => s.SettingName == "FacebookAppId").SettingValue,
OAuthCallbackPath = "/facebookoauth.axd"
};
container.Register(Component.For <FacebookConfig>().LifestyleSingleton().Instance(facebookConfig));
}
public void Install(Castle.Windsor.IWindsorContainer container, Castle.MicroKernel.SubSystems.Configuration.IConfigurationStore store)
{
List<Setting> settings;
using(var connection = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
{
connection.Open();
settings = connection.Query<Setting>("SELECT SettingName, SettingValue FROM settings WHERE SettingName LIKE 'facebook%'").ToList();
}
var facebookConfig = new FacebookConfig
{
FacebookAppSecret = settings.FirstOrDefault(s => s.SettingName == "FacebookAppSecret").SettingValue,
FacebookAppId = settings.FirstOrDefault(s => s.SettingName == "FacebookAppId").SettingValue,
OAuthCallbackPath = "/facebookoauth.axd"
};
container.Register(Component.For<FacebookConfig>().LifestyleSingleton().Instance(facebookConfig));
}
public List <T> Query <T>(string sql) where T : class
{
List <T> lResult = new List <T>();
try
{
//using (var cn = new System.Data.SqlClient.SqlConnection(ConnStr))
//{
// return cn.Query<T>(sql).ToList<T>();
//}
if (_ProviderName == "System.Data.SqlClient")
{
using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(ConnStr))
{
lResult = cn.Query <T>(sql).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).ToList <T>();
}
}
else
{
using (System.Data.SqlClient.SqlConnection cn = SqlClient)
{
lResult = cn.Query <T>(sql).ToList <T>();
}
}
}
catch (Exception ex)
{
return(null);
}
return(lResult);
}
