public IList <Item> GetItems(int count)
{
IList <Item> ieskomasArr = new List <Item>();
var comp = new MySqlCompiler();
var query = new Query("preke").Limit(count);
var cc = comp.Compile(query).ToString();
var command = new MySqlCommand(comp.Compile(query).ToString(), connection);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Item ieskomas = new Item();
ieskomas.pavadinimas = reader["pavadinimas"].ToString();
ieskomas.tipas = reader["tipas"].ToString();
ieskomas.path = reader["imagePath"].ToString();
ieskomas.kaina = (float)reader["kaina"];
ieskomas.matomas = (bool)reader["matomas"];
ieskomas.reitingas = (float)reader["reitingas"];
ieskomas.aprasymas = reader["aprasymas"].ToString();
ieskomas.id = (int)reader["id"];
ieskomasArr.Add(ieskomas);
}
connection.Close();
return(ieskomasArr);
}
public Item GetItemById(int id)
{
var comp = new MySqlCompiler();
Item ieskomas = new Item();
var query = new Query("preke").Where("id", id);
var cc = comp.Compile(query).ToString();
var command = new MySqlCommand(comp.Compile(query).ToString(), connection);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
ieskomas.pavadinimas = reader["pavadinimas"].ToString();
ieskomas.tipas = reader["tipas"].ToString();
ieskomas.path = reader["imagePath"].ToString();
ieskomas.kaina = (float)reader["kaina"];
ieskomas.matomas = (bool)reader["matomas"];
ieskomas.reitingas = (float)reader["reitingas"];
ieskomas.aprasymas = reader["aprasymas"].ToString();
ieskomas.id = (int)reader["id"];
}
return(ieskomas);
}
public static MySqlCommand ApplyQuery(this MySqlCommand cmd, Query query)
{
var sqlResult = _compiler.Compile(query);
cmd.CommandText = sqlResult.RawSql;
cmd.Parameters.AddRange(sqlResult.ToParams());
return(cmd);
}
protected override SqlResult GetSqlResult(Query query)
{
var compiler = new MySqlCompiler();
SqlResult sqlResult = compiler.Compile(query);
return(sqlResult);
}
private string[] Compile(Query q)
{
return(new[] {
mssql.Compile(q.Clone()).ToString(),
mysql.Compile(q.Clone()).ToString(),
pgsql.Compile(q.Clone()).ToString(),
});
}
private string[] Compile(Query q)
{
return(new[] {
_sqlsrv.Compile(q.Clone()).ToString(),
_mysql.Compile(q.Clone()).ToString(),
_pg.Compile(q.Clone()).ToString(),
});
}
public void LikeTest()
{
var compiler = new MySqlCompiler();
var query = new Query("some_example_table");
var exp = new ExpressionParser <ExpressionTestClass>(x => x.Description.Like("matter"),
ExpressionTestClass.Mapping(),
query);
var whereClause = compiler.Compile(exp.ToSQL()).ToString();
Assert.IsTrue(whereClause == "SELECT * FROM `some_example_table` WHERE LOWER(`description`) like 'matter'");
}
public void InStringArrayTest()
{
var compiler = new MySqlCompiler();
var query = new Query("some_example_table");
var exp = new ExpressionParser <ExpressionTestClass>(x => x.Description.In("day", "word", "sunset"),
ExpressionTestClass.Mapping(),
query);
var whereClause = compiler.Compile(exp.ToSQL()).ToString();
Assert.IsTrue(whereClause == "SELECT * FROM `some_example_table` WHERE `description` IN ('day', 'word', 'sunset')");
}
public void InUintArrayTest()
{
var compiler = new MySqlCompiler();
var query = new Query("some_example_table");
var exp = new ExpressionParser <ExpressionTestClass>(x => x.Id.In <uint>(1, 2, 3, 4),
ExpressionTestClass.Mapping(),
query);
var whereClause = compiler.Compile(exp.ToSQL()).ToString();
Assert.IsTrue(whereClause == "SELECT * FROM `some_example_table` WHERE `id` IN ('1', '2', '3', '4')");
}
public void SimpleBoolTest()
{
var compiler = new MySqlCompiler();
var query = new Query("some_example_table");
var exp = new ExpressionParser <ExpressionTestClass>(x => !x.IsActive && x.IsActive,
ExpressionTestClass.Mapping(),
query);
var whereClause = compiler.Compile(exp.ToSQL()).ToString();
Assert.IsTrue(whereClause == "SELECT * FROM `some_example_table` WHERE `is_active` = true AND `is_active` = false");
}
public void updateCart(Cart cart)
{
var comp = new MySqlCompiler();
object temp;
temp = new
{
pavadinimas = cart.pavadinimas,
sukurimoData = cart.sukurimoData,
patvirtintas = cart.patvirtintas,
prekes = cart.prekes
};
var query = new Query("krepšelis").Where("Id", cart.id).AsUpdate(temp);
var str = comp.Compile(query).Sql;
var command = new MySqlCommand(comp.Compile(query).ToString(), connection);
connection.Open();
command.ExecuteReader();
connection.Close();
}
public void CreateComment(Comment comment)
{
var comp = new MySqlCompiler();
var query = new Query("komentaras").AsInsert(comment);
var command = new MySqlCommand(comp.Compile(query).ToString(), connection);
connection.Open();
command.ExecuteReader();
connection.Close();
}
public void createCart(Cart cart)
{
var comp = new MySqlCompiler();
var query = new Query("krepšelis").AsInsert(cart);
var command = new MySqlCommand(comp.Compile(query).ToString(), connection);
connection.Open();
command.ExecuteReader();
connection.Close();
}
public void createOrder(Order order)
{
var comp = new MySqlCompiler();
var query = new Query("užsakymas").AsInsert(order);
var command = new MySqlCommand(comp.Compile(query).ToString(), connection);
connection.Open();
command.ExecuteReader();
connection.Close();
}
/// <summary>
/// Get MySQl script for table exists abstract method
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
protected override string GetTableExistScript(string table)
{
var query = new Query("information_schema.tables").Select("*").Where("table_name", table);
var result = _mySqlCompiler.Compile(query);
return($@"SELECT EXISTS({result});");
}
public void CombinedConditionTest()
{
var compiler = new MySqlCompiler();
var query = new Query("some_example_table");
var exp = new ExpressionParser <ExpressionTestClass>(x => x.Description.Like("matter") &&
!x.IsActive &&
x.Id.In <uint>(1, 2, 3, 4) &&
x.Description.In("day", "word", "sunset"),
ExpressionTestClass.Mapping(),
query);
var whereClause = compiler.Compile(exp.ToSQL()).ToString();
Assert.IsTrue(whereClause == "SELECT * FROM `some_example_table` WHERE `description` IN ('day', 'word', 'sunset') AND `id` IN ('1', '2', '3', '4') AND `is_active` = false AND LOWER(`description`) like 'matter'");
}
private void usersTable_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 3) //edit
{
new AddEditUser((User)usersTable.CurrentRow.Tag).ShowDialog();
LoadData();
}
if (e.ColumnIndex == 4) //delete
{
User user = (User)usersTable.CurrentRow.Tag;
if (MessageBox.Show("Уволить " + user.FullName + "?", "CONFIRM", MessageBoxButtons.YesNoCancel) == DialogResult.Yes)
{
var compille = new MySqlCompiler();
var query = new Query("fires").AsInsert(new
{
user_id = user.id,
status = 0,
updated_at = DateTime.Now
});
micron.Exec(compille.Compile(query).ToString());
LoadData();
}
}
if (e.ColumnIndex == 5) //retraining
{
User user = (User)usersTable.CurrentRow.Tag;
if (MessageBox.Show("Отправить на переквалификацию " + user.FullName + "?", "CONFIRM", MessageBoxButtons.YesNoCancel) == DialogResult.Yes)
{
var compille = new MySqlCompiler();
var query = new Query("fires").AsInsert(new
{
user_id = user.id,
status = 1,
updated_at = DateTime.Now
});
micron.Exec(compille.Compile(query).ToString());
LoadData();
}
}
}
private void saveBtn_Click(object sender, EventArgs e)
{
if (validateInput())
{
var compiller = new MySqlCompiler();
var query = new Query("users");
if (_User == null)
{
query = query.AsInsert(new
{
patronymic = userPatronymic.Text,
secondname = userSecondname.Text,
firstname = userFirstname.Text,
birthday = userBirthday.Value,
role_id = (userRole.SelectedItem as ComboboxItem).Value.ToString(),
placeliving = userPlaceliving.Text,
passport_series = userPassportSeries.Text,
passport_number = userPassportNumber.Text,
passport_place = userPassportPlace.Text,
email = userEmail.Text,
});
}
else
{
query = query.Where("id", _User.id).AsUpdate(new
{
patronymic = userPatronymic.Text,
secondname = userSecondname.Text,
firstname = userFirstname.Text,
birthday = userBirthday.Value,
role_id = (userRole.SelectedItem as ComboboxItem).Value.ToString(),
placeliving = userPlaceliving.Text,
passport_series = userPassportSeries.Text,
passport_number = userPassportNumber.Text,
passport_place = userPassportPlace.Text,
email = userEmail.Text,
});
}
micron.Exec(compiller.Compile(query).ToString());
}
}
public void CreateItem(Item item, string path)
{
var comp = new MySqlCompiler();
var c = new
{
pavadinimas = item.pavadinimas,
kaina = item.kaina,
aprasymas = item.aprasymas,
tipas = item.tipas,
reitingas = item.reitingas,
imagePath = path,
matomas = item.matomas
};
var query = new Query("preke").AsInsert(c);
var command = new MySqlCommand(comp.Compile(query).ToString(), connection);
connection.Open();
command.ExecuteReader();
connection.Close();
}
public static object AddNewRegUser(RequestModel request)
{
// Setup the connection and compiler
var connection = new MySqlConnection(ConfigurationManager.AppSettings["MySqlDBConn"].ToString());
var compiler = new MySqlCompiler();
var db = new QueryFactory(connection, compiler);
SuccessResponse successResponseModel = new SuccessResponse();
db.Connection.Open();
using (var scope = db.Connection.BeginTransaction())
{
try
{
var test = JsonConvert.DeserializeObject <Dictionary <string, dynamic> >(Convert.ToString(request.RequestData));
object User;
test.TryGetValue("User", out User);
test.Remove("User");
object Document_SOE;
test.TryGetValue("Document_SOE", out Document_SOE);
List <string> _Document_SOE = Document_SOE as List <string>;
test.Remove("Document_SOE");
if (_Document_SOE != null && _Document_SOE.Count > 0)
{
//convert and add key
}
if (User != null)
{
Dictionary <string, object> _User = JsonConvert.DeserializeObject <Dictionary <string, object> >(User.ToString());
//check if email exists
object Email;
_User.TryGetValue("Email", out Email);
string _Email = Email.ToString();
var response = db.Query("User").Where("Email", _Email).Get();
if (response != null && response.Count() > 0)
{
//return error
return(new SuccessResponse(null, HttpStatusCode.Conflict, "Email already exists"));
}
object Password;
_User.TryGetValue("Password", out Password);
string _Password = Password.ToString();
string _PasswordUnhashed = Password.ToString();
_User.Remove("Password");
if (!string.IsNullOrEmpty(_Password))
{
//convert and add key
_Password = DBManagerUtility._encodeJWT(new Dictionary <string, string>()
{
{ "Password", _Password }
}, AppConstants.AppSecretKeyPassword);
_User.Add("Password", _Password);
}
_User.Add("RegistrationConfirmation", "Y");
_User.Add("isVerified", "N");
var query = db.Query("User").AsInsert(_User);
SqlKata.SqlResult compiledQuery = compiler.Compile(query);
//Inject the Identity in the Compiled Query SQL object
var sql = compiledQuery.Sql + "; SELECT @@IDENTITY as ID;";
//Name Binding house the values that the insert query needs
var IdentityKey = db.Select <string>(sql, compiledQuery.NamedBindings).FirstOrDefault();
test.Add("UserId", IdentityKey);
var resRegUser = db.Query("RegisteredUser").Insert(test);
scope.Commit();
//testing
Dictionary <string, string> responseData = new Dictionary <string, string>();
#region issue AuthToken
var pairs = new List <KeyValuePair <string, string> >
{
new KeyValuePair <string, string>("grant_type", "password"),
new KeyValuePair <string, string>("username", _Email),
new KeyValuePair <string, string> ("Password", _PasswordUnhashed),
new KeyValuePair <string, string> ("scope", "USER")
};
var content = new FormUrlEncodedContent(pairs);
ServicePointManager.ServerCertificateValidationCallback += (sender, cert, chain, sslPolicyErrors) => true;
using (var client = new HttpClient())
{
var responseToken = client.PostAsync(Constants.BaseUrl + "token", content).Result;
var responseContent = responseToken.Content.ReadAsStringAsync().Result;
responseData = JsonConvert.DeserializeObject <Dictionary <string, string> >(responseContent);
}
#endregion
bool hasData = true;
successResponseModel = new SuccessResponse(responseData, hasData);
}
else
{
scope.Rollback();
}
}
catch (Exception ex)
{
//Logger.WriteErrorLog(ex);
scope.Rollback();
return(new ErrorResponse(ex.Message, HttpStatusCode.BadRequest));
}
}
return(successResponseModel);
}
public IActionResult Post([FromBody] SQLRequest req)
{
var conn = new MySqlConnection("server=192.168.99.100;port=3306;user=root;password=root;database=employees");
var cmd = new MySqlCommand("", conn);
// var compiler = new SqlServerCompiler();
var compiler = new MySqlCompiler();
var q = new Query(req.From);
var paramCounter = 0;
foreach (var literal in req.Where)
{
var val = literal.Values[0];
q.Where(
literal.FieldName,
literal.Operator,
val.Value);
// var p = cmd.CreateParameter();
// p.Value = val.Value;
// p.ParameterName = "p" + paramCounter;
cmd.Parameters.AddWithValue("@p" + paramCounter, val.Value);
paramCounter++;
// .Value = val.Value
}
q.Limit(req.Limit);
q.Offset(req.Offset);
cmd.Parameters.AddWithValue("@p" + paramCounter, req.Limit);
paramCounter++;
if (req.Offset > 0)
{
cmd.Parameters.AddWithValue("@p" + paramCounter, req.Offset);
paramCounter++;
}
var sql = compiler.Compile(q).Sql;
Console.Write(sql);
cmd.CommandText = sql;
conn.Open();
var resp = cmd.ExecuteReader();
var responseList = new List <Dictionary <string, object> >();
while (resp.Read())
{
var rowDict = new Dictionary <string, object>();
for (var i = 0; i < resp.FieldCount; i++)
{
rowDict.Add(resp.GetName(i), resp[i]);
}
responseList.Add(rowDict);
}
conn.Close();
return(Ok(new SQLResponse()
{
ResultSet = responseList,
Next = new SQLRequest()
{
Select = req.Select,
From = req.From,
Where = req.Where,
OrderBy = req.OrderBy,
Limit = req.Limit,
Offset = req.Offset + req.Limit
},
Page = 1,
Pages = 10,
Count = 100
}));
}
public IActionResult Query()
{
try
{
/*
* Method a gelen json isteği deserialize edilir.
*/
var req = HttpContext.Request;
if (req.ContentType.ToLower(enCulture) != "application/json")
{
throw new Exception("Content-Type application/json olmalı");
}
string bodyStr;
using (StreamReader reader
= new StreamReader(req.Body, Encoding.UTF8, true, 1024, true))
{
var t = reader.ReadToEndAsync();
t.Wait();
bodyStr = t.Result;
}
//Database bağlantısı yapılır.
ResultActions result = new ResultActions();
MySqlCompiler qcompiler;
var query = new QueryFactory(new MySqlConnection(MyConfig.Value.Database), qcompiler = new MySqlCompiler());
//Longtitude, Latitude bilgisi gelen requestten parse edilir
dynamic reqParams = JsonConvert.DeserializeObject(bodyStr);
decimal lng = reqParams.longtitude;
decimal lat = reqParams.latitude;
//Api Key kontrolü yapılır.
try
{
string key = HttpContext.Request.Headers["apiKey"].ToString();
if (key != MyConfig.Value.Key)
{
return(new ObjectResult(new ResultActions()
{
Success = false,
ErrorMessage = "Kimlik hatalı",
Actions = null
}));
}
}
catch (Exception)
{
//Api Key hatalı ise aşağıdaki hata mesajı görüntülenir.
return(new ObjectResult(new ResultActions()
{
Success = false,
ErrorMessage = "Kimlik hatalı",
Actions = null
}));
}
//1KM mesafe bilgileri hesaplanır.
decimal lngKM1 = 0.004073m;
decimal latKM1 = 0.010857m;
decimal lngLow = lng - lngKM1 / 2m;
decimal latLow = lat - latKM1 / 2m;
decimal lngHigh = lng + lngKM1 / 2m;
decimal latHigh = lat + latKM1 / 2m;
//Database üzerinden kaza bildirimleri alınır.
var q = query.FromQuery(new SqlKata.Query("actions")).WhereBetween <decimal>("Lng", lngLow, lngHigh).WhereBetween <decimal>("Lat", latLow, latHigh);
var r = qcompiler.Compile(q);
var results = q.Get().ToArray();
//Alınan sonuçlar map edilir.
result.Actions = (from item in results
select new AmanDiyimAction()
{
Id = item.Id,
Action = item.ActionDetail,
Lng = Convert.ToDecimal(item.Lng),
Lat = Convert.ToDecimal(item.Lat),
Type = item.ActionType
}).ToList();
result.Success = true;
//Map edilen sonuçlar response olarak dönülür.
return(new ObjectResult(result));
}
catch (Exception)
{
//Serviste bir hata oluştuğunda aşağıdaki hata mesajı görüntülenir.
return(new ObjectResult(new ResultActions()
{
Actions = null,
ErrorMessage = "İstek hatalı",
Success = false
}));
}
}
public static SqlResult ToMySQL(this Query query)
{
var compiler = new MySqlCompiler();
return(compiler.Compile(query));
}
public static SqlResult CompileWithLastId(this MySqlCompiler compiler, Query query)
{
SqlResult result = compiler.Compile(query);
return(new SqlResult(result.Sql + ";SELECT LAST_INSERT_ID();", result.RawBindings));
}
public static object SaveAddNewProperty(RequestModel request)
{
var connection = new MySqlConnection(ConfigurationManager.AppSettings["MySqlDBConn"].ToString());
var compiler = new MySqlCompiler();
var db = new QueryFactory(connection, compiler);
SuccessResponse successResponseModel = new SuccessResponse();
db.Connection.Open();
using (var scope = db.Connection.BeginTransaction())
{
try
{
var test = JsonConvert.DeserializeObject <Dictionary <string, dynamic> >(Convert.ToString(request.RequestData));
object PropertyType;
test.TryGetValue("PropertyType", out PropertyType);
string _PropertyType = PropertyType.ToString();
object PropertyPics;
test.TryGetValue("PropertyPics", out PropertyPics);
List <string> _PropertyPics = PropertyPics as List <string>;
test.Remove("PropertyPics");
if (_PropertyPics != null && _PropertyPics.Count > 0)
{
//convert and add key
}
object PropertyVideos;
test.TryGetValue("PropertyVideos", out PropertyVideos);
List <string> _PropertyVideos = PropertyVideos as List <string>;
test.Remove("PropertyVideos");
if (_PropertyVideos != null && _PropertyVideos.Count > 0)
{
//convert and add key
}
object Documents;
test.TryGetValue("Documents", out Documents);
List <string> _Documents = Documents as List <string>;
test.Remove("Documents");
if (_Documents != null && _Documents.Count > 0)
{
//convert and add key
}
object PropertyShare;
test.TryGetValue("PropertyShare", out PropertyShare);
test.Remove("PropertyShare");
object PropertyPrediction;
test.TryGetValue("PropertyPrediction", out PropertyPrediction);
test.Remove("PropertyPrediction");
object Developmental;
test.TryGetValue("Developmental", out Developmental);
test.Remove("Developmental");
object DevelopmentalPrediction;
test.TryGetValue("DevelopmentalPrediction", out DevelopmentalPrediction);
test.Remove("DevelopmentalPrediction");
object RentalProperty;
test.TryGetValue("RentalProperty", out RentalProperty);
test.Remove("RentalProperty");
// You can register the QueryFactory in the IoC container
var query = db.Query("propertydetail").AsInsert(test);
SqlKata.SqlResult compiledQuery = compiler.Compile(query);
//Inject the Identity in the Compiled Query SQL object
var sql = compiledQuery.Sql + "; SELECT @@IDENTITY as ID;";
//Name Binding house the values that the insert query needs
var IdentityKey = db.Select <string>(sql, compiledQuery.NamedBindings).FirstOrDefault();
Dictionary <string, object> _PropertyShare = JsonConvert.DeserializeObject <Dictionary <string, object> >(PropertyShare.ToString());
if (_PropertyShare != null)
{
_PropertyShare.Add("PropertyId", IdentityKey);
var resPropertyShare = db.Query("PropertyShare").Insert(_PropertyShare);
}
List <Dictionary <string, object> > _PropertyPrediction = JsonConvert.DeserializeObject <List <Dictionary <string, object> > >(PropertyPrediction.ToString());
if (_PropertyPrediction != null)
{
foreach (var pp in _PropertyPrediction)
{
pp.Add("PropertyId", IdentityKey);
var resPropertyShare = db.Query("PropertyPrediction").Insert(pp);
}
}
if (_PropertyType == "D")
{
Dictionary <string, object> _Developmental = JsonConvert.DeserializeObject <Dictionary <string, object> >(Developmental.ToString());
if (_Developmental != null)
{
_Developmental.Add("PropertyId", IdentityKey);
var resDevelopmental = db.Query("Developmental").Insert(_Developmental);
}
List <Dictionary <string, object> > _DevelopmentalPrediction = JsonConvert.DeserializeObject <List <Dictionary <string, object> > >(DevelopmentalPrediction.ToString());
if (_DevelopmentalPrediction != null)
{
foreach (var dp in _DevelopmentalPrediction)
{
dp.Add("PropertyId", IdentityKey);
var resDevelopmentalPrediction = db.Query("DevelopmentalPrediction").Insert(dp);
}
}
}
if (_PropertyType == "R")
{
Dictionary <string, object> _RentalProperty = JsonConvert.DeserializeObject <Dictionary <string, object> >(RentalProperty.ToString());
if (_RentalProperty != null)
{
object RentalContract;
_RentalProperty.TryGetValue("RentalContract", out RentalContract);
List <string> _RentalContract = RentalContract as List <string>;
_RentalProperty.Remove("RentalContract");
if (_RentalContract != null && _RentalContract.Count > 0)
{
//convert and add key
}
_RentalProperty.Add("PropertyId", IdentityKey);
var resRentalProperty = db.Query("RentalProperty").Insert(_RentalProperty);
}
}
bool hasData = true;
scope.Commit();
successResponseModel = new SuccessResponse("", hasData, "Record Saved");
}
catch (Exception ex)
{
//Logger.WriteErrorLog(ex);
scope.Rollback();
return(new ErrorResponse(ex.Message, HttpStatusCode.BadRequest));
}
}
return(successResponseModel);
}