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 ActionResult Delete(int cartID)
{
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
string query = "DELETE FROM Cart Where CartID = ?";
IfxCommand cmd = new IfxCommand(query, Con);
cmd.Parameters.Add("cartid", IfxType.Serial).Value = cartID;
cmd.ExecuteNonQuery();
Con.Close();
}
return(RedirectToAction("Index"));
}
public ActionResult Delete(int slno)
{
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
string query = "DELETE FROM Mobiles Where SLNo = ?";
IfxCommand cmd = new IfxCommand(query, Con);
cmd.Parameters.Add("slno", IfxType.Serial).Value = slno;
cmd.ExecuteNonQuery();
Con.Close();
}
return(RedirectToAction("Index"));
}
/// <summary>
/// Inicializa la conexión establecida
/// </summary>
public void AbrirConexion()
{
if (conexion == null)
{
conexion = new IfxConnection(cadenaConexion);
}
if (conexion.State == ConnectionState.Closed)
{
conexion = new IfxConnection(cadenaConexion);
conexion.Open();
}
}
public override object CreaConexion(string unaCadenaConexion)
{
IfxConnection conexion = new IfxConnection(unaCadenaConexion);
if (conexion == null)
{
throw new Exception("Falla de conexion a la base de datos");
}
if (conexion.State == ConnectionState.Open)
{
conexion.Open();
}
return(conexion);
}
public bool Delete(string sql)
{
String _connectionString =
"Server=ol_svr_custom;" +
"Host=localhost;" +
"Service=turbo;" +
"Database=registration;" +
"User ID=informix;" +
"Password=123456;" +
"Client Locale=ru_ru.CP1251;" +
"Database Locale=ru_ru.915;" +
"Max Pool Size=500;" +
"Pooling=True;" +
"Protocol=olsoctcp;" +
"Connection Lifetime=1200;" +
"Connection Timeout=1;";
try
{
IfxConnection _Connection = new IfxConnection()
{
ConnectionString = _connectionString
};
_Connection.Open();
try
{
IfxCommand _command = new IfxCommand
{
Connection = _Connection,
CommandText = sql
};
_command.ExecuteNonQuery();
if (_Connection != null)
{
_Connection.Close();
}
return(true);
}
catch
{
return(false);
}
}
catch
{
return(false);
}
}
public IActionResult Index()
{
DataTable table = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
try
{
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM cart", Con);
ifx.Fill(table);
}
catch (Exception ex)
{
string createTable = "Create table cart (cartid 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, 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 cart", Con);
ifx.Fill(table);
}
finally
{
Con.Close();
}
}
List <Cart> cartList = new List <Cart>();
for (int i = 0; i < table.Rows.Count; i++)
{
Cart cart = new Cart();
cart.CartID = Convert.ToInt32(table.Rows[i]["CartID"]);
cart.SLNo = Convert.ToInt32(table.Rows[i]["SLNo"]);
cart.MobileName = table.Rows[i]["MobileName"].ToString();
cart.Description = table.Rows[i]["Description"].ToString();
cart.PicURL = table.Rows[i]["PicURL"].ToString();
cart.Model = table.Rows[i]["Model"].ToString();
cart.Features = table.Rows[i]["Features"].ToString();
cart.Color = table.Rows[i]["Color"].ToString();
cart.SimType = table.Rows[i]["SimType"].ToString();
cart.Price = Convert.ToDecimal(table.Rows[i]["Price"]);
cart.Quantity = Convert.ToInt32(table.Rows[i]["Quantity"]);
cart.TotalAmount = Convert.ToDecimal(table.Rows[i]["TotalAmount"]);
cartList.Add(cart);
}
return(View(cartList));
}
private void InsertUser(object sender, RoutedEventArgs e)
{
errormessage.Text = "";
int rows = 0;
string insertUserSQL = "Insert into User (firstname, lastname, email, username, usertype, password, address) " +
"values('" + firstname + "','" + lastname + "','" + email + "','" + username + "','" + usertype + "','" + password + "','" + address + "')";
con = new IfxConnection(cs);
con.Open();
try
{
IfxCommand cmd = new IfxCommand(insertUserSQL, con);
cmd.CommandType = CommandType.Text;
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string createTableSQL = "create table user (userid serial PRIMARY KEY, firstname varchar(20), lastname varchar(20), " +
"email varchar(30), username varchar(20), usertype varchar(20), " +
"password varchar(30), address varchar(150))";
IfxCommand cmd1 = new IfxCommand(createTableSQL, con);
cmd1.CommandType = CommandType.Text;
cmd1.ExecuteNonQuery();
IfxCommand cmd2 = new IfxCommand(insertUserSQL, con);
cmd2.CommandType = CommandType.Text;
rows = cmd2.ExecuteNonQuery();
}
finally
{
con.Close();
}
if (rows > 0)
{
if (usertype.Equals("Admin"))
{
errormessage.Text = "Admin registered successfully.";
}
else
{
errormessage.Text = "User registered successfully.";
}
}
else
{
errormessage.Text = "Registeration failed.";
}
}
public ActionResult Create(ProductModel productModel)
{
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
string query = "INSERT INTO Product (productname, price, count) VALUES(?, ?, ?)";
IfxCommand cmd = new IfxCommand(query, Con);
cmd.Parameters.Add("productname", IfxType.VarChar).Value = productModel.ProductName;
cmd.Parameters.Add("price", IfxType.Decimal).Value = productModel.Price;
cmd.Parameters.Add("count", IfxType.Int).Value = productModel.Count;
cmd.ExecuteNonQuery();
}
return(RedirectToAction("Index"));
}
public ActionResult Edit(ProductModel productModel)
{
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
string query = "UPDATE Product SET productname = ? , price= ? , count = ? Where productid = ?";
IfxCommand cmd = new IfxCommand(query, Con);
cmd.Parameters.Add("productname", IfxType.VarChar).Value = productModel.ProductName;
cmd.Parameters.Add("price", IfxType.Decimal).Value = productModel.Price;
cmd.Parameters.Add("count", IfxType.Int).Value = productModel.Count;
cmd.Parameters.Add("productid", IfxType.Serial).Value = productModel.ProductID;
cmd.ExecuteNonQuery();
}
return(RedirectToAction("Index"));
}
/// <summary>
/// Establishes connection with the server
/// </summary>
/// <param name="connectionString"></param>
/// <returns>bool</returns>
public bool EstablishConnection(String connectionString)
{
try
{
_connection = new IfxConnection(connectionString);
_connection.Open();
}
catch (Exception excep)
{
WriteLine(String.Format("Connection Error: {0}", excep.Message));
return(false);
}
return(true);
}
//EJECUTA UN QUERY
public static DataTable ExecuteQuery(IfxCommand command, string chain)
{
DataTable result = new DataTable();
IfxConnection conexionIFX = new IfxConnection();
IBM.Data.Informix.IfxDataAdapter datoIFX = default(IBM.Data.Informix.IfxDataAdapter);
conexionIFX = new IfxConnection(chain);
conexionIFX.Open();
command.Connection = conexionIFX;
datoIFX = new IfxDataAdapter();
datoIFX.SelectCommand = command;
datoIFX.Fill(result);
datoIFX.Dispose();
conexionIFX.Close();
return(result);
}
public static IfxConnection connect()
{
string connection = Config.connStr;
//string connection = ConfigurationManager.ConnectionStrings["Connect"].ConnectionString;
IfxConnection con = new IfxConnection(connection);
if (con.State == ConnectionState.Open)
{
con.Close();
}
else
{
con.Open();
}
return(con);
}
public static IfxConnection connect()
{
string connection = "DataBase=webapp;Server=ol_informix1410_9;User ID = informix; Password=Rinvoke1;";
//string connection = ConfigurationManager.ConnectionStrings["Connect"].ConnectionString;
IfxConnection con = new IfxConnection(connection);
if (con.State == ConnectionState.Open)
{
con.Close();
}
else
{
con.Open();
}
return(con);
}
/// <summary>
/// The demo app needs to be invoked as EXE -conn "connection string" -log "log filename"
/// Eg: Transactions.exe //This will pick the connection string from ConnInfo.xml
/// OR
/// Transactions.exe -conn "Database=perf;Server=localhost:9092;User ID=informix;Password=informix123;"
/// OR
/// Transactions.exe -conn "Database=perf;Server=localhost:9092;User ID=informix;Password=informix123;" -log log.txt
/// </summary>
/// <param name="args"></param>
public static void Main(string[] args)
{
if (args.Length != 0)
{
try
{
IfxConnection conn = new IfxConnection(args[1]);
try
{
conn.Open();
string server = conn.ServerType;
string version = conn.ServerVersion;
string type = conn.ServerType;
conn.Close();
Console.Write($"{server}\n{type} versão {version}\n\nConectado com sucesso!");
}
catch (Exception ex)
{
if (ex.InnerException == null)
{
Console.Write($"{conn.ServerType}\nErro ao tentar conectar: \n{ex.Message}");
}
else
{
Console.Write($"{conn.ServerType}\nErro ao tentar conectar: \n{ex.Message}\nDetalhe: {ex.InnerException.Message}");
}
}
}
catch (Exception ex)
{
if (ex.InnerException == null)
{
Console.Write(ex.Message);
}
else
{
Console.Write(ex.InnerException.Message);
}
}
}
else
{
Console.Write("É necessário passar a Connection String");
}
}
private IfxConnection EstablishConnection()
{
try
{
var connection = new IfxConnection(Settings.MISDBConnectionString);
connection.DatabaseLocale = "en_US.CP1252";
connection.ClientLocale = "en_US.CP1252";
connection.Open();
return(connection);
}
catch (Exception ex)
{
Log.LogException(ex);
Log.LogTrace(ex.Message + ". Check error log for more details.");
return(null);
}
}
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 createTable = "Create table Product (productid serial PRIMARY KEY, productname varchar(50), price decimal(18,2), count int)";
IfxCommand cmd = new IfxCommand(createTable, Con);
cmd.ExecuteNonQuery();
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Product", Con);
ifx.Fill(table);
}
}
return(View(table));
}
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 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 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;
}
/// <summary>
/// 运行存储过程,有Para。返回影响的行。
/// </summary>
/// <param name="spName"></param>
/// <param name="conn"></param>
/// <param name="paras"></param>
public static int RunSP(string spName, Paras paras, IfxConnection conn)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
IfxCommand cmd = new IfxCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
// 加入参数
foreach (Para para in paras)
{
IfxParameter myParameter = new IfxParameter(para.ParaName, para.val);
myParameter.Size = para.Size;
cmd.Parameters.Add(myParameter);
}
int i = cmd.ExecuteNonQuery();
conn.Close();
return(i);
}
public ActionResult Edit(Mobiles mobile)
{
// To create a Unique file name and URL everytime when User upload a new picture
string ImageFileName = Path.GetFileNameWithoutExtension(mobile.ImageFile.FileName);
string ImageFileExtension = Path.GetExtension(mobile.ImageFile.FileName);
string FinalImageName = ImageFileName + DateTime.Now.ToString("yymmssfff") + ImageFileExtension;
mobile.PicURL = FinalImageName;
// To save that newly uploaded image to Disk location inside wwwroot/Images folder
var uploads = Path.Combine(hostingEnvironment.WebRootPath, "Images");
var imagePath = Path.Combine(uploads, FinalImageName);
mobile.ImageFile.CopyTo(new FileStream(imagePath, FileMode.Create));
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
string query = "UPDATE Mobiles SET MobileName = ? , Price= ? , Quantity = ? , Description = ? , PicURL = ? , Model = ? , " +
"Features = ? , Color = ? , SimType = ? Where SLNo = ?";
IfxCommand cmd = new IfxCommand(query, Con);
cmd.Parameters.Add("mobilename", IfxType.VarChar).Value = mobile.MobileName;
cmd.Parameters.Add("price", IfxType.Decimal).Value = mobile.Price;
cmd.Parameters.Add("quantity", IfxType.Int).Value = mobile.Quantity;
cmd.Parameters.Add("description", IfxType.VarChar).Value = mobile.Description;
cmd.Parameters.Add("picurl", IfxType.VarChar).Value = mobile.PicURL;
cmd.Parameters.Add("model", IfxType.VarChar).Value = mobile.Model;
cmd.Parameters.Add("features", IfxType.VarChar).Value = mobile.Features;
cmd.Parameters.Add("color", IfxType.VarChar).Value = mobile.Color;
cmd.Parameters.Add("simtype", IfxType.VarChar).Value = mobile.SimType;
cmd.Parameters.Add("slno", IfxType.Serial).Value = mobile.SLNo;
cmd.ExecuteNonQuery();
Con.Close();
}
return(RedirectToAction("Index"));
}
public void OpenConnection()
{
myConnection.Open();
}
public ActionResult CreateOrder()
{
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
DataTable cartTable = new DataTable();
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM cart", Con);
ifx.Fill(cartTable);
Con.Close();
List <MyOrders> orderList = new List <MyOrders>();
for (int i = 0; i < cartTable.Rows.Count; i++)
{
MyOrders order = new MyOrders();
order.PurchaseDate = DateTime.UtcNow.ToString();
order.SLNo = Convert.ToInt32(cartTable.Rows[i]["SLNo"]);
order.MobileName = cartTable.Rows[i]["MobileName"].ToString();
order.Description = cartTable.Rows[i]["Description"].ToString();
order.PicURL = cartTable.Rows[i]["PicURL"].ToString();
order.Model = cartTable.Rows[i]["Model"].ToString();
order.Features = cartTable.Rows[i]["Features"].ToString();
order.Color = cartTable.Rows[i]["Color"].ToString();
order.SimType = cartTable.Rows[i]["SimType"].ToString();
order.Price = Convert.ToDecimal(cartTable.Rows[i]["Price"]);
order.Quantity = Convert.ToInt32(cartTable.Rows[i]["Quantity"]);
order.TotalAmount = Convert.ToDecimal(cartTable.Rows[i]["TotalAmount"]);
orderList.Add(order);
}
foreach (MyOrders order in orderList)
{
Con.Open();
int SLNo = order.SLNo;
int availableQuantity = 0;
string selectMobileDetails = "select Quantity from mobiles where SLNo = ?";
IfxCommand cmd = new IfxCommand(selectMobileDetails, Con);
cmd.Parameters.Add("slno", IfxType.Serial).Value = SLNo;
try
{
IfxDataReader rows = cmd.ExecuteReader();
while (rows.Read())
{
availableQuantity = Convert.ToInt32(rows[0]);
}
rows.Close();
}
catch (IfxException ex)
{
Con.Close();
order.ErrorMessage = "Error : " + ex.Message;
}
if (order.Quantity > availableQuantity)
{
Con.Close();
order.ErrorMessage = "Cannot purchase " + order.Quantity + " quantities, available quantities are : " + availableQuantity;
}
else
{
int newMobileQuantity = availableQuantity - order.Quantity;
string updateMobileQuantity = "UPDATE Mobiles SET Quantity = ? Where SLNo = ?";
IfxCommand cmd1 = new IfxCommand(updateMobileQuantity, Con);
cmd1.Parameters.Add("quantity", IfxType.Int).Value = newMobileQuantity;
cmd1.Parameters.Add("slno", IfxType.Serial).Value = SLNo;
cmd1.ExecuteNonQuery();
try
{
insertNewOrder(Con, order);
}
catch (Exception ex)
{
string createOrderTable = "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 cmd2 = new IfxCommand(createOrderTable, Con);
cmd2.ExecuteNonQuery();
insertNewOrder(Con, order);
}
finally
{
Con.Close();
order.ErrorMessage = "Purchase successful";
}
}
Con.Close();
}
Con.Open();
string delQuery = "DELETE FROM Cart";
IfxCommand delCmd = new IfxCommand(delQuery, Con);
delCmd.ExecuteNonQuery();
Con.Close();
}
return(RedirectToAction("Index"));
}
// 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));
}
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 ActionResult Create(Mobiles mobilesModel)
{
// To create a Unique file name and URL everytime when User upload a new picture
string ImageFileName = Path.GetFileNameWithoutExtension(mobilesModel.ImageFile.FileName);
string ImageFileExtension = Path.GetExtension(mobilesModel.ImageFile.FileName);
string FinalImageName = ImageFileName + DateTime.Now.ToString("yymmssfff") + ImageFileExtension;
mobilesModel.PicURL = FinalImageName;
// To save that newly uploaded image to Disk location inside wwwroot/Images folder
var uploads = Path.Combine(hostingEnvironment.WebRootPath, "Images");
var imagePath = Path.Combine(uploads, FinalImageName);
FileStream fileStream = new FileStream(imagePath, FileMode.Create);
mobilesModel.ImageFile.CopyTo(fileStream);
fileStream.Close();
//string fileByteArray = null;
//var fileBytes = 0;
/*
* if (mobilesModel.ImageFile.Length > 0)
* {
* using (var ms = new MemoryStream())
* {
* //mobilesModel.ImageFile.CopyTo(ms);
* //var fileBytes = ms.ToArray();
* //fileByteArray = Convert.ToBase64String();
* // act on the Base64 data
*
* // To save the newly added Mobile and the Image disk imagePath to Database table (Mobiles)
*/
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
// Insert the form data into mobiles table but not the picture
string query = "INSERT INTO Mobiles (MobileName, Price, Quantity, Description, PicURL, Model, Features, Color, SimType) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
IfxCommand cmd = new IfxCommand(query, Con);
cmd.Parameters.Add("mobilename", IfxType.VarChar).Value = mobilesModel.MobileName;
cmd.Parameters.Add("price", IfxType.Decimal).Value = mobilesModel.Price;
cmd.Parameters.Add("quantity", IfxType.Int).Value = mobilesModel.Quantity;
cmd.Parameters.Add("description", IfxType.VarChar).Value = mobilesModel.Description;
cmd.Parameters.Add("picurl", IfxType.VarChar).Value = mobilesModel.PicURL;
cmd.Parameters.Add("model", IfxType.VarChar).Value = mobilesModel.Model;
cmd.Parameters.Add("features", IfxType.VarChar).Value = mobilesModel.Features;
cmd.Parameters.Add("color", IfxType.VarChar).Value = mobilesModel.Color;
cmd.Parameters.Add("simtype", IfxType.VarChar).Value = mobilesModel.SimType;
cmd.ExecuteNonQuery();
cmd.Dispose();
// Getting the latest inserted row's slno to insert the picture in the same row
string selQuery = "Select max(slno) from Mobiles";
IfxCommand selcmd = new IfxCommand(selQuery, Con);
int serialnumber = -1;
try
{
IfxDataReader rows = selcmd.ExecuteReader();
while (rows.Read())
{
serialnumber = Convert.ToInt32(rows[0]);
}
rows.Close();
selcmd.Dispose();
string updatePicQuery = "update mobiles set(imagefile) = (Filetoblob(" + "'" + imagePath + "'" + ", 'client', 'mobiles', 'imagefile')) where slno = ?";
IfxCommand insertPiccmd = new IfxCommand(updatePicQuery, Con);
insertPiccmd.Parameters.Add("slno", IfxType.Int).Value = serialnumber;
insertPiccmd.ExecuteNonQuery();
insertPiccmd.Dispose();
// Delete the temprary created image file from Disk
FileInfo file = new FileInfo(imagePath);
if (file.Exists)
{
file.Delete();
}
}
catch (IfxException ex)
{
}
finally
{
Con.Close();
}
}
return(RedirectToAction("Index"));
}
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 void Submit_Click(object sender, RoutedEventArgs e)
{
if (textBoxEmail.Text.Length == 0)
{
errormessage.Text = "Enter an email.";
textBoxEmail.Focus();
}
else if (!Regex.IsMatch(textBoxEmail.Text, @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"))
{
errormessage.Text = "Enter a valid email.";
textBoxEmail.Select(0, textBoxEmail.Text.Length);
textBoxEmail.Focus();
}
else
{
string firstname = textBoxFirstName.Text;
string lastname = textBoxLastName.Text;
string email = textBoxEmail.Text;
string username = textBoxUserName.Text;
string usertype = comboBoxUserType.Text;
string password = passwordBox1.Password;
if (username.Length == 0)
{
errormessage.Text = "Enter username.";
textBoxUserName.Focus();
}
else
{
if (passwordBox1.Password.Length == 0)
{
errormessage.Text = "Enter password.";
passwordBox1.Focus();
}
else if (passwordBoxConfirm.Password.Length == 0)
{
errormessage.Text = "Enter Confirm password.";
passwordBoxConfirm.Focus();
}
else if (passwordBox1.Password != passwordBoxConfirm.Password)
{
errormessage.Text = "Confirm password must be same as password.";
passwordBoxConfirm.Focus();
}
else
{
if (usertype.Length == 0)
{
errormessage.Text = "Select user type.";
comboBoxUserType.Focus();
}
errormessage.Text = "";
string address = textBoxAddress.Text;
con = new IfxConnection(cs);
con.Open();
IfxCommand cmd = new IfxCommand("Insert into User (firstname,lastname,email,username,usertype,password,address) values('" + firstname + "','" + lastname + "','" + email + "','" + username + "','" + usertype + "','" + password + "','" + address + "')", con);
cmd.CommandType = CommandType.Text;
int rows = cmd.ExecuteNonQuery();
con.Close();
if (rows > 0)
{
if (usertype.Equals("Admin"))
{
errormessage.Text = "Admin registered successfully.";
}
else
{
errormessage.Text = "User registered successfully.";
}
}
else
{
errormessage.Text = "Registeration Failed";
}
}
}
}
}
// 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);
}
public override void RunCommand(object sender)
{
var engine = (Core.Automation.Engine.AutomationEngineInstance)sender;
string query = "select * from alumno";
string connectionString;
connectionString = "Database = mi_prueba_base_datos; Host = 127.0.0.1; Server = dr_informix1210_4; Service = 10987; UID = informix; Password = A3.cY2zd8*CBjTFM;";//Protocol = onsoctcp;
string ConnectionString = "Host=" + "127.0.0.1" + "; " +
"Service=" + "10987" + "; " +
"Server=" + "dr_informix1210_4" + "; " +
"Database=" + "mi_prueba_base_datos" + "; " +
"User Id=" + "informix" + "; " +
"Password="******"A3.cY2zd8*CBjTFM" + "; ";
//Can add other DB parameters here like DELIMIDENT, DB_LOCALE etc
//Full list in Client SDK's .Net Provider Reference Guide p 3:13
IfxConnection conn = new IfxConnection();
conn.ConnectionString = ConnectionString;
try
{
conn.Open();
Console.WriteLine("Made connection!");
Console.ReadLine();
}
catch (IfxException ex)
{
Console.WriteLine("Problem with connection attempt: "
+ ex.Message);
}
IfxConnection connection = null;
try
{
connection.Open();
}
catch (IfxException ex)
{
}
//Create Command
IfxCommand cmd = new IfxCommand(query, connection);
//Create a data reader and Execute the command
DataTable dataTable = new DataTable();
IfxDataAdapter adapter = new IfxDataAdapter(cmd);
adapter.Fill(dataTable);
dataTable.TableName = v_DatasetName;
engine.DataTables.Add(dataTable);
engine.AddVariable(v_DatasetName, dataTable);
try
{
connection.Close();
}
catch (IfxException ex)
{
}
}
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 ActionResult AddToCart(Mobiles mobileDetails)
{
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
int selectedQuantity = mobileDetails.Quantity;
Mobiles mobile = new Mobiles();
DataTable mobileTable = new DataTable();
string query = "SELECT * FROM Mobiles Where SLNo = ?";
IfxDataAdapter ifx = new IfxDataAdapter(query, Con);
ifx.SelectCommand.Parameters.Add("SLNo", IfxType.Serial).Value = mobileDetails.SLNo;
ifx.Fill(mobileTable);
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();
}
else
{
Con.Close();
mobile.ErrorMessage = "Error : Unable to get mobile details";
}
if (selectedQuantity > mobile.Quantity)
{
Con.Close();
mobile.ErrorMessage = "Cannot purchase " + selectedQuantity + " quantities, available quantities are : " + mobile.Quantity;
}
else
{
try
{
searchSLNoInCartTable(Con, mobileDetails.SLNo, mobile, selectedQuantity);
}
catch (Exception ex)
{
string createCartTable = "Create table cart (cartid 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, Price decimal(18, 2), Quantity int NULL, TotalAmount decimal(18,2))";
IfxCommand cmd2 = new IfxCommand(createCartTable, Con);
cmd2.ExecuteNonQuery();
searchSLNoInCartTable(Con, mobileDetails.SLNo, mobile, selectedQuantity);
}
finally
{
Con.Close();
mobile.ErrorMessage = "Added to cart successfully";
}
}
// return View(mobile);
return(RedirectToAction("Index", "Home"));
}
}
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();
}
public ViewResult Purchase(OrderDetailsModel orderDetailsModel)
{
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
int availableQuantity = 0;
string selectProductDetails = "select Count from product where productid = ?";
IfxCommand cmd = new IfxCommand(selectProductDetails, Con);
cmd.Parameters.Add("productid", IfxType.Serial).Value = orderDetailsModel.ProductID;
try
{
IfxDataReader rows = cmd.ExecuteReader();
while (rows.Read())
{
availableQuantity = Convert.ToInt32(rows[0]);
}
rows.Close();
}
catch (IfxException ex)
{
Con.Close();
orderDetailsModel.ErrorMessage = "Error : " + ex.Message;
}
if (orderDetailsModel.Count > availableQuantity)
{
Con.Close();
orderDetailsModel.ErrorMessage = "Cannot purchase " + orderDetailsModel.Count + " quantities, available quantities are : " + availableQuantity;
}
else
{
int newProductQuantity = availableQuantity - orderDetailsModel.Count;
string updateProductQuantity = "UPDATE Product SET count = ? Where productid = ?";
IfxCommand cmd1 = new IfxCommand(updateProductQuantity, Con);
cmd1.Parameters.Add("count", IfxType.Int).Value = newProductQuantity;
cmd1.Parameters.Add("productid", IfxType.Serial).Value = orderDetailsModel.ProductID;
cmd1.ExecuteNonQuery();
try
{
insertNewOrder(Con, orderDetailsModel);
}
catch (Exception ex)
{
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 cmd2 = new IfxCommand(createOrderTable, Con);
cmd2.ExecuteNonQuery();
insertNewOrder(Con, orderDetailsModel);
}
finally
{
Con.Close();
orderDetailsModel.ErrorMessage = "Purchase successful";
}
}
return(View(orderDetailsModel));
//return RedirectToAction("Index");
}
}
public List <Citizen> Update(string id, string sql)
{
String _connectionString =
"Server=ol_svr_custom;" +
"Host=localhost;" +
"Service=turbo;" +
"Database=registration;" +
"User ID=informix;" +
"Password=123456;" +
"Client Locale=ru_ru.CP1251;" +
"Database Locale=ru_ru.915;" +
"Max Pool Size=500;" +
"Pooling=True;" +
"Protocol=olsoctcp;" +
"Connection Lifetime=1200;" +
"Connection Timeout=1;";
try
{
IfxConnection _Connection = new IfxConnection()
{
ConnectionString = _connectionString
};
_Connection.Open();
try
{
IfxCommand _command = new IfxCommand
{
Connection = _Connection,
CommandText = sql
};
_command.ExecuteNonQuery();
_command.CommandText = $"select * from citizens where id = {id}";
IfxDataReader _dataReader;
_dataReader = _command.ExecuteReader();
Citizen citizen = new Citizen();
string Id = "";
while (_dataReader.Read())
{
Id = _dataReader.GetString(0);
}
_dataReader.Close();
_command.CommandText = $"select * from citizens where id = {Id}";
_dataReader = _command.ExecuteReader();
List <Citizen> citizens = new List <Citizen> {
};
while (_dataReader.Read())
{
if (!_dataReader.IsDBNull(0))
{
citizen = new Citizen();
citizen.Id = _dataReader.GetString(0);
citizen.LastName = _dataReader.GetString(1);
citizen.FirstName = _dataReader.GetString(2);
citizen.MiddleName = _dataReader.GetString(3);
string dt = _dataReader.GetString(4);
if (dt.Length == 10)
{
citizen.DateOfBirth = _dataReader.GetDateTime(4).ToString("d");
}
citizens.Add(citizen);
}
}
if (_dataReader != null)
{
_dataReader.Close();
}
if (_Connection != null)
{
_Connection.Close();
}
if (citizens.Count > 0)
{
return(citizens);
}
else
{
return(null);
}
}
catch
{
return(null);
}
}
catch
{
return(null);
}
}
public Search Select(string sqlCount, string sql)
{
String _connectionString =
"Server=ol_svr_custom;" +
"Host=localhost;" +
"Service=turbo;" +
"Database=registration;" +
"User ID=informix;" +
"Password=123456;" +
"Client Locale=ru_ru.CP1251;" +
"Database Locale=ru_ru.915;" +
"Max Pool Size=500;" +
"Pooling=True;" +
"Protocol=olsoctcp;" +
"Connection Lifetime=1200;" +
"Connection Timeout=1;";
try
{
IfxConnection _Connection = new IfxConnection()
{
ConnectionString = _connectionString
};
_Connection.Open();
try
{
Search _Search = new Search
{
Total = "0",
ListCitizen = new List <Citizen>()
};
IfxCommand _command = new IfxCommand
{
Connection = _Connection,
CommandText = sqlCount
};
IfxDataReader _dataReader = _command.ExecuteReader();
while (_dataReader.Read())
{
_Search.Total = _dataReader.GetString(0);
}
_dataReader.Close();
_command.CommandText = sql;
_dataReader = _command.ExecuteReader();
while (_dataReader.Read())
{
if (!_dataReader.IsDBNull(0))
{
Citizen citizen = new Citizen();
citizen.Id = _dataReader.GetString(0);
citizen.LastName = _dataReader.GetString(1);
citizen.FirstName = _dataReader.GetString(2);
citizen.MiddleName = _dataReader.GetString(3);
string dt = _dataReader.GetString(4);
if (dt.Length == 10)
{
citizen.DateOfBirth = _dataReader.GetDateTime(4).ToString("d");
}
_Search.ListCitizen.Add(citizen);
}
}
if (_dataReader != null)
{
_dataReader.Close();
}
if (_Connection != null)
{
_Connection.Close();
}
return(_Search);
}
catch
{
return(null);
}
}
catch
{
return(null);
}
}
