/// <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; }