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 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 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();
}
/// <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 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();
* }*/
}
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();
}
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 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 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 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();
}
}
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 void Create()
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
if (!string.IsNullOrEmpty(_schemaName))
{
using (var command = new HanaCommand($"CREATE SCHEMA \"{_schemaName}\";", Connection, Transaction))
command.ExecuteNonQuery();
}
string createCommand = $"CREATE SEQUENCE {NameWithSchema} INCREMENT BY 2 MINVALUE 0 MAXVALUE 100 START WITH 2 CACHE 10 CYCLE";
using (var command = new HanaCommand(createCommand, Connection, Transaction))
command.ExecuteNonQuery();
}
public DataTable VentasGeneralesDetalladaSAPDT(string Marca)
{
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_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(dt);
return(dt);
}
catch (SqlException)
{
throw new Exception();
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
cmd.Parameters.Clear();
}
}
public DataTable SapListaPrecioDetalleDT(string Lista)
{
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_DETALLES";
HanaParameter List = new HanaParameter();
// Parametro Canal.
List = cmd.CreateParameter();
List.HanaDbType = HanaDbType.VarChar;
List.Direction = ParameterDirection.Input;
List.Value = Lista;
cmd.Parameters.Add(List);
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 Execute(string commandText
, HanaParameter parameter = null
, List <HanaParameter> parameters = null
, Dictionary <string, object> paramsOutputValue = null
, CommandType type = CommandType.StoredProcedure
, HanaConnection connection = null
, HanaTransaction transaction = null)
{
HanaCommand command = null;
try
{
if (connection == null)
{
connection = OpenConnection();
}
command = PrepareCommand(commandText, connection, type, parameter, parameters, transaction);
command.ExecuteNonQuery();
if (parameter != null || parameters != null)
{
foreach (HanaParameter x in parameters)
{
if (x.Direction == ParameterDirection.Output)
{
paramsOutputValue.Add(x.ParameterName, command.Parameters[x.ParameterName].Value);
}
}
}
}
catch (HanaException)
{
throw;
}
finally
{
CloseCommand(command);
if (transaction == null)
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 新增、修改、刪除
/// 傳入參數對SQL資料做更動
/// 此方法可自行設定要用哪種Type(SP OR SQLString)
/// 包含交易物件
/// </summary>
/// <param name="sqlString">SqlSreing語法或SP名稱</param>
/// <param name="cmdType">使用的Type</param>
/// <param name="connection">連線物件(為了確保同一個連線)</param>
/// <param name="tran">交易物件(為了確保同一筆交易)</param>
/// <param name="paramsArr">HanaParameter參數陣列</param>
/// <returns>回傳資料異動數</returns>
public int ExcuteSQL(string sqlString, CommandType cmdType, ref HanaConnection connection, ref HanaTransaction tran, object[] paramsArr)
{
int num;
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 = tran, Text = sqlString
};
SettingCommand(cmd, cmdSetModel, cmdParams);
try
{
result = cmd.ExecuteNonQuery();
SqlParameterOutputSetting(cmdParams);
num = result;
}
catch (HanaException sqlException)
{
throw new Exception(string.Concat("存取SQL Server發生錯誤. SysInfo=", sqlException.Message));
}
catch (Exception exception)
{
throw exception;
}
return(num);
}
public DataTable VentasSTOCKSAPConsultar(String CodigoStock)
{
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_LOG_WEB_Stock";
HanaParameter param = new HanaParameter();
param = cmd.CreateParameter();
param.HanaDbType = HanaDbType.NVarChar;
param.Direction = ParameterDirection.Input;
param.Value = CodigoStock;
cmd.Parameters.Add(param);
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 SaveLayout(System.IO.MemoryStream stream, string id)
{
try
{
HanaConnection conn = new HanaConnection(string.Format("Server={0};UserID={1};Password={2}", ConfigurationManager.AppSettings["SAPServer"], ConfigurationManager.AppSettings["SAPUsername"], ConfigurationManager.AppSettings["SAPPassword"]));
conn.Open();
HanaCommand insertCmd = new HanaCommand(string.Format("UPDATE {0}.\"@PRINTDOCUMENT\" SET \"LAYOUT\" = ? WHERE \"Code\" LIKE ?", System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]), conn);
HanaParameter parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarBinary;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
insertCmd.Parameters[0].Value = stream.ToArray();
insertCmd.Parameters[1].Value = id;
int recordsAffected = insertCmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet SAPSeguimientoOCDS(DateTime FechaInicio, DateTime FechaFin, string CodProveedor, string NomProveedor, string Solicitud, string Orden, string Entrada, string Factura)
{
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_LOG_RPT_SEGUIMIENTO_OC";
HanaParameter fechaI = new HanaParameter();
HanaParameter fechaFin = new HanaParameter();
HanaParameter codPro = new HanaParameter();
HanaParameter nomPro = new HanaParameter();
HanaParameter Soli = new HanaParameter();
HanaParameter Ord = new HanaParameter();
HanaParameter entrada = new HanaParameter();
HanaParameter factura = new HanaParameter();
// Parametro Fecha 1.
fechaI = cmd.CreateParameter();
fechaI.HanaDbType = HanaDbType.Date;
fechaI.Direction = ParameterDirection.Input;
fechaI.Value = FechaInicio;
cmd.Parameters.Add(fechaI);
// Parametro Fecha 2.
fechaFin = cmd.CreateParameter();
fechaFin.HanaDbType = HanaDbType.Date;
fechaFin.Direction = ParameterDirection.Input;
fechaFin.Value = FechaFin;;
cmd.Parameters.Add(fechaFin);
codPro = cmd.CreateParameter();
codPro.HanaDbType = HanaDbType.VarChar;
codPro.Direction = ParameterDirection.Input;
codPro.Value = CodProveedor;
cmd.Parameters.Add(codPro);
nomPro = cmd.CreateParameter();
nomPro.HanaDbType = HanaDbType.VarChar;
nomPro.Direction = ParameterDirection.Input;
nomPro.Value = NomProveedor;
cmd.Parameters.Add(nomPro);
Soli = cmd.CreateParameter();
Soli.HanaDbType = HanaDbType.VarChar;
Soli.Direction = ParameterDirection.Input;
Soli.Value = Solicitud;
cmd.Parameters.Add(Soli);
Ord = cmd.CreateParameter();
Ord.HanaDbType = HanaDbType.VarChar;
Ord.Direction = ParameterDirection.Input;
Ord.Value = Orden;
cmd.Parameters.Add(Ord);
entrada = cmd.CreateParameter();
entrada.HanaDbType = HanaDbType.VarChar;
entrada.Direction = ParameterDirection.Input;
entrada.Value = Entrada;
cmd.Parameters.Add(entrada);
factura = cmd.CreateParameter();
factura.HanaDbType = HanaDbType.VarChar;
factura.Direction = ParameterDirection.Input;
factura.Value = Factura;
cmd.Parameters.Add(factura);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dts, "ACSA_LOG_RPT_SEGUIMIENTO_OC");
}
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();
* }*/
}
public static string InsertUpdatePrintDocument(string name, string group,
string sqlQuery, string printDocumentID,
DateTime?modifiedDate = null)
{
var cmd = "";
var isInsert = false;
if (printDocumentID == "" || printDocumentID == "0")
{
var printID = Utility.hanaConnection(string.Format("SELECT TOP 1 \"Code\" FROM {0}.\"@PRINTDOCUMENT\" ORDER BY \"Code\" * 1 DESC", System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]));
if (printID == null)
{
printDocumentID = "1";
}
else
{
if (printID.Rows.Count == 0)
{
printDocumentID = "1";
}
else
{
printDocumentID = (Convert.ToDecimal(printID.Rows[0]["Code"]) + 1).ToString();
}
}
HanaConnection conn = new HanaConnection(string.Format("Server={0};UserID={1};Password={2}", ConfigurationManager.AppSettings["SAPServer"], ConfigurationManager.AppSettings["SAPUsername"], ConfigurationManager.AppSettings["SAPPassword"]));
conn.Open();
HanaCommand insertCmd = new HanaCommand(string.Format("INSERT INTO {0}.\"@PRINTDOCUMENT\" (\"Name\", \"QUERY\", \"Code\", \"GROUP\", \"PRINTNAME\", \"U_IsDeleted\") VALUES ( ?, ?, ?, ?, ?, 0)", System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]), conn);
HanaParameter parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarBinary;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
insertCmd.Parameters[0].Value = group + ' ' + name;
insertCmd.Parameters[1].Value = System.Text.Encoding.ASCII.GetBytes(sqlQuery);
insertCmd.Parameters[2].Value = printDocumentID;
insertCmd.Parameters[3].Value = group;
insertCmd.Parameters[4].Value = name;
int recordsAffected = insertCmd.ExecuteNonQuery();
conn.Close();
isInsert = true;
}
else
{
HanaConnection conn = new HanaConnection(string.Format("Server={0};UserID={1};Password={2}", ConfigurationManager.AppSettings["SAPServer"], ConfigurationManager.AppSettings["SAPUsername"], ConfigurationManager.AppSettings["SAPPassword"]));
conn.Open();
HanaCommand insertCmd = new HanaCommand(string.Format("UPDATE {0}.\"@PRINTDOCUMENT\" SET \"Name\" = ?, \"QUERY\" = ?, \"GROUP\" = ?, \"PRINTNAME\" = ? WHERE \"Code\" = ?", System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]), conn);
HanaParameter parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarBinary;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
parm = new HanaParameter();
parm.HanaDbType = HanaDbType.VarChar;
insertCmd.Parameters.Add(parm);
insertCmd.Parameters[0].Value = group + ' ' + name;
insertCmd.Parameters[1].Value = System.Text.Encoding.ASCII.GetBytes(sqlQuery);
insertCmd.Parameters[2].Value = group;
insertCmd.Parameters[3].Value = name;
insertCmd.Parameters[4].Value = printDocumentID;
int recordsAffected = insertCmd.ExecuteNonQuery();
conn.Close();
}
if (isInsert)
{
var obj = Utility.hanaConnection(string.Format("SELECT \"Code\" FROM {1}.\"@PRINTDOCUMENT\" WHERE \"U_IsDeleted\" = 0 AND \"Name\" LIKE '{0}'", group + ' ' + name, System.Configuration.ConfigurationManager.AppSettings["SAPDatabase"]));
printDocumentID = obj == null || obj.Rows.Count == 0 ? "0" : obj.Rows[0]["Code"].ToString();
}
return(printDocumentID);
}
public DataTable DocumentosAutorizadosConsultar(DateTime fechaInicial, DateTime fechaFinal, string numSAP, string codCliente, string codAlmacen)
{
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_FLUJODEOVS";
HanaParameter FechaInicio = new HanaParameter();
HanaParameter FechaFin = new HanaParameter();
HanaParameter NumSAP = new HanaParameter();
HanaParameter CodCliente = new HanaParameter();
HanaParameter CodAlmacen = new HanaParameter();
// Parametro Fecha 1.
FechaInicio = cmd.CreateParameter();
FechaInicio.HanaDbType = HanaDbType.Date;
FechaInicio.Direction = ParameterDirection.Input;
FechaInicio.Value = fechaInicial;
cmd.Parameters.Add(FechaInicio);
// Parametro Fecha 2.
FechaFin = cmd.CreateParameter();
FechaFin.HanaDbType = HanaDbType.Date;
FechaFin.Direction = ParameterDirection.Input;
FechaFin.Value = fechaFinal;;
cmd.Parameters.Add(FechaFin);
// Parametro Numero de Orden de Venta.
NumSAP = cmd.CreateParameter();
NumSAP.HanaDbType = HanaDbType.VarChar;
NumSAP.Direction = ParameterDirection.Input;
NumSAP.Value = numSAP;
cmd.Parameters.Add(NumSAP);
// Parametro Cliente.
CodCliente = cmd.CreateParameter();
CodCliente.HanaDbType = HanaDbType.VarChar;
CodCliente.Direction = ParameterDirection.Input;
CodCliente.Value = codCliente;
cmd.Parameters.Add(CodCliente);
// Parametro Almacen.
CodAlmacen = cmd.CreateParameter();
CodAlmacen.HanaDbType = HanaDbType.VarChar;
CodAlmacen.Direction = ParameterDirection.Input;
CodAlmacen.Value = codAlmacen;
cmd.Parameters.Add(CodAlmacen);
cmd.ExecuteNonQuery();
HanaDataAdapter ada = new HanaDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
catch
{
throw new Exception();
}
finally
{
if (cnx.State == ConnectionState.Open)
{
cnx.Close();
}
cmd.Parameters.Clear();
}
}
public DataTable CostoInvetarioDSDT(DateTime FechaInicio, DateTime FechaFin, string CodCliente, 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_CON_RPT_INVENTARIO";
HanaParameter f1 = new HanaParameter();
HanaParameter f2 = new HanaParameter();
HanaParameter cc = new HanaParameter();
HanaParameter c = new HanaParameter();
// Parametro Fecha 1.
f1 = cmd.CreateParameter();
f1.HanaDbType = HanaDbType.Date;
f1.Direction = ParameterDirection.Input;
f1.Value = FechaInicio;
cmd.Parameters.Add(f1);
// Parametro Fecha 2.
f2 = cmd.CreateParameter();
f2.HanaDbType = HanaDbType.Date;
f2.Direction = ParameterDirection.Input;
f2.Value = FechaFin;;
cmd.Parameters.Add(f2);
// Parametro Cliente.
cc = cmd.CreateParameter();
cc.HanaDbType = HanaDbType.VarChar;
cc.Direction = ParameterDirection.Input;
cc.Value = CodCliente;
cmd.Parameters.Add(cc);
// Parametro Canal.
c = cmd.CreateParameter();
c.HanaDbType = HanaDbType.VarChar;
c.Direction = ParameterDirection.Input;
c.Value = Canal;
cmd.Parameters.Add(c);
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 DataTable SAPSeguimientoOCIMPDT(DateTime FechaInicio, DateTime FechaFin, string CodProveedor, string NomProveedor, string Solicitud, string Orden, string Reserva, string Entrada, string Precio, string Transferencia)
{
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_LOG_RPT_SEGUIMIENTO_OC_IMP";
HanaParameter fechaI = new HanaParameter();
HanaParameter fechaFin = new HanaParameter();
HanaParameter codPro = new HanaParameter();
HanaParameter nomPro = new HanaParameter();
HanaParameter Soli = new HanaParameter();
HanaParameter Ord = new HanaParameter();
HanaParameter reser = new HanaParameter();
HanaParameter entrada = new HanaParameter();
HanaParameter precio = new HanaParameter();
HanaParameter transferencia = new HanaParameter();
// Parametro Fecha 1.
fechaI = cmd.CreateParameter();
fechaI.HanaDbType = HanaDbType.Date;
fechaI.Direction = ParameterDirection.Input;
fechaI.Value = FechaInicio;
cmd.Parameters.Add(fechaI);
// Parametro Fecha 2.
fechaFin = cmd.CreateParameter();
fechaFin.HanaDbType = HanaDbType.Date;
fechaFin.Direction = ParameterDirection.Input;
fechaFin.Value = FechaFin;;
cmd.Parameters.Add(fechaFin);
codPro = cmd.CreateParameter();
codPro.HanaDbType = HanaDbType.VarChar;
codPro.Direction = ParameterDirection.Input;
codPro.Value = CodProveedor;
cmd.Parameters.Add(codPro);
nomPro = cmd.CreateParameter();
nomPro.HanaDbType = HanaDbType.VarChar;
nomPro.Direction = ParameterDirection.Input;
nomPro.Value = NomProveedor;
cmd.Parameters.Add(nomPro);
Soli = cmd.CreateParameter();
Soli.HanaDbType = HanaDbType.VarChar;
Soli.Direction = ParameterDirection.Input;
Soli.Value = Solicitud;
cmd.Parameters.Add(Soli);
Ord = cmd.CreateParameter();
Ord.HanaDbType = HanaDbType.VarChar;
Ord.Direction = ParameterDirection.Input;
Ord.Value = Orden;
cmd.Parameters.Add(Ord);
reser = cmd.CreateParameter();
reser.HanaDbType = HanaDbType.VarChar;
reser.Direction = ParameterDirection.Input;
reser.Value = Reserva;
cmd.Parameters.Add(reser);
entrada = cmd.CreateParameter();
entrada.HanaDbType = HanaDbType.VarChar;
entrada.Direction = ParameterDirection.Input;
entrada.Value = Entrada;
cmd.Parameters.Add(entrada);
precio = cmd.CreateParameter();
precio.HanaDbType = HanaDbType.VarChar;
precio.Direction = ParameterDirection.Input;
precio.Value = Precio;
cmd.Parameters.Add(precio);
transferencia = cmd.CreateParameter();
transferencia.HanaDbType = HanaDbType.VarChar;
transferencia.Direction = ParameterDirection.Input;
transferencia.Value = Transferencia;
cmd.Parameters.Add(transferencia);
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 DataSet VentaClienteDS(DateTime fechaInicial, DateTime fechaFinal)
{
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_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(dts, "ACSA_VEN_RPT_VENTACLIENTES");
}
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();
* }*/
}
public DataSet VentasGeneralesDetalladaSAPEEDS(DateTime fecha1, DateTime fecha2, string Canal)
{
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_VentaGCxDetalladaProducto";
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(dts, "ACSA_VTAS_WEB_VentaGCxDetalladaProducto");
}
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();
* }*/
}
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;
}