public void SetValue(IDbDataParameter parameter, object value) { var cursor = value as RefCursor; if (cursor != null) { HandleRefCursor(parameter, cursor); } }
/// <summary> /// 转换为数据库的对应参数类型 /// </summary> /// <param name="dbType"></param> /// <returns></returns> public System.Data.Common.DbParameter[] ConvertToDbParameter(eDBType dbType) { if (_dicParamNameValue == null | _dicParamNameValue.Count == 0) return new System.Data.Common.DbParameter[0]; System.Data.Common.DbParameter[] pras = new System.Data.Common.DbParameter[_dicParamNameValue.Count]; switch (dbType) { case eDBType.Oracle: System.Data.OracleClient.OracleParameter[] pOras = new System.Data.OracleClient.OracleParameter[_dicParamNameValue.Count]; int iOra = 0; foreach (string name in _dicParamNameValue.Keys) { pOras[iOra++] = new System.Data.OracleClient.OracleParameter(name, _dicParamNameValue[name]); } return pOras; case eDBType.SqlServer: System.Data.SqlClient.SqlParameter[] pSqls = new System.Data.SqlClient.SqlParameter[_dicParamNameValue.Count]; int iSql = 0; foreach (string name in _dicParamNameValue.Keys) { pSqls[iSql++] = new System.Data.SqlClient.SqlParameter(name, _dicParamNameValue[name]); } return pSqls; case eDBType.Access: System.Data.OleDb.OleDbParameter[] oSqls = new System.Data.OleDb.OleDbParameter[_dicParamNameValue.Count]; int iOledb = 0; foreach (string name in _dicParamNameValue.Keys) { oSqls[iOledb++] = new System.Data.OleDb.OleDbParameter(name, _dicParamNameValue[name]); } return oSqls; case eDBType.MySql: case eDBType.DB2: case eDBType.Sybase: default: break; } return new System.Data.Common.DbParameter[0]; }
protected override void SetParameter(int index, System.Data.Common.DbParameter value) { }
protected abstract void SetParameter(string parameterName, System.Data.Common.DbParameter value);
/// <summary> /// 挂单历史查询 /// </summary> /// <param name="Lqc">查询条件</param> /// <param name="pageindex">第几页,从1开始</param> /// <param name="pagesize">每页多少条</param> /// <param name="page">输出参数(总页数)</param> /// <returns>挂单历史记录</returns> public List <LTradeHoldOrder> GetLTradeHoldOrder(LQueryCon Lqc, int pageindex, int pagesize, ref int page) { List <LTradeHoldOrder> list = new List <LTradeHoldOrder>(); System.Data.Common.DbDataReader dbreader = null; TradeUser TdUser = new TradeUser(); System.Data.Common.DbParameter OutputParam = DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@PageCount", DbParameterType.String, 0, ParameterDirection.Output); try { string userId = string.Empty; string AndStr = string.Empty; string PartSearchCondition = string.Empty; string ParentOrgID = string.Empty; if (!ComFunction.ExistUserLoginID(Lqc.LoginID, ref TdUser)) { return(list); } if (UserType.NormalType == TdUser.UType) //普通用户 { AndStr += string.Format("and userid='{0}' ", TdUser.UserID); } else { if (UserType.OrgType == TdUser.UType && !string.IsNullOrEmpty(TdUser.OrgId)) { PartSearchCondition = " and orgid in (select orgid from #tmp) "; ParentOrgID = TdUser.OrgId; } if (!string.IsNullOrEmpty(Lqc.TradeAccount)) { AndStr += string.Format(" and userid='{0}' ", ComFunction.GetUserId(Lqc.TradeAccount)); } } if ("ALL" != Lqc.ProductName.ToUpper()) { AndStr += string.Format(" and ProductName='{0}'", Lqc.ProductName); } if ("ALL" != Lqc.OrderType.ToUpper()) { AndStr += string.Format(" and ordertype='{0}'", Lqc.OrderType); } string SubSelectList = "orgname,Account,ProductName,HoldOrderID,productCode,OrderType,HoldPrice,lossPrice,profitPrice,frozenMoney,validtime,ordertime,istrade,OrderID,quantity,tradetime "; string selectlist = "orgname,Account,ProductName,HoldOrderID,productCode,OrderType,HoldPrice,lossPrice,profitPrice,frozenMoney,validtime,ordertime,istrade,OrderID,quantity,tradetime "; string SearchCondition = string.Format("where tradetime >= '{0}' and tradetime <='{1}' {2} {3} ", Lqc.StartTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), Lqc.EndTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), AndStr, PartSearchCondition); dbreader = DbHelper.RunProcedureGetDataReader("GetRecordFromPageEx", new System.Data.Common.DbParameter[] { DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@selectlist", DbParameterType.String, selectlist, ParameterDirection.Input), DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@SubSelectList", DbParameterType.String, SubSelectList, ParameterDirection.Input), DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@TableSource", DbParameterType.String, "V_L_Trade_HoldOrder", ParameterDirection.Input), //表名或视图表 DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@TableOrder", DbParameterType.String, "a", ParameterDirection.Input), //排序后的表名称 即子查询结果集的别名 DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@SearchCondition", DbParameterType.String, SearchCondition, ParameterDirection.Input), DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@OrderExpression", DbParameterType.String, "order by tradetime desc", ParameterDirection.Input), //排序 表达式 DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@ParentOrgID", DbParameterType.String, ParentOrgID, ParameterDirection.Input), //父级组织ID DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@PageIndex", DbParameterType.Int, pageindex, ParameterDirection.Input), DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@PageSize", DbParameterType.Int, pagesize, ParameterDirection.Input), OutputParam }); while (dbreader.Read()) { LTradeHoldOrder ltradeHoldOrder = new LTradeHoldOrder(); ltradeHoldOrder.OrgName = System.DBNull.Value != dbreader["orgname"] ? dbreader["orgname"].ToString() : string.Empty; ltradeHoldOrder.TradeAccount = System.DBNull.Value != dbreader["Account"] ? dbreader["Account"].ToString() : string.Empty; ltradeHoldOrder.HoldOrderID = System.DBNull.Value != dbreader["HoldOrderID"] ? dbreader["HoldOrderID"].ToString() : string.Empty; ltradeHoldOrder.ProductName = System.DBNull.Value != dbreader["ProductName"] ? dbreader["ProductName"].ToString() : string.Empty; ltradeHoldOrder.ProductCode = System.DBNull.Value != dbreader["productCode"] ? dbreader["productCode"].ToString() : string.Empty; ltradeHoldOrder.OrderType = System.DBNull.Value != dbreader["OrderType"] ? dbreader["OrderType"].ToString() : string.Empty; ltradeHoldOrder.HoldPrice = System.DBNull.Value != dbreader["HoldPrice"] ? Convert.ToDouble(dbreader["HoldPrice"]) : 0; ltradeHoldOrder.LossPrice = System.DBNull.Value != dbreader["lossPrice"] ? Convert.ToDouble(dbreader["lossPrice"]) : 0; ltradeHoldOrder.ProfitPrice = System.DBNull.Value != dbreader["profitPrice"] ? Convert.ToDouble(dbreader["profitPrice"]) : 0; ltradeHoldOrder.FrozenMoney = System.DBNull.Value != dbreader["frozenMoney"] ? Convert.ToDouble(dbreader["frozenMoney"]) : 0; ltradeHoldOrder.ValidTime = System.DBNull.Value != dbreader["validtime"] ? Convert.ToDateTime(dbreader["validtime"]) : DateTime.MinValue; ltradeHoldOrder.OrderTime = System.DBNull.Value != dbreader["ordertime"] ? Convert.ToDateTime(dbreader["ordertime"]) : DateTime.MinValue; ltradeHoldOrder.State = System.DBNull.Value != dbreader["istrade"] ? dbreader["istrade"].ToString() : string.Empty; ltradeHoldOrder.OrderID = System.DBNull.Value != dbreader["OrderID"] ? dbreader["OrderID"].ToString() : string.Empty; ltradeHoldOrder.Quantity = System.DBNull.Value != dbreader["quantity"] ? Convert.ToDouble(dbreader["quantity"]) : 0; ltradeHoldOrder.TradeTime = System.DBNull.Value != dbreader["tradetime"] ? Convert.ToDateTime(dbreader["tradetime"]) : DateTime.MinValue; list.Add(ltradeHoldOrder); } } catch (Exception ex) { ComFunction.WriteErr(ex); if (null != list && list.Count > 0) { list.Clear(); } } finally { if (null != dbreader) { dbreader.Close(); dbreader.Dispose(); page = Convert.ToInt32(OutputParam.Value); } } return(list); }
public override void AdjustParameter(System.Data.Common.DbParameter param) { throw new NotImplementedException(); }
public static void Log(string url, string referrer, string text) { string sql = "INSERT INTO __LogRequests(REQ_URL, REQ_Referrer, REQ_Text) VALUES ( @__url, @__ref, @__text );"; using (System.Data.Common.DbConnection conn = new System.Data.SqlClient.SqlConnection(s_connectionString)) { if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } using (System.Data.Common.DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; System.Data.Common.DbParameter paramUrl = cmd.CreateParameter(); paramUrl.ParameterName = "__url"; if (url != null) { paramUrl.Value = url; } else { paramUrl.Value = System.DBNull.Value; } paramUrl.DbType = System.Data.DbType.String; cmd.Parameters.Add(paramUrl); System.Data.Common.DbParameter paramReferrer = cmd.CreateParameter(); paramReferrer.ParameterName = "__ref"; if (referrer != null) { paramReferrer.Value = referrer; } else { paramReferrer.Value = System.DBNull.Value; } paramReferrer.DbType = System.Data.DbType.String; cmd.Parameters.Add(paramReferrer); System.Data.Common.DbParameter paramText = cmd.CreateParameter(); paramText.ParameterName = "__text"; if (text != null) { paramText.Value = text; } else { paramText.Value = System.DBNull.Value; } paramText.DbType = System.Data.DbType.String; cmd.Parameters.Add(paramText); cmd.ExecuteNonQuery(); } // End Using cmd if (conn.State != System.Data.ConnectionState.Closed) { conn.Close(); } } // End Using conn } // End Sub Log
public DataTable QueryInformRecords(InfomrRecordSearchConditoin conditoin) { DataTable dt = null; using (var op = new DbOperator("System.Data.SqlClient", ConnectionManager.B3BConnectionString)) { ClearParameters(); if (!string.IsNullOrWhiteSpace(conditoin.Carrier)) { AddParameter("@Carrier", conditoin.Carrier); } else { AddParameter("@Carrier", DBNull.Value); } if (!string.IsNullOrWhiteSpace(conditoin.FlightNo)) { AddParameter("@FlightNo", conditoin.FlightNo); } else { AddParameter("@FlightNo", DBNull.Value); } if (!string.IsNullOrWhiteSpace(conditoin.Departure)) { AddParameter("@Departure", conditoin.Departure); } else { AddParameter("@Departure", DBNull.Value); } if (conditoin.TransferType.HasValue) { AddParameter("@TransferType", (byte)conditoin.TransferType.Value); } else { AddParameter("@TransferType", DBNull.Value); } if (!string.IsNullOrWhiteSpace(conditoin.Arrival)) { AddParameter("@Arrival", conditoin.Arrival); } else { AddParameter("@Arrival", DBNull.Value); } if (conditoin.PurchaserId.HasValue) { AddParameter("@PurchaserId", conditoin.PurchaserId.Value); } else { AddParameter("@PurchaserId", DBNull.Value); } if (conditoin.InformType.HasValue) { AddParameter("@InformMethod", (byte)conditoin.InformType.Value); } else { AddParameter("@InformMethod", DBNull.Value); } if (conditoin.InformResult.HasValue) { AddParameter("@InformResult", (byte)conditoin.InformResult.Value); } else { AddParameter("@InformResult", DBNull.Value); } if (conditoin.InformTimeFrom.HasValue) { AddParameter("@InformTimeFrom", conditoin.InformTimeFrom.Value); } else { AddParameter("@InformTimeFrom", DBNull.Value); } if (conditoin.InformTimeTo.HasValue) { AddParameter("@InformTimeTo", conditoin.InformTimeTo.Value); } else { AddParameter("@InformTimeTo", DBNull.Value); } System.Data.Common.DbParameter oTotalTicketCount = op.AddParameter("@oTotalCount"); oTotalTicketCount.DbType = System.Data.DbType.Int32; oTotalTicketCount.Direction = System.Data.ParameterDirection.Output; dt = op.ExecuteTable("dbo.P_QueryFlightInformDetail", System.Data.CommandType.StoredProcedure); } return(dt); }
// ---------------------------------- public static System.Collections.Generic.List <FlagCount> GetFlags(double dimMin) { System.Collections.Generic.List <FlagCount> ls = new System.Collections.Generic.List <FlagCount>(); string sql = @" -- DECLARE @dimMin float -- SET @dimMin = 30.0 ;WITH CTE AS ( SELECT flag ,country ,country_id ,width ,height ,b64 ,@dimMin/width AS rW ,@dimMin/height AS rH , CASE WHEN @dimMin/width > @dimMin/height THEN @dimMin/width ELSE @dimMin/height END AS rMAX , CASE WHEN @dimMin/width > @dimMin/height THEN @dimMin/height ELSE @dimMin/width END AS rMIN --,geoip_locations_temp.continent_name --,geoip_locations_temp.country_iso_code ,geoip_locations_temp.country_name FROM flags LEFT JOIN geoip.geoip_locations_temp ON geoip_locations_temp.country_iso_code = FLAGS.flag ) SELECT flag ,country ,country_id ,width ,height --,CEILING(width * rMIN) AS wRedim --,CEILING(height * rMIN) AS hRedim ,CAST(ROUND(width * rMIN, 0) AS integer) AS wRedim ,CAST(ROUND(height * rMIN, 0) AS integer) AS hRedim ,b64 --,ABS(CHECKSUM(NEWID())) % (10+1 -0) + 0 AS rn ,ABS(CHECKSUM(NEWID())) % (150+1 -2) + 2 AS flagCount ,country_name FROM CTE WHERE flag IN ('NL', 'CH', 'AD', 'FR', 'IT', 'NP') ORDER BY flag "; using (System.Data.Common.DbCommand cmd = GetConnection().CreateCommand()) { cmd.CommandText = sql; // dimMin System.Data.Common.DbParameter width = cmd.CreateParameter(); width.ParameterName = "@dimMin"; width.Value = dimMin; width.DbType = System.Data.DbType.Double; cmd.Parameters.Add(width); ls = GetList <FlagCount>(cmd); } // End Using cmd return(ls); }
/// <summary> /// Assign the value of a parameter before a command executes /// </summary> /// <param name="parameter">The parameter to configure</param> /// <param name="value">Parameter value</param> public abstract void SetValue(IDbDataParameter parameter, T value);
protected override void SetDbParameterValue(System.Data.Common.DbParameter parameter, System.Data.Entity.Core.Metadata.Edm.TypeUsage parameterType, object value) { base.SetDbParameterValue(parameter, parameterType, value); }
/// <summary> /// 配额查询 /// </summary> /// <param name="code"></param> /// <param name="quotaType"></param> /// <param name="quotaWhere"></param> /// <param name="quotaValue"></param> /// <returns></returns> public List <FilterSysQuotaDTO> FilterQuota(int uid, string code, string quotaType, int quotaWhere, int quotaValue, int pageIndex, int pageSize, ref int totalCount, ref int pageCount) { string procName = "[sp_FilterQuota]"; string sqlWhere = ""; if (!string.IsNullOrEmpty(code)) { sqlWhere += string.Format(" and su.Code like '%{0}%'", code); } if (!string.IsNullOrEmpty(quotaType)) { sqlWhere += string.Format(" and QuotaType='{0}'", quotaType); } string whereStr = ""; switch (quotaWhere) { case 0: whereStr = " = "; break; case 1: whereStr = " > "; break; case 2: whereStr = " >= "; break; case 3: whereStr = " < "; break; case 4: whereStr = " <= "; break; } if (quotaWhere >= 0) { sqlWhere += string.Format(" and MaxNum{0}{1}", whereStr, quotaValue); } var fc = IoC.Resolve <IDbContextFactory>(); System.Data.Common.DbParameter[] dbParameters = new System.Data.Common.DbParameter[] { fc.GetDbParameter("@uid", System.Data.DbType.Int32), fc.GetDbParameter("@pageSize", System.Data.DbType.Int32), fc.GetDbParameter("@pageIndex", System.Data.DbType.Int32), fc.GetDbParameter("@sqlWhere", System.Data.DbType.String), fc.GetDbParameter("@totalCount", System.Data.DbType.Int32), }; dbParameters[0].Value = uid; dbParameters[1].Value = pageSize; dbParameters[2].Value = pageIndex; dbParameters[3].Value = sqlWhere; dbParameters[4].Value = 0; dbParameters[4].Direction = System.Data.ParameterDirection.Output; var source = this.ExProc <FilterSysQuotaDTO>(procName, dbParameters); totalCount = Convert.ToInt32(dbParameters[4].Value); pageCount = totalCount % pageSize == 0 ? totalCount / pageSize : (totalCount / pageSize) + 1; //组织数据 return(source); }
protected override void SetParameter(int index, System.Data.Common.DbParameter value) { _Parameters[index] = value as SqlParameter; }
protected override void SetParameter(int index, DbParameter value) { parameters[index] = (H2Parameter)value; }
public static object ParseNullField(System.Data.Common.DbParameter oParameter) { //************************** //* Atualiza parâmetro nulo //************************** if (!oParameter.SourceColumnNullMapping && oParameter.Value == null) { switch (oParameter.DbType) { case DbType.AnsiString: oParameter.Value = string.Empty; break; case DbType.AnsiStringFixedLength: oParameter.Value = string.Empty; break; case DbType.Binary: oParameter.Value = 0; break; case DbType.Boolean: oParameter.Value = false; break; case DbType.Byte: oParameter.Value = 0; break; case DbType.Currency: oParameter.Value = 0; break; case DbType.Date: oParameter.Value = DateTime.Now; break; case DbType.DateTime: oParameter.Value = DateTime.Now; break; case DbType.DateTime2: oParameter.Value = DateTime.Now; break; case DbType.DateTimeOffset: oParameter.Value = DateTime.Now; break; case DbType.Decimal: oParameter.Value = 0; break; case DbType.Double: oParameter.Value = 0; break; case DbType.Guid: oParameter.Value = string.Empty; break; case DbType.Int16: oParameter.Value = 0; break; case DbType.Int32: oParameter.Value = 0; break; case DbType.Int64: oParameter.Value = 0; break; case DbType.Object: oParameter.Value = new object(); break; case DbType.SByte: oParameter.Value = 0; break; case DbType.Single: oParameter.Value = 0; break; case DbType.String: oParameter.Value = string.Empty; break; case DbType.StringFixedLength: oParameter.Value = string.Empty; break; case DbType.Time: oParameter.Value = DateTime.Now; break; case DbType.UInt16: oParameter.Value = 0; break; case DbType.UInt32: oParameter.Value = 0; break; case DbType.UInt64: oParameter.Value = 0; break; case DbType.VarNumeric: oParameter.Value = 0; break; case DbType.Xml: oParameter.Value = string.Empty; break; } } //******************************************* //* Retorna coleção de parâmetros atualizada //******************************************* return(oParameter.Value); }
private static string BuildSqlWithSqlParameter(string sql, System.Data.Common.DbParameterCollection paras) { System.Data.Common.DbParameter[] arrParas = new System.Data.Common.DbParameter[paras.Count]; for (int i = 0; i < paras.Count; i++) { arrParas[i] = paras[i]; } Array.Sort(arrParas, new DbParameterForSort()); object[] objParas = new object[arrParas.Length]; for (int i = 0; i < arrParas.Length; i++) { sql = sql.Replace(arrParas[i].ParameterName, "{" + i.ToString() + "}"); switch (arrParas[i].DbType) { case System.Data.DbType.AnsiString: case System.Data.DbType.AnsiStringFixedLength: case System.Data.DbType.String: case System.Data.DbType.StringFixedLength: case System.Data.DbType.Xml: objParas[i] = arrParas[i].Value as string; break; case System.Data.DbType.Boolean: if (arrParas[i].Value == null) { objParas[i] = null; } else { objParas[i] = arrParas[i].Value.ToString(); } break; case System.Data.DbType.Date: if (arrParas[i].Value == null) { objParas[i] = null; } else { objParas[i] = ((DateTime)arrParas[i].Value).ToString("yyyy-MM-dd"); } break; case System.Data.DbType.DateTime: case System.Data.DbType.DateTime2: if (arrParas[i].Value == null) { objParas[i] = null; } else { objParas[i] = ((DateTime)arrParas[i].Value).ToString("yyyy-MM-dd HH:mm:ss"); } break; case System.Data.DbType.Time: if (arrParas[i].Value == null) { objParas[i] = null; } else { objParas[i] = ((DateTime)arrParas[i].Value).ToString("HH:mm:ss"); } break; case System.Data.DbType.Byte: case System.Data.DbType.UInt16: case System.Data.DbType.UInt32: case System.Data.DbType.UInt64: if (arrParas[i].Value == null) { objParas[i] = null; } else { objParas[i] = ulong.Parse(arrParas[i].Value.ToString()); } break; case System.Data.DbType.Decimal: case System.Data.DbType.Double: case System.Data.DbType.Single: if (arrParas[i].Value == null) { objParas[i] = null; } else { objParas[i] = double.Parse(arrParas[i].Value.ToString()); } break; case System.Data.DbType.Int16: case System.Data.DbType.Int32: case System.Data.DbType.Int64: case System.Data.DbType.SByte: if (arrParas[i].Value == null) { objParas[i] = null; } else { objParas[i] = long.Parse(arrParas[i].Value.ToString()); } break; default: throw new System.Data.DataException(string.Format("Invalid parameter DataType: {0}", arrParas[i].DbType)); } } return(BuildSql(sql, objParas)); }
public override void SetValue(IDbDataParameter parameter, T value) { base.SetValue(parameter, value); parameter.DbType = DbType.Xml; }
public static void SaveFlagImageInDb(string iso, int w, int h, string base64) { string sql = @" UPDATE [flags] SET [width] = @width ,[height] = @height ,[b64] = @b64 WHERE [flag] = @iso_country2 "; using (System.Data.Common.DbConnection con = GetConnection()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = sql; //cmd.Parameters.Add("@width", System.Data.SqlDbType.Int).Value = w; //cmd.Parameters.Add("@height", System.Data.SqlDbType.Int).Value = h; //cmd.Parameters.Add("@b64", System.Data.SqlDbType.VarChar).Value = base64; //cmd.Parameters.Add("@iso_country2", System.Data.SqlDbType.NVarChar).Value = iso; System.Data.Common.DbParameter width = cmd.CreateParameter(); width.ParameterName = "width"; width.Value = w; width.DbType = System.Data.DbType.Int32; cmd.Parameters.Add(width); System.Data.Common.DbParameter height = cmd.CreateParameter(); height.ParameterName = "height"; height.Value = h; height.DbType = System.Data.DbType.Int32; cmd.Parameters.Add(height); System.Data.Common.DbParameter b64 = cmd.CreateParameter(); b64.ParameterName = "b64"; b64.Value = base64; b64.DbType = System.Data.DbType.AnsiString; cmd.Parameters.Add(b64); System.Data.Common.DbParameter country = cmd.CreateParameter(); country.ParameterName = "iso_country2"; country.Value = iso; country.DbType = System.Data.DbType.String; cmd.Parameters.Add(country); cmd.ExecuteNonQuery(); } if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } }
public IEnumerable <PolicyHarmonyInfo> QueryPolicyHarmonyInfos(Pagination pagination, PolicyHarmonyQueryParameter condition) { IList <PolicyHarmonyInfo> result = new List <PolicyHarmonyInfo>(); using (var dbOperator = new DbOperator(Provider, ConnectionString)) { if (!string.IsNullOrEmpty(condition.Departure)) { dbOperator.AddParameter("@iDeparture", condition.Departure); } if (!string.IsNullOrEmpty(condition.Arrival)) { dbOperator.AddParameter("@iArrival", condition.Arrival); } if (!string.IsNullOrEmpty(condition.Airline)) { dbOperator.AddParameter("@iAirline", condition.Airline); } if (condition.PolicyType.HasValue) { dbOperator.AddParameter("@iPolicyType", condition.PolicyType.Value); } if (condition.EffectTimeStart.HasValue) { dbOperator.AddParameter("@iEffectiveLowerDate", condition.EffectTimeStart.Value); } if (condition.EffectTimeEnd.HasValue) { dbOperator.AddParameter("@iEffectiveUpperDate", condition.EffectTimeEnd.Value); } dbOperator.AddParameter("@iPageIndex", pagination.PageIndex); dbOperator.AddParameter("@iPageSize", pagination.PageSize); System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount"); totalCount.DbType = System.Data.DbType.Int32; totalCount.Direction = System.Data.ParameterDirection.Output; using (var reader = dbOperator.ExecuteReader("[dbo].[P_QueryPolicyHarmonys]", System.Data.CommandType.StoredProcedure)) { while (reader.Read()) { var info = new PolicyHarmonyInfo(); info.Id = reader.GetGuid(0); info.Airlines = reader.GetString(1); info.Departure = reader.GetString(2); info.Arrival = reader.GetString(3); info.PolicyType = (Common.Enums.PolicyType)reader.GetInt32(4); info.EffectiveLowerDate = reader.GetDateTime(5); info.EffectiveUpperDate = reader.GetDateTime(6); info.DeductionType = (Common.Enums.DeductionType)reader.GetInt32(7); info.HarmonyValue = reader.GetDecimal(8); info.Remark = reader.GetString(9); info.Account = reader.GetString(10); info.CreateTime = reader.GetDateTime(11); info.LastModifyTime = reader.GetDateTime(12); info.LastModifyName = reader.GetString(13); result.Add(info); } } if (pagination.GetRowCount) { pagination.RowCount = (int)totalCount.Value; } } return(result); }
public override void SetValue(IDbDataParameter parameter, Blarg value) { parameter.Value = ((object)value.Value) ?? DBNull.Value; }
public List <ValueGroupRow> GetValueGroupRowsSorted(string aGrouping, string aLevel, bool emptyRowSetIsOK, string aSortOrderLanguage) { string sortOrderLanguage = aSortOrderLanguage; if (!this.mLanguageCodes.Contains(aSortOrderLanguage)) { log.Error("requsted sortOrder language not in extaction, using first in list of languages"); sortOrderLanguage = this.mLanguageCodes[0]; } bool sortOnPrimaryLanguage = !this.mDbConfig.isSecondaryLanguage(sortOrderLanguage); List <ValueGroupRow> myOut = new List <ValueGroupRow>(); SqlDbConfig dbconf = DB; string sqlString = GetValueGroup_SQLString_NoWhere(); // // WHERE VPL.Grouping = '<aGrouping>' // AND VPL.GroupCode = '<aGroupCode>' // System.Data.Common.DbParameter[] parameters = null; sqlString += " WHERE " + DB.ValueGroup.GroupingCol.Is(); if (aLevel != null) { sqlString += " AND " + DB.ValueGroup.GroupLevelCol.Is(); parameters = new System.Data.Common.DbParameter[2]; parameters[0] = DB.ValueGroup.GroupingCol.GetStringParameter(aGrouping); parameters[1] = DB.ValueGroup.GroupLevelCol.GetStringParameter(aLevel); } else { parameters = new System.Data.Common.DbParameter[1]; parameters[0] = DB.ValueGroup.GroupingCol.GetStringParameter(aGrouping); } if (sortOnPrimaryLanguage) { sqlString += " ORDER BY " + DB.ValueGroup.SortCodeCol.Id() + ", "; } else { sqlString += " ORDER BY " + DB.ValueGroupLang2.SortCodeCol.Id(sortOrderLanguage) + ", "; } sqlString += DB.ValueGroup.GroupCodeCol.Id() + ", " + DB.ValueGroup.ValueCodeCol.Id(); DataSet ds = mSqlCommand.ExecuteSelect(sqlString, parameters); DataRowCollection myRows = ds.Tables[0].Rows; if (myRows.Count < 1 && !emptyRowSetIsOK) { throw new PCAxis.Sql.Exceptions.DbException(35, " Grouping = " + aGrouping); } foreach (DataRow sqlRow in myRows) { ValueGroupRow outRow = new ValueGroupRow(sqlRow, DB, mLanguageCodes); myOut.Add(outRow); } return(myOut); }
public static void SetValue(IDbDataParameter parameter, object value) { handler.SetValue(parameter, value); }
internal abstract void InitializeParameter(ProviderType type, System.Data.Common.DbParameter parameter, object value);
/// <summary> /// 市价单历史查询 /// </summary> /// <param name="Lqc">查询条件</param> /// <param name="Ltype">"1"平仓历史 "2"入库历史</param> /// <param name="pageindex">第几页,从1开始</param> /// <param name="pagesize">每页多少条</param> /// <param name="page">输出参数(总页数)</param> /// <returns>市价单历史记录</returns> public List <LTradeOrder> GetLTradeOrder(LQueryCon Lqc, string Ltype, int pageindex, int pagesize, ref int page) { List <LTradeOrder> list = new List <LTradeOrder>(); System.Data.Common.DbDataReader dbreader = null; TradeUser TdUser = new TradeUser(); System.Data.Common.DbParameter OutputParam = DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@PageCount", DbParameterType.String, 0, ParameterDirection.Output); try { string AndStr = string.Empty; string PartSearchCondition = string.Empty; string ParentOrgID = string.Empty; if (!ComFunction.ExistUserLoginID(Lqc.LoginID, ref TdUser)) { return(list); } if (UserType.NormalType == TdUser.UType) //普通用户 { AndStr += string.Format("and userid='{0}' ", TdUser.UserID); } else { if (UserType.OrgType == TdUser.UType && !string.IsNullOrEmpty(TdUser.OrgId)) { PartSearchCondition = " and orgid in (select orgid from #tmp) "; ParentOrgID = TdUser.OrgId; } if (!string.IsNullOrEmpty(Lqc.TradeAccount)) { AndStr += string.Format(" and [Account] like '{0}%' ", Lqc.TradeAccount); } } if (!string.IsNullOrEmpty(Lqc.OrgName)) { AndStr += string.Format(" and [orgname] like '{0}%' ", Lqc.OrgName); } //入库单查询 if ("2" == Ltype) { AndStr += " and overtype='2'"; } else { AndStr += " and overtype<>'2'";//平仓单查询 } if ("ALL" != Lqc.ProductName.ToUpper()) { AndStr += string.Format(" and ProductName='{0}'", Lqc.ProductName); } if ("ALL" != Lqc.OrderType.ToUpper()) { AndStr += string.Format(" and ordertype='{0}'", Lqc.OrderType); } //内部子查询字段列表 string SubSelectList = "orgname,Account,ProductName,historyOrderId,productcode,lossprice,profitPrice,Orderprice,overType,overprice,profitValue,tradefee,storagefee,Overtime,Orderid,ordertype,quantity,ordertime,adjustbase,valuedot,unit,lowerprice "; //选择字段列表 string selectlist = "orgname,Account,ProductName,historyOrderId,productcode,lossprice,profitPrice,Orderprice,overType,overprice,profitValue,tradefee,storagefee,Overtime,Orderid,ordertype,quantity,ordertime,adjustbase,valuedot,unit,lowerprice "; //查询条件 string SearchCondition = string.Format("where overtime >= '{0}' and overtime <='{1}' {2} {3} ", Lqc.StartTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), Lqc.EndTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), AndStr, PartSearchCondition); dbreader = DbHelper.RunProcedureGetDataReader("GetRecordFromPageEx", new System.Data.Common.DbParameter[] { DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@selectlist", DbParameterType.String, selectlist, ParameterDirection.Input), DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@SubSelectList", DbParameterType.String, SubSelectList, ParameterDirection.Input), DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@TableSource", DbParameterType.String, "V_L_Trade_Order", ParameterDirection.Input), //表名或视图表 DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@TableOrder", DbParameterType.String, "a", ParameterDirection.Input), //排序后的表名称 即子查询结果集的别名 DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@SearchCondition", DbParameterType.String, SearchCondition, ParameterDirection.Input), DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@OrderExpression", DbParameterType.String, "order by overtime desc", ParameterDirection.Input), //排序 表达式 DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@ParentOrgID", DbParameterType.String, ParentOrgID, ParameterDirection.Input), //父级组织ID DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@PageIndex", DbParameterType.Int, pageindex, ParameterDirection.Input), DbHelper.CreateDbParameter(JinTong.Jyrj.Data.DataBase.Type, "@PageSize", DbParameterType.Int, pagesize, ParameterDirection.Input), OutputParam }); while (dbreader.Read()) { LTradeOrder ltradeOrder = new LTradeOrder(); ltradeOrder.OrgName = System.DBNull.Value != dbreader["orgname"] ? dbreader["orgname"].ToString() : string.Empty; ltradeOrder.TradeAccount = System.DBNull.Value != dbreader["Account"] ? dbreader["Account"].ToString() : string.Empty; ltradeOrder.HistoryOrderId = System.DBNull.Value != dbreader["historyOrderId"] ? dbreader["historyOrderId"].ToString() : string.Empty; ltradeOrder.ProductName = System.DBNull.Value != dbreader["ProductName"] ? dbreader["ProductName"].ToString() : string.Empty; ltradeOrder.ProductCode = System.DBNull.Value != dbreader["productcode"] ? dbreader["productcode"].ToString() : string.Empty; ltradeOrder.OrderPrice = System.DBNull.Value != dbreader["Orderprice"] ? Convert.ToDouble(dbreader["Orderprice"]) : 0; ltradeOrder.OverType = System.DBNull.Value != dbreader["overType"] ? dbreader["overType"].ToString() : string.Empty; ltradeOrder.OverPrice = System.DBNull.Value != dbreader["overprice"] ? Convert.ToDouble(dbreader["overprice"]) : 0; ltradeOrder.ProfitValue = System.DBNull.Value != dbreader["profitValue"] ? Convert.ToDouble(dbreader["profitValue"]) : 0; ltradeOrder.TradeFee = System.DBNull.Value != dbreader["tradefee"] ? Convert.ToDouble(dbreader["tradefee"]) : 0; ltradeOrder.StorageFee = System.DBNull.Value != dbreader["storagefee"] ? Convert.ToDouble(dbreader["storagefee"]) : 0; ltradeOrder.LossPrice = System.DBNull.Value != dbreader["lossprice"] ? Convert.ToDouble(dbreader["lossprice"]) : 0; ltradeOrder.ProfitPrice = System.DBNull.Value != dbreader["profitPrice"] ? Convert.ToDouble(dbreader["profitPrice"]) : 0; ltradeOrder.OverTime = System.DBNull.Value != dbreader["Overtime"] ? Convert.ToDateTime(dbreader["Overtime"]) : DateTime.MinValue; ltradeOrder.OrderId = System.DBNull.Value != dbreader["Orderid"] ? dbreader["Orderid"].ToString() : string.Empty; ltradeOrder.OrderType = System.DBNull.Value != dbreader["ordertype"] ? dbreader["ordertype"].ToString() : string.Empty; ltradeOrder.Quantity = System.DBNull.Value != dbreader["quantity"] ? Convert.ToDouble(dbreader["quantity"]) : 0; ltradeOrder.OrderTime = System.DBNull.Value != dbreader["ordertime"] ? Convert.ToDateTime(dbreader["ordertime"]) : DateTime.MinValue; if ("0" == ltradeOrder.OrderType) { ltradeOrder.ProductMoney = System.Math.Round(ltradeOrder.OverPrice / Convert.ToDouble(dbreader["adjustbase"]) * Convert.ToDouble(dbreader["valuedot"]) * Convert.ToDouble(dbreader["quantity"]), 2, MidpointRounding.AwayFromZero); } else { //ltradeOrder.ProductMoney = System.Math.Round(Convert.ToDouble(sqldr["unit"]) * Convert.ToDouble(sqldr["lowerprice"]) * Convert.ToDouble(sqldr["quantity"]), 2, MidpointRounding.AwayFromZero); //卖单入库的货款(即所谓的折旧费) 已经计算在盈亏里面了 所以没有所谓的货款 ltradeOrder.ProductMoney = 0; } list.Add(ltradeOrder); } } catch (Exception ex) { ComFunction.WriteErr(ex); if (null != list && list.Count > 0) { list.Clear(); } } finally { if (null != dbreader) { dbreader.Close(); dbreader.Dispose(); page = Convert.ToInt32(OutputParam.Value); } } return(list); }
public static void Test() { // SchemaPorter.YamlSpace.RootNode retVal = null; // SchemaPorter.foofoo.RootNode retVal = null; // SchemaPorter.VerySimpleYamlSchema.RootObject retVal = null; SchemaPorter.SimpleYamlSchema.RootObject retVal = null; Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer(); using (System.IO.FileStream fs = System.IO.File.OpenRead(@"d:\username\Documents\Visual Studio 2017\Projects\BlueMine\BlueMine\Code\EN.json")) { // using (System.IO.TextReader sr = new System.IO.StringReader(@"")) using (System.IO.TextReader sr = new System.IO.StreamReader(fs, System.Text.Encoding.UTF8)) { using (Newtonsoft.Json.JsonReader jr = new Newtonsoft.Json.JsonTextReader(sr)) { // retVal = ser.Deserialize<SchemaPorter.YamlSpace.RootNode>(jr); // retVal = ser.Deserialize<SchemaPorter.foofoo.RootNode>(jr); // retVal = ser.Deserialize<SchemaPorter.VerySimpleYamlSchema.RootObject>(jr); retVal = ser.Deserialize <SchemaPorter.SimpleYamlSchema.RootObject>(jr); } // End Using jr } // End Using sr } // End Using fs System.Collections.Generic.Dictionary <string, string> dict = new System.Collections.Generic.Dictionary <string, string>(); System.Type tt = typeof(SchemaPorter.SimpleYamlSchema.DE); System.Reflection.PropertyInfo[] pis = tt.GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance); using (System.Data.Common.DbConnection con = SchemaPorter.SqlFactory.GetConnection()) { // System.Console.WriteLine(con.ConnectionString); if (con.State != System.Data.ConnectionState.Open) { con.Open(); } foreach (System.Reflection.PropertyInfo pi in pis) { if (!object.ReferenceEquals(pi.PropertyType, typeof(string))) { continue; } string pname = pi.Name; string value = (string)pi.GetValue(retVal.en, null); System.Console.WriteLine($"Name: {pname}, Value: {value}"); using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = "INSERT INTO T_Translations(TR_Key, TR_Value) VALUES(@p1, @p2); "; System.Data.Common.DbParameter p1 = cmd.CreateParameter(); p1.ParameterName = "@p1"; p1.Value = (pname == null ? (object)System.DBNull.Value : (object)pname); p1.DbType = System.Data.DbType.String; cmd.Parameters.Add(p1); System.Data.Common.DbParameter p2 = cmd.CreateParameter(); p2.ParameterName = "@p2"; p2.Value = (value == null ? (object)System.DBNull.Value : (object)value); p2.DbType = System.Data.DbType.String; cmd.Parameters.Add(p2); cmd.ExecuteNonQuery(); } // End Using cmd } // Next pi if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End Using con System.Console.WriteLine(retVal); } // End Sub Test
protected abstract void SetParameter(int index, System.Data.Common.DbParameter value);
public ActionResult Manage(LocalPasswordModel model) { bool hasLocalAccount = OAuthWebSecurity.HasLocalAccount(WebSecurity.GetUserId(User.Identity.Name)); ViewBag.HasLocalPassword = hasLocalAccount; ViewBag.ReturnUrl = Url.Action("Manage"); if (hasLocalAccount) { if (ModelState.IsValid) { // ChangePassword will throw an exception rather than return false in certain failure scenarios. bool changePasswordSucceeded; try { using (var context = new UsersContext()) { var args = new System.Data.Common.DbParameter[] { new System.Data.SqlClient.SqlParameter { ParameterName = "pwd", Value = model.NewPassword }, new System.Data.SqlClient.SqlParameter { ParameterName = "uid", Value = User.Identity.Name } }; context.Database.ExecuteSqlCommand("Update MEMB_INFO SET memb__pwd = @pwd where memb___id=@uid", args); } changePasswordSucceeded = WebSecurity.ChangePassword(User.Identity.Name, model.OldPassword, model.NewPassword); } catch (Exception) { changePasswordSucceeded = false; } if (changePasswordSucceeded) { return(RedirectToAction("Manage", new { Message = ManageMessageId.ChangePasswordSuccess })); } else { ModelState.AddModelError("", "The current password is incorrect or the new password is invalid."); } } } else { // User does not have a local password so remove any validation errors caused by a missing // OldPassword field ModelState state = ModelState["OldPassword"]; if (state != null) { state.Errors.Clear(); } if (ModelState.IsValid) { try { WebSecurity.CreateAccount(User.Identity.Name, model.NewPassword); return(RedirectToAction("Manage", new { Message = ManageMessageId.SetPasswordSuccess })); } catch (Exception) { ModelState.AddModelError("", String.Format("Unable to create local account. An account with the name \"{0}\" may already exist.", User.Identity.Name)); } } } // If we got this far, something failed, redisplay form return(View(model)); }
protected override void ApplyParameterInfo(System.Data.Common.DbParameter parameter, System.Data.DataRow datarow, System.Data.StatementType statementType, bool whereClause) { }
void IHttpHandler.ProcessRequest(HttpContext context) { object parent = context.Request.Query["id"].ToString(); string contentType = context.Request.Headers["content-type"]; if (string.IsNullOrWhiteSpace((string)parent) && "application/x-www-form-urlencoded".Equals(contentType, System.StringComparison.InvariantCultureIgnoreCase)) { if (context.Request.Form != null) { parent = context.Request.Form["id"].ToString(); } } if ("null".Equals((string)parent, System.StringComparison.OrdinalIgnoreCase) || string.IsNullOrWhiteSpace((string)parent)) { parent = System.DBNull.Value; } string sql = @" -- DECLARE @__in_parent varchar(36) -- SET @__in_parent = 'F0000000-E000-0000-0000-000000000002' -- -- SET @__in_parent = 'BEB6CD1D-5ACB-4FB1-93F4-A3F07A053DB7' -- SET @__in_parent = NULL SELECT T_FMS_Navigation.NA_UID AS id ,T_FMS_Navigation.NA_NA_UID AS parent ,T_FMS_Translation.FT_DE AS text --,T_FMS_Navigation.NA_Sort ,CASE WHEN EXISTS ( SELECT * FROM T_FMS_Navigation AS Children WHERE Children.NA_Status = 1 AND Children.NA_NA_UID = T_FMS_Navigation.NA_UID ) THEN 1 ELSE 0 END AS hasChildren FROM T_FMS_Navigation LEFT JOIN T_FMS_Translation ON T_FMS_Translation.FT_UID = T_FMS_Navigation.NA_FT_UID WHERE T_FMS_Navigation.NA_Status = 1 AND ( NA_NA_UID = @__in_parent OR ( @__in_parent IS NULL AND NA_NA_UID IS NULL ) ) ORDER BY -- T_FMS_Navigation.NA_Sort, text "; CoreDb.ReadDAL readDAL = (CoreDb.ReadDAL)context.RequestServices.GetService( typeof(CoreDb.ReadDAL) ); using (System.Data.Common.DbCommand cmd = readDAL.CreateCommand(sql)) { System.Data.Common.DbParameter param = cmd.CreateParameter(); param.ParameterName = "__in_parent"; param.DbType = System.Data.DbType.AnsiString; param.Size = 36; param.Value = parent; cmd.Parameters.Add(param); using (System.Data.DataTable dt = readDAL.GetDataTable(cmd)) { // https://stackoverflow.com/questions/17154967/is-content-encoding-being-set-to-utf-8-invalid // context.Response.Headers["content-encoding"] = "utf-8"; // context.Response.ContentType = "text/plain; charset=utf-8"; context.Response.StatusCode = StatusCodes.Status200OK; context.Response.ContentType = "application/json; charset=utf-8"; DataTableHelper.Serialize(context, dt); } // End Using dt } // End Using cmd } // End Sub ProcessRequest
protected override void SetParameter(string parameterName, System.Data.Common.DbParameter value) { }
public static System.Collections.Generic.List <FlagCount> GetFlags_old(double dimMin) { System.Collections.Generic.List <FlagCount> ls = new System.Collections.Generic.List <FlagCount>(); //CoreDb.LinqHelper.GetGetter<FlagCount>(""); //CoreDb.LinqHelper.GetSetter<FlagCount>(""); //CoreDb.LinqHelper.GetGetters<FlagCount>(); //CoreDb.LinqHelper.GetSetters<FlagCount>(); string sql = @" -- DECLARE @dimMin float -- SET @dimMin = 30.0 ;WITH CTE AS ( SELECT flag ,country ,country_id ,width ,height ,b64 ,@dimMin/width AS rW ,@dimMin/height AS rH , CASE WHEN @dimMin/width > @dimMin/height THEN @dimMin/width ELSE @dimMin/height END AS rMAX , CASE WHEN @dimMin/width > @dimMin/height THEN @dimMin/height ELSE @dimMin/width END AS rMIN --,geoip_locations_temp.continent_name --,geoip_locations_temp.country_iso_code ,geoip_locations_temp.country_name FROM flags LEFT JOIN geoip.geoip_locations_temp ON geoip_locations_temp.country_iso_code = FLAGS.flag ) SELECT flag ,country ,country_id ,width ,height --,CEILING(width * rMIN) AS wRedim --,CEILING(height * rMIN) AS hRedim ,CAST(ROUND(width * rMIN, 0) AS integer) AS wRedim ,CAST(ROUND(height * rMIN, 0) AS integer) AS hRedim ,b64 --,ABS(CHECKSUM(NEWID())) % (10+1 -0) + 0 AS rn ,ABS(CHECKSUM(NEWID())) % (150+1 -2) + 2 AS flagCount ,country_name FROM CTE WHERE flag IN ('NL', 'CH', 'AD', 'FR', 'IT', 'NP') ORDER BY flag "; using (System.Data.Common.DbConnection con = GetConnection()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = sql; // dimMin System.Data.Common.DbParameter width = cmd.CreateParameter(); width.ParameterName = "@dimMin"; width.Value = dimMin; width.DbType = System.Data.DbType.Double; cmd.Parameters.Add(width); using (System.Data.Common.DbDataReader idr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection | System.Data.CommandBehavior.SequentialAccess)) { int fieldCount = idr.FieldCount; string[] fields = new string[fieldCount]; // CoreDb.Getter_t<FlagCount>[] getters = new CoreDb.Getter_t<FlagCount>[fieldCount]; CoreDb.Setter_t <FlagCount>[] setters = new CoreDb.Setter_t <FlagCount> [fieldCount]; for (int i = 0; i < fieldCount; ++i) { fields[i] = idr.GetName(i); // getters[i] = CoreDb.LinqHelper.GetGetter<FlagCount>(fields[i]); setters[i] = CoreDb.LinqHelper.GetSetter <FlagCount>(fields[i]); } // Next i while (idr.Read()) { FlagCount tThisValue = System.Activator.CreateInstance <FlagCount>(); for (int i = 0; i < fieldCount; ++i) { if (setters[i] != null) { object value = idr.GetValue(i); setters[i](tThisValue, value); } // End if (setters[i] != null) } // Next i ls.Add(tThisValue); } // Whend } // End Using idr } // End Using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End Using con return(ls); }
private void HandleRefCursor(IDbDataParameter parameter, RefCursor cursor) { parameter.Direction = cursor.Direction; parameter.SetProviderSpecificDbType("RefCursor"); }
public IEnumerable <DataTransferObject.Foundation.BasicPriceView> QueryBasicPrice(string airline, string departure, string arrival, Core.Pagination pagination) { IList <DataTransferObject.Foundation.BasicPriceView> result = new List <DataTransferObject.Foundation.BasicPriceView>(); string fields = "[Id],[Airline],[Departure],[Arrival],[FlightDate],[ETDZDate],[Price],[Mileage],ModifyTime"; string catelog = "[dbo].[T_BasicPrice]"; string orderbyFiled = "[Airline] DESC,[FlightDate] DESC,[Departure],[Arrival],ModifyTime desc"; string where = ""; if (!string.IsNullOrWhiteSpace(airline)) { where += "([Airline] IS NULL OR [Airline] = '" + airline + "') AND "; } if (!string.IsNullOrWhiteSpace(departure) || !string.IsNullOrWhiteSpace(arrival)) { if (!string.IsNullOrWhiteSpace(departure) && string.IsNullOrWhiteSpace(arrival)) { where += "([Departure] = '" + departure + "' OR [Arrival] = '" + departure + "') AND "; } if (!string.IsNullOrWhiteSpace(arrival) && string.IsNullOrWhiteSpace(departure)) { where += "([Departure] = '" + arrival + "' OR [Arrival] = '" + arrival + "') AND "; } if (!string.IsNullOrWhiteSpace(arrival) && !string.IsNullOrWhiteSpace(departure)) { where += "(([Departure] = '" + arrival + "' AND [Arrival] = '" + departure + "') OR ([Departure] = '" + departure + "' AND [Arrival] = '" + arrival + "')) AND "; } } if (where.Length > 0) { where = where.Remove(where.Length - 5, 5); } using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("@iField", fields); dbOperator.AddParameter("@iCatelog", catelog); dbOperator.AddParameter("@iCondition", where); dbOperator.AddParameter("@iOrderBy", orderbyFiled); dbOperator.AddParameter("@iPagesize", pagination.PageSize); dbOperator.AddParameter("@iPageIndex", pagination.PageIndex); dbOperator.AddParameter("@iGetCount", pagination.GetRowCount); System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount"); totalCount.DbType = System.Data.DbType.Int32; totalCount.Direction = System.Data.ParameterDirection.Output; using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader("dbo.P_Pagination", System.Data.CommandType.StoredProcedure)) { while (reader.Read()) { DataTransferObject.Foundation.BasicPriceView basicPriceView = new DataTransferObject.Foundation.BasicPriceView(); basicPriceView.Id = reader.GetGuid(0); basicPriceView.Airline = reader.IsDBNull(1) ? string.Empty : reader.GetString(1); basicPriceView.Departure = reader.GetString(2); basicPriceView.Arrival = reader.GetString(3); basicPriceView.FlightDate = reader.GetDateTime(4); basicPriceView.ETDZDate = reader.GetDateTime(5); basicPriceView.Price = reader.GetDecimal(6); basicPriceView.Mileage = reader.GetDecimal(7); basicPriceView.ModifyTime = reader.IsDBNull(8) ? new DateTime(2013, 10, 26, 0, 0, 0) : reader.GetDateTime(8); result.Add(basicPriceView); } } if (pagination.GetRowCount) { pagination.RowCount = (int)totalCount.Value; } } return(result); }