public static int SelectedItemId(ComboBoxItem.Type type, string selected_text)
{
// initialize
int value = 0;
string query;
switch (type)
{
case ComboBoxItem.Type.Unit:
query = "SELECT UNIT_ID AS ID FROM UNITLIST WHERE UNIT ='" + selected_text + "'";
break;
case ComboBoxItem.Type.Sys:
query = "SELECT SYS_ID AS ID FROM SYSLIST WHERE SYS ='" + selected_text + "'";
break;
case ComboBoxItem.Type.Comp_Type:
default:
query = "SELECT COMP_TYPE_ID AS ID FROM COMPTYPES WHERE COMP_TYPE ='" + selected_text + "'";
break;
}
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.ExecuteReader(CommandType.Text, query);
while (dbmgr.DataReader.Read())
{
value = Convert.ToInt32(dbmgr.DataReader["id"]);
return value;
}
}
catch
{
return value;
}
finally
{
dbmgr.Dispose();
}
return value;
}
// creates arraylist for ComboBox, given initial message & type
public static ArrayList FetchComboBoxList(string initMsg, ComboBoxItem.Type type, int id)
{
// initialize
ArrayList list = new ArrayList();
string query;
// determine table based on type passed
switch (type)
{
case ComboBoxItem.Type.FAFZ:
default:
query = "SELECT FZ_ID, FZ FROM FZLIST WHERE FA_ID = @fa_id ORDER BY FZ";
break;
}
// connect to database
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.CreateParameters(1);
dbmgr.AddParameters(0, "@fa_id", id);
dbmgr.ExecuteReader(CommandType.Text, query);
// add the default item to list
if (initMsg.Length > 0)
list.Add(new ComboBoxItem(initMsg, "0"));
// add subsequent items to list
while (dbmgr.DataReader.Read())
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), dbmgr.DataReader.GetInt32(0).ToString()));
}
catch (Exception ex)
{
throw ex;
}
return list;
}
// update lists (1) insert, (2) update, (3) delete
public static void SaveComboBoxList(ComboBoxItem item, IList list, ref bool in_use)
{
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.BeginTransaction();
switch (item.update_type)
{
// insert item to list
case 1:
dbmgr.CreateParameters(2);
dbmgr.AddParameters(0, "@name", item.name);
dbmgr.AddParameters(1, "@desc", item.desc);
dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, item.query + "_u");
break;
// update item in list
case 2:
dbmgr.CreateParameters(3);
dbmgr.AddParameters(0, "@value", item.value);
dbmgr.AddParameters(1, "@name", item.name);
dbmgr.AddParameters(2, "@desc", item.desc);
dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, item.query + "_u");
break;
// delete item from list
case 3:
dbmgr.CreateParameters(2);
dbmgr.AddParameters(0, "@value", item.value);
dbmgr.AddParameters(1, "@in_use", in_use, true);
dbmgr.ExecuteNonQuery(CommandType.StoredProcedure, item.query + "_d");
in_use = Convert.ToBoolean(((System.Data.Common.DbParameter)dbmgr.Parameters.GetValue(1)).Value);
break;
}
dbmgr.CommitTransaction();
}
catch (Exception ex)
{
dbmgr.RollbackTransaction();
throw (ex);
}
finally
{
dbmgr.Dispose();
}
}
// creates arraylist for ComboBox, given initial message & type
public static ArrayList FetchComboBoxList(string initMsg, ComboBoxItem.Type type)
{
// initialize
ArrayList list = new ArrayList();
string query;
// determine table based on type passed
switch (type)
{
case ComboBoxItem.Type.Box:
query = "SELECT * FROM BOXLIST ORDER BY BOX";
break;
case ComboBoxItem.Type.Unit:
query = "SELECT * FROM UNITLIST ORDER BY UNIT";
break;
case ComboBoxItem.Type.Sys:
query = "SELECT * FROM SYSLIST ORDER BY SYS";
break;
case ComboBoxItem.Type.Position:
query = "SELECT * FROM POSLIST ORDER BY POS";
break;
case ComboBoxItem.Type.Train:
query = "SELECT * FROM TRAINLIST ORDER BY TRAIN";
break;
case ComboBoxItem.Type.Method:
query = "SELECT * FROM SSD_METHODLIST ORDER BY METHOD";
break;
case ComboBoxItem.Type.Comp_Type:
query = "SELECT * FROM COMPTYPES ORDER BY COMP_TYPE";
break;
case ComboBoxItem.Type.DrawingType:
query = "SELECT * FROM DWGTYPES WHERE DWGTYPE_ID <= 4";
break;
case ComboBoxItem.Type.FZone:
query = "SELECT * FROM FZLIST ORDER BY FZ";
break;
case ComboBoxItem.Type.FArea:
query = "SELECT * FROM FALIST ORDER BY FA";
break;
case ComboBoxItem.Type.KSF:
query = "SELECT * FROM KSFLIST ORDER BY KSF";
break;
case ComboBoxItem.Type.Bin:
query = "SELECT BIN_ID, BIN, BIN_DESC FROM BINLIST ORDER BY BIN_ID";
break;
case ComboBoxItem.Type.ExportTables:
query = "SELECT COLTYPE_ID, COLTYPE_NAME, QUERY, EDIT FROM TABLECOLUMNTYPES WHERE EXPORT = 1";
break;
case ComboBoxItem.Type.LookupTables:
default:
query = "SELECT COLTYPE_ID, COLTYPE_NAME, QUERY, EDIT FROM TABLECOLUMNTYPES WHERE LEVEL = 1 ORDER BY COLTYPE_NAME";
break;
}
// connect to database
User user = (User)System.Web.HttpContext.Current.Session[Constant.session.User];
IDBManager dbmgr = new DBManager(user.plantDBStr);
dbmgr.ConnectionString = user.plantDBStr;
try
{
dbmgr.Open();
dbmgr.ExecuteReader(CommandType.Text, query);
// add the default item to list
if (initMsg.Length > 0)
list.Add(new ComboBoxItem(initMsg, "0"));
// add subsequent items to list
while (dbmgr.DataReader.Read())
{
switch (type)
{
case ComboBoxItem.Type.ExportTables:
// set item's name(COLTYPE_NAME), desc(""), value(ID), query(QUERY)
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), "", dbmgr.DataReader.GetInt32(0).ToString(), dbmgr.DataReader.GetString(2)));
break;
case ComboBoxItem.Type.LookupTables:
// set item's name(COLTYPE_NAME), desc(""), value(ID|EDIT), query(QUERY)
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), "", dbmgr.DataReader.GetInt32(0).ToString() + "|" + dbmgr.DataReader.GetBoolean(3).ToString(), dbmgr.DataReader.GetString(2)));
break;
case ComboBoxItem.Type.Bin:
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1) + ": " + dbmgr.DataReader.GetString(2), dbmgr.DataReader.GetInt32(0).ToString()));
break;
default:
list.Add(new ComboBoxItem(dbmgr.DataReader.GetString(1), dbmgr.DataReader.GetInt32(0).ToString()));
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
return list;
}