public EmergentOrder QueryEmergentOrder(decimal id, OrderIdType orderIdType) { string sql = "SELECT Id,[Type],Content,[Time],Account,OrderIdType FROM T_EmergentOrder WHERE Id=@Id AND OrderIdType =@OrderIdType"; EmergentOrder result = null; using (DbOperator dboperator = new DbOperator(Provider, ConnectionString)) { dboperator.AddParameter("Id", id); dboperator.AddParameter("OrderIdType", orderIdType); using (System.Data.Common.DbDataReader reader = dboperator.ExecuteReader(sql)) { if (reader.Read()) { result = new EmergentOrder(); result.Id = reader.GetDecimal(0); result.Type = (OrderStatus)reader.GetInt32(1); result.Content = reader.GetString(2); result.Time = reader.GetDateTime(3); result.Account = reader.GetString(4); result.OrderIdTypeValue = (OrderIdType)reader.GetByte(5); } } } return(result); }
public DataTransferObject.Organization.WithholdingView GetWithholding(WithholdingAccountType accountType, Guid company) { DataTransferObject.Organization.WithholdingView withholding = null; //[AccountNo] = @AccountNo AND string sql = "SELECT [AccountNo],[AccountType],[Time],[Status],[Amount],[Company] FROM [T_Withholding] WHERE [AccountType]=@AccountType AND [Company] = @Company "; using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString)) { //dbOperator.AddParameter("AccountNo", withholdingView.AccountNo); dbOperator.AddParameter("AccountType", accountType); dbOperator.AddParameter("Company", company); using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader(sql)) { if (reader.Read()) { withholding = new DataTransferObject.Organization.WithholdingView(); withholding.AccountNo = reader.GetString(0); withholding.AccountType = (WithholdingAccountType)reader.GetByte(1); withholding.Time = reader.GetDateTime(2); withholding.Status = (WithholdingProtocolStatus)reader.GetByte(3); withholding.Amount = reader.GetDecimal(4); withholding.Company = reader.GetGuid(5); } } } return(withholding); }
public void MapProperties(System.Data.Common.DbDataReader dr) { ID = dr.GetLong("ID"); Invoice_ID = dr.GetString("Invoice_ID"); Payment_Type = (DepositType)dr.GetByte("Payment_Type"); Detail = dr.GetString("Detail"); Payment = dr.GetDouble("Payment"); Total_Payment = dr.GetDouble("Total_Payment"); Balance = dr.GetDouble("Balance"); Created_By = dr.GetLong("Created_By"); Updated_By = dr.GetLong("Updated_By"); Created_Date = dr.GetDateTime("Created_Date"); Updated_Date = dr.GetDateTime("Updated_Date"); Organization_ID = dr.GetLong("Organization_ID"); IsActive = dr.GetBoolean("IsActive"); IsDeleted = dr.GetBoolean("IsDeleted"); }
private void DoSearch() { try { _searching = true; _isStop = false; SetRecordText(null, txtRecord); string sql = "select Count(*) from c2lp_optRecord " + _where; int sum = Convert.ToInt32(BaseServer._SqlHelp.ExecuteScalar(sql, CommandType.Text)); if (sum == 0) { MessageBox.Show("没有查到符合条件的相关记录!"); return; } SetRecordText("共查到" + sum + "条记录.", txtRecord); sql = sql.Replace("Count(*)", "optTime,optNumber,optType,optCustomerId") + " order by optTime desc ,optTypeId,optCustomerId"; using (System.Data.Common.DbDataReader r = BaseServer._SqlHelp.ExecuteReader(sql, CommandType.Text)) { while (r.Read()) { if (_isStop) { SetRecordText("已手动停止显示剩余记录.", txtRecord); break; } DateTime optTime = r.GetDateTime(0); string optNumber = r.GetString(1); string optType = r.GetString(2); int optCustomerId = r.GetInt32(3); string optCustomer = ""; if (_dicConsignor.ContainsKey(optCustomerId)) { optCustomer = _dicConsignor[optCustomerId]; } SetRecordText(string.Format("{0} {1} {2} {3}", optTime.ToString("MM-dd HH:mm:ss"), optNumber, optType, optCustomer), txtRecord); } } } catch (Exception ex) { SetRecordText(ex.Message, txtRecord); } finally { _searching = false; SetRecordText("查询完毕.", txtRecord); SetRecordText("CNM", btnSearch); } }
public static DateTime?GetReaderValueDate(System.Data.Common.DbDataReader reader, string name) { var ordinal = reader.GetOrdinal(name); if (reader.IsDBNull(ordinal)) { return(null); } try { return(reader.GetDateTime(ordinal)); } catch (Exception ex) { throw ex; } return(null); }
public EmergentOrder QueryEmergentOrder(decimal id, DataTransferObject.Order.OrderStatus status) { EmergentOrder emergentOrder = new EmergentOrder(); string sql = "select Id,[Type],Content,[Time],Account from T_EmergentOrder WHERE Id =@Id AND [Type] = @Type"; using (DbOperator dboperator = new DbOperator(Provider, ConnectionString)) { dboperator.AddParameter("Id", id); dboperator.AddParameter("Type", status); using (System.Data.Common.DbDataReader reader = dboperator.ExecuteReader(sql)) { if (reader.Read()) { emergentOrder.Id = reader.GetDecimal(0); emergentOrder.Type = (DataTransferObject.Order.OrderStatus)reader.GetInt32(1); emergentOrder.Content = reader.GetString(2); emergentOrder.Time = reader.GetDateTime(3); emergentOrder.Account = reader.GetString(4); } } } return(emergentOrder); }
/// <summary> /// Generate CSV formatted output for the given reader. /// </summary> public string Generate(System.Data.Common.DbDataReader reader) { var builder = new StringBuilder(); var schema = reader.GetSchemaTable(); var colcount = reader.FieldCount; var nullable = new bool[colcount]; var datatype = new Type[colcount]; var typename = new string[colcount]; for (int c = 0; c < colcount; c++) { nullable[c] = true; datatype[c] = reader.GetFieldType(c); typename[c] = reader.GetDataTypeName(c); if (c == 0) { if (this.Settings.AddLineNumbers) { if (this.Settings.QuotedStrings) { builder.Append(this.Settings.StringQuote); } builder.Append("Line"); if (this.Settings.QuotedStrings) { builder.Append(this.Settings.StringQuote); } builder.Append(this.Settings.FieldSeparator); } } else { builder.Append(this.Settings.FieldSeparator); } if (this.Settings.QuotedStrings) { builder.Append(this.Settings.StringQuote); } builder.Append(reader.GetName(c)); if (this.Settings.QuotedStrings) { builder.Append(this.Settings.StringQuote); } } builder.Append(this.Settings.LineSeparator); var lineNumber = 0; while (reader.Read()) { lineNumber++; for (int c = 0; c < colcount; c++) { if (c == 0) { if (this.Settings.AddLineNumbers) { builder.Append(lineNumber); builder.Append(this.Settings.FieldSeparator); } } else { builder.Append(this.Settings.FieldSeparator); } if (nullable[c] && reader.IsDBNull(c)) { } else { if (datatype[c] == typeof(String)) { if (this.Settings.QuotedStrings) { builder.Append(this.Settings.StringQuote); } builder.Append(ToCsvableString(reader.GetString(c))); if (this.Settings.QuotedStrings) { builder.Append(this.Settings.StringQuote); } } else if (datatype[c] == typeof(DateTime)) { builder.Append(reader.GetDateTime(c).ToString(this.Settings.DateTimeFormat, this.Settings.FormatProvider)); } else if (datatype[c] == typeof(Boolean)) { builder.Append(reader.GetBoolean(c) ? this.Settings.BooleanTrue : this.Settings.BooleanFalse); } else { builder.AppendFormat(this.Settings.FormatProvider, "{0}", reader.GetValue(c)); } } } builder.Append(this.Settings.LineSeparator); } return(builder.ToString()); }
/// <summary> /// Gets a datetime column. Slower then GetOrdinal + GetDateTime /// </summary> /// <param name="reader"></param> /// <param name="name"></param> /// <returns></returns> public static DateTime GetDateTime(this System.Data.Common.DbDataReader reader, string name) { int ordinal = reader.GetOrdinal(name); return(reader.GetDateTime(ordinal)); }
/// <summary> /// Gets a datetime column. /// </summary> /// <param name="reader"></param> /// <param name="ordinal"></param> /// <returns></returns> public static DateTime?GetNDateTime(this System.Data.Common.DbDataReader reader, int ordinal) { return(reader.IsDBNull(ordinal) ? (DateTime?)null : reader.GetDateTime(ordinal)); }
/// <summary> /// Gets a datetime column. Slower then GetOrdinal + GetDateTime /// </summary> /// <param name="reader"></param> /// <param name="name"></param> /// <returns></returns> public static DateTime?GetNDateTime(this System.Data.Common.DbDataReader reader, string name) { int ordinal = reader.GetOrdinal(name); return(reader.IsDBNull(ordinal) ? (DateTime?)null : reader.GetDateTime(ordinal)); }
///<summary> ///Return scalesValue from database from <paramref name="fromDate"/> to <paramref name="toDate"/> ///</summary> ///<param name="fromDate">from Date, if null will get all value until toDate</param> ///<param name="toDate">take until Date, if null will get all value after fromDate</param> public List <ScalesValue> getScalesValue(string fromDate, string toDate) { try { string condition = null; if (fromDate != null) { condition = " where Time >= @fromDate"; } if (toDate != null) { if (condition == null) { condition = " where "; } else { condition += " and "; } condition += "Time <= @toDate"; } string query = String.Format("select {0}, {1}, {2} from {3} {4} order by {5}", COL_SCALES_TIME, COL_SCALES_WEIGHT, COL_SCALES_TAG, TABLE_SCALES_VALUE, condition, COL_SCALES_TIME); //SqlCeCommand cmd = new SqlCeCommand(query, mConnection); setQuery(query); if (fromDate != null) { mCmd.Parameters.AddWithValue("@fromDate", DateTime.Parse(fromDate)); } if (toDate != null) { mCmd.Parameters.AddWithValue("@toDate", DateTime.Parse(toDate)); } System.Data.Common.DbDataReader reader = mCmd.ExecuteReader(); List <ScalesValue> list = new List <ScalesValue>(); while (reader.Read()) { ScalesValue value = new ScalesValue(reader.GetDateTime(0), (float)reader.GetDouble(1), reader.GetString(2)); list.Add(value); } reader.Close(); return(list); } catch (Exception e) { UnhandledExceptionEventArgs arg = new UnhandledExceptionEventArgs(e, false); CurrentDomain_UnhandledException(null, arg); return(null); } }
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); }