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(); }