public QueryBuilderTest()
{
_sqlsrv = new SqlServerCompiler();
_mysql = new MySqlCompiler();
_pg = new PostgresCompiler();
}
public static object GetAllPropertyByPropertyStatus(RequestModel request)
{
var test = JsonConvert.DeserializeObject <Dictionary <string, dynamic> >(Convert.ToString(request.RequestData));
object _PropertyType;
test.TryGetValue("PropertyType", out _PropertyType);
// 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();
try
{
// You can register the QueryFactory in the IoC container
IEnumerable <IDictionary <string, object> > response;
response = db.Query("propertydetail")
.Where("PropertyType", _PropertyType)
.Get()
.Cast <IDictionary <string, object> >(); //db.Query("jpexperience").Where("ExpId", 6).Where("ProfileId", 4).First();
bool hasData = (response != null) ? true : false;
if (hasData)
{
foreach (var row in response)
{
//var _PropertyType = row["PropertyType"];
var PropertyID = row["PropertyID"];
int _PropertyID = Convert.ToInt32(PropertyID);
if ((_PropertyType.ToString() == "D"))
{
object response_dev = db.Query("developmental").Where("PropertyID", _PropertyID).Get().Cast <IDictionary <string, object> >();
row.Add("developmental", response_dev);
object response_dev_pred = db.Query("developmentalprediction").Where("PropertyID", _PropertyID).Get().Cast <IDictionary <string, object> >();
row.Add("developmentalprediction", response_dev_pred);
}
else if ((_PropertyType.ToString() == "R"))
{
object response_rent = db.Query("rentalproperty").Where("PropertyID", _PropertyID).Get().Cast <IDictionary <string, object> >();
row.Add("rental", response_rent);
}
object response_pred = db.Query("propertyprediction").Where("PropertyID", _PropertyID).Get().Cast <IDictionary <string, object> >();
row.Add("propertyprediction", response_pred);
}
}
successResponseModel = new SuccessResponse(response, hasData);
}
catch (Exception ex)
{
//Logger.WriteErrorLog(ex);
return(new ErrorResponse(ex.Message, HttpStatusCode.BadRequest));
}
return(successResponseModel);
}
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 MySqlLimitTests()
{
compiler = Compilers.Get <MySqlCompiler>(EngineCodes.MySql);
}
public static object Verify(RequestModel request)
{
var test = JsonConvert.DeserializeObject <Dictionary <string, object> >(Convert.ToString(request.RequestData));
var connection = new MySqlConnection(ConfigurationManager.AppSettings["MySqlDBConn"].ToString());
var compiler = new MySqlCompiler();
var db = new QueryFactory(connection, compiler);
SuccessResponse successResponseModel = new SuccessResponse();
try
{
if (test != null)
{
object Token;
test.TryGetValue("Token", out Token);
string _Token = Token.ToString();
var obj = JsonConvert.DeserializeObject <Dictionary <string, object> >(DBManagerUtility._decodeJWT(_Token, AppConstants.AppSecretLinkObject) as string);
object Type;
test.TryGetValue("Type", out Type);
string _Type = Type.ToString();
object Email;
obj.TryGetValue("Email", out Email);
string _Email = Email.ToString();
var response = db.Query("User").Where("Email", _Email).First();
if (response != null)
{
if (_Type == "FPASS")
{
object newPassword;
test.TryGetValue("Password", out newPassword);
string _newPassword = newPassword.ToString();
_newPassword = DBManagerUtility._encodeJWT(new Dictionary <string, string>()
{
{ "Password", _newPassword }
}, AppConstants.AppSecretKeyPassword);
var resUser = db.Query("User").Where("Email", _Email).Update(new
{
Password = _newPassword
});
successResponseModel = new SuccessResponse(null, HttpStatusCode.OK, "Password Updated");
}
else if (_Type == "VERIFY")
{
var resUser = db.Query("User").Where("Email", _Email).Update(new
{
isVerified = "Y"
});
successResponseModel = new SuccessResponse(null, HttpStatusCode.OK, "User Verified");
}
else
{
successResponseModel = new SuccessResponse(null, HttpStatusCode.BadRequest, "Invalid Type");
}
}
else
{
successResponseModel = new SuccessResponse(null, HttpStatusCode.BadRequest, "Invalid Email");
}
}
else
{
successResponseModel = new SuccessResponse(null, HttpStatusCode.BadRequest, "Object is null");
}
}
catch (Exception ex)
{
return(new ErrorResponse(ex.Message, HttpStatusCode.BadRequest));
}
return(successResponseModel);
}
public static object BuyShare(RequestModel request)
{
var test = JsonConvert.DeserializeObject <Dictionary <string, dynamic> >(Convert.ToString(request.RequestData));
object _PropertyID;
test.TryGetValue("PropertyID", out _PropertyID);
object TotalShareQuantity;
test.TryGetValue("TotalShareQuantity", out TotalShareQuantity);
int _totalShareQuantity = Convert.ToInt32(TotalShareQuantity);
object ShareQty;
test.TryGetValue("ShareQty", out ShareQty);
int _ShareQty = Convert.ToInt32(ShareQty);
object ShareMarketValue;
test.TryGetValue("ShareBuyingValue", out ShareMarketValue);
object TotalAmount;
test.TryGetValue("ShareAmount", out TotalAmount);
object RegUserID;
test.TryGetValue("RegUserID", out RegUserID);
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
{
//object response;
var response = db.Query("UserShare")
.SelectRaw("SUM(`ShareQty`) as SumShares")
.Where("PropertyID", _PropertyID)
.Get()
.Cast <IDictionary <string, object> >();
//total share -usershare-sharehold
int sumShare = 0;
if (response != null)
{
sumShare = response.ElementAt(0)["SumShares"] == null ? 0 : Convert.ToInt32(response.ElementAt(0)["SumShares"]);
}
response = db.Query("PropertyShareHold")
.Select("PropertyID", "ShareQty")
.SelectRaw("MAX(`URN`) as MaxURN")
.Where("PropertyID", _PropertyID)
.Get()
.Cast <IDictionary <string, object> >();
int holdShare = 0;
if (response != null)
{
holdShare = response.ElementAt(0)["ShareQty"] == null ? 0 : Convert.ToInt32(response.ElementAt(0)["ShareQty"]);
}
int remainingShares = _totalShareQuantity - holdShare - sumShare;
if (remainingShares > _ShareQty)
{
Dictionary <string, object> UserShareRecord = new Dictionary <string, object>()
{
{ "PropertyID", _PropertyID },
{ "RegUserID", RegUserID },
{ "DateofInvestment", DateTime.Now.Date },
{ "ShareMarketValue", ShareMarketValue },
{ "ShareStatus", "H" },
{ "TotalAmount", TotalAmount },
{ "ShareQty", ShareQty }
};
remainingShares = remainingShares - _ShareQty;
var res1 = db.Query("PropertyShare").Where("PropertyID", _PropertyID).Update(new { AvailableShares = remainingShares });
var res = db.Query("UserShare").Insert(UserShareRecord);
bool hasData = true;
scope.Commit();
successResponseModel = new SuccessResponse(res, hasData);
}
else
{
//handle when remaining share is less
}
}
catch (Exception ex)
{
scope.Rollback();
return(new ErrorResponse(ex.Message, HttpStatusCode.BadRequest));
}
return(successResponseModel);
}
}
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 QueryBuilderTest()
{
mssql = new SqlServerCompiler();
mysql = new MySqlCompiler();
pgsql = new PostgresCompiler();
}
static void Main(string[] args)
{
try
{
//POSTGRESQL TEST
string strConnection = "Server=localhost;Database=testdb;Uid=root;Pwd=senha;SslMode=none";
Compiler compiler = new MySqlCompiler();
MySqlConnection connection = new MySqlConnection(strConnection);
Credit c = new Credit();
c.Description = "SOUZA E ALFAMEZA";
c.Created = DateTime.Now.AddDays(-150);
//var result = connection.Insert(c).Save();
c = connection.Insert(c).Save <Credit>();
c.Description = "SOUZA E ALFAMEZA E BUCETA";
var k = connection.Update(c).Change();
System.Console.WriteLine("{0} {1} {2}", c.Id, c.Description, c.Created);
//var ca = connection.Update(c).Change();
//var b = ca;
//var result = connection.PrepareQuery(x => x.From("test").OrderBy("id").Where("id", 133).Limit(1)).FindOne<Test>();
//connection.Insert()
//var count0 = connection
// .Insert(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
// .Save();
//var id = count0.Id;
//var count1 = connection
// .Insert(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
// .Save();
//var a = 100;
#region init
//var multiple = connection.MultipleBuild();
//multiple.Clear();
//var resultInsert = multiple
// .AddInsert<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
// .AddInsert<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
// .AddInsert<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
// .AddInsert<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
// .ExecuteMultiple()
// .ToList();
//var a = resultInsert[0].GetValue<long>();
//multiple.Clear();
//var resultSelect = multiple
// .AddSelect<Test>(x => x.From("test").Where("id", 1))
// .AddSelect<Test>(x => x.From("test").Where("id", 2))
// .AddSelect<Test>(x => x.From("test").Where("id", 3))
// .AddSelect<Test>(x => x.From("test").Where("id", 4))
// .ExecuteMultiple()
// .ToList();
//var b = resultSelect;
//var ctest = resultSelect[1].GetValue<IEnumerable<object>>().Select(x => (Test)x);
//foreach (IResultItems item in resultSelect)
//{
// var ab = item.Value;
// var c = ab.GetType();
// foreach (Test t in item.GetValue<IEnumerable<object>>().Select(x => (Test)x))
// {
// System.Console.WriteLine("{0:000} {1}", t.Id, t.Name);
// }
// System.Console.WriteLine("-----------------------------------------------------");
//}
//multiple.Clear();
//var resultUpdate = multiple
// .AddUpdate(x => x.From("test").Where("id", 1).AsUpdate(new { name = Guid.NewGuid().ToString() }))
// .AddUpdate(x => x.From("test").Where("id", 2).AsUpdate(new { name = Guid.NewGuid().ToString() }))
// .AddUpdate(x => x.From("test").Where("id", 3).AsUpdate(new { name = Guid.NewGuid().ToString() }))
// .AddUpdate(x => x.From("test").Where("id", 4).AsUpdate(new { name = Guid.NewGuid().ToString() }))
// .ExecuteMultiple()
// .ToList();
//multiple.Clear();
//var resultDelete = multiple
// .AddDelete(x => x.From("test").Where("id", 1).AsDelete())
// .ExecuteMultiple()
// .ToList();
//var resultsI = connection.MultipleBuild().AddInsert<int>(x => x.From("")).AddInsert<int>(x => x.From("")).ExecuteMultiple();
//var resultsD = connection.MultipleBuild().AddDelete(x => x.From("")).ExecuteMultiple();
//var resultsS = connection.MultipleBuild().AddSelect<int>(x => x.From("")).ExecuteMultiple();
//var resultsU = connection.MultipleBuild().AddUpdate(x => x.From("")).ExecuteMultiple();
//.AddInsert<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
//.AddInsert<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
//.AddSelect<Test>(x => x.From("test").OrderBy("id"))
///*.AddQuery<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
//.AddQuery<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))
//.AddQuery<int>(x => x.From("test").AsInsert(new { name = Guid.NewGuid().ToString() }, true))*/
////.AddUpdate(x => x.From("test").Where("id", 1).AsUpdate(new { name = Guid.NewGuid().ToString() }))
//.ExecuteMultiple()
//.ToList();
//var aba = connection.SoftBuild();
// .From("credit")
// .AsInsert(new Dictionary<string, object>
// {
// ["description"] = "desc1",
// ["created"] = DateTime.Now.AddDays(-3)
// }, true);
// var result = a.SaveInsert<int>();
//var b = result;
//var c = new QueryBuilderSoftDapper(connection, compiler);
//var reader = c.QueryBuilderMultipleCollection()
// .AddQuery(x => x.From("credit").OrderBy("id"))
// .AddQuery(x => x.From("credit").OrderBy("description"))
// .AddQuery(x => x.From("credit").Where("id", 1))
// .AddQuery(x => x.From("credit").AsCount())
// .Results();
//IList<Credit> credit0 = reader.Read<Credit>().ToList();
//IList<Credit> credit1 = reader.Read<Credit>().ToList();
//Credit credit3 = reader.ReadFirst<Credit>();
//int count = reader.ReadFirst<int>();
//var b = 10;
//var peoples = r.Read<People>();
//var credits = r.Read<Credit>();
//int countPeople = r.ReadFirst<int>();
//var result = c.AddQuery<Credit>(x =>
// x.From("Credit")
// .AsInsert(new Dictionary<string, object>
// {
// ["Description"] = "Testando 123",
// ["Created"] = DateTime.Now.AddDays(-1)
// })
// )
// .AddQuery<Credit>(x =>
// x.From("Credit")
// .AsInsert(new Dictionary<string, object>
// {
// ["Description"] = "Testando 345",
// ["Created"] = DateTime.Now.AddDays(-2)
// }))
// .Results();
#endregion
}
catch (Exception ex)
{
throw ex;
}
Console.WriteLine("");
Console.WriteLine("");
Console.WriteLine("Execução finalizada com sucesso !!!");
Console.WriteLine("");
Console.ReadKey();
}
/// <summary>
/// Recommneded to be only called once to initialize the compiler/connection instances
/// </summary>
/// <param name="host"></param>
/// <param name="port"></param>
/// <param name="user"></param>
/// <param name="pwd"></param>
/// <param name="db"></param>
/// <param name="SslMode"></param>
public static void Initialize(string host, int port, string user, string pwd, string db, string SslMode = "None")
{
Compiler = new MySqlCompiler();
Connection = new MySqlConnection($"Host={host};Port={port};User={user};Password={pwd};Database={db};SslMode={SslMode};convert zero datetime=True");
}
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 HttpResponseMessage Donate(Object Donation)
{
var test = JsonConvert.DeserializeObject <Dictionary <string, dynamic> >(Convert.ToString(Donation));
object DonationDetails;
test.TryGetValue("DonationDetails", out DonationDetails);
//object PickUpLat;
//test.TryGetValue("PickUpLat", out PickUpLat);
//double _PickUpLat = Convert.ToDouble(PickUpLat);
//object PickUpLng;
//test.TryGetValue("PickUpLng", out PickUpLng);
//double _PickUpLng = Convert.ToDouble(PickUpLng);
object UserId;
test.TryGetValue("UserId", out UserId);
int _UserId = Convert.ToInt32(UserId);
object TypeId;
test.TryGetValue("DonationTypeId", out TypeId);
int _TypeId = Convert.ToInt32(TypeId);
var connection = new MySqlConnection(ConfigurationManager.AppSettings["MySqlDBConn"].ToString());
var compiler = new MySqlCompiler();
var db = new QueryFactory(connection, compiler);
db.Connection.Open();
using (var scope = db.Connection.BeginTransaction())
{
try
{
Dictionary <string, object> DonationObj = new Dictionary <string, object>()
{
{ "RequestDate", DateTime.Now.Date },
{ "DonationTypeId", _TypeId },
{ "UserId", _UserId },
{ "DonationStatus", DonationStatusPending }
//donationdetails
};
var res = db.Query("userDonation").InsertGetId <int>(new
{
RequestDate = DateTime.Now.Date,
DonationTypeId = _TypeId,
UserId = _UserId,
DonationStatus = DonationStatusPending
});
List <Dictionary <string, object> > _DonationDetails = JsonConvert.DeserializeObject <List <Dictionary <string, object> > >(DonationDetails.ToString());
foreach (var item in _DonationDetails)
{
item.Add("DonationId", res);
var resp = db.Query("Donationdetails").Insert(item);
}
scope.Commit();
return(Request.CreateResponse(HttpStatusCode.Created, new Dictionary <string, int>()
{
{ "LastInsertedID", res }
}));
}
catch (Exception ex)
{
scope.Rollback();
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex));
}
}
}
/// <summary>
/// Create new instance of MySQL script generator
/// </summary>
/// <param name="options"></param>
public MySqlTestGenerator(Action <TestGeneratorOptions> options = null) :
base(DatabaseKind.MySql, options)
{
_mySqlCompiler = new MySqlCompiler();
}