public void SetUp()
{
Connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
Processor = new HanaProcessor(Connection, new HanaGenerator(), new TextWriterAnnouncer(System.Console.Out), new ProcessorOptions(), new HanaDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
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 void SetUp()
{
Connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
Processor = new HanaProcessor(Connection, new HanaGenerator(), new TextWriterAnnouncer(System.Console.Out), new ProcessorOptions(), new HanaDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
public void TestInsertOperationHanaShortTypes()
{
IScriptBuilder builder = new ScriptHanaBuilder();
var cliente = new Employee()
{
Id = 1, Name = "Moisés", Age = 25
};
var hanaConnection = new HanaConnection();
hanaConnection.ConnectionString = ConnectionStringReader.GetConnstring("hana");
hanaConnection.Open();
var trans = hanaConnection.BeginTransaction();
var createTableScript = builder.GetCreateTableCommand <Employee>();
hanaConnection.Execute(createTableScript);
hanaConnection.Insert <Employee>(cliente);
var employeeFromDatabase = hanaConnection.GetAll <Employee>();
Assert.AreEqual(1, employeeFromDatabase.Count());
hanaConnection.Execute("drop table \"Employee\"");
}
public async Task <string> TestSapConnection()
{
try
{
using (var conn = new HanaConnection(_options.Value.DbConnectionString))
{
conn.Open();
var schema = _sapServiceSettingsService.GetSapSchema("AR");
var query = $"select * from {schema}.oeml";
var da = new HanaDataAdapter(query, conn);
var dt = new DataTable("Invoices");
da.Fill(dt);
conn.Close();
}
}
catch (Exception ex)
{
_logger.LogError(ex, "Error testing Hana connection");
return(ex.Message);
}
return("Successfully");
}
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);
}
private async Task <DataTable> GetInvoiceRecords(string clientPrefix, int clientId, string sapSystem, int?fileId = null)
{
using (var conn = new HanaConnection(_options.Value.DbConnectionString))
{
conn.Open();
var query = string.Empty;
/* Invoices */
query += CreateInvoiceQuery("FC", clientPrefix, clientId, sapSystem, fileId);
query += " UNION ";
/* Credit Notes */
query += CreateInvoiceQuery("NC", clientPrefix, clientId, sapSystem, fileId);
var da = new HanaDataAdapter(query, conn);
var dt = new DataTable("Invoices");
da.Fill(dt);
conn.Close();
return(dt);
}
}
public static Entities1 Create()
{
string schemaName = "SUHUT_DATAACCESS";
DbCompiledModel compiledModel;
string connString = ConfigurationManager.ConnectionStrings["Model1"].ToString();
using (var connection = new HanaConnection(connString))
{
//connection.Open();
compiledModel = modelCache.GetOrAdd(
Tuple.Create(connection.ConnectionString, schemaName),
t =>
{
var builder = new DbModelBuilder();
//builder.Conventions.Remove<PluralizingTableNameConvention>();
//builder.Conventions.Remove<ForeignKeyIndexConvention>();
builder = IduEfConfigurations.SetConfigurations(builder, schemaName);
var model = builder.Build(connection);
return(model.Compile());
});
return(new Entities1(connString, compiledModel));
}
}
public static HanaConnection ConnectToDataBase()
{
HanaConnection connection;
connection = new HanaConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Hana"].ConnectionString);
return(connection);
}
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 ScopedConnection()
{
Connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
Processor = new HanaProcessor(Connection, new HanaGenerator(), new TextWriterAnnouncer(TestContext.Out), new ProcessorOptions(), new HanaDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
/// <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 static void EndTransaction(ref HanaConnection sqlConnection, ref HanaTransaction sqlTransaction)
{
if (sqlTransaction != null)
{
sqlTransaction.Dispose();
}
CloseConnection(sqlConnection);
}
private static void CloseConnection(HanaConnection connection)
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
private static HanaConnection OpenConnection()
{
HanaConnection connection = new HanaConnection();
connection.ConnectionString = Constantes.CNX_STRING;
connection.Open();
return(connection);
}
/// <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);
}
/// <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 SapDocAutorizadosDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch
{
throw new Exception();
}
}
public SapCostosInventarioDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch (SqlException)
{
throw new Exception();
}
}
public SAPListaPrecioStockDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch (SqlException)
{
throw new Exception();
}
}
public SapStockXMarcaDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch (SqlException)
{
throw new Exception();
}
}
public SAPVentaArticulosXFechasDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch (HanaException)
{
throw new Exception();
}
}
public SAPVentasGCPuntoDetalleDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch (SqlException)
{
throw new Exception();
}
}
public void SetUp()
{
if (!IntegrationTestOptions.Hana.IsEnabled)
{
Assert.Ignore();
}
Connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
Processor = new HanaProcessor(Connection, new HanaGenerator(), new TextWriterAnnouncer(TestContext.Out), new ProcessorOptions(), new HanaDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
public SAPSeguimientoOCDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch (SqlException)
{
throw new Exception();
}
}
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 SAPStockxProductoDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch (SqlException)
{
throw new Exception();
}
}
public SAPVentasClientesDatos()
{
try
{
cnx = new HanaConnection(MiConexi.GetConex());
}
catch (SqlException)
{
throw new Exception();
}
}
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);
}
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 CallingProcessWithPerformDbOperationExpressionWhenInPreviewOnlyModeWillNotMakeDbChanges()
{
var output = new StringWriter();
var connection = new HanaConnection(IntegrationTestOptions.Hana.ConnectionString);
var processor = new HanaProcessor(
connection,
new HanaGenerator(),
new TextWriterAnnouncer(output),
new ProcessorOptions { PreviewOnly = true },
new HanaDbFactory());
bool tableExists;
try
{
var expression =
new PerformDBOperationExpression
{
Operation = (con, trans) =>
{
var command = con.CreateCommand();
command.CommandText = "CREATE TABLE ProcessTestTable (test int NULL) ";
command.Transaction = trans;
command.ExecuteNonQuery();
}
};
processor.Process(expression);
tableExists = processor.TableExists("", "ProcessTestTable");
}
finally
{
processor.RollbackTransaction();
}
tableExists.ShouldBeFalse();
var fmOutput = output.ToString();
Assert.That(fmOutput, Is.StringContaining("/* Performing DB Operation */"));
}
