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 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);
}
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;
}
}
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");
}
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";
}
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;
}
}
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;
}