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