public DataTable InsertExchangeRateList(string startDate, string endDate) { DataTable dt = new DataTable(); Boolean flagError = false; var url = ConfigurationManager.AppSettings["NBRMUrl"]; var SOAPaction = ConfigurationManager.AppSettings["NBRM_SOAPAction"]; var action = ConfigurationManager.AppSettings["NBRMAction"]; Dictionary <string, string> parameters = new Dictionary <string, string>(); parameters.Add("StartDate", startDate); parameters.Add("EndDate", endDate); string result = soapHelper.CallWebService(url, action, parameters, SOAPaction); result = result.Replace("<", "<"); result = result.Replace(">", ">"); int from = result.IndexOf("<KursZbir>"); int to = result.LastIndexOf("</KursZbir>"); if (from != -1 && to != -1) { string tmp = "<Root>" + result.Substring(from, to - from + 11) + "</Root>"; XmlDocument doc = new XmlDocument(); doc.LoadXml(tmp); string json = JsonConvert.SerializeXmlNode(doc); string array = json.Substring(json.IndexOf("[") + 1, json.Length - json.IndexOf("[") - 3); string[] currencies = array.Split('}'); for (int i = 0; i < currencies.Length - 1; i++) { if (i != 0) { currencies[i] = currencies[i].Substring(1, currencies[i].Length - 1); } currencies[i] = currencies[i] + "}"; ExchangeRateListModel currency = JsonConvert.DeserializeObject <ExchangeRateListModel>(currencies[i]); dt = new DataTable(); List <sqlTbl> sqlParasList = new List <sqlTbl>(); DateTime dF = Convert.ToDateTime(currency.Datum_f); string dateFrom = dF.ToString("yyyy-MM-dd"); DateTime dT = Convert.ToDateTime(currency.Datum); string dateTo = dT.ToString("yyyy-MM-dd"); sqlParasList.Add(new sqlTbl("@codeIN", currency.Valuta)); sqlParasList.Add(new sqlTbl("@currencyShortcutIN", currency.Oznaka)); sqlParasList.Add(new sqlTbl("@currencyIN", currency.NazivMak)); sqlParasList.Add(new sqlTbl("@dateIN", dateTo)); sqlParasList.Add(new sqlTbl("@dateFromIN", dateFrom)); sqlParasList.Add(new sqlTbl("@countryEnglishIN", currency.DrzavaAng)); sqlParasList.Add(new sqlTbl("@currencyEnglishIN", currency.NazivAng)); sqlParasList.Add(new sqlTbl("@middleRateIN", currency.Sreden)); sqlParasList.Add(new sqlTbl("@countryIN", currency.Drzava)); sqlParasList.Add(new sqlTbl("@countryAlbanianIN", currency.DrzavaAl)); sqlParasList.Add(new sqlTbl("@currencyAlbanianIN", currency.ValutaNaziv_AL)); sqlParasList.Add(new sqlTbl("@insertDateIN", DateTime.Now.ToString("yyyy-MM-dd"))); dt = dbConnectionHelper.procedureRequest("InsertExchangeRateList", sqlParasList, "iqmarket"); if (dbConnectionHelper.getError() != "") { flagError = true; } else { flagError = false; } } dt = new DataTable(); List <sqlTbl> sqlPList = new List <sqlTbl>(); DateTime d = Convert.ToDateTime(startDate); string date = d.ToString("yyyy-MM-dd"); //Makedonski denari sqlPList.Add(new sqlTbl("@codeIN", "807")); sqlPList.Add(new sqlTbl("@currencyShortcutIN", "MKD")); sqlPList.Add(new sqlTbl("@currencyIN", "македонски денари")); sqlPList.Add(new sqlTbl("@dateIN", date)); sqlPList.Add(new sqlTbl("@dateFromIN", date)); sqlPList.Add(new sqlTbl("@countryEnglishIN", "Macedonia")); sqlPList.Add(new sqlTbl("@currencyEnglishIN", "")); sqlPList.Add(new sqlTbl("@middleRateIN", "1")); sqlPList.Add(new sqlTbl("@countryIN", "Македонија")); sqlPList.Add(new sqlTbl("@countryAlbanianIN", "")); sqlPList.Add(new sqlTbl("@currencyAlbanianIN", "")); sqlPList.Add(new sqlTbl("@insertDateIN", DateTime.Now.ToString("yyyy-MM-dd"))); dt = dbConnectionHelper.procedureRequest("InsertExchangeRateList", sqlPList, "iqmarket"); if (dbConnectionHelper.getError() != "") { flagError = true; } else { flagError = false; } } else { dt.TableName = "Not inserted"; return(dt); } if (flagError) { dt.TableName = "Error"; return(dt); } else { dt.TableName = "Table"; return(dt); } }
//private IEnumerable<dynamic> dbGetAgentList(ref int totalRecordCount ) private dynamic dbGetAgentList(ref int totalRecordCount) { bool initGrid = Request.Query["type"].ToString() == "initGrid" ? true : false; bool exportGrid = Request.Query["type"].ToString() == "exportGrid" ? true : false; string remoteIP = this.HttpContext.Connection.RemoteIpAddress.ToString(); string localIP = this.HttpContext.Connection.LocalIpAddress.ToString(); string page = Request.Query["page"].ToString(); string start = Request.Query["start"].ToString(); string limit = Request.Query["limit"].ToString(); List <dynamic> rows = new List <dynamic>(); GIGridInitModel giGridInitModel = new GIGridInitModel() { ColumnList = new List <GIGridColumn>() }; try { using (SqlConnection sqlConnection = new SqlConnection( GIxUtils.DecodeConnectionString( _configuration, ref _loginRequest, Request.Headers["X-WebGI-Authentication"], Request.Headers["X-WebGI-Version"]))) { sqlConnection.Open(); using (SqlCommand sqlCommand = sqlConnection.CreateCommand()) { sqlCommand.Connection = sqlConnection; sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = "dbo.[usp_WebGI_GetExchangeRateList]"; //sqlCommand.Parameters.AddWithValue("@APIKey", apiKey); sqlCommand.Parameters.AddWithValue("@IP_Local", localIP); sqlCommand.Parameters.AddWithValue("@IP_Remote", remoteIP); sqlCommand.Parameters.AddWithValue("@InitGrid", initGrid); sqlCommand.Parameters.AddWithValue("@ExportGrid", exportGrid); sqlCommand.Parameters.AddWithValue("@Salt", _loginRequest.salt); sqlCommand.Parameters.AddWithValue("@Version", _loginRequest.version); sqlCommand.Parameters.AddWithValue("@page", page); sqlCommand.Parameters.AddWithValue("@start", start); sqlCommand.Parameters.AddWithValue("@limit", limit); sqlCommand.Parameters.AddWithValue("@sort", Request.Query["sort"].ToString()); sqlCommand.Parameters.AddWithValue("@iso", Request.Query["iso"].ToString()); sqlCommand.Parameters.AddWithValue("@date", Request.Query["date"].ToString()); SqlParameter outputValue = sqlCommand.Parameters.Add("@totalCount", SqlDbType.Int); outputValue.Direction = ParameterDirection.Output; SqlDataReader recordSet = sqlCommand.ExecuteReader(); using (recordSet) { object value; while (recordSet.Read()) { if (initGrid == true) { #region helper properties GIGridColumn column = new GIGridColumn(); if ((value = recordSet[recordSet.GetOrdinal("Title")]) != System.DBNull.Value) { column.Title = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("DataIndex")]) != System.DBNull.Value) { column.DataIndex = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("DisplayField")]) != System.DBNull.Value) { column.DisplayField = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("ValueField")]) != System.DBNull.Value) { column.ValueField = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("ValueType")]) != System.DBNull.Value) { column.ValueType = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("Width")]) != System.DBNull.Value) { column.Width = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("Flex")]) != System.DBNull.Value) { column.Flex = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("Renderer")]) != System.DBNull.Value) { column.Renderer = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("IsFilter")]) != System.DBNull.Value) { column.IsFilter = (bool)value; } if ((value = recordSet[recordSet.GetOrdinal("IsNotColumn")]) != System.DBNull.Value) { column.IsNotColumn = (bool)value; } if ((value = recordSet[recordSet.GetOrdinal("IsHidden")]) != System.DBNull.Value) { column.IsHidden = (bool)value; } if ((value = recordSet[recordSet.GetOrdinal("IsMenuDisabled")]) != System.DBNull.Value) { column.IsMenuDisabled = (bool)value; } if ((value = recordSet[recordSet.GetOrdinal("IsGridSummaryRow")]) != System.DBNull.Value) { column.IsGridSummaryRow = (bool)value; } if ((value = recordSet[recordSet.GetOrdinal("IsLocked")]) != System.DBNull.Value) { column.IsLocked = (bool)value; } if ((value = recordSet[recordSet.GetOrdinal("SummaryRenderer")]) != System.DBNull.Value) { column.SummaryRenderer = (string)value; } giGridInitModel.ColumnList.Add(column); #endregion } else { ExchangeRateListModel model = new ExchangeRateListModel(); var properties = model.GetType().GetProperties(); foreach (var el in properties) { string name = el.Name; value = recordSet[recordSet.GetOrdinal(name)]; if (value != System.DBNull.Value) { switch (el.PropertyType.Name) { case "Int32": el.SetValue(model, (int)value); break; case "String": el.SetValue(model, (string)value); break; case "Boolean": el.SetValue(model, (bool)value); break; case "Decimal": el.SetValue(model, (decimal)value); break; case "DateTime": el.SetValue(model, (DateTime)value); break; } } } rows.Add(model); } } if (initGrid == true && recordSet.NextResult() && recordSet.Read()) { if ((value = recordSet[recordSet.GetOrdinal("Title")]) != System.DBNull.Value) { giGridInitModel.Title = (string)value; } if ((value = recordSet[recordSet.GetOrdinal("Toolbar")]) != System.DBNull.Value) { giGridInitModel.Toolbar = (string)value; } } recordSet.Close(); recordSet.Dispose(); if (outputValue.Value != null) { totalRecordCount = (int)outputValue.Value; } } } sqlConnection.Close(); sqlConnection.Dispose(); } } catch (Exception ex) { GIxUtils.Log(ex); throw new Exception(ex.Message); } if (initGrid == false) { return(rows); } return(giGridInitModel); }