public void Close()
{
try
{
Conn.Close();
}
catch (Exception)
{
throw;
}
}
public async Task <Person> GetById(int id)
{
var req = _conn.CreateCommand();
req.CommandText = $"SELECT * FROM person WHERE id={id};";
await _conn.OpenAsync();
var res = await req.ExecuteReaderAsync();
var p = FromDb(res);
_conn.Close();
return(p.First());
}
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 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();
}
// 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 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));
}
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));
}
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 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));
}
}
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 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 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}""}}");
}
}
}
/// <summary>
/// Finaliza la conexión establecida
/// </summary>
public void CerrarConexion()
{
if (conexion.State == ConnectionState.Open)
{
conexion.Close();
conexion.Dispose();
}
}
/// <summary>
/// Clean up
/// </summary>
public void CleanUp()
{
if (_connection != null)
{
_connection.Close();
}
_outStream.Close();
}
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 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 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"));
}
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 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);
}
}
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 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));
}
//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 = "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);
}
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);
}
/// <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");
}
}
/// <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 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"));
}
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 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;
}
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";
}
}
}
}
}
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;
}
}
