/// <summary>
/// 設定HanaCommand物件的方法
/// </summary>
/// <param name="cmd">HanaCommand物件</param>
/// <param name="cmdSetModel">cmd設定物件</param>
/// <param name="cmdParams">HanaParameter參數陣列</param>
private void SettingCommand(HanaCommand cmd, CmdSettingModel cmdSetModel, HanaParameter[] cmdParams)
{
cmd.Connection = cmdSetModel.Conn;
cmd.CommandText = cmdSetModel.Text;
if (cmdSetModel.Trans != null)
{
cmd.Transaction = cmdSetModel.Trans;
}
else if (cmdSetModel.Conn.State != ConnectionState.Open)
{
cmdSetModel.Conn.Open();
}
cmd.Parameters.Clear();
if (cmdParams != null)
{
HanaParameter[] sqlParameterArray = cmdParams;
for (int i = 0; i < sqlParameterArray.Length; i++)
{
HanaParameter param = sqlParameterArray[i];
cmd.Parameters.Add(param);
}
SqlParameterOriginal(cmdParams);
}
}
public void Create(IEnumerable<string> columnDefinitions)
{
var sb = new StringBuilder();
if (!string.IsNullOrEmpty(_schemaName))
sb.AppendFormat("CREATE SCHEMA \"{0}\";", _schemaName);
sb.Append("CREATE TABLE ");
sb.Append(NameWithSchema);
sb.Append(" (");
foreach (string definition in columnDefinitions)
{
sb.Append(definition);
sb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
var s = sb.ToString();
using (var command = new HanaCommand(s, Connection, Transaction))
command.ExecuteNonQuery();
}
public void Create(IEnumerable <string> columnDefinitions)
{
var sb = new StringBuilder();
if (!string.IsNullOrEmpty(_schemaName))
{
sb.AppendFormat("CREATE SCHEMA \"{0}\";", _schemaName);
}
sb.Append("CREATE TABLE ");
sb.Append(NameWithSchema);
sb.Append(" (");
foreach (string definition in columnDefinitions)
{
sb.Append(definition);
sb.Append(", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
var s = sb.ToString();
using (var command = new HanaCommand(s, Connection, Transaction))
command.ExecuteNonQuery();
}
public void WithDefaultValueOn(string column)
{
const int defaultValue = 1;
using (var command = new HanaCommand(string.Format(" ALTER TABLE {0}.{1} ALTER {2} SET DEFAULT {3}", quoter.QuoteSchemaName(_schemaName), quoter.QuoteTableName(Name), quoter.QuoteColumnName(column), defaultValue), Connection, Transaction))
command.ExecuteNonQuery();
}
public DataTable SapListaPrecioDT()
{
DataTable dt = new DataTable();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VTAS_WEB_LISTA_PRECIO";
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
catch (SqlException)
{
throw new Exception();
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
cmd.Parameters.Clear();
}
}
public static T ExecuteToEntity <T>(string commandText
, RowMapper <T> rowMapper
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, CommandType type = CommandType.StoredProcedure) where T : class
{
HanaConnection connection = null;
HanaCommand command = null;
HanaDataReader dataReader = null;
T entity = null;
try
{
connection = OpenConnection();
command = PrepareCommand(commandText, connection, type, parameter, parameters);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
entity = rowMapper(dataReader);
}
}
catch (HanaException)
{
throw;
}
finally
{
CloseDataReader(dataReader);
CloseCommand(command);
CloseConnection(connection);
}
return(entity);
}
public static T ExecuteScalar <T>(string commandText
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, CommandType type = CommandType.StoredProcedure)
{
HanaConnection connection = null;
HanaCommand command = null;
T result;
try
{
connection = OpenConnection();
command = PrepareCommand(commandText, connection, type, parameter, parameters);
result = (T)command.ExecuteScalar();
}
catch (HanaException)
{
throw;
}
finally
{
CloseCommand(command);
CloseConnection(connection);
}
return(result);
}
public static DataSet GetDataSet(string commandText
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, CommandType type = CommandType.StoredProcedure)
{
HanaConnection connection = null;
HanaCommand command = null;
HanaDataAdapter da = new HanaDataAdapter();
DataSet ds = new DataSet();
try
{
connection = OpenConnection();
command = PrepareCommand(commandText, connection, type, parameter, parameters);
da.SelectCommand = command;
da.Fill(ds);
}
catch (HanaException)
{
throw;
}
finally
{
CloseDataAdapter(da);
CloseCommand(command);
CloseConnection(connection);
}
return(ds);
}
public static string getAbsEntry(string id)
{
string result = "";
//List<dataFromOINS> serviceCallsArr = new List<dataFromOINS>();
using (HanaCommand command = new HanaCommand()
{
Connection = connection
})
{
command.CommandText = @"select ""AbsEntry"" from ""OBTN""";
command.CommandText += string.Format(@"where ""DistNumber""='{0}'", id);
connection.Open();
using (HanaDataAdapter da = new HanaDataAdapter(command.CommandText, connection))
{
using (DataTable dt = new DataTable())
{
da.Fill(dt);
result = dt.Rows[0]["AbsEntry"].ToString();
}
}
connection.Close();
return(result);
}
}
public DataSet VentasGeneralesDetalladaSAPDS(string Marca)
{
DataSet dts = new DataSet();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VTAS_WEB_LISTAPRECIOSTOCK";
HanaParameter mar = new HanaParameter();
// Parametro Canal.
mar = cmd.CreateParameter();
mar.HanaDbType = HanaDbType.VarChar;
mar.Direction = ParameterDirection.Input;
mar.Value = Marca;
cmd.Parameters.Add(mar);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dts, "ACSA_VTAS_WEB_LISTAPRECIOSTOCK");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Parameters.Clear();
}
//return (dts.Tables["VentasMensualesGCDetalladoxCanal"]);
return(dts);
/*
*
* catch (SqlException)
* {
*
* throw new Exception();
*
* }
* finally
* {
* if (cnx.State == ConnectionState.Open)
* {
* cnx.Close();
* }
* cmd.Parameters.Clear();
* }*/
}
/// <summary>
/// 新增、修改、刪除
/// 傳入參數對SQL資料做更動
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// 不含交易物件的方法
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料異動數</returns>
public int ExcuteSQL(string sqlString, CommandType cmdType, object[] paramsArr)
{
int num;
using (HanaConnection connection = new HanaConnection(_connectionString))
{
int result = 0;
HanaCommand cmd = new HanaCommand()
{
CommandType = cmdType
};
HanaParameter[] cmdParams = null;
if (paramsArr != null)
{
cmdParams = new HanaParameter[paramsArr.Length];
SettingParams(cmdParams, paramsArr);
}
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = null, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
result = cmd.ExecuteNonQuery();
SqlParameterOutputSetting(cmdParams);
cmd.Parameters.Clear();
num = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
cmd.Dispose();
}
}
return(num);
}
public DataTable VentasGCSAPConsultar(DateTime fecha1, DateTime fecha2, string Canal)
{
DataTable dt = new DataTable();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VTAS_WEB_VentaGC";
HanaParameter f1 = new HanaParameter();
HanaParameter f2 = new HanaParameter();
HanaParameter pc = new HanaParameter();
// Parametro Fecha 1.
f1 = cmd.CreateParameter();
f1.HanaDbType = HanaDbType.Date;
f1.Direction = ParameterDirection.Input;
f1.Value = fecha1;
cmd.Parameters.Add(f1);
// Parametro Fecha 2.
f2 = cmd.CreateParameter();
f2.HanaDbType = HanaDbType.Date;
f2.Direction = ParameterDirection.Input;
f2.Value = fecha2;;
cmd.Parameters.Add(f2);
// Parametro Canal.
pc = cmd.CreateParameter();
pc.HanaDbType = HanaDbType.VarChar;
pc.Direction = ParameterDirection.Input;
pc.Value = Canal;
cmd.Parameters.Add(pc);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
catch (SqlException)
{
throw new Exception();
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
cmd.Parameters.Clear();
}
}
public void WithUniqueConstraintOn(string column, string name)
{
var sb = new StringBuilder();
sb.Append(string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} UNIQUE ({2})", _quoter.QuoteTableName(Name), _quoter.QuoteConstraintName(name), _quoter.QuoteColumnName(column)));
using (var command = new HanaCommand(sb.ToString(), Connection, Transaction))
command.ExecuteNonQuery();
}
private static void CloseCommand(HanaCommand command)
{
if (command != null)
{
command.Parameters.Clear();
command.Dispose();
}
}
/// <summary>
/// 查詢
/// 傳入SqlString跟使用的Type跟HanaParameter參數回傳DataTable
/// Text=SQL語法
/// StoredProcedure=SP
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>查詢的DataTable</returns>
public DataTable QueryDataTable(string sqlString, CommandType cmdType, object[] paramsArr)
{
DataTable dataTable;
using (HanaConnection connection = new HanaConnection(_connectionString))
{
HanaCommand cmd = new HanaCommand()
{
CommandType = cmdType
};
HanaParameter[] cmdParams = null;
if (paramsArr != null)
{
cmdParams = new HanaParameter[paramsArr.Length];
SettingParams(cmdParams, paramsArr);
}
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = null, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
using (HanaDataAdapter da = new HanaDataAdapter(cmd))
{
DataTable dt = new DataTable();
try
{
try
{
da.Fill(dt);
SqlParameterOutputSetting(cmdParams);
cmd.Parameters.Clear();
dataTable = dt;
}
catch (HanaException sqlException)
{
throw new Exception(sqlException.Message);
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
cmd.Dispose();
}
}
}
return(dataTable);
}
public void Drop()
{
var sb = new StringBuilder();
sb.AppendFormat("DROP TABLE {0}", NameWithSchema);
if (!string.IsNullOrEmpty(_schemaName))
sb.AppendFormat(";DROP SCHEMA \"{0}\"", _schemaName);
using (var command = new HanaCommand(sb.ToString(), Connection, Transaction))
command.ExecuteNonQuery();
}
/// <summary>
/// 回傳Scalar(一筆資料的一個欄位)
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料</returns>
public object ExecuteScalar(string sqlString, CommandType cmdType, object[] paramsArr)
{
object obj;
object result = null;
using (HanaConnection connection = new HanaConnection(_connectionString))
{
HanaCommand cmd = new HanaCommand()
{
CommandType = cmdType
};
HanaParameter[] cmdParams = null;
if (paramsArr != null)
{
cmdParams = new HanaParameter[paramsArr.Length];
SettingParams(cmdParams, paramsArr);
}
try
{
CmdSettingModel cmdSetModel = new CmdSettingModel()
{
Conn = connection, Trans = null, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
result = cmd.ExecuteScalar();
SqlParameterOutputSetting(cmdParams);
obj = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
connection.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
}
}
return(obj);
}
static void Main(string[] args)
{
string hostName = "43875a52-8238-4efd-aaa1-19b0f9f714ee.hana.trial-us10.hanacloud.ondemand.com";
string userName = "******";
string password = "******";
string port = "443";
string connectionString = $"Server={hostName}:{port};UID={userName};PWD={password};encrypt=true;sslValidateCertificate=false";
try
{
using (var conn = new HanaConnection(connectionString))
{
conn.Open();
Console.WriteLine("Connected");
//Create table
//var createTableQuery = $"alter table todo alter (\"NAME\" NVARCHAR(500));";
//using (var cmd = new HanaCommand(createTableQuery, conn))
//{
// int n = cmd.ExecuteNonQuery();
//}
// Querying table
var query = "SELECT ID, NAME From todo";
using (var cmd = new HanaCommand(query, conn))
using (var reader = cmd.ExecuteReader())
{
Console.WriteLine("Query result:");
var sbCol = new System.Text.StringBuilder();
for (var i = 0; i < reader.FieldCount; i++)
{
sbCol.Append(reader.GetName(i).PadRight(20));
}
Console.WriteLine(sbCol.ToString());
// Print rows
while (reader.Read())
{
var sbRow = new System.Text.StringBuilder();
for (var i = 0; i < reader.FieldCount; i++)
{
sbRow.Append(reader[i].ToString().PadRight(20));
}
Console.WriteLine(sbRow.ToString());
}
conn.Close();
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error - " + ex.Message);
Console.WriteLine(ex.ToString());
}
}
public void Drop()
{
using (var command = new HanaCommand("DROP SEQUENCE " + NameWithSchema, Connection, Transaction))
command.ExecuteNonQuery();
if (!string.IsNullOrEmpty(_schemaName))
{
using (var command = new HanaCommand(string.Format("DROP SCHEMA \"{0}\"", _schemaName), Connection, Transaction))
command.ExecuteNonQuery();
}
}
public void Drop()
{
using (var command = new HanaCommand("DROP SEQUENCE " + NameWithSchema, Connection, Transaction))
command.ExecuteNonQuery();
if (!string.IsNullOrEmpty(_schemaName))
{
using (var command = new HanaCommand($"DROP SCHEMA \"{_schemaName}\"", Connection, Transaction))
command.ExecuteNonQuery();
}
}
public CadastroPedido ConsultaUltimoPedido()
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
CadastroPedido _Retorno = new CadastroPedido();
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaUltimoPedido, Schema);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
while (productInfoReader.Read())
{
var DocEntry = productInfoReader.GetString(0);
var GetPedido = Service.Get($"Orders({DocEntry})");
_Retorno = JsonConvert.DeserializeObject <CadastroPedido>(GetPedido.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
//var GetJob = Service.Get($"JOB?$select=*&$filter=U_NEO_DocEntry eq '{DocEntry}'");
//var RetObjJob = Newtonsoft.Json.JsonConvert.DeserializeObject<CadastroJobs>(GetJob.Documento, new Newtonsoft.Json.JsonSerializerSettings { NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore });
//if (RetObjJob.value.ToList().Count > 0)
//{
// _Retorno.CadastroJob = new CadastroJob[RetObjJob.value.ToList().Count];
// _Retorno.CadastroJob = RetObjJob.value;
//}
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}
public void Create()
{
if (!string.IsNullOrEmpty(_schemaName))
{
using (var command = new HanaCommand(string.Format("CREATE SCHEMA \"{0}\";", _schemaName), Connection, Transaction))
command.ExecuteNonQuery();
}
string createCommand = string.Format("CREATE SEQUENCE {0} INCREMENT BY 2 MINVALUE 0 MAXVALUE 100 START WITH 2 CACHE 10 CYCLE", NameWithSchema);
using (var command = new HanaCommand(createCommand, Connection, Transaction))
command.ExecuteNonQuery();
}
public void Drop()
{
var sb = new StringBuilder();
sb.AppendFormat("DROP TABLE {0}", NameWithSchema);
if (!string.IsNullOrEmpty(_schemaName))
{
sb.AppendFormat(";DROP SCHEMA \"{0}\"", _schemaName);
}
using (var command = new HanaCommand(sb.ToString(), Connection, Transaction))
command.ExecuteNonQuery();
}
public void Create()
{
if (!string.IsNullOrEmpty(_schemaName))
{
using (var command = new HanaCommand(string.Format("CREATE SCHEMA \"{0}\";", _schemaName), Connection, Transaction))
command.ExecuteNonQuery();
}
string createCommand = string.Format("CREATE SEQUENCE {0} INCREMENT BY 2 MINVALUE 0 MAXVALUE 100 START WITH 2 CACHE 10 CYCLE", NameWithSchema);
using (var command = new HanaCommand(createCommand, Connection, Transaction))
command.ExecuteNonQuery();
}
public string WithIndexOn(string column)
{
var indexName = string.Format("idx_{0}", column);
var quotedObjectName = _quoter.QuoteTableName(Name);
var quotedIndexName = _quoter.QuoteIndexName(indexName);
using (var command = new HanaCommand(string.Format("CREATE INDEX {0} ON {1} ({2})", quotedIndexName, quotedObjectName, _quoter.QuoteColumnName(column)), Connection, Transaction))
command.ExecuteNonQuery();
return(indexName);
}
public CadastroPN ConsultaUltimoPN()
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
CadastroPN _Retorno = new CadastroPN();
try
{
//var RetLogin = Service.Login();
//if (!RetLogin.Sucesso)
//{
// Log.Error("Erro do Login SL");
//}
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaUltimoPN, Schema);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
while (productInfoReader.Read())
{
var CardCode = productInfoReader.GetString(0);
var GetPN = Service.Get($"BusinessPartners('{CardCode}')");
_Retorno = JsonConvert.DeserializeObject <CadastroPN>(GetPN.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}
public DataTable VentaClienteDT(DateTime fechaInicial, DateTime fechaFinal)
{
DataTable dt = new DataTable();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VEN_RPT_VENTACLIENTES";
HanaParameter f1 = new HanaParameter();
HanaParameter f2 = new HanaParameter();
// Parametro Fecha 1.
f1 = cmd.CreateParameter();
f1.HanaDbType = HanaDbType.Date;
f1.Direction = ParameterDirection.Input;
f1.Value = fechaInicial;
cmd.Parameters.Add(f1);
// Parametro Fecha 2.
f2 = cmd.CreateParameter();
f2.HanaDbType = HanaDbType.Date;
f2.Direction = ParameterDirection.Input;
f2.Value = fechaFinal;;
cmd.Parameters.Add(f2);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
catch (SqlException)
{
throw new Exception();
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
cmd.Parameters.Clear();
}
}
public static void addData(Form1.allFields data, int param)
{
using (HanaCommand command = new HanaCommand()
{
Connection = connection
})
{
connection.Open();
data.createDate = (data.createDate != null) ? data.createDate.Replace(".", string.Empty) : null;
data.instDate = (data.instDate != null) ? data.instDate.Replace(".", string.Empty) : null;
data.shipingDate = (data.shipingDate != null) ? data.shipingDate.Replace(".", string.Empty) : null;
data.snDate = (data.snDate != null) ? data.snDate.Replace(".", string.Empty) : null;
data.instDate = (data.instDate == "") ? null : data.instDate;
data.snDate = (data.snDate == "") ? null : data.snDate;
data.insID = (data.insID == "") ? null : data.insID;
string cmdAdd = "";
if (param == 1)
{
cmdAdd = string.Format(@"INSERT INTO ""BRANCHES_EURO"".""@ACC_PAINTS_IT"" (""DocEntry"",""LineId"",""U_Code"",""U_Name"",""U_Picture"",""U_SerialNumber"",""U_Articul"",""U_EAN"",""U_InstDate"",""U_LifeTime"",""U_drDownDate"",""U_Comment"",""U_Warning"",""U_Characteristics"",""U_Modification"",""U_insID"") VALUES ({0}, {1}, '{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',{15})", Int32.Parse(data.docEntry), Int32.Parse(data.lineId), data.code1, data.title, data.photo, data.serialNumber, data.articul, data.EAN, data.instDate, data.srokExpl, data.snDate, data.comment, data.warning, data.charact, data.modif, Int32.Parse(data.insID));
// cmdAdd = string.Format(@"INSERT INTO ""BRANCHES_EURO"".""@ACC_PAINTS_IT"" (""DocEntry"",""LineId"",""U_Code"",""U_Name"",""U_Picture"",""U_SerialNumber"",""U_Articul"",""U_EAN"",""U_InstDate"",""U_LifeTime"",""U_drDownDate"",""U_Comment"",""U_Warning"",""U_Characteristics"",""U_Modification"",""U_insID"") VALUES (" + Int32.Parse(data.docEntry) + "," + Int32.Parse(data.lineId) + "," + data.code1 + "," + data.title + "," + data.photo + "," + data.serialNumber + "," + data.articul + "," + data.EAN + "," + data.instDate + "," + data.srokExpl + "," + data.snDate + "," + data.comment + "," + data.warning + "," + data.charact + "," + data.modif + "," + Int32.Parse(data.insID) + ")");
using (HanaCommand cmd = new HanaCommand()
{
CommandText = cmdAdd, Connection = connection
})
{
cmd.ExecuteNonQuery();
}
//command.CommandText = string.Format(@"INSERT INTO ""BRANCHES_EURO"".""@ACC_PAINTS_IT"" (""DocEntry"",""LineId"",""U_Code"",""U_Name"",""U_Picture"",""U_SerialNumber"",""U_Articul"",""U_EAN"",""U_InstDate"",""U_LifeTime"",""U_drDownDate"",""U_Comment"",""U_Warning"",""U_Characteristics"",""U_Modification"",""U_insID"") VALUES ({0}, {1}, '{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',{15})", Int32.Parse(data.docEntry), Int32.Parse(data.lineId), data.code1, data.title, data.photo, data.serialNumber, data.articul, data.EAN, data.instDate, data.srokExpl, data.snDate, data.comment, data.warning, data.charact, data.modif, Int32.Parse(data.insID));
//command.CommandText = string.Format(@"insert into ""@ACC_PAINTS_IT"" (""DocEntry"",""LineId"",""U_Code"",""U_Name"",""U_InstDate"",""U_drDownDate"",""U_insID"") values ({0},{1},'{2}','{3}','{4}','{5}',{6})", Int32.Parse(data.docEntry), Int32.Parse(data.lineId), data.code1, data.title, data.instDate, data.snDate, Int32.Parse(data.insID));
//string cmdAdd = @"INSERT INTO ""BRANCHES_EURO"".""@ACC_PAINTS_IT"" (""DocEntry"",""LineId"",""U_Code"",""U_Name"",""U_InstDate"",""U_insID"") VALUES (" + Int32.Parse(data.docEntry) + "," + Int32.Parse(data.lineId) + "," + data.code1 + "," + data.title + "," + data.instDate + "," + Int32.Parse(data.insID) + ")";
//command.CommandText = string.Format(@"INSERT INTO ""BRANCHES_EURO"".""@ACC_PAINTS_IT"" (""DocEntry"",""LineId"",""U_InstDate"",""U_drDownDate"",""U_insID"") VALUES ({0},{1},'{2}','{3}',{4})", Int32.Parse(data.docEntry), Int32.Parse(data.lineId), data.instDate, data.snDate, Int32.Parse(data.insID));
//command.ExecuteNonQuery();
}
else if (param == 2)
{
cmdAdd = @"INSERT INTO ""BRANCHES_EURO"".""@ACC_PAINTS_ITEMS_2"" (""DocEntry"",""LineId"",""U_Code"",""U_TitlePaint"",""U_BatchNumber"",""U_Articul"",""U_EAN"",""U_ManufDate"",""U_ShelfLife"",""U_DeliveryDate"",""U_Comment"",""U_Warning"",""U_insID"") VALUES (" + Int32.Parse(data.docEntry) + "," + Int32.Parse(data.lineId) + "," + data.code1 + "," + data.title + "," + data.batchNumber + "," + data.serialNumber + "," + data.articul + "," + data.EAN + "," + data.createDate + "," + data.srokExpl + "," + data.shipingDate + "," + data.comment + "," + data.warning + "," + Int32.Parse(data.insID) + ")";
//command.CommandText = string.Format(@"INSERT INTO ""BRANCHES_EURO"".""@ACC_PAINTS_ITEMS_2"" (""DocEntry"",""LineId"",""U_Code"",""U_TitlePaint"",""U_BatchNumber"",""U_Articul"",""U_EAN"",""U_ManufDate"",""U_ShelfLife"",""U_DeliveryDate"",""U_Comment"",""U_Warning"",""U_insID"") VALUES ({0}, {1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12})", Int32.Parse(data.docEntry), Int32.Parse(data.lineId), data.code1, data.title, data.batchNumber, data.articul, data.EAN, data.createDate, data.srokExpl, data.shipingDate, data.comment,data.warning,Int32.Parse(data.insID));
}
connection.Close();
}
}
public DataTable VentasGeneralesDetalladaSAPDT(DateTime FechaInicial, DateTime FechaFinal)
{
DataTable dt = new DataTable();
try
{
cnx.Open();
HanaCommand cmd = new HanaCommand("", cnx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
cmd.CommandText = "SBO_ACSA_PROD.ACSA_VEN_RPT_VENTAXARTICULO";
HanaParameter fechaInicial = new HanaParameter();
HanaParameter fechaFinal = new HanaParameter();
// Parametro Fecha Inicial.
fechaInicial = cmd.CreateParameter();
fechaInicial.HanaDbType = HanaDbType.Date;
fechaInicial.Direction = ParameterDirection.Input;
fechaInicial.Value = FechaInicial;
cmd.Parameters.Add(fechaInicial);
// Parametro Fecha Final.
fechaFinal = cmd.CreateParameter();
fechaFinal.HanaDbType = HanaDbType.Date;
fechaFinal.Direction = ParameterDirection.Input;
fechaFinal.Value = FechaFinal;
cmd.Parameters.Add(fechaFinal);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
catch (SqlException)
{
throw new Exception();
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
cmd.Parameters.Clear();
}
}
public static void updateData(Form1.allFields data, int param)
{
using (HanaCommand command = new HanaCommand()
{
Connection = connection
})
{
connection.Open();
string cmdAdd = "";
if (param == 1)
{
using (HanaCommand cmd = new HanaCommand()
{
CommandText = cmdAdd, Connection = connection
})
{
command.CommandText = @"UPDATE ""BRANCHES_EURO"".""@ACC_PAINTS_IT"" SET (";
command.CommandText += string.Format(@"""DocEntry""={0}", 1); //Int32.Parse(data.docEntry));
command.CommandText += string.Format(@"""LineId""={0}", 1); //Int32.Parse(data.lineId));
command.CommandText += string.Format(@"""U_Code""='{0}'", "001"); //data.code1);
command.CommandText += string.Format(@"""U_Name""='{0}'", "Товар 1 [Batch]"); //data.title);
command.CommandText += string.Format(@"where ""insID""={0}", 2); //Int32.Parse(data.insID));
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
cmdAdd = @"UPDATE ""BRANCHES_EURO"".""@ACC_PAINTS_IT"" SET (""DocEntry"",""LineId"",""U_Code"",""U_Name"",""U_Picture"",""U_SerialNumber"",""U_Articul"",""U_EAN"",""U_InstDate"",""U_LifeTime"",""U_drDownDate"",""U_Comment"",""U_Warning"",""U_Characteristics"",""U_Modification"") VALUES (" + Int32.Parse(data.docEntry) + "," + Int32.Parse(data.lineId) + "," + data.code1 + "," + data.title + "," + data.photo + "," + data.serialNumber + "," + data.articul + "," + data.EAN + "," + data.instDate + "," + data.srokExpl + "," + data.snDate + "," + data.comment + "," + data.warning + "," + data.charact + "," + data.modif + ")";
//cmdAdd = @"UPDATE ""BRANCHES_EURO"".""@ACC_PAINTS_ITEMS_2"" SET ""LineId""=" + Int32.Parse(data.lineId) + "WHERE \"DocEntry\"=" + Int32.Parse(data.docEntry);
//cmdAdd = @"UPDATE ""BRANCHES_EURO"".""@ACC_PAINTS_ITEMS_2"" SET ""LineId""= ' WHERE \"DocEntry\"=" + Int32.Parse(data.docEntry);
}
else if (param == 2)
{
cmdAdd = @"UPDATE ""BRANCHES_EURO"".""@ACC_PAINTS_ITEMS_2"" SET ""LineId""=" + Int32.Parse(data.lineId) + "WHERE \"U_insID\"=" + Int32.Parse(data.insID);
}
using (HanaCommand cmd = new HanaCommand()
{
CommandText = cmdAdd, Connection = connection
})
{
cmd.ExecuteNonQuery();
}
connection.Close();
}
}
private DataTable GetReportData()
{
DataGridView gr = new DataGridView();
HanaConnection conn = new HanaConnection(string.Format("Server={0};UserID={1};Password={2}", ConfigurationManager.AppSettings["SAPServer"], ConfigurationManager.AppSettings["SAPUsername"], ConfigurationManager.AppSettings["SAPPassword"]));
conn.Open();
HanaCommand selectCmd = new HanaCommand(string.Format("SELECT \"QUERY\" AS \"SqlQuery\" FROM {1}.\"@PRINTDOCUMENT\" WHERE \"Code\" LIKE '{0}'", this.oid, ConfigurationManager.AppSettings["SAPDatabase"]), conn);
HanaDataReader dr = selectCmd.ExecuteReader();
gr.DataSource = dr;
DataTable dt = new DataTable();
dt.Load(dr);
dr.Close();
conn.Close();
if (dt == null || dt.Rows.Count == 0)
{
return(null);
}
var cmdText = System.Text.ASCIIEncoding.ASCII.GetString((byte[])dt.Rows[0]["SqlQuery"]);
if (string.IsNullOrEmpty(cmdText))
{
return(null);
}
return(Utility.hanaConnection(cmdText));
//var conn1 = DBConnection.conn;
//try
//{
// var obj = Utility.hanaConnection(string.Format("SELECT \"QUERY\" AS \"SqlQuery\" FROM \"@PRINTDOCUMENT\" WHERE \"Code\" LIKE '{0}'", this.oid));
// if (obj == null || obj.Rows.Count == 0) return null;
// var cmdText = System.Text.ASCIIEncoding.ASCII.GetString((byte[])obj.Rows[0]["SqlQuery"]);
// if (string.IsNullOrEmpty(cmdText)) return null;
// return
// Utility.GetObjects(cmdText, conn1);
//}
//catch (Exception)
//{
// return null;
//}
}
public void CallUpdateProcedure(HanaConnection connection, int docEntry, int result)
{
try
{
HanaCommand cmd = new HanaCommand(Queries.UpdateResult, connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@DocEntry", SqlDbType.Int).Value = docEntry;
cmd.Parameters.Add("@Result", SqlDbType.Int).Value = result;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
CallUpdateProcedure(connection, docEntry, 3);
}
}
public CadastroAtividade ConsultaUltimoAtividade()
{
WS.ServiceLayer.ServiceLayer Service = new WS.ServiceLayer.ServiceLayer();
CadastroAtividade _Retorno = new CadastroAtividade();
try
{
using (HanaConnection conn = new HanaConnection(ConfigurationManager.ConnectionStrings["Hana"].ConnectionString))
{
conn.Open();
var Schema = ConfigurationManager.AppSettings["CompanyDB"];
string Sql = string.Format(Properties.Resources.ConsultaUltimoAtividade, Schema);
using (HanaCommand cmd3 = new HanaCommand(Sql, conn))
{
using (HanaDataReader productInfoReader3 = cmd3.ExecuteReader())
{
HanaCommand cmd = new HanaCommand(Sql, conn);
HanaDataReader productInfoReader = cmd.ExecuteReader();
while (productInfoReader.Read())
{
var DocEntry = productInfoReader.GetString(0);
var CardName = productInfoReader.GetString(1);
var GetAtividade = Service.Get($"Activities({DocEntry})");
_Retorno = JsonConvert.DeserializeObject <CadastroAtividade>(GetAtividade.Documento, new Newtonsoft.Json.JsonSerializerSettings {
NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore
});
_Retorno.CardName = CardName;
}
}
}
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
}
finally
{
//Service.Logout();
}
return(_Retorno);
}
/// <summary>
/// Update and initialize components
/// </summary>
private void CleanCommands()
{
if (SAPSettings.Current.DbServerType == SAPbobsCOM.BoDataServerTypes.dst_HANADB)
{
SQLCommand = new HanaCommand
{
Connection = (HanaConnection)SQLConnection
};
}
else
{
SQLCommand = new SqlCommand
{
Connection = (SqlConnection)SQLConnection
};
}
}
public void WithUniqueConstraintOn(string column, string name)
{
var sb = new StringBuilder();
sb.Append(string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} UNIQUE ({2})", quoter.QuoteTableName(Name), quoter.QuoteConstraintName(name), quoter.QuoteColumnName(column)));
using (var command = new HanaCommand(sb.ToString(), Connection))
command.ExecuteNonQuery();
constraints.Add(name);
}
public void WithDefaultValueOn(string column)
{
const int defaultValue = 1;
using (var command = new HanaCommand(string.Format(" ALTER TABLE {0}.{1} ALTER {2} SET DEFAULT {3}", quoter.QuoteSchemaName(_schemaName), quoter.QuoteTableName(Name), quoter.QuoteColumnName(column), defaultValue), Connection, Transaction))
command.ExecuteNonQuery();
}
public string WithIndexOn(string column)
{
var indexName = string.Format("idx_{0}", column);
var quotedObjectName = quoter.QuoteTableName(Name);
var quotedIndexName = quoter.QuoteIndexName(indexName);
indexies.Add(quotedIndexName);
using (var command = new HanaCommand(string.Format("CREATE INDEX {0} ON {1} ({2})", quotedIndexName, quotedObjectName, quoter.QuoteColumnName(column)), Connection, Transaction))
command.ExecuteNonQuery();
return indexName;
}