private void VehicleLastGPS(string vehicleID, out float gps_lat, out float gps_lon, out int gps_time)
{
gps_lat = 0.0F; gps_lon = 0.0F; gps_time = 0;
OdbcConnection connIfx = new OdbcConnection(ConfigurationManager.AppSettings.Get("MadsODBC"));
try
{
connIfx.Open();
OdbcCommand myCommand = new OdbcCommand("select tl_lat_y, tl_long_x, tl_date_time from tlayer where tl_fleet='H' and tl_veh_nbr=" + vehicleID + " order by tl_date_time desc", connIfx);
OdbcDataReader myReader = myCommand.ExecuteReader();
try
{
if (myReader.Read())
{
gps_lat = myReader.GetFloat(0);
gps_lon = myReader.GetFloat(1);
gps_time = myReader.GetInt32(2);
}
myReader.Close();
myCommand.Dispose();
connIfx.Close();
}
catch
{
connIfx.Close();
}
}
catch
{
connIfx.Close();
}
}
public static List <AssessmentItem> getList(OdbcDataReader reader)
{
List <AssessmentItem> list = new List <AssessmentItem>();
AssessmentItem assessments;
while (reader.Read())
{
assessments = new AssessmentItem();
assessments.IT_ID = reader.GetInt32(0);
assessments.IT_Name = reader.GetString(1);
assessments.I_ID = reader.GetInt32(2);
assessments.IT_Price = reader.GetFloat(3);
list.Add(assessments);
}
return(list);
}
/// <summary>
/// Gets the value of the column at the specified index as a float.
/// </summary>
/// <param name="columnIndex">Index of column to examine.</param>
/// <returns>Value of the column as a float.</returns>
public float GetFloat(int columnIndex)
{
Type columnType = _reader.GetFieldType(columnIndex);
if (columnType == typeof(decimal))
{
return((float)_reader.GetDecimal(columnIndex));
}
if (columnType == typeof(double))
{
return((float)_reader.GetDouble(columnIndex));
}
return(_reader.GetFloat(columnIndex));
}
public static ArrayList FindOrderDetailsByOrderID(int orderID)
{
ArrayList orderDetails = new ArrayList();
string connectionString = DataUtilities.ConnectionString;
if ((connectionString != null) && (connectionString.Length > 0))
{
try
{
OdbcConnection dataConnection = new OdbcConnection();
dataConnection.ConnectionString = connectionString;
dataConnection.Open();
OdbcCommand dataCommand = new OdbcCommand();
dataCommand.Connection = dataConnection;
// Build Command String
StringBuilder commandText =
new StringBuilder("SELECT * FROM [Orders Details] WHERE OrdersID = ");
commandText.Append(orderID);
dataCommand.CommandText = commandText.ToString();
OdbcDataReader dataReader = dataCommand.ExecuteReader();
while (dataReader.Read())
{
OrderDetail orderDetail = new OrderDetail();
orderDetail.OrderID = dataReader.GetInt32(0);
orderDetail.ProductID = dataReader.GetInt32(1);
orderDetail.UnitPrice = dataReader.GetDecimal(2);
orderDetail.QuantityOrdered = dataReader.GetInt16(3);
orderDetail.Discount = dataReader.GetFloat(4);
orderDetails.Add(orderDetail);
}
dataConnection.Close();
}
catch (Exception e)
{
Console.WriteLine("error: " + e.Message);
}
}
return(orderDetails);
}
public static List <Test> getList(OdbcDataReader reader)
{
List <Test> list = new List <Test>();
Test tests;
while (reader.Read())
{
tests = new Test();
tests.IT_ID = reader.GetInt32(0);
tests.C_ID = reader.GetInt32(1);
tests.IT_Name = reader.GetString(2);
tests.T_Date = reader.GetDate(3);
tests.IT_Price = reader.GetFloat(4);
list.Add(tests);
}
return(list);
}
public static List <Hospitalization> getList(OdbcDataReader reader)
{
List <Hospitalization> list = new List <Hospitalization>();
Hospitalization hospitalizations;
while (reader.Read())
{
hospitalizations = new Hospitalization();
hospitalizations.C_ID = reader.GetInt32(0);
hospitalizations.S_ID = reader.GetInt32(1);
hospitalizations.H_In = reader.GetDate(2);
hospitalizations.H_Out = reader.GetDate(3);
hospitalizations.H_Sum = reader.GetFloat(4);
list.Add(hospitalizations);
}
return(list);
}
public static List <Drug> getList(OdbcDataReader reader)
{
List <Drug> list = new List <Drug>();
Drug drug;
while (reader.Read())
{
drug = new Drug();
drug.D_ID = reader.GetInt32(0);
drug.D_Name = reader.GetString(1);
drug.D_Standard = reader.GetString(2);
drug.D_PurchasingPrice = reader.GetInt32(3);
drug.D_SellingPrice = reader.GetFloat(4);
drug.D_Store = reader.GetInt32(5);
list.Add(drug);
}
return(list);
}
public static List <Prescript> getList(OdbcDataReader reader)
{
List <Prescript> list = new List <Prescript>();
Prescript prescript;
while (reader.Read())
{
prescript = new Prescript();
prescript.D_ID = reader.GetInt32(0);
prescript.C_ID = reader.GetInt32(1);
prescript.D_Name = reader.GetString(2);
prescript.D_Number = reader.GetInt32(3);
prescript.D_Totalprice = reader.GetFloat(4);
prescript.P_Date = reader.GetDateTime(5);
prescript.P_Notes = reader.GetString(6);
list.Add(prescript);
}
return(list);
}
/// <summary>
/// Test OdbcTransaction class
/// </summary>
public static void Test_Various_DataTypes()
{
TestCases.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, "; //1
sql += "c_smallint smallint, "; //2
sql += "c_integer integer, "; //3
sql += "c_bigint bigint, "; //4
sql += "c_numeric numeric(15,1), "; //5
sql += "c_float float, "; //6
sql += "c_decimal decimal(15,3), "; //7
sql += "c_double double, "; //8
sql += "c_char char, "; //9
sql += "c_varchar varchar(4096), "; //10
sql += "c_time time, "; //11
sql += "c_date date, "; //12
sql += "c_timestamp timestamp, "; //13
sql += "c_datetime datetime, "; //14
sql += "c_bit bit(1), "; //15
sql += "c_varbit bit varying(4096), "; //16
sql += "c_monetary monetary, "; //17
sql += "c_string string"; //18
sql += ")";
TestCases.ExecuteSQL(sql, conn);
sql = "insert into t values(";
sql += "1, "; //0
sql += "11, "; //1
sql += "111, "; //2
sql += "1111, "; //3
sql += "1.1, "; //4
sql += "1.11, "; //5
sql += "1.111, "; //6
sql += "1.1111, "; //7
sql += "'a', "; //8
sql += "'abcdfghijk', "; //9
sql += "TIME '13:15:45 pm', "; //10
sql += "DATE '00-10-31', "; //11
sql += "TIMESTAMP '13:15:45 10/31/2008', "; //12
sql += "DATETIME '13:15:45 10/31/2008', "; //13
sql += "B'1', "; //14
sql += "B'111', "; //15
sql += "123456789, "; //16
sql += "'qwerty'"; //17
sql += ")";
TestCases.ExecuteSQL(sql, conn);
sql = "select * from t";
using (OdbcCommand cmd = new OdbcCommand(sql, conn))
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Assert.IsTrue(reader.GetInt32(0) == 1);
Assert.IsTrue(reader.GetInt16(1) == 11);
Assert.IsTrue(reader.GetInt32(2) == 111);
Assert.IsTrue(reader.GetInt64(3) == 1111);
Assert.IsTrue(reader.GetDecimal(4) == (decimal)1.1);
Assert.IsTrue(reader.GetFloat(5) == (float)1.11); //"Single"
Assert.IsTrue(reader.GetDecimal(6) == (decimal)1.111);
Assert.IsTrue(reader.GetDouble(7) == (double)1.1111);
//We use GetString() because GetChar() is not supported or System.Data.OleDb.
//http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getchar
Assert.IsTrue(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID)
Assert.IsTrue(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID)
//GetGateTime cannot cast just the time value in a DateTime object, so we use TimeSpan
//Assert.IsTrue(reader.GetTimeSpan(10) == new TimeSpan(13, 15, 45));//"TimeSpan"
Assert.IsTrue(reader.GetDateTime(11) == new DateTime(2000, 10, 31)); //"DateTime"
Assert.IsTrue(reader.GetDateTime(12) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime"
// Console.WriteLine(reader.GetValue(13));
// Assert.IsTrue(reader.GetDateTime(13) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime"
//The GetByte() method does not perform any conversions and the driver does not give tha data as Byte
//http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getbyte
//Use GetValue() or GetBytes() methods to retrieve BIT coulumn value
// Assert.IsTrue((reader.GetValue(14) as byte[])[0] == (byte)0); //"Byte[]" ("bit(1)" in CUBRID)
//Or
Assert.IsTrue(reader.GetString(14) == "80");
Assert.IsTrue(reader.GetString(15) == "E0");
Assert.IsTrue(reader.GetString(16) == "123456789.0000000000000000");
Assert.IsTrue(reader.GetString(17) == "qwerty"); //"String"
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
public static void Test_Various_DataTypes2()
{
TestCases.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, "; //1
sql += "c_smallint smallint, "; //2
sql += "c_integer integer, "; //3
sql += "c_bigint bigint, "; //4
sql += "c_numeric numeric(15,1), "; //5
sql += "c_float float, "; //6
sql += "c_decimal decimal(15,3), "; //7
sql += "c_double double, "; //8
sql += "c_char char, "; //9
sql += "c_varchar varchar(4096), "; //10
sql += "c_time time, "; //11
sql += "c_date date, "; //12
sql += "c_timestamp timestamp, "; //13
sql += "c_datetime datetime, "; //14
sql += "c_bit bit(1), "; //15
sql += "c_varbit bit varying(4096), "; //16
sql += "c_monetary monetary, "; //17
sql += "c_string string"; //18
sql += ")";
TestCases.ExecuteSQL(sql, conn);
using (OdbcCommand cmd = new OdbcCommand(null, conn))
{
sql = "insert into t values(?, ?, ?, ?, ?, ?,";
sql += "?, ?, ?, ?, ?, ?,";
sql += "?, ?, ?, ?, ?, ?";
sql += ")";
OdbcParameter p1 = new OdbcParameter("?p1", OdbcType.Int);
p1.Value = 1;
cmd.Parameters.Add(p1);
OdbcParameter p2 = new OdbcParameter("?p2", OdbcType.SmallInt);
p2.Value = 11;
cmd.Parameters.Add(p2);
OdbcParameter p3 = new OdbcParameter("?p3", OdbcType.Int);
p3.Value = 111;
cmd.Parameters.Add(p3);
OdbcParameter p4 = new OdbcParameter("?p4", OdbcType.BigInt);
p4.Value = 1111;
cmd.Parameters.Add(p4);
OdbcParameter p5 = new OdbcParameter("?p5", OdbcType.Numeric);
p5.Value = 1.1;
cmd.Parameters.Add(p5);
OdbcParameter p6 = new OdbcParameter("?p6", OdbcType.Double);
p6.Value = 1.11;
cmd.Parameters.Add(p6);
OdbcParameter p7 = new OdbcParameter("?p7", OdbcType.Decimal);
p7.Value = 1.111;
cmd.Parameters.Add(p7);
OdbcParameter p8 = new OdbcParameter("?p8", OdbcType.Double);
p8.Value = 1.1111;
cmd.Parameters.Add(p8);
OdbcParameter p9 = new OdbcParameter("?p9", OdbcType.Char);
p9.Value = 'a';
cmd.Parameters.Add(p9);
OdbcParameter p10 = new OdbcParameter("?p10", OdbcType.VarChar);
p10.Value = "abcdfghijk";
cmd.Parameters.Add(p10);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
sql = "select * from t";
using (OdbcCommand cmd = new OdbcCommand(sql, conn))
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Assert.IsTrue(reader.GetInt32(0) == 1);
Assert.IsTrue(reader.GetInt16(1) == 11);
Assert.IsTrue(reader.GetInt32(2) == 111);
Assert.IsTrue(reader.GetInt64(3) == 1111);
Assert.IsTrue(reader.GetDecimal(4) == (decimal)1.1);
Assert.IsTrue(reader.GetFloat(5) == (float)1.11); //"Single"
Assert.IsTrue(reader.GetDecimal(6) == (decimal)1.111);
Assert.IsTrue(reader.GetDouble(7) == (double)1.1111);
Assert.IsTrue(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID)
Assert.IsTrue(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID)
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_METADATA_SubprToActivityList.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <param name="SubProcessID"></param>
/// <returns>returnListMETADATA_SubprToActivityListBySubProcess[]</returns>
public returnListMETADATA_SubprToActivityListBySubProcess[] ListMETADATA_SubprToActivityListBySubProcess(int?maxRowsToReturn, int SubProcessID)
{
returnListMETADATA_SubprToActivityListBySubProcess[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_Sequence\", \"c_u_NodeType\", \"c_u_BOOLisKeyPoint\", \"c_u_Text\", \"c_u_ListIdsBusRoles\", \"c_u_ListIdsApps\", \"c_r_SubProcess\" FROM \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" WHERE \"c_r_SubProcess\"=? ORDER BY c_u_Sequence";
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_Sequence\", \"c_u_NodeType\", \"c_u_BOOLisKeyPoint\", \"c_u_Text\", \"c_u_ListIdsBusRoles\", \"c_u_ListIdsApps\", \"c_r_SubProcess\" FROM \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" WHERE \"c_r_SubProcess\"=? ORDER BY c_u_Sequence " + " LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_Sequence\", \"c_u_NodeType\", \"c_u_BOOLisKeyPoint\", \"c_u_Text\", \"c_u_ListIdsBusRoles\", \"c_u_ListIdsApps\", \"c_r_SubProcess\" FROM \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" WHERE \"c_r_SubProcess\"=? ORDER BY c_u_Sequence";
}
cmd.Parameters.Add("1_SubProcessID", OdbcType.Int);
cmd.Parameters["1_SubProcessID"].Value = (object)SubProcessID;
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListMETADATA_SubprToActivityListBySubProcess> rvl = new List <returnListMETADATA_SubprToActivityListBySubProcess>();
while (dr.Read())
{
returnListMETADATA_SubprToActivityListBySubProcess cr = new returnListMETADATA_SubprToActivityListBySubProcess();
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cr.Sequence = null;
}
else
{
cr.Sequence = typeof(float).Equals(dr.GetFieldType(1))? (double)dr.GetFloat(1): dr.GetDouble(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'NodeType'");
}
else
{
cr.NodeType = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
cr.BOOLisKeyPoint = null;
}
else
{
cr.BOOLisKeyPoint = typeof(short).IsAssignableFrom(dr.GetFieldType(3))? (dr.GetInt16(3) != 0): dr.GetBoolean(3);
}
if (dr.IsDBNull(4))
{
cr.Text = null;
}
else
{
cr.Text = dr.GetString(4);
}
if (dr.IsDBNull(5))
{
cr.ListIdsBusRoles = null;
}
else
{
cr.ListIdsBusRoles = dr.GetString(5);
}
if (dr.IsDBNull(6))
{
cr.ListIdsApps = null;
}
else
{
cr.ListIdsApps = dr.GetString(6);
}
if (dr.IsDBNull(7))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'SubProcessID'");
}
else
{
cr.SubProcessID = dr.GetInt32(7);
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_METADATA_SubprToActivityList.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetMETADATA_SubprToActivityList</returns>
public returnGetMETADATA_SubprToActivityList GetMETADATA_SubprToActivityList(int ID)
{
returnGetMETADATA_SubprToActivityList rv = new returnGetMETADATA_SubprToActivityList();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_Sequence\",\"c_u_NodeType\",\"c_u_BOOLisKeyPoint\",\"c_u_Text\",\"c_u_ListIdsBusRoles\",\"c_u_ListIdsApps\",\"c_r_SubProcess\" from \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
rv.Sequence = null;
}
else
{
rv.Sequence = typeof(float).Equals(dr.GetFieldType(1))? (double)dr.GetFloat(1): dr.GetDouble(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'NodeType'");
}
else
{
rv.NodeType = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
rv.BOOLisKeyPoint = null;
}
else
{
rv.BOOLisKeyPoint = typeof(short).IsAssignableFrom(dr.GetFieldType(3))? (dr.GetInt16(3) != 0): dr.GetBoolean(3);
}
if (dr.IsDBNull(4))
{
rv.Text = null;
}
else
{
rv.Text = dr.GetString(4);
}
if (dr.IsDBNull(5))
{
rv.ListIdsBusRoles = null;
}
else
{
rv.ListIdsBusRoles = dr.GetString(5);
}
if (dr.IsDBNull(6))
{
rv.ListIdsApps = null;
}
else
{
rv.ListIdsApps = dr.GetString(6);
}
if (dr.IsDBNull(7))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'SubProcessID'");
}
else
{
rv.SubProcessID = dr.GetInt32(7);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}