private void button2_Click(object sender, EventArgs e)
{
try
{
this.textBox1.Clear();
var connection_str = System.Configuration.ConfigurationManager.ConnectionStrings["DEMO_UD"];
string s = connection_str.ConnectionString;
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
this.textBox1.AppendText("Connected.........................(UniData Account)" + Environment.NewLine);
U2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT ID, FNAME,LNAME FROM STUDENT_NF_SUB";
DataSet ds = new DataSet();
m_da = new U2DataAdapter(cmd);
m_da.Fill(ds);
m_U2CommandBuilder = new U2CommandBuilder(m_da);
this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
this.textBox1.AppendText("Done........................." + Environment.NewLine);
}
catch (Exception e3)
{
this.textBox1.AppendText(e3.Message);
MessageBox.Show(e3.Message);
}
}
private void ExecuteDataReader(U2Connection con)
{
try
{
if (settings.AccessMode == "Native")
{
// Need to confirm how to use UniDataSet
}
else
{
U2Command cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = settings.CommandText;
U2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
// Do something
}
}
}
catch (System.Exception ex)
{
throw ex;
}
}
private void ExecuteDataSet(U2Connection con)
{
try
{
if (settings.AccessMode == "Native")
{
// Need to confirm how to use UniDataSet
}
else
{
U2Command cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = settings.CommandText;
U2DataAdapter da = new U2DataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
}
}
catch (System.Exception ex)
{
throw ex;
}
}
public async Task <string> QueryUsers(int pageNumber, int pageSize)
{
string lRetJsonData = string.Empty;
try
{
U2ConnectionStringBuilder l = new U2ConnectionStringBuilder();
l.UserID = "admjjin";
l.Password = "******";
l.Server = "192.168.102.132";
l.Database = "HS.SALES";
l.ServerType = "universe";
l.Connect_Timeout = 360;
l.RpcServiceType = "uvcs";
l.AccessMode = "Native";
string lconnstr = l.ToString();
U2Connection c = new U2Connection();
c.ConnectionString = lconnstr;
await c.OpenAsync();
U2Command cmd = c.CreateCommand();
cmd.CommandText = string.Format("Action=Select;File=CUSTOMER;Attributes=CUSTID,FNAME,LNAME,PRODID,BUY_DATE;Where=CUSTID>0;Sort=CUSTID");
lRetJsonData = await cmd.ExecuteJsonAsync();
c.Close();
}
catch (Exception ee)
{
throw ee;
}
return(lRetJsonData);
}
private void ExecuteCommand(U2Connection con)
{
try
{
if (settings.AccessMode == "Native")
{
// Native mode
UniSession us1 = con.UniSession;
UniCommand uniCmd = us1.CreateUniCommand();
uniCmd.Command = settings.CommandText;
uniCmd.Execute();
// Get response string but not output
string strNative = uniCmd.Response;
}
else
{
// SQL mode
U2Command cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = settings.CommandText;
cmd.ExecuteNonQuery();
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
}
}
public static async Task <List <Customer> > CallSubroutine()
{
try
{
List <Customer> lRetList = new List <Customer>();
U2Connection conn = CreateConnection.GetConnection();
await conn.OpenAsync();
U2Command command = conn.CreateCommand();
Console.WriteLine("Connected.........................");
command.CommandText = "CALL *HS.SALES*GETCUSTOMER()"; // UniVerse subroutine, returns multi-value data
U2DataReader dr = await command.ExecuteReaderAsync();
while (await dr.ReadAsync())
{
Customer lCust = new Customer();
lCust.CustomerId = await dr.GetFieldValueAsync <int>(0);
lCust.FirstName = await dr.GetFieldValueAsync <string>(1);
lCust.LastName = await dr.GetFieldValueAsync <string>(2);
lRetList.Add(lCust);
}
conn.Close();
return(lRetList);
}
catch (Exception ee)
{
string lErr = ee.Message;
throw;
}
}
public static List <Orders> ReadExistingOrders(String OrderID)
{
List <Orders> Cart = new List <Orders>();
OrderID = OrderID;
string connection = ConfigurationManager.AppSettings["MVWriter"];
U2Connection con = new U2Connection();
con.ConnectionString = connection;
con.Open();
U2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT PROD, QUAN, COST FROM UNNEST SHOPPINGCART ON CARTS WHERE RECID = '" + OrderID + "'";
DataSet ds = new DataSet();
U2DataAdapter da = new U2DataAdapter(cmd);
da.Fill(ds);
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
Orders ExistingLineItems = new Orders {
Cost = dr["COST"].ToString(), Quantity = dr["QUAN"].ToString(), Serial = dr["PROD"].ToString()
};
Cart.Add(ExistingLineItems);
}
con.Close();
return(Cart);
}
public ActionResult Index()
{
List <APMST> sample = new List <APMST>();
try
{
string s = ConfigurationManager.AppSettings["TESTString"];
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
U2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT PROD,SALE, DESC1 FROM IVMST"; // FNAME = SingleValue, PRICE = multivalue
DataSet ds = new DataSet();
U2DataAdapter da = new U2DataAdapter(cmd);
da.Fill(ds);
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
//APMST loadRecord = new APMST { NAME = dr["NAME"].ToString() };
APMST loadRecord = new APMST {
NAME = dr["PROD"].ToString()
};
sample.Add(loadRecord);
}
con.Close();
}
catch (Exception e)
{
// Console.WriteLine(e.Message);
if (e.InnerException != null)
{
}
}
finally
{
// string line = Console.ReadLine();
}
return(View(sample));
}
public ActionResult GetProduct(string term)
{
List <Product> ProductsFound = new List <Product>();
string Upperterm = term.ToUpper();
try
{
string s = ConfigurationManager.AppSettings["TESTString"];
U2Connection con = new U2Connection();
U2Command cmd = con.CreateCommand();
con.ConnectionString = s;
con.Open();
cmd.CommandText = "SELECT PROD,SALE, DESC1 FROM IVMST WHERE UPPER(DESC1) LIKE '%" + Upperterm + "%'";
U2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Product ProductList = new Product {
DESC1 = dr["DESC1"].ToString(),
PROD = dr["PROD"].ToString(),
SALE = dr["SALE"].ToString()
};
ProductsFound.Add(ProductList);
}
con.Close();
}
catch (Exception e)
{
// Console.WriteLine(e.Message);
if (e.InnerException != null)
{
}
}
finally
{
// string line = Console.ReadLine();
}
return(Json(ProductsFound, JsonRequestBehavior.AllowGet));
}
public static async Task <DataTable> CallSubroutine()
{
try
{
DataTable lRetDT = new DataTable("EmpTable");
lRetDT.Columns.Add("ID", typeof(Int32));
lRetDT.Columns.Add("Name", typeof(string));
lRetDT.Columns.Add("HireDate", typeof(DateTime));
U2Connection conn = CreateConnection.GetConnection();
await conn.OpenAsync();
U2Command command = conn.CreateCommand();
Console.WriteLine("Connected.........................");
command.CommandText = "CALL *HS.SALES*SELECT_SUBROUTINE(?,?)"; // UniVerse subroutine, returns multi-value data
command.Parameters.Clear();
command.CommandType = CommandType.StoredProcedure;
U2Parameter p1 = new U2Parameter();
p1.Direction = ParameterDirection.InputOutput;
p1.Value = "1";//INPUT
p1.ParameterName = "@arg1";
U2Parameter p2 = new U2Parameter();
p2.Direction = ParameterDirection.InputOutput;
p2.Value = "";//OUTPUT (multi-value data
p2.ParameterName = "@arg2";
command.Parameters.Add(p1);
command.Parameters.Add(p2);
await command.ExecuteNonQueryAsync();
string s1 = command.Parameters[0].Value.ToString(); //INPUT
string s2 = command.Parameters[1].Value.ToString(); // OUTPUT
p2.MV_To_DataTable(lRetDT); //Convert multi-value data to C# DataTable using Schema
conn.Close();
return(lRetDT);
}
catch (Exception ee)
{
string lErr = ee.Message;
throw;
}
}
public static async Task <string> CallSubroutine()
{
try
{
string lRetJsonData = string.Empty;
U2Connection conn = CreateConnection.GetConnection();
await conn.OpenAsync();
U2Command command = conn.CreateCommand();
Console.WriteLine("Connected.........................");
command.CommandText = "CALL *HS.SALES*SELECT_SUBROUTINE(?,?)"; // UniVerse subroutine, returns multi-value data
command.Parameters.Clear();
command.CommandType = CommandType.StoredProcedure;
U2Parameter p1 = new U2Parameter();
p1.Direction = ParameterDirection.InputOutput;
p1.Value = "1";//INPUT
p1.ParameterName = "@arg1";
U2Parameter p2 = new U2Parameter();
p2.Direction = ParameterDirection.InputOutput;
p2.Value = "";//OUTPUT (multi-value data
p2.ParameterName = "@arg2";
command.Parameters.Add(p1);
command.Parameters.Add(p2);
await command.ExecuteNonQueryAsync();
string s1 = command.Parameters[0].Value.ToString(); //INPUT
string s2 = command.Parameters[1].Value.ToString(); // OUTPUT
List <Employee> lRetEmpList = p2.MV_To_POCO <Employee>(); //Convert multi-value data to C# POCO Class using Schema
lRetJsonData = Newtonsoft.Json.JsonConvert.SerializeObject(lRetEmpList);
conn.Close();
return(lRetJsonData);
}
catch (Exception ee)
{
string lErr = ee.Message;
throw;
}
}
static void Main(string[] args)
{
try
{
Console.WriteLine("start.........................");
Stopwatch sw = new Stopwatch();
sw.Start();
U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
conn_str.UserID = "administrator";
conn_str.Password = "******";
conn_str.Server = "localhost";
conn_str.Database = "XDEMO";
conn_str.ServerType = "UNIVERSE";
conn_str.Pooling = false;
string s = conn_str.ToString();
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
Console.WriteLine("Connected...");
U2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM PRODUCTS";
DataSet ds = new DataSet();
U2DataAdapter da = new U2DataAdapter(cmd);
da.Fill(ds);
sw.Stop();
TimeSpan elapsed = sw.Elapsed;
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", elapsed.Hours, elapsed.Minutes, elapsed.Seconds, elapsed.Milliseconds / 10);
int nSec = elapsed.Seconds;
con.Close();
Console.WriteLine("Time Taken in seconds:" + elapsedTime);
Console.WriteLine("End........................... ");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
Console.WriteLine("Enter to exit:");
string line = Console.ReadLine();
}
}
public static bool UpdateProducts(List <Orders> Cart, string OrderID, string Product)
{
try
{
int lRecID = 99;
U2ConnectionStringBuilder conn_bldr = new U2ConnectionStringBuilder();
conn_bldr.UserID = "administrator";
conn_bldr.Password = "******";
conn_bldr.Server = "myserver";
conn_bldr.ServerType = "universe";
conn_bldr.Database = "HS.SALES";
conn_bldr.AccessMode = "Native";
conn_bldr.RpcServiceType = "uvcs";
string lConnStr = conn_bldr.ConnectionString;
U2Connection lConn = new U2Connection();
lConn.ConnectionString = lConnStr;
lConn.Open();
UniSession lUniSession = lConn.UniSession;
U2Command cmd = lConn.CreateCommand();
//CUSTID,FNAME,LNAME : Single Value
//PRODID, BUY_DATE : Multi Value
//Syntax : Action=Update;File=?;Attributes=?;Where=?;Sort
UniDynArray lArr = new UniDynArray(lUniSession, "2/1/1991");
lArr.Insert(1, -1, "3/9/1991");
lArr.Insert(1, -1, "4/1/1991");
string lCmd = string.Format("UPDATE SHOPPINGCART SET FNAME={0},BUY_DATE='{1}' WHERE CUSTID={2} ", "Fred2", lArr.StringValue, lRecID);
cmd.CommandText = lCmd;
int l2 = cmd.ExecuteNonQuery();
lConn.Close();
}
catch (Exception e2)
{
string lErr = e2.Message;
if (e2.InnerException != null)
{
lErr += lErr + e2.InnerException.Message;
}
}
return(true);
}
public static List <Orders> GetProducts(List <Orders> Cart)
{
List <Orders> shoppingCart = Cart;
U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
conn_str.UserID = "Demo";
conn_str.Password = "******";
conn_str.Server = "localhost";
conn_str.Database = "pwdemo";
conn_str.ServerType = "UNIVERSE";
conn_str.Pooling = false;
string s = conn_str.ToString();
U2Connection con1 = new U2Connection();
con1.ConnectionString = s;
con1.Open();
U2Command cmd1 = con1.CreateCommand();
foreach (var Product in shoppingCart)
{
try
{
cmd1.CommandText = "SELECT [DESC_POS] FROM IVMST WHERE ID=" + Product.Serial.ToString();
U2DataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
Product.ProdDescription = string.Format(dr1["DESC_POS"].ToString());
dr1.Close();
}
}
catch (Exception ex)
{
}
}
con1.Close();
return(shoppingCart);
}
public static bool DeleteProducts(List <Orders> Cart, string OrderID, string Product)
{
/// // First Insert, then Print, then Delete
try
{
Console.WriteLine(Environment.NewLine + "Start...");
U2ConnectionStringBuilder conn_bldr = new U2ConnectionStringBuilder();
conn_bldr.UserID = "Demo";
conn_bldr.Password = "******";
conn_bldr.Server = "localhost";
conn_bldr.ServerType = "universe";
conn_bldr.Database = "pwdemo";
conn_bldr.AccessMode = "Native";
conn_bldr.RpcServiceType = "uvcs";
string lConnStr = conn_bldr.ConnectionString;
U2Connection lConn = new U2Connection();
lConn.ConnectionString = lConnStr;
lConn.Open();
UniSession lUniSession = lConn.UniSession;
U2Command cmd = lConn.CreateCommand();
// delete inserted value
cmd.CommandText = string.Format("Action=Delete;File=SHOPPINGCART;Where=newrecid={0}", OrderID);
int l2 = cmd.ExecuteNonQuery();
//close connection
lConn.Close();
}
catch (Exception e2)
{
string lErr = e2.Message;
if (e2.InnerException != null)
{
lErr += lErr + e2.InnerException.Message;
}
}
// public static bool InsertOrderItem(List<Orders> LineItem, string OrderNumber)
return(true);
}
public async Task <string> UpdateUser(object customer)
{
int lRet = -1;
try
{
var cust = (IDictionary <string, object>)customer;
foreach (var property in (IDictionary <String, Object>)cust)
{
Console.WriteLine(property.Key + ": " + property.Value);
var t1 = property.Key;
var t2 = property.Value;
}
U2ConnectionStringBuilder l = new U2ConnectionStringBuilder();
l.UserID = "admin";
l.Password = "******";
l.Server = "192.168.102.132";
l.Database = "HS.SALES";
l.ServerType = "universe";
l.Connect_Timeout = 360;
l.RpcServiceType = "uvcs";
l.AccessMode = "Native";
string lconnstr = l.ToString();
U2Connection c = new U2Connection();
c.ConnectionString = lconnstr;
await c.OpenAsync();
U2Command cmd = c.CreateCommand();
//cmd.CommandText = string.Format("Action=Select;File=CUSTOMER;Attributes=CUSTID,FNAME,LNAME,PRODID,BUY_DATE;Where=CUSTID>0;Sort=CUSTID");
//cmd.CommandText = string.Format("Action=Update;File=CUSTOMER;Attributes=FNAME={0},LNAME={1},DISCOUNT{2};Where=CUSTID={3}", cust["FNAME"], cust["LNAME"], cust["DISCOUNT"], cust["CUSTID"]);
cmd.CommandText = string.Format("Action=Update;File=CUSTOMER;Attributes=FNAME={0},LNAME={1};Where=CUSTID={2}", cust["FNAME"], cust["LNAME"], cust["CUSTID"]);
lRet = await cmd.ExecuteNonQueryAsync();
c.Close();
}
catch (Exception ee)
{
throw ee;
}
return("200");
}
private static void FillDataSetWithUNNEST()
{
try
{
U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
conn_str.UserID = "user";
conn_str.Password = "******";
conn_str.Server = "localhost";
conn_str.Database = "HS.SALES";
conn_str.ServerType = "UNIVERSE";
conn_str.FirstNormalForm = false;
conn_str.Pooling = false;
string s = conn_str.ToString();
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
Console.WriteLine("Connected.........................");
U2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT FNAME, PRICE FROM UNNEST CUSTOMER ON ORDERS"; // FNAME = SingleValue, PRICE = multivalue
DataSet ds = new DataSet();
U2DataAdapter da = new U2DataAdapter(cmd);
da.Fill(ds);
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr["FNAME"] + "==" + dr["PRICE"]);
}
con.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
//Console.WriteLine("Enter to exit:");
//string line = Console.ReadLine();
}
}
static void Main(string[] args)
{
try
{
U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
conn_str.UserID = "user";
conn_str.Password = "******";
conn_str.Server = "localhost";
conn_str.Database = "demo";
conn_str.ServerType = "UNIDATA";
conn_str.Pooling = false;
conn_str.FirstNormalForm = false;
string s = conn_str.ToString();
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
Console.WriteLine("Connected.........................");
U2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM STUDENT UNNEST NL_ALL CGA ;";
DataSet ds = new DataSet();
U2DataAdapter da = new U2DataAdapter(cmd);
da.Fill(ds);
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr["FNAME"] + "==" + dr["SEMESTER"]);
}
con.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
Console.WriteLine("Enter to exit:");
string line = Console.ReadLine();
}
}
static void Main(string[] args)
{
try
{
U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
conn_str.UserID = "user";
conn_str.Password = "******";
conn_str.Server = "localhost";
conn_str.Database = "HS.SALES";
conn_str.ServerType = "UNIVERSE";
conn_str.Pooling = false;
string s = conn_str.ToString();
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
Console.WriteLine("Connected.........................");
U2Command cmd = con.CreateCommand();
cmd.CommandText = "LIST CUSTOMER";
DataSet ds = new DataSet();
U2DataAdapter da = new U2DataAdapter(cmd);
da.FillWithTOXML(ds);
Console.WriteLine(ds.GetXml());
con.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
Console.WriteLine("Enter to exit:");
string line = Console.ReadLine();
}
}
static void Main(string[] args)
{
try
{
U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
conn_str.UserID = "user";
conn_str.Password = "******";
conn_str.Server = "localhost";
conn_str.Database = "HS.SALES";
conn_str.ServerType = "UNIVERSE";
conn_str.Pooling = false;
string s = conn_str.ToString();
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
Console.WriteLine("Connected.........................");
U2Command cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM CUSTOMER";
U2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string s1 = string.Format("FNAME={0} LNAME={1}", dr["FNAME"], dr["LNAME"] + Environment.NewLine);
Console.WriteLine(s1);
}
con.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
Console.WriteLine("Enter to exit:");
string line = Console.ReadLine();
}
}
/// <summary>
/// Get dataset in SQL mode
/// </summary>
/// <param name="conStrBdr"></param>
/// <param name="strTableName"></param>
/// <returns></returns>
private DataSet GetSQLDataSet(U2ConnectionStringBuilder conStrBdr, string strTableName)
{
try
{
U2Connection con = new U2Connection();
con.ConnectionString = conStrBdr.ToString();
con.Open();
U2Command cmd = con.CreateCommand();
cmd.CommandText = string.Format("SELECT * FROM [{0}]", strTableName);
U2DataAdapter da = new U2DataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
return(ds);
}
catch (Exception e)
{
throw e;
}
}
public static bool InsertOrderItem(List <Orders> LineItem, string OrderNumber)
{
try
{
U2ConnectionStringBuilder conn_bldr = new U2ConnectionStringBuilder();
conn_bldr.UserID = "Demo";
conn_bldr.Password = "******";
conn_bldr.Server = "localhost";
conn_bldr.ServerType = "universe";
conn_bldr.Database = "pwdemo";
conn_bldr.AccessMode = "Native";
conn_bldr.RpcServiceType = "uvcs";
string lConnStr = conn_bldr.ConnectionString;
U2Connection lConn = new U2Connection();
lConn.ConnectionString = lConnStr;
lConn.Open();
UniSession lUniSession = lConn.UniSession;
U2Command cmd = lConn.CreateCommand();
//Unique sale ID or invoice Number. Do we add userid as well?
// We will work with the assumption that it's one order - 5001
int newrecid = Convert.ToInt32(OrderNumber);
UniDynArray Product = new UniDynArray(lUniSession);
UniDynArray Quantity = new UniDynArray(lUniSession);
UniDynArray Sale = new UniDynArray(lUniSession);
foreach (var order in LineItem)
{
string ProdID = order.Serial.ToString();
string Quan = order.Quantity.ToString();
string price = order.Cost.ToString();
Product.Insert(1, -1, ProdID);
Quantity.Insert(1, -1, Quan);
Sale.Insert(1, -1, price);
}
string lCmd = string.Format("INSERT INTO SHOPPINGCART (RECID,PROD,QUAN,SALE) VALUES('{0}','{1}','{2}','{3}')", newrecid, Product, Quantity, Sale);
cmd.CommandText = lCmd;
int l2 = cmd.ExecuteNonQuery();
lConn.Close();
}
catch (Exception e2)
{
string lErr = e2.Message;
if (e2.InnerException != null)
{
lErr += lErr + e2.InnerException.Message;
}
return(false);
}
return(true);
}
//public static bool InsertOrderItem(Orders LineItem, string OrderNumber)
//{
// string s = ConfigurationManager.AppSettings["TESTString"];
// U2Connection con = new U2Connection();
// con.ConnectionString = s;
// con.Open();
// UniSession LUniSession = con.UniSession;
// U2Command cmd = con.CreateCommand();
// int recID = 9999;
// int Prod = 1234;
// int quantity = 1;
// double cost = 9.99;
// //Insert
// string lcmd = string.Format("INSERT INTO SHOPPINGCART RECID,PROD,QUAN,COST) VALUES('{0}','{1}','{2}','{3}')", recID, Prod, quantity, cost);
// cmd.CommandText = lcmd;
// try { int L2 = cmd.ExecuteNonQuery(); }
// catch(Exception ex) { return false; }
// finally { }
// return true;
//}
public static bool InsertOrderItem(List <Orders> LineItem, string OrderNumber)
{
try
{
U2ConnectionStringBuilder conn_bldr = new U2ConnectionStringBuilder();
conn_bldr.UserID = "Demo";
conn_bldr.Password = "******";
conn_bldr.Server = "localhost";
conn_bldr.ServerType = "universe";
conn_bldr.Database = "pwdemo";
conn_bldr.AccessMode = "Native";
conn_bldr.RpcServiceType = "uvcs";
string lConnStr = conn_bldr.ConnectionString;
U2Connection lConn = new U2Connection();
lConn.ConnectionString = lConnStr;
lConn.Open();
UniSession lUniSession = lConn.UniSession;
U2Command cmd = lConn.CreateCommand();
//Unique sale ID or invoice Number. Do we add userid as well?
// We will work with the assumption that it's one order - 5001
int newrecid = 5001;
UniDynArray Product = new UniDynArray(lUniSession);
UniDynArray Quantity = new UniDynArray(lUniSession);
UniDynArray Sale = new UniDynArray(lUniSession);
foreach (var order in LineItem)
{
string ProdID = order.Serial.ToString();
string Quan = order.Quantity.ToString();
string price = order.Cost.ToString();
Product.Insert(1, -1, ProdID);
Quantity.Insert(1, -1, Quan);
Sale.Insert(1, -1, price);
}
// As we add reccords, we will need to read , loop and drop into UniDynArray.
// For now, just enter line item.
//Sale.Insert(1, -1, price2);
//Sale.Insert(1, -1, price3);
string lCmd = string.Format("INSERT INTO SHOPPINGCART (RECID,PROD,QUAN,SALE) VALUES('{0}','{1}','{2}','{3}')", newrecid, Product, Quantity, Sale);
//string lCmd = string.Format("INSERT INTO SHOPPINGCART VALUES (newrecid, Product, Quantity, Sale)");
//string lCmd = string.Format("INSERT INTO SHOPPINGCART (PROD,QUAN,SALE) VALUES('{0}','{1}','{2}')", Product, Quantity, Sale);
cmd.CommandText = lCmd;
int l2 = cmd.ExecuteNonQuery();
//print inserted value
//cmd.CommandText = string.Format("Action=Select;File=SHOPPINGCART;Attributes=RECID,PROD,QUAN,SALE;Where=RECID={0}", newrecid);
//U2DataAdapter da = new U2DataAdapter(cmd);
//DataSet ds = new DataSet();
//da.Fill(ds);
// delete inserted value
//Console.WriteLine(Environment.NewLine + "Start : Delete new inserted value...............");
//cmd.CommandText = string.Format("Action=Delete;File=SHOPPINGCART;Where=RECID={0}", newrecid);
//l2 = cmd.ExecuteNonQuery();
//close connection
lConn.Close();
}
catch (Exception e2)
{
string lErr = e2.Message;
if (e2.InnerException != null)
{
lErr += lErr + e2.InnerException.Message;
}
return(false);
}
return(true);
}
//file for customers -- IVCUST
//NAME_1 = NAME of customer
//ADDR_1 = Street address of customer -
//ADDR_2 = Second address of customer
//CSZ = City, St Zip of customer
//Phone_1 = Primary Phone number of customer
public ActionResult GetCustomer(string term)
{
List <Computyme.Models.Customer.Customer> CustomerFound = new List <Computyme.Models.Customer.Customer>();
string Upperterm = term.ToUpper();
try
{
string s = ConfigurationManager.AppSettings["TESTString"];
U2Connection con = new U2Connection();
U2Command cmd = con.CreateCommand();
con.ConnectionString = s;
con.Open();
cmd.CommandText = "SELECT NAME_1,ADDR_1, ADDR_2,CSZ, PHONE_1,EMAIL FROM IVCUST WHERE UPPER(NAME_1) LIKE '%" + Upperterm + "%'";
U2DataReader dr = cmd.ExecuteReader();
string cell = "111-111-1111";
string email = "None";
while (dr.Read())
{
try
{
if (dr["Phone_1"] != null)
{
cell = (dr["Phone_1"] ?? "").ToString();
}
}
catch { cell = "111-111-1111"; }
try
{
if (dr["EMAIL"] != null)
{
email = (dr["EMAIL"] ?? "").ToString();
}
}
catch { email = "None"; }
string Fullstring = (dr["NAME_1"] ?? "").ToString();
string LName = Fullstring.Substring(0, Fullstring.IndexOf(","));
string FName = Fullstring.Replace(LName, "").Replace(",", "");
string address = (dr["ADDR_1"] ?? "").ToString();
Computyme.Models.Customer.Customer CustomerList = new Computyme.Models.Customer.Customer
{
Fname = FName,
Lname = LName,
Address = (dr["ADDR_1"] ?? "").ToString(),
Cell = cell,
Email = email
};
CustomerFound.Add(CustomerList);
}
con.Close();
}
catch (Exception e)
{
// Console.WriteLine(e.Message);
if (e.InnerException != null)
{
}
}
finally
{
// string line = Console.ReadLine();
}
return(Json(CustomerFound, JsonRequestBehavior.AllowGet));
}
public static async Task <string> CallSubroutine()
{
try
{
string lRet = string.Empty;
U2Connection conn = CreateConnection.GetConnection();
await conn.OpenAsync();
U2Command command = conn.CreateCommand();
Console.WriteLine("Connected.........................");
command.CommandText = "CALL *GETXMLSUB(?,?,?,?,?,?)"; // UniVerse subroutine
command.Parameters.Clear();
command.CommandType = CommandType.StoredProcedure;
U2Parameter p1 = new U2Parameter();
p1.Direction = ParameterDirection.InputOutput;
p1.Value = "LIST CUSTOMER";
p1.ParameterName = "@arg1";
U2Parameter p2 = new U2Parameter();
p2.Direction = ParameterDirection.InputOutput;
p2.Value = "";
p2.ParameterName = "@arg2";
U2Parameter p3 = new U2Parameter();
p3.Direction = ParameterDirection.InputOutput;
p3.Value = "";
p3.ParameterName = "@arg3";
U2Parameter p4 = new U2Parameter();
p4.Direction = ParameterDirection.InputOutput;
p4.Value = "";
p4.ParameterName = "@arg4";
U2Parameter p5 = new U2Parameter();
p5.Direction = ParameterDirection.InputOutput;
p5.Value = "";
p5.ParameterName = "@arg5";
U2Parameter p6 = new U2Parameter();
p6.Direction = ParameterDirection.InputOutput;
p6.Value = "";
p6.ParameterName = "@arg6";
command.Parameters.Add(p1);
command.Parameters.Add(p2);
command.Parameters.Add(p3);
command.Parameters.Add(p4);
command.Parameters.Add(p5);
command.Parameters.Add(p6);
await command.ExecuteNonQueryAsync();
string s1 = command.Parameters[0].Value.ToString(); //INPUT command
string s2 = command.Parameters[1].Value.ToString(); // INPUT command option
string lOutputValue = command.Parameters[2].Value.ToString(); // OUTPUT xml
string s4 = command.Parameters[3].Value.ToString(); //OUTPUT xsd
string s5 = command.Parameters[4].Value.ToString(); //OUTPUT msg #
string s6 = command.Parameters[5].Value.ToString(); //OUTPUT msg description
conn.Close();
return(lOutputValue);
}
catch (Exception ee)
{
string lErr = ee.Message;
throw;
}
}
static void Main(string[] args)
{
try
{
U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
conn_str.UserID = "user";
conn_str.Password = "******";
conn_str.Server = "localhost";
conn_str.Database = "HS.SALES";
conn_str.ServerType = "UNIVERSE";
conn_str.Pooling = false;
string s = conn_str.ToString();
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
Console.WriteLine("Connected.........................");
U2Command command = con.CreateCommand();
command.CommandText = "CALL *GETXMLSUB(?,?,?,?,?,?)"; // UniVerse subroutine
command.Parameters.Clear();
command.CommandType = CommandType.StoredProcedure;
U2Parameter p1 = new U2Parameter();
p1.Direction = ParameterDirection.InputOutput;
p1.Value = "LIST CUSTOMER";
p1.ParameterName = "@arg1";
U2Parameter p2 = new U2Parameter();
p2.Direction = ParameterDirection.InputOutput;
p2.Value = "";
p2.ParameterName = "@arg2";
U2Parameter p3 = new U2Parameter();
p3.Direction = ParameterDirection.InputOutput;
p3.Value = "";
p3.ParameterName = "@arg3";
U2Parameter p4 = new U2Parameter();
p4.Direction = ParameterDirection.InputOutput;
p4.Value = "";
p4.ParameterName = "@arg4";
U2Parameter p5 = new U2Parameter();
p5.Direction = ParameterDirection.InputOutput;
p5.Value = "";
p5.ParameterName = "@arg5";
U2Parameter p6 = new U2Parameter();
p6.Direction = ParameterDirection.InputOutput;
p6.Value = "";
p6.ParameterName = "@arg6";
command.Parameters.Add(p1);
command.Parameters.Add(p2);
command.Parameters.Add(p3);
command.Parameters.Add(p4);
command.Parameters.Add(p5);
command.Parameters.Add(p6);
command.ExecuteNonQuery();
string s1 = command.Parameters[0].Value.ToString(); //command
string s2 = command.Parameters[1].Value.ToString(); // command option
string s3 = command.Parameters[2].Value.ToString(); // xml
string s4 = command.Parameters[3].Value.ToString(); //xsd
string s5 = command.Parameters[4].Value.ToString(); // msg #
string s6 = command.Parameters[5].Value.ToString(); // msg description
Console.WriteLine("Subroutine Output:" + s3 + Environment.NewLine);
Console.WriteLine("Subroutine Output:" + s4 + Environment.NewLine);
con.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
Console.WriteLine("Enter to exit:");
string line = Console.ReadLine();
}
}
private void button3_Click(object sender, EventArgs e)
{
try
{
this.textBox1.AppendText("Start..." + Environment.NewLine);
//strat get U2 data
U2ConnectionStringBuilder conn_bldr = new U2ConnectionStringBuilder();
conn_bldr.UserID = "admin";
conn_bldr.Password = "******";
conn_bldr.Server = "192.168.102.132";
conn_bldr.ServerType = "universe";
conn_bldr.Database = "HS.SALES";
conn_bldr.AccessMode = "Native";
conn_bldr.RpcServiceType = "uvcs";
string lConnStr = conn_bldr.ConnectionString;
U2Connection lConn = new U2Connection();
lConn.ConnectionString = lConnStr;
lConn.Open();
Console.WriteLine("Connected...");
U2Command cmd = lConn.CreateCommand();
// CUSTID,FNAME,LNAME : Single Value
//PRODID, BUY_DATE : Multi Value
//Syntax : Action=?;File=?;Attributes=?;Where=?;Sort
cmd.CommandText = string.Format("Action=Select;File=CUSTOMER;Attributes=CUSTID,FNAME,LNAME,PRODID,BUY_DATE;Where=CUSTID>0;Sort=CUSTID");
U2DataAdapter da = new U2DataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
//end get U2 data
//load table storage
string lTableStorageName = "CUSTOMER3";
StorageCredentials creds = new StorageCredentials(accountName, accountKey);
CloudStorageAccount account = new CloudStorageAccount(creds, useHttps: true);
CloudTableClient client = account.CreateCloudTableClient();
CloudTable table = client.GetTableReference("CUSTOMER6");
table.CreateIfNotExists();
string lUri = table.Uri.ToString();
// Console.WriteLine(table.Uri.ToString());
this.textBox1.AppendText(lUri + Environment.NewLine);
foreach (DataRow item in dt.Rows)
{
var t1 = item["CUSTID"].ToString();
var t2 = item["Z_MV_KEY"].ToString();
DateTime lhiredate;
if (item["BUY_DATE"] == DBNull.Value)
{
lhiredate = DateTime.Now;// new DateTime();
}
else
{
lhiredate = (DateTime)item["BUY_DATE"];
}
CustomerEntity entity = new CustomerEntity(t1, t2)
{
FNAME = (string)item["FNAME"],
LNAME = (string)item["LNAME"],
PRODID = (string)item["PRODID"],
BUY_DATE = lhiredate
};
TableOperation insertOperation = TableOperation.InsertOrReplace(entity);
table.Execute(insertOperation);
this.textBox1.AppendText("Entity inserted!" + Environment.NewLine);
}
this.textBox1.AppendText("End..." + Environment.NewLine);
}
catch (Exception ex)
{
this.textBox1.AppendText(ex.Message + Environment.NewLine);
}
}
public ActionResult About()
{
string NewOrderID = null; //rnd.Next(1, 10000).ToString(); // creates a number between 1 and 6
string s = ConfigurationManager.AppSettings["TESTString"];
U2Connection con = new U2Connection();
U2Command cmd = con.CreateCommand();
con.ConnectionString = s;
con.Open();
cmd.CommandText = "SELECT MAX(RECID) FROM SHOPPINGCART";
U2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string MaxID = dr["RECID"].ToString();
NewOrderID = (Convert.ToInt32(MaxID) + 1).ToString();
}
con.Close();
if (Request.Cookies["ComputymeOrder"] == null)
{
//Drop cookie
HttpCookie myCookie = new HttpCookie("ComputymeOrder");
myCookie["OrderID"] = NewOrderID;
myCookie["OrderStatus"] = "New";
myCookie.Expires = DateTime.Now.AddDays(1d);
Response.Cookies.Add(myCookie);
}
else
{
string OrderStatus;
//if (Request.Cookies["ComputymeOrder"]["OrderID"] != null )
if (Request.Cookies["ComputymeOrder"]["OrderID"] != null)
{
OrderStatus = Request.Cookies["ComputymeOrder"]["OrderStatus"];
if (OrderStatus == "New")
{
NewOrderID = Request.Cookies["ComputymeOrder"]["OrderID"];
}
}
else
{
//Drop cookie
HttpCookie myCookie = new HttpCookie("ComputymeOrder");
myCookie["OrderID"] = NewOrderID;
myCookie["OrderStatus"] = "New";
myCookie.Expires = DateTime.Now.AddDays(1d);
Response.Cookies.Add(myCookie);
}
// Check if it exists.
}
Computyme.Models.NewOrder.UniqueOrder OrderID = new Models.NewOrder.UniqueOrder {
OrderID = NewOrderID, UserID = "4561"
};
ViewData["TransacionID"] = OrderID;
return(View());
}