public IActionResult Index()
{
DataTable table = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
try
{
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM orderdetails", Con);
ifx.Fill(table);
}
catch (Exception ex)
{
string createTable = "Create table orderdetails (orderid serial PRIMARY KEY, SLNo int, MobileName nvarchar(100) NULL, " +
" Description nvarchar(250) NULL, PicURL nvarchar(250) NULL, Model nvarchar(50) NULL, Features nvarchar(200) NULL, " +
"Color nvarchar(20) NULL, SimType nvarchar(10) NULL, PurchaseDate varchar(50), Price decimal(18, 2), Quantity int NULL, TotalAmount decimal(18,2))";
IfxCommand cmd = new IfxCommand(createTable, Con);
cmd.ExecuteNonQuery();
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM orderdetails", Con);
ifx.Fill(table);
}
finally
{
Con.Close();
}
return(View(table));
}
}
public IActionResult Index()
{
DataTable table = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
try
{
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Mobiles", Con);
ifx.Fill(table);
}
catch (Exception ex)
{
string createTable = "Create table Mobiles (SLNo serial PRIMARY KEY, MobileName nvarchar(100) NULL, Price decimal(18, 2)," +
" Quantity int NULL, Description nvarchar(250) NULL, PicURL nvarchar(250) NULL," +
" Model nvarchar(50) NULL, Features nvarchar(200) NULL, Color nvarchar(20) NULL, SimType nvarchar(10) NULL, ImageFile Blob)";
IfxCommand cmd = new IfxCommand(createTable, Con);
cmd.ExecuteNonQuery();
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Mobiles", Con);
ifx.Fill(table);
}
finally
{
Con.Close();
}
}
return(View(table));
}
private static void Main(string[] args)
{
var connString = "Host=127.0.0.1;Server = alx_test; User ID = informix; password = 462753;";
var conn = new IfxConnection(connString);
conn.Open();
var createDbCommand = conn.CreateCommand();
var selectDbCommand = conn.CreateCommand();
var createTableCommand = conn.CreateCommand();
createDbCommand.CommandText = "CREATE DATABASE IF NOT EXISTS 'alxr64_PeopleRegistry_Demo';"
;
selectDbCommand.CommandText = "DATABASE 'alxr64_PeopleRegistry_Demo';";
createTableCommand.CommandText = @"CREATE TABLE IF NOT EXISTS person (
id SERIAL not null,
FirstName VARCHAR(100),
LastName VARCHAR(100),
SurName VARCHAR(100),
BirthDate DATE);";
createDbCommand.ExecuteNonQuery();
selectDbCommand.ExecuteNonQuery();
createTableCommand.ExecuteReader();
}
// GET: /Product/Edit/5
public ActionResult Edit(int id)
{
ProductModel productModel = new ProductModel();
DataTable tblProd = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
// Prone to SQL enjection
string query = "SELECT * FROM Product Where productid = ?";
IfxDataAdapter ifx = new IfxDataAdapter(query, Con);
ifx.SelectCommand.Parameters.Add("productid", IfxType.Serial).Value = id;
ifx.Fill(tblProd);
}
if (tblProd.Rows.Count == 1)
{
productModel.ProductID = Convert.ToInt32(tblProd.Rows[0][0].ToString());
productModel.ProductName = tblProd.Rows[0][1].ToString();
productModel.Price = Convert.ToDecimal(tblProd.Rows[0][2].ToString());
productModel.Count = Convert.ToInt32(tblProd.Rows[0][3].ToString());
return(View(productModel));
}
else
{
return(RedirectToAction("Index"));
}
}
public ActionResult Index()
{
DataTable table = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
try
{
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Product", Con);
ifx.Fill(table);
}
catch (Exception ex)
{
string createProductTable = "Create table Product (productid serial PRIMARY KEY, productname varchar(50), price decimal(18,2), count int)";
IfxCommand cmd = new IfxCommand(createProductTable, Con);
cmd.ExecuteNonQuery();
string createOrderTable = "Create table orderdetails (orderid serial PRIMARY KEY, productid int, productname varchar(50), price decimal(18,2), count int, totalamount decimal(18,2))";
IfxCommand cmd1 = new IfxCommand(createOrderTable, Con);
cmd1.ExecuteNonQuery();
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Product", Con);
ifx.Fill(table);
}
}
return(View(table));
}
private void SetConnection(IDbConnection value)
{
if (_connection != value)
{
_connection = (IfxConnection)value;
}
}
public ActionResult EachProductDetails(Mobiles mobile)
{
DataTable mobileTable = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
// Prone to SQL enjection
string query = "SELECT * FROM Mobiles Where SLNo = ?";
IfxDataAdapter ifx = new IfxDataAdapter(query, Con);
ifx.SelectCommand.Parameters.Add("SLNo", IfxType.Serial).Value = mobile.SLNo;
ifx.Fill(mobileTable);
Con.Close();
}
Mobiles mobileDetail = new Mobiles();
for (int i = 0; i < mobileTable.Rows.Count; i++)
{
mobileDetail.SLNo = Convert.ToInt32(mobileTable.Rows[0][0].ToString());
mobileDetail.MobileName = mobileTable.Rows[0][1].ToString();
mobileDetail.Price = Convert.ToDecimal(mobileTable.Rows[0][2].ToString());
mobileDetail.Quantity = Convert.ToInt32(mobileTable.Rows[0][3].ToString());
mobileDetail.Description = mobileTable.Rows[0][4].ToString();
mobileDetail.PicURL = mobileTable.Rows[0][5].ToString();
mobileDetail.Model = mobileTable.Rows[0][6].ToString();
mobileDetail.Features = mobileTable.Rows[0][7].ToString();
mobileDetail.Color = mobileTable.Rows[0][8].ToString();
mobileDetail.SimType = mobileTable.Rows[0][9].ToString();
}
return(View(mobileDetail));
}
public static Response ExecuteNonQuery(string query, Connection connection, bool call = true)
{
using (var ifxConnection = new IfxConnection(connection.ConnectionString))
{
try
{
ifxConnection.Open();
var ifxCommand = new IfxCommand(query)
{
Connection = ifxConnection,
CommandTimeout = 0
};
return(new Response(ifxCommand.ExecuteNonQuery()));
}
catch (Exception exception)
{
if (!call)
{
return(new Response(Status.Exception, exception.Message));
}
ExecuteNonQuery(query, connection, false);
return(new Response(Status.Exception, exception.Message));
}
finally
{
ifxConnection.Close();
}
}
}
public void GetPosition(out int x, out int y)
{
x = 0; y = 0;
string strConn = ConfigurationSettings.AppSettings.Get("MadsConnect");
IfxConnection conn = new IfxConnection(strConn);
try
{
conn.Open();
}
catch
{
return;
}
using (IfxCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select vh_gps_lat,vh_gps_long from vehicle where vh_nbr=" + this.VehicleID;
IfxDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
x = Int32.Parse(rdr["vh_gps_long"].ToString());
y = Int32.Parse(rdr["vh_gps_lat"].ToString());
}
}
conn.Close();
}
private static string TestConnectionInformix()
{
IfxConnection conn = new IfxConnection(_connectionString);
//conn.ConnectionString = _connectionString;
try
{
conn.Open();
string server = conn.Server;
string version = conn.ServerVersion;
string type = conn.ServerType;
conn.Close();
return($"{server}\n{type} versão {version}\n\nConectado com sucesso!");
}
catch (IfxException ex)
{
if (ex.InnerException == null)
{
return($"{conn.Server}\nErro ao tentar conectar: \n{ex.Message}");
}
else
{
return($"{conn.Server}\nErro ao tentar conectar: \n{ex.Message}\nDetalhe: {ex.InnerException.Message}");
}
}
}
public static DataTable RunSPReturnDataTable(string spName, Paras paras, IfxConnection conn)
{
try
{
IfxCommand salesCMD = new IfxCommand(spName, conn);
salesCMD.CommandType = CommandType.StoredProcedure;
/// 加上他们的餐数
foreach (Para para in paras)
{
IfxParameter myParm = salesCMD.Parameters.Add(para.ParaName, para.DAType);
myParm.Value = para.val;
}
//selectCMD.CommandTimeout =60;
IfxDataAdapter sda = new IfxDataAdapter(salesCMD);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
DataTable dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
return(dt);
}
catch (System.Exception ex)
{
throw ex;
}
}
public static int RunSP(string spName, Paras paras)
{
switch (DBAccess.AppCenterDBType)
{
case DBType.MSSQL:
SqlConnection conn = new SqlConnection(SystemConfig.AppCenterDSN);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
return(DBProcedure.RunSP(spName, paras, conn));
break;
case DBType.Informix:
IfxConnection conn1 = new IfxConnection(SystemConfig.AppCenterDSN);
if (conn1.State != ConnectionState.Open)
{
conn1.Open();
}
return(DBProcedure.RunSP(spName, paras, conn1));
break;
default:
throw new Exception("尚未处理。");
break;
}
}
private bool UpdateMISHotlist(IMISHotlistUpdate update, string encryptedIdentifier)
{
Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
try
{
using (IfxConnection connection = EstablishConnection())
{
IfxCommand command = connection.CreateCommand();
if (update.Change == "Add")
{
command.CommandText = $"INSERT INTO Hotlist VALUES ('{encryptedIdentifier}') ";
}
else if (update.Change == "Delete")
{
command.CommandText = $"DELETE FROM Hotlist WHERE ac_nr = '{encryptedIdentifier}'";
}
}
}
catch (Exception ex)
{
Log.LogException(ex);
Log.LogTrace(ex.Message + ". Check error log for more details.");
return(false);
}
Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
return(true);
}
// GET: /Mobiles/Edit/5
public ActionResult Edit(int SLNo)
{
Mobiles mobile = new Mobiles();
DataTable mobileTable = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
// Prone to SQL enjection
string query = "SELECT * FROM Mobiles Where SLNo = ?";
IfxDataAdapter ifx = new IfxDataAdapter(query, Con);
ifx.SelectCommand.Parameters.Add("SLNo", IfxType.Serial).Value = SLNo;
ifx.Fill(mobileTable);
Con.Close();
}
if (mobileTable.Rows.Count == 1)
{
mobile.SLNo = Convert.ToInt32(mobileTable.Rows[0][0].ToString());
mobile.MobileName = mobileTable.Rows[0][1].ToString();
mobile.Price = Convert.ToDecimal(mobileTable.Rows[0][2].ToString());
mobile.Quantity = Convert.ToInt32(mobileTable.Rows[0][3].ToString());
mobile.Description = mobileTable.Rows[0][4].ToString();
mobile.PicURL = mobileTable.Rows[0][5].ToString();
mobile.Model = mobileTable.Rows[0][6].ToString();
mobile.Features = mobileTable.Rows[0][7].ToString();
mobile.Color = mobileTable.Rows[0][8].ToString();
mobile.SimType = mobileTable.Rows[0][9].ToString();
return(View(mobile));
}
else
{
return(RedirectToAction("Index"));
}
}
public override void Execute(IJobExecutionContext context)
{
Log.LogTrace("[Enter]" + JobName);
try
{
var updates = DataProvider.GetListOfMISValidationListUpdates();
using (IfxConnection conn = EstablishConnection())
{
RemoveDeletedUpdates(updates.Where(x => x.Action == "Delete"), conn);
InsertNewUpdates(updates.Where(x => x.Action == "Add"), conn);
UpdateMISValidationList(updates.Where(x => x.Action == "Update"), conn);
}
foreach (var update in updates)
{
//DataProvider.SetSentMISValidationUpdate(update);
}
}
catch (Exception ex)
{
Log.LogException(ex);
Log.LogTrace(ex.Message + ". Check error log for more details.");
}
Log.LogTrace("[Exit]" + JobName);
}
public static void Reset(string connectionString)
{
// Ensure that our date format matches that of the .unl files.
Environment.SetEnvironmentVariable("DBDATE", "MDY4/");
using (var cn = new IfxConnection(connectionString)) {
cn.Open();
DeleteRecords(cn, "catalog");
DeleteRecords(cn, "cust_calls");
DeleteRecords(cn, "call_type");
DeleteRecords(cn, "state");
DeleteRecords(cn, "items");
DeleteRecords(cn, "orders");
DeleteRecords(cn, "stock");
DeleteRecords(cn, "manufact");
DeleteRecords(cn, "customer");
LoadRecords(cn, connectionString, "customer");
LoadRecords(cn, connectionString, "manufact");
LoadRecords(cn, connectionString, "stock");
LoadRecords(cn, connectionString, "orders");
LoadRecords(cn, connectionString, "items");
LoadRecords(cn, connectionString, "state");
LoadRecords(cn, connectionString, "call_type");
LoadRecords(cn, connectionString, "cust_calls");
LoadRecords(cn, connectionString, "catalog");
}
Environment.SetEnvironmentVariable("DBDATE", null);
}
public IActionResult Index()
{
DataTable table = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
string query = "SELECT SUM(TotalAmount) FROM Cart";
IfxCommand cmd = new IfxCommand(query, Con);
Con.Open();
int sum = 0;
try
{
IfxDataReader rows = cmd.ExecuteReader();
while (rows.Read())
{
sum = Convert.ToInt32(rows[0]);
}
rows.Close();
cmd.Dispose();
}
catch (IfxException ex)
{
}
finally
{
Con.Close();
}
table.Columns.Add("TotalAmount", typeof(int));
{
table.Rows.Add(sum);
}
}
return(View(table));
}
/// <summary>
/// Crea un objeto Transaccion de la base de datos y la retorna como 'object'
/// </summary>
/// <param name="unaConexion">Una conexion con estado Open</param>
/// <returns>Transaccion como 'object'</returns>
public override object CreaTransaccion(object unaConexion)
{
IfxConnection conn = ((IfxConnection)unaConexion);
IfxTransaction transaccion = conn.BeginTransaction();
return(transaccion);
}
public void BeginTransaction()
{
connection = new IfxConnection(chain);
connection.Open();
transaction = connection.BeginTransaction();
handding_transaction = true;
}
private static async Task <string> TestConnectionInformix()
{
IfxConnection conn = new IfxConnection(_connectionString);
//conn.ConnectionString = _connectionString;
try
{
conn.Open();
string server = conn.Server;
string version = conn.ServerVersion;
string type = conn.ServerType;
conn.Close();
return($@"{{""error"": ""false"", server: ""{server}"" - {type}, ""version"": ""{version}"", ""status"": ""Conectado com sucesso!""}}");
}
catch (IfxException ex)
{
if (ex.InnerException == null)
{
return($@"{{""error"": ""true"", ""server"": ""{conn.Server}"", ""version"": null, ""status"": ""{ex.Message}""}}");
}
else
{
return($@"{{""error"": ""true"", ""server"": ""{conn.Server}"", ""version"": null, ""status"": ""{ex.Message} - Detail: {ex.InnerException.Message}""}}");
}
}
}
public Location(XmlNode locationNode)
{
theVehicle = new Vehicle(locationNode.SelectSingleNode("/location_request/vehicle").InnerXml);
//string sqlConnString = "Host=192.168.1.120;Service=6032;Server=mads_se;User ID=net_book;password=Mickey;Database=/usr/taxi/mads";
string sqlConnString = ConfigurationSettings.AppSettings.Get("MadsOBC");
IfxConnection conn = new IfxConnection(sqlConnString);
conn.Open();
using (IfxCommand ct = conn.CreateCommand())
{
string sqlQuery = "select vh_gps_long,vh_gps_lat from vehicle where vh_nbr=" + theVehicle.VehNbr.ToString();
ct.CommandText = sqlQuery;
IfxDataReader dr = ct.ExecuteReader();
if (dr.Read())
{
theVehicle.X = Convert.ToInt32(dr["vh_gps_long"]);
theVehicle.Y = Convert.ToInt32(dr["vh_gps_lat"]);
}
else
{
theVehicle.X = 0;
theVehicle.Y = 0;
}
}
conn.Close();
}
public static void Reset(string connectionString)
{
// Ensure that our date format matches that of the .unl files.
Environment.SetEnvironmentVariable("DBDATE", "MDY4/");
using (var cn = new IfxConnection(connectionString)) {
cn.Open();
DeleteRecords(cn, "catalog");
DeleteRecords(cn, "cust_calls");
DeleteRecords(cn, "call_type");
DeleteRecords(cn, "state");
DeleteRecords(cn, "items");
DeleteRecords(cn, "orders");
DeleteRecords(cn, "stock");
DeleteRecords(cn, "manufact");
DeleteRecords(cn, "customer");
LoadRecords(cn, connectionString, "customer");
LoadRecords(cn, connectionString, "manufact");
LoadRecords(cn, connectionString, "stock");
LoadRecords(cn, connectionString, "orders");
LoadRecords(cn, connectionString, "items");
LoadRecords(cn, connectionString, "state");
LoadRecords(cn, connectionString, "call_type");
LoadRecords(cn, connectionString, "cust_calls");
LoadRecords(cn, connectionString, "catalog");
}
Environment.SetEnvironmentVariable("DBDATE", null);
}
public static IEnumerable <Table> GetTables(IfxConnection cn)
{
var tables = new List <Table>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = @"
select trim(st.tabname), st.tabtype
from 'informix'.systables st
where st.tabid >= 100
and (st.tabtype = 'T' or st.tabtype = 'V')";
using (var reader = command.ExecuteReader()) {
for (; reader.Read();)
{
string tabName = reader[0] as string;
string tabType = reader[1] as string;
tables.Add(new Table(tabName, null, (tabType == "T") ? TableType.Table : TableType.View));
}
}
return(tables);
}
private static void DeleteRecords(IfxConnection cn, string tableName)
{
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("delete from {0}", tableName);
cmd.ExecuteNonQuery();
}
}
private static void DeleteRecords(IfxConnection cn, string tableName)
{
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("delete from {0}", tableName);
cmd.ExecuteNonQuery();
}
}
public void CreateConnection(string configuration, string sql)
{
this.sql = sql;
myConnection = new IfxConnection();
myConnection.ConnectionString = configuration;
}
private void button1_Click(object sender, RoutedEventArgs e)
{
if (textBoxUserName.Text.Length == 0)
{
errormessage.Text = "Enter a user name.";
textBoxUserName.Focus();
}
else
{
string username = textBoxUserName.Text;
string password = passwordBox1.Password;
DataSet dataSet = new DataSet();
string selectSQL = "Select * from user where username='******' and password='******'";
con = new IfxConnection(cs);
con.Open();
try
{
IfxCommand cmd = new IfxCommand(selectSQL, con);
cmd.CommandType = CommandType.Text;
IfxDataAdapter adapter = new IfxDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
string name = dataSet.Tables[0].Rows[0]["FirstName"].ToString() + " " + dataSet.Tables[0].Rows[0]["LastName"].ToString();
string userType = dataSet.Tables[0].Rows[0]["UserType"].ToString();
if (userType == "Admin")
{
AllUserDetails allUser = new AllUserDetails(name);
allUser.Show();
}
else
{
UserProfile userProfile = new UserProfile();
userProfile.TextBlockName.Text = name;
userProfile.Show();
}
Close();
}
else
{
errormessage.Text = "Sorry! Please enter correct username/password.";
}
}
catch (Exception ex)
{
errormessage.Text = "You need to register first before login.";
}
finally
{
con.Close();
}
}
}
public void Conn()
{
using (IfxConnection connection = new IfxConnection())
{
string sqlcmd = "";
using (IfxCommand cmd = new IfxCommand())
{
}
}
}
public IActionResult Index()
{
string mycmd = "select * from Mobiles";
dt = new DataTable();
try
{
dt = ecomDAL.SelactAll(mycmd);
}
catch (Exception ex)
{
string createTable = "Create table Mobiles (SLNo serial PRIMARY KEY, MobileName nvarchar(100) NULL, Price decimal(18, 2)," +
" Quantity int NULL, Description nvarchar(250) NULL, PicURL nvarchar(250) NULL," +
" Model nvarchar(50) NULL, Features nvarchar(200) NULL, Color nvarchar(20) NULL, SimType nvarchar(10) NULL, ImageFile Blob)";
Boolean status = ecomDAL.DDLOpperation(createTable);
if (status)
{
dt = ecomDAL.SelactAll(mycmd);
}
}
List <Mobiles> list = new List <Mobiles>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Mobiles mob = new Mobiles();
mob.SLNo = Convert.ToInt32(dt.Rows[i]["SLNo"]);
mob.MobileName = dt.Rows[i]["MobileName"].ToString();
mob.Price = Convert.ToDecimal(dt.Rows[i]["Price"]);
mob.Description = dt.Rows[i]["Description"].ToString();
mob.PicURL = dt.Rows[i]["PicURL"].ToString();
list.Add(mob);
// Downloading the photo from databse and storing it on the disk
// To save that newly uploaded image to Disk location inside wwwroot/Images folder
var downloads = Path.Combine(hostingEnvironment.WebRootPath, "DownloadImages");
var imagePath = Path.Combine(downloads, mob.PicURL);
FileInfo file = new FileInfo(imagePath);
if (file.Exists)
{
file.Delete();
}
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
string selectImage = "select LOTOFILE (imagefile, " + "'" + imagePath + "!'" + ", 'client') from mobiles where slno = ?";
IfxCommand selectImagecmd = new IfxCommand(selectImage, Con);
selectImagecmd.Parameters.Add("slno", IfxType.Serial).Value = mob.SLNo;
selectImagecmd.ExecuteScalar();
Con.Close();
}
}
return(View(list));
}
public static int RunSP(string spName, IfxConnection conn)
{
IfxCommand cmd = new IfxCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
return(cmd.ExecuteNonQuery());
}
public override object CreaConexionClon()
{
IfxConnection conn = new IfxConnection(this.Conexion.ConnectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return(conn);
}
/// <summary>
/// Establece la conexión a utilizar
/// </summary>
/// <param name="conn"></param>
public void AsignarConexion(object conn)
{
if (conn is IfxConnection)
{
conexion = conn as IfxConnection;
}
else if (conexion != null)
{
conexion.Dispose();
conexion = null;
}
}
public static IEnumerable<Table> GetTables(IfxConnection cn)
{
var tables = new List<Table>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = @"
select trim(st.tabname), st.tabtype
from 'informix'.systables st
where st.tabid >= 100
and (st.tabtype = 'T' or st.tabtype = 'V')";
using (var reader = command.ExecuteReader()) {
for (; reader.Read(); ) {
string tabName = reader[0] as string;
string tabType = reader[1] as string;
tables.Add(new Table(tabName, null, (tabType == "T") ? TableType.Table : TableType.View));
}
}
return tables;
}
public static IEnumerable<Column> GetColumns(Table table, IfxConnection cn)
{
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = @"
select trim(sc.colname), sc.coltype, sc.collength
from 'informix'.systables st, 'informix'.syscolumns sc
where st.tabname = ?
and (st.tabtype = 'T' or st.tabtype = 'V')
and st.tabid >= 100
and st.tabid = sc.tabid";
command.Parameters.Add("st.tabname", table.ActualName);
var informixColumnInfos = new List<InformixColumnInfo>();
using (var reader = command.ExecuteReader()) {
for (; reader.Read(); ) {
string colName = reader[0] as string;
int colType = Convert.ToInt32(reader[1]);
int colLength = Convert.ToInt32(reader[2]);
var informixColumnInfo = InformixColumnInfoCreator.CreateColumnInfo(
colName,
colType,
colLength);
informixColumnInfos.Add(informixColumnInfo);
}
}
var results =
from c in informixColumnInfos
select new Column(c.Name, table, c.IsAutoincrement, c.DbType, c.Capacity);
return results;
}
public List<FACSHeader> GetFacility(string tel_num)
{
try
{
List<FACSHeader> facilities = new List<FACSHeader>();
using (IfxConnection ifxcon = new IfxConnection(INFORMIXDB))
{
DataSet ds = new DataSet();
// Initialize Command and DataAdapter
IfxCommand cmd;
IfxDataAdapter da;
// Open Connection and call stored procedure.
ifxcon.Open();
cmd = new IfxCommand("ht_one_facs_dash", ifxcon);
cmd.CommandType = CommandType.StoredProcedure;
// Stored procedure parameters.
cmd.Parameters.Add("p_tel_no ", IfxType.VarChar, 256).Value = tel_num;
// Fill Dataset using DataAdapter
da = new IfxDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
FACSHeader fac = new FACSHeader();
fac.Tel_Num = dr[0].ToString().Trim();
fac.Status = dr[1].ToString().Trim();
fac.Remarks = dr[2].ToString().Trim();
fac.Cable_Pair = dr[3].ToString().Trim();
fac.Port = dr[4].ToString().Trim();
facilities.Add(fac);
}
ifxcon.Close();
}
return facilities;
}
catch (Exception)
{
throw;
}
}
public List<FACSDetail> GetFacilityDetail(string tel_num)
{
try
{
List<FACSDetail> facDetails = new List<FACSDetail>();
using (IfxConnection ifxcon = new IfxConnection(INFORMIXDB))
{
DataSet ds = new DataSet();
// Initialize Command and DataAdapter
IfxCommand cmd;
IfxDataAdapter da;
// Open Connection and call stored procedure.
ifxcon.Open();
cmd = new IfxCommand("ht_one_facs", ifxcon);
cmd.CommandType = CommandType.StoredProcedure;
// Stored procedure parameters.
cmd.Parameters.Add("p_tel_no ", IfxType.VarChar, 256).Value = tel_num;
// Fill Dataset using DataAdapter
da = new IfxDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
FACSDetail facD = new FACSDetail();
facD.Tel_Num = dr[0].ToString().Trim();
facD.Status = dr[1].ToString().Trim();
facD.Native_ACO = dr[2].ToString().Trim();
facD.Current_ACO = dr[3].ToString().Trim();
facD.Switch_Type = dr[4].ToString().Trim();
facD.CLLI = dr[5].ToString().Trim();
facD.Terminal = dr[6].ToString().Trim();
facD.Port = dr[7].ToString().Trim();
facD.Facilities = dr[8].ToString().Trim();
facD.XBox = dr[9].ToString().Trim();
facD.InterIsland_PIC = dr[11].ToString().Trim();
facD.InterNational_PIC = dr[10].ToString().Trim();
facD.Address = dr[12].ToString().Trim();
facD.Remarks = dr[13].ToString().Trim();
facD.Service_Type = dr[14].ToString().Trim();
facD.MITS = dr[15].ToString().Trim();
facD.SR_TN = dr[16].ToString().Trim();
facDetails.Add(facD);
}
ifxcon.Close();
}
return facDetails;
}
catch (Exception)
{
throw;
}
}
public Pic GetPic(string id, string tn, int index)
{
Pic selectedPic = new Pic();
try
{
List<Pic> pics = new List<Pic>();
using (OracleConnection con = new OracleConnection(KENANDB))
{
DataSet ds = new DataSet();
// Initialize Command and DataAdapter.
OracleCommand cmd;
OracleDataAdapter da;
// Open Connection and call stored procedure.
con.Open();
cmd = new OracleCommand("ARBOR.HT_ONE_PKG.GET_PIC", con);
cmd.CommandType = CommandType.StoredProcedure;
// Stored procedure parameters.
cmd.Parameters.Add("iAccountNo", OracleType.VarChar, 20).Value = id;
cmd.Parameters["iAccountNo"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("oPicInfo", OracleType.Cursor).Direction = ParameterDirection.Output;
// Fill Dataset using DataAdapter
da = new OracleDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Pic pic = new Pic();
pic.subscr_no = dr[0].ToString().Trim();
pic.tn = dr[1].ToString().Trim();
pic.formattedTn = dr[2].ToString().Trim();
pic.mainland = dr[3].ToString().Trim();
pic.interisland = dr[4].ToString().Trim();
pic.international = dr[5].ToString().Trim();
pics.Add(pic);
}
selectedPic = pics.Where(a => a.tn == tn).Single<Pic>();
selectedPic.index = index;
con.Close();
}
}
catch (Exception)
{
throw;
}
try
{
List<NBPic> facDetails = new List<NBPic>();
using (IfxConnection ifxcon = new IfxConnection(INFORMIXDB))
{
DataSet ds = new DataSet();
// Initialize Command and DataAdapter
IfxCommand cmd;
IfxDataAdapter da;
// Open Connection and call stored procedure.
ifxcon.Open();
cmd = new IfxCommand("ht_one_facs", ifxcon);
cmd.CommandType = CommandType.StoredProcedure;
// Stored procedure parameters.
cmd.Parameters.Add("p_tel_no ", IfxType.VarChar, 256).Value = tn;
// Fill Dataset using DataAdapter
da = new IfxDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
NBPic facD = new NBPic();
facD.InterIsland_PIC = dr[11].ToString().Trim();
facD.InterNational_PIC = dr[10].ToString().Trim();
facDetails.Add(facD);
}
ifxcon.Close();
}
selectedPic.NBPics = facDetails;
}
catch (Exception)
{
throw;
}
return selectedPic;
}
public static IEnumerable<ForeignKey> GetForeignKeys(Table table, IfxConnection cn)
{
var fkqis = new List<ForeignKeyQueryInfo>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select st.tabid from 'informix'.systables st where st.tabname = ?";
command.Parameters.Add("st.tabname", table.ActualName);
int? tabid = null;
try {
tabid = command.ExecuteScalar() as int?;
}
catch (Exception) {
}
if (tabid.HasValue) {
command.CommandText = @"
select
si.part1, si.part2, si.part3, si.part4,
si.part5, si.part6, si.part7, si.part8,
si.part9, si.part10, si.part11, si.part12,
si.part13, si.part14, si.part15, si.part16,
trim(st.tabname) as fktabname,
si.tabid as fktabid,
trim(rt.tabname) as pktabname,
rc.tabid as pktabid,
sr.primary as pkconstraintid
from
'informix'.systables st,
'informix'.sysconstraints sc,
'informix'.sysindexes si,
'informix'.sysreferences sr,
'informix'.systables rt,
'informix'.sysconstraints rc
where
st.tabid = ?
and st.tabid = sc.tabid
and sc.constrtype = 'R'
and sc.constrid = sr.constrid
and sc.tabid = si.tabid
and sc.idxname = si.idxname
and rt.tabid = sr.ptabid
and rc.tabid = sr.ptabid
and sr.primary = rc.constrid";
command.Parameters.Clear();
command.Parameters.Add("st.tabid", tabid);
using (var reader = command.ExecuteReader()) {
for (; reader.Read(); ) {
var fkqi = new ForeignKeyQueryInfo();
fkqi.FKTabName = reader["fktabname"] as string;
fkqi.FKTabId = Convert.ToInt32(reader["fktabid"]);
fkqi.PKTabName = reader["pktabname"] as string;
fkqi.PKTabId = Convert.ToInt32(reader["pktabid"]);
fkqi.PKConstraintId = Convert.ToInt32(reader["pkconstraintid"]);
for (int i = 0; i < 16; i++) {
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0) break;
fkqi.FKParts.Add(part);
}
fkqis.Add(fkqi);
}
}
command.CommandText = @"
select
part1, part2, part3, part4,
part5, part6, part7, part8,
part9, part10, part11, part12,
part13, part14, part15, part16
from
'informix'.sysindexes si,
'informix'.sysconstraints sc
where
si.tabid = sc.tabid
and si.idxname = sc.idxname
and sc.constrid = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.constrid", null);
foreach (var fkqi in fkqis) {
command.Parameters["sc.constrid"].Value = fkqi.PKConstraintId;
using (var reader = command.ExecuteReader()) {
for (; reader.Read(); ) {
for (int i = 0; i < 16; i++) {
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0) break;
fkqi.PKParts.Add(part);
}
}
}
}
command.CommandText = "select trim(sc.colname) from 'informix'.syscolumns sc where sc.tabid = ? and sc.colno = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", null);
command.Parameters.Add("sc.colno", null);
foreach (var fkqi in fkqis) {
command.Parameters["sc.tabid"].Value = fkqi.FKTabId;
foreach (int part in fkqi.FKParts) {
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
fkqi.FKColumnNames.Add(columnName);
}
command.Parameters["sc.tabid"].Value = fkqi.PKTabId;
foreach (int part in fkqi.PKParts) {
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
fkqi.PKColumnNames.Add(columnName);
}
}
}
var results =
from fkqi in fkqis
select new ForeignKey(
detailTable: new ObjectName(null, fkqi.FKTabName),
columns: fkqi.FKColumnNames,
masterTable: new ObjectName(null, fkqi.PKTabName),
masterColumns: fkqi.PKColumnNames);
return results.ToArray();
}
private static void LoadRecords(IfxConnection cn, string connectionString, string tableName)
{
DatabaseSchemaHelper databaseSchemaHelper = new DatabaseSchemaHelper(connectionString);
var schemaColumns = databaseSchemaHelper.GetColumns(tableName);
string[] columnNames = (from schemaColumn in schemaColumns select schemaColumn.ActualName).ToArray();
string joinedColumnNames = string.Join(", ", columnNames);
int numColumns = columnNames.Length;
// Load the .unl file (renamed to .txt and added to the project as a resource file).
string fileContents = Resources.Resources.ResourceManager.GetString(tableName);
string[] lines = fileContents.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
string[] placeHolders = new string[numColumns];
for (int i = 0; i < numColumns; i++) {
placeHolders[i] = "?";
}
string joinedPlaceHolders = string.Join(", ", placeHolders);
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, joinedColumnNames, joinedPlaceHolders);
for (int i = 0; i < numColumns; i++) {
cmd.Parameters.Add(columnNames[i], null);
}
foreach (var line in lines) {
string[] values = line.Split('|');
// "value1|value2|value3|" after splitting on '|' yields "value1", "value2", value3, ""
// i.e. there is an empty extra value at the end of the array.
// And, there is usually a blank line at the end of the file.
if (values.Length != (numColumns + 1)) {
break;
}
for (int i = 0; i < numColumns; i++) {
if (!string.IsNullOrEmpty(values[i])) {
// This is a temporary hack for the "lead_time" column of the "manufact" table.
// We should lookup the coltype and collength of the column
// using tableName and schemaColumns[i].ActualName. Then, if coltype
// is 14 (INTERVAL) then we need to look at collength to determine the
// detailed type of INTERVAL. Then, create a new instance of IfxTimeSpan
// or IfxMonthSpan and parse the string, values[i]. There may be other column types
// that need to be handled specially. I guess we need to test lots more
// different column types.
//
// SYSCOLUMNS
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_025.htm
//
// Storing Column Length
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_027.htm
//
// Using the Informix System Catalogs
// http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
//
// collength = 836 = 0x344
// length = 3
// first_qualifier = 4
// last_qualifier = 4
//
if (tableName == "manufact" && schemaColumns[i].ActualName == "lead_time") {
cmd.Parameters[i].Value = new IfxTimeSpan(Convert.ToInt32(values[i]), IfxTimeUnit.Day);
}
else {
cmd.Parameters[i].Value = values[i];
}
}
else {
cmd.Parameters[i].Value = DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}
// http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
// http://stackoverflow.com/questions/320045/how-do-i-get-constraint-details-from-the-name-in-informix
public static Key GetPrimaryKey(Table table, IfxConnection cn)
{
var columnNames = new List<string>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select st.tabid from 'informix'.systables st where st.tabname = ?";
command.Parameters.Add("st.tabname", table.ActualName);
int? tabid = null;
try {
tabid = command.ExecuteScalar() as int?;
}
catch (Exception) {
}
if (tabid.HasValue) {
command.CommandText = @"
select
si.part1, si.part2, si.part3, si.part4,
si.part5, si.part6, si.part7, si.part8,
si.part9, si.part10, si.part11, si.part12,
si.part13, si.part14, si.part15, si.part16
from
'informix'.sysconstraints sc,
'informix'.sysindexes si
where
sc.tabid = ?
and sc.constrtype = 'P'
and si.tabid = sc.tabid
and si.idxname = sc.idxname";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", tabid);
var parts = new List<int>();
using (var reader = command.ExecuteReader()) {
if (reader.Read()) {
for (int i = 0; i < 16; i++) {
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0) break;
parts.Add(part);
}
}
}
command.CommandText = "select trim(sc.colname) from 'informix'.syscolumns sc where sc.tabid = ? and sc.colno = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", tabid);
command.Parameters.Add("sc.colno", null);
foreach (int part in parts) {
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
columnNames.Add(columnName);
}
}
return new Key(columnNames);
}
