public static Decimal?GetDecimalOrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetDecimal(ordinal));
}
}
public static Decimal GetDecimalOrDefault(this READER reader, int ordinal) => reader.IsDBNull(ordinal) ? default : reader.GetDecimal(ordinal);
private AccountTransactionEvent GetAccountTransactionEventFromRecord(NpgsqlDataReader reader)
{
var accountTransactionEvent = new AccountTransactionEvent
{
AccountAction = (AccountAction) reader.GetValue(13),
Funds = reader.GetDecimal(10)
};
FillBaseEventPropertiesFromRecord(accountTransactionEvent, reader);
return accountTransactionEvent;
}
private DebugEvent GetDebugEventFromRecord(NpgsqlDataReader reader)
{
var debugEvent = new DebugEvent
{
Command = (CommandType)reader.GetValue(8),
StockSymbol = (reader.IsDBNull(9)) ? null : reader.GetString(9),
Funds = (reader.IsDBNull(10)) ? null : (decimal?) reader.GetDecimal(10),
FileName = (reader.IsDBNull(11)) ? null : reader.GetString(11),
DebugMessage = (reader.IsDBNull(12)) ? null : reader.GetString(12)
};
FillBaseEventPropertiesFromRecord(debugEvent, reader);
return debugEvent;
}
private QuoteServerEvent GetQuoteServerEventFromRecord(NpgsqlDataReader reader)
{
var quoteServerEvent = new QuoteServerEvent
{
StockSymbol = reader.GetString(9),
Price = reader.GetDecimal(10),
QuoteServerTime = reader.GetDateTime(14),
CryptoKey = reader.GetString(15)
};
FillBaseEventPropertiesFromRecord(quoteServerEvent, reader);
return quoteServerEvent;
}
private UserCommandEvent GetUserCommandEventFromRecord(NpgsqlDataReader reader)
{
var userCommandEvent = new UserCommandEvent
{
Command = (CommandType) reader.GetValue(8),
StockSymbol = (reader.IsDBNull(9)) ? null : reader.GetString(9),
Funds = (reader.IsDBNull(10)) ? null : (decimal?) reader.GetDecimal(10)
};
FillBaseEventPropertiesFromRecord(userCommandEvent, reader);
return userCommandEvent;
}
//
//-------------------------------------------------------------------------------------------------
//
public bool ds2browse(string browse_type_in, string browse_category_in, string browse_actor_in,
string browse_title_in, int batch_size_in, int customerid_out, ref int rows_returned,
ref int[] prod_id_out, ref string[] title_out, ref string[] actor_out, ref decimal[] price_out,
ref int[] special_out, ref int[] common_prod_id_out, ref double rt)
{
// Products table: PROD_ID INT, CATEGORY TINYINT, TITLE VARCHAR(50), ACTOR VARCHAR(50),
// PRICE DECIMAL(12,2), SPECIAL TINYINT, COMMON_PROD_ID INT
int i_row;
string data_in = null;
int[] category_out = new int[GlobalConstants.MAX_ROWS];
#if (USE_WIN32_TIMER)
long ctr0 = 0, ctr = 0, freq = 0;
#else
TimeSpan TS = new TimeSpan();
DateTime DT0;
#endif
switch(browse_type_in)
{
case "category":
Browse_By_Category.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Category.Parameters["category_in"].Value = Convert.ToInt32(browse_category_in);
data_in = browse_category_in;
break;
case "actor":
Browse_By_Actor.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Actor.Parameters["actor_in"].Value = "\"" + browse_actor_in + "\"";
data_in = "\"" + browse_actor_in + "\"";
break;
case "title":
Browse_By_Title.Parameters["batch_size_in"].Value = batch_size_in;
Browse_By_Title.Parameters["title_in"].Value = "\"" + browse_title_in + "\"";
data_in = "\"" + browse_title_in + "\"";
break;
}
// Console.WriteLine("Thread {0}: Calling Browse w/ browse_type= {1} batch_size_in= {2} data_in= {3}",
// Thread.CurrentThread.Name, browse_type_in, batch_size_in, data_in);
#if (USE_WIN32_TIMER)
QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
QueryPerformanceCounter(ref ctr0); // Start response time clock
#else
DT0 = DateTime.Now;
#endif
try
{
switch(browse_type_in)
{
case "category":
Rdr = Browse_By_Category.ExecuteReader();
break;
case "actor":
Rdr = Browse_By_Actor.ExecuteReader();
break;
case "title":
Rdr = Browse_By_Title.ExecuteReader();
break;
}
i_row = 0;
while (Rdr.Read())
{
prod_id_out[i_row] = Rdr.GetInt32(0);
category_out[i_row] = Rdr.GetInt16(1);
title_out[i_row] = Rdr.GetString(2);
actor_out[i_row] = Rdr.GetString(3);
price_out[i_row] = Rdr.GetDecimal(4);
special_out[i_row] = Rdr.GetInt16(5);
common_prod_id_out[i_row] = Rdr.GetInt32(6);
++i_row;
}
Rdr.Close();
rows_returned = i_row;
}
catch (NpgsqlException e)
{
Console.WriteLine("Thread {0}: Error in Browse: {1}", Thread.CurrentThread.Name, e.Message);
return(false);
}
#if (USE_WIN32_TIMER)
QueryPerformanceCounter(ref ctr); // Stop response time clock
rt = (ctr - ctr0)/(double) freq; // Calculate response time
#else
TS = DateTime.Now - DT0;
rt = TS.TotalSeconds; // Calculate response time
#endif
return(true);
}
//
//-------------------------------------------------------------------------------------------------
//
public bool ds2purchase(int cart_items, int[] prod_id_in, int[] qty_in, int customerid_out,
ref int neworderid_out, ref bool IsRollback, ref double rt)
{
int i, j;
#if (USE_WIN32_TIMER)
long ctr0 = 0, ctr = 0, freq = 0;
#else
TimeSpan TS = new TimeSpan();
DateTime DT0;
#endif
//Cap cart_items at 10 for this implementation of stored procedure
cart_items = System.Math.Min(10, cart_items);
// Extra, non-stored procedure query to find total cost of purchase
Decimal netamount_in = 0;
//Modified by GSK for parameterization of query below - Affects performance in case of Query Caching
//string cost_query = "select PROD_ID, PRICE from PRODUCTS where PROD_ID in (" + prod_id_in[0];
//for (i=1; i<cart_items; i++) cost_query = cost_query + "," + prod_id_in[i];
//cost_query = cost_query + ")";
////Console.WriteLine(cost_query);
//NpgsqlCommand cost_command = new NpgsqlCommand(cost_query, objConn);
//Parameterized query by GSK
string cost_query = "select PROD_ID, PRICE from PRODUCTS where PROD_ID in ( @ARG0";
for ( i = 1 ; i < cart_items ; i++ ) cost_query = cost_query + ", @ARG" + i;
cost_query = cost_query + ")";
NpgsqlCommand cost_command = new NpgsqlCommand ( cost_query , objConn );
string ArgHolder;
for ( i = 0 ; i < cart_items ; i++ )
{
ArgHolder = "@ARG" + i;
cost_command.Parameters.Add ( ArgHolder , NpgsqlDbType.Integer );
cost_command.Parameters[ArgHolder].Value = prod_id_in[i];
//Console.WriteLine (cost_command.Parameters[ArgHolder].Value);
}
Rdr = cost_command.ExecuteReader();
while (Rdr.Read())
{
j = 0;
int prod_id = Rdr.GetInt32(0);
while (prod_id_in[j] != prod_id) ++j; // Find which product was returned
netamount_in = netamount_in + qty_in[j] * Rdr.GetDecimal(1);
//Console.WriteLine(j + " " + prod_id + " " + Rdr.GetDecimal(1));
}
Rdr.Close();
// Can use following code instead if you don't want extra roundtrip to database:
// Random rr = new Random(DateTime.Now.Millisecond);
// Decimal netamount_in = (Decimal) (0.01 * (1 + rr.Next(40000)));
Decimal taxamount_in = (Decimal) 0.0825 * netamount_in;
Decimal totalamount_in = netamount_in + taxamount_in;
//Console.WriteLine(netamount_in);
Purchase.Parameters["customerid_in"].Value = customerid_out;
Purchase.Parameters["number_items"].Value = cart_items;
Purchase.Parameters["netamount_in"].Value = netamount_in;
Purchase.Parameters["taxamount_in"].Value = taxamount_in;
Purchase.Parameters["totalamount_in"].Value = totalamount_in;
Purchase.Parameters["prod_id_in0"].Value = prod_id_in[0]; Purchase.Parameters["qty_in0"].Value = qty_in[0];
Purchase.Parameters["prod_id_in1"].Value = prod_id_in[1]; Purchase.Parameters["qty_in1"].Value = qty_in[1];
Purchase.Parameters["prod_id_in2"].Value = prod_id_in[2]; Purchase.Parameters["qty_in2"].Value = qty_in[2];
Purchase.Parameters["prod_id_in3"].Value = prod_id_in[3]; Purchase.Parameters["qty_in3"].Value = qty_in[3];
Purchase.Parameters["prod_id_in4"].Value = prod_id_in[4]; Purchase.Parameters["qty_in4"].Value = qty_in[4];
Purchase.Parameters["prod_id_in5"].Value = prod_id_in[5]; Purchase.Parameters["qty_in5"].Value = qty_in[5];
Purchase.Parameters["prod_id_in6"].Value = prod_id_in[6]; Purchase.Parameters["qty_in6"].Value = qty_in[6];
Purchase.Parameters["prod_id_in7"].Value = prod_id_in[7]; Purchase.Parameters["qty_in7"].Value = qty_in[7];
Purchase.Parameters["prod_id_in8"].Value = prod_id_in[8]; Purchase.Parameters["qty_in8"].Value = qty_in[8];
Purchase.Parameters["prod_id_in9"].Value = prod_id_in[9]; Purchase.Parameters["qty_in9"].Value = qty_in[9];
// Console.WriteLine("Thread {0}: Calling Purchase w/ customerid = {1} number_items= {2}",
// Thread.CurrentThread.Name, customerid_out, cart_items);
#if (USE_WIN32_TIMER)
QueryPerformanceFrequency(ref freq); // obtain system freq (ticks/sec)
QueryPerformanceCounter(ref ctr0); // Start response time clock
#else
DT0 = DateTime.Now;
#endif
bool deadlocked;
do
{
try
{
deadlocked = false;
neworderid_out = (int) Purchase.ExecuteScalar();
}
catch (NpgsqlException e)
{
if (e.Code == "40P01")
{
deadlocked = true;
Random r = new Random(DateTime.Now.Millisecond);
int wait = r.Next(1000);
Console.WriteLine("Thread {0}: Purchase deadlocked...waiting {1} msec, then will retry",
Thread.CurrentThread.Name, wait);
Thread.Sleep(wait); // Wait up to 1 sec, then try again
}
else if (e.Code == "P0001")
{
deadlocked=false;
neworderid_out = 0;
}
else
{
Console.WriteLine("Thread {0}: SQL Error {1} in Purchase: {2}",
Thread.CurrentThread.Name, e.Code, e.Message);
return(false);
}
}
} while (deadlocked);
#if (USE_WIN32_TIMER)
QueryPerformanceCounter(ref ctr); // Stop response time clock
rt = (ctr - ctr0)/(double) freq; // Calculate response time
#else
TS = DateTime.Now - DT0;
rt = TS.TotalSeconds; // Calculate response time
#endif
if (neworderid_out == 0) IsRollback = true;
return(true);
}
public bool GetByPrimaryKey(string pKey)
{
string sQuery = "select * from tbm_bookingdetail WHERE detailid='" + pKey + "'";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
try
{
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("detailid")))
{
m_detailid = rdr.GetString(rdr.GetOrdinal("detailid"));
}
else
{
m_detailid = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("bookingid")))
{
m_bookingid = rdr.GetString(rdr.GetOrdinal("bookingid"));
}
else
{
m_bookingid = "";
};
//
if (!rdr.IsDBNull(rdr.GetOrdinal("carid")))
{
m_carid = rdr.GetString(rdr.GetOrdinal("carid"));
}
else
{
m_carid = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("price")))
{
m_price = rdr.GetDecimal(rdr.GetOrdinal("price"));
}
else
{
m_price = 0;
};
//
if (!rdr.IsDBNull(rdr.GetOrdinal("opadd")))
{
m_opadd = rdr.GetString(rdr.GetOrdinal("opadd"));
}
else
{
m_opadd = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd")))
{
m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd"));
}
else
{
m_pcadd = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("luadd")))
{
m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd"));
}
else
{
m_luadd = System.DateTime.MinValue;
};
if (!rdr.IsDBNull(rdr.GetOrdinal("opedit")))
{
m_opedit = rdr.GetString(rdr.GetOrdinal("opedit"));
}
else
{
m_opedit = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit")))
{
m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit"));
}
else
{
m_pcedit = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("luedit")))
{
m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit"));
}
else
{
m_luedit = System.DateTime.MinValue;
};
m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt"));
}
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
}
