public DataTable FuzzySearch(string content)
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_FuzzySearchProduct");
sp.AddParam("@Content", SqlDbType.NVarChar, content);
return(DataProvider.Instance.GetTable(_Loginer.DBName, sp.SqlCommand, tb_Product.__TableName));
}
/// <summary>
/// Marks orphaned file records (the ones that do not have a referencing binary record anymore) as Deleted.
/// </summary>
public void CleanupFilesSetDeleteFlag()
{
var isLocalTransaction = false;
if (!TransactionScope.IsActive)
{
TransactionScope.Begin();
isLocalTransaction = true;
}
try
{
using (var proc = new SqlProcedure {
CommandText = CleanupFileSetIsdeletedScript, CommandType = CommandType.Text
})
{
proc.CommandType = CommandType.Text;
proc.ExecuteNonQuery();
}
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Commit();
}
}
catch (Exception ex)
{
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Rollback();
}
throw new DataException("Error during setting deleted flag on files.", ex);
}
}
private SqlProcedure _deleteItemById(SqlProcedureNames item)
{
var procedure = new SqlProcedure {
FileName = item.FileName,
Catalog = item.ActionName,
ProcedureBody =
string.Format(
"IF EXISTS (SELECT * FROM sys.objects WHERE name = '{1}') DROP PROCEDURE {0}.[{1}] \n" +
"GO \n\n" +
"CREATE PROCEDURE {0}.[{1}](@Id {3}) AS\n" +
"BEGIN \n" +
" DECLARE @sucsess bit = 0;\n" +
" IF(@Id = 0 or @Id IS NULL)\n" +
" BEGIN raiserror('The value should not be 0 or NULL', 15, 1) SELECT @sucsess return END\n" +
" ELSE IF NOT EXISTS(SELECT top(1) Id FROM {0}.[{2}] WHERE Id = @Id)\n" +
" BEGIN raiserror('row not exist', 15, 1) SELECT @sucsess return; END\n" +
" ELSE \n" +
" BEGIN TRANSACTION\n" +
" BEGIN TRY \n" +
" DELETE FROM {0}.[{2}] WHERE Id = @Id; SET @sucsess = 1;\n" +
" COMMIT TRANSACTION END TRY \n" +
" BEGIN CATCH SET @sucsess = 0; \n" +
" ROLLBACK TRANSACTION \n" +
" THROW; \n" +
" END CATCH \n" +
" SET sucsess =1; SELECT @sucsess \n" +
"END \n" +
"GO \n\n\n\n", item.ShemeName, item.ProcedureName, item.TableName, item.KeyType)
};
return(procedure);
}
private static SqlProcedure GetBlobContextProcedure(int fileId, bool clearStream, int versionId, int propertyTypeId)
{
// this is a helper method to aid both the sync and
// async version of the GetBlobContext operation
var sql = GetBlobContextDataFileStreamScript;
// add clear stream prefix of necessary
if (clearStream)
{
sql = ClearFileStreamByFileIdScript + sql;
}
var cmd = new SqlProcedure {
CommandText = sql, CommandType = CommandType.Text
};
cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = fileId;
// security check: the given fileid must belong to the given version id and propertytypeid
if (clearStream)
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
}
return(cmd);
}
private SqlProcedure _deleteAll(SqlProcedureNames item)
{
var procedure = new SqlProcedure {
FileName = item.FileName,
Catalog = item.ActionName,
ProcedureBody = string.Format(
"IF EXISTS (SELECT * FROM sys.objects WHERE name = '{1}') DROP PROCEDURE {0}.[{1}] \n" +
"GO \n\n" +
"CREATE PROCEDURE {0}.[{1}] AS\n" +
"BEGIN\n" +
" DECLARE @sucsess bit =0;\n" +
" IF NOT EXISTS (SELECT top(1) Id FROM {0}.[{2}])\n" +
" BEGIN\n" +
" SET @sucsess = 1;\n" +
" END\n" +
" ELSE\n" +
" BEGIN\n" +
" BEGIN TRY\n" +
" DELETE FROM {0}.[{2}]\n" +
" SET @sucsess = 1;\n" +
" END TRY\n" +
" BEGIN CATCH\n" +
" THROW; \n" +
" END CATCH \n" +
" END\n" +
" SELECT @sucsess\n" +
"END \n" +
"GO \n\n\n\n", item.ShemeName, item.ProcedureName, item.TableName)
};
return(procedure);
}
protected override void ExecuteAction(ActionArgs args, ActionResult result)
{
if (args.CommandName.Equals("Custom") && args.CommandArgument.Equals("Execute"))
{
using (SqlProcedure sp = new SqlProcedure("sp_TYM_Order_Execute"))
{
sp.AddParameter("@Username", Context.User.Identity.Name);
if (sp.ExecuteScalar().ToString().Equals("0"))
{
result.ShowAlert("Invalid Material Number");
result.Refresh();
}
else
{
result.ShowAlert("Execute OK");
result.Refresh();
}
}
}
else if (args.CommandName.Equals("Custom") && args.CommandArgument.Equals("ClearData"))
{
using (SqlProcedure sp = new SqlProcedure("sp_TYM_Order_ClearData"))
{
sp.ExecuteNonQuery();
result.Refresh();
}
}
}
public DataTable GetSUPPLIERCUSTOMERByAttributeCodes(string attributeCodes, bool nameWithCode)
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_GetCustomerByAttributeCodes");
sp.AddParam("@AttributeCodes", SqlDbType.VarChar, attributeCodes);
sp.AddParam("@NameWithCode", SqlDbType.VarChar, nameWithCode ? "Y" : "N");
return(DataProvider.Instance.GetTable(_Loginer.DBName, sp.SqlCommand, "tb_SUPPLIERCUSTOMER"));
}
public DataTable FuzzySearch(string content)
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_FuzzySearchCustomer");
sp.AddParam("@Content", SqlDbType.VarChar, content);
sp.AddParam("@AttributeCodes", SqlDbType.VarChar, "");
return(DataProvider.Instance.GetTable(_Loginer.DBName, sp.SqlCommand, tb_SUPPLIERCUSTOMER.__TableName));
}
public void r100Implementation(SystemMenuModel instance)
{
using (XmlReader reader = XmlReader.Create(HttpContext.Current.Server.MapPath("~/web.sitemap")))
{
string title = string.Empty;
while (reader.Read())
{
if (reader.IsStartElement())
{
switch (reader.Name)
{
case "siteMapNode":
string name = reader["title"];
string description = reader["description"];
string url = reader["url"];
string roles = reader["roles"] == null?string.Empty:reader["roles"];
if (!name.Contains('^'))
//if (title != null && description != null && url != null && roles != null && !name.Contains('^'))
{
using (SqlProcedure sp = new SqlProcedure("sp_ConfigSystemMenu"))
{
if (description != string.Empty)
{
//sp.AddParameter("@title", title);
sp.AddParameter("@name", name);
sp.AddParameter("@description", description);
sp.AddParameter("@url", url);
sp.AddParameter("@roles", roles);
sp.AddParameter("@username", Context.User.Identity.Name);
sp.ExecuteScalar();
}
}
//if (description == string.Empty)
//{
// title = reader["title"];
//}
//else if (title != string.Empty)
//{
// using (SqlProcedure sp = new SqlProcedure("sp_ConfigSystemMenu"))
// {
// sp.AddParameter("@title", title);
// sp.AddParameter("@name", name);
// sp.AddParameter("@description", description);
// sp.AddParameter("@url", url);
// sp.AddParameter("@roles", roles);
// sp.AddParameter("@username", Context.User.Identity.Name);
// sp.ExecuteScalar();
// }
//}
}
break;
}
}
}
}
}
public void r104Implementation(Contratado_Solicitud_VacacionesModel instance)
{
// This is the placeholder for method implementation.
//string conStr = ConfigurationManager.ConnectionStrings["arhmexdb_DEV"].ToString();
int diasFinalesAutorizados = Convert.ToInt32(instance.Dias_Autorizados);
using (SqlProcedure sql = new SqlProcedure("dbo.sp_Revision_De_Solicitud"))
{
sql.Command.CommandTimeout = 60 * 10;
//Definición parametro especial para recibir el RETURN del SP
sql.AddParameter("RetVal", 0);
sql.Parameters["RetVal"].Direction = ParameterDirection.ReturnValue;
sql.AddParameter("@Tipo_Prestacion_Id", 1); // PRESTACION TIPO VACACION
sql.AddParameter("@Estado_Solicitud_Vacaciones", 4); // 4. Significa AUTORIZADO CON CAMBIOS
sql.AddParameter("@Solicitud_Vacaciones_Key", instance.Solicitud_Vacaciones_Key);
sql.AddParameter("@Dias_Autorizados", diasFinalesAutorizados);
//sql.AddParameter("@Contratante_Id", instance.Contratante_Id);
sql.AddParameter("@Contratado_Id", instance.Contratado_Id);
sql.AddParameter("@Fecha_Solicitud", instance.Fecha_Solicitud);
sql.AddParameter("@Dias_Solicitados", instance.Dias_Solicitados);
sql.AddParameter("@Fecha_Inicio_Vacaciones", instance.Fecha_Inicio_Vacaciones);
sql.AddParameter("@Fecha_Fin_Vacaciones", instance.Fecha_Fin_Vacaciones);
sql.AddParameter("@Notas_Autorizacion_Rechazo", "");
/*Parametros de sistema**/
sql.AddParameter("@Creado_Por_Id", UserId);
sql.AddParameter("@Transaccion_Id", DBNull.Value);
sql.AddParameter("@Fijar_Bitacora", 1);
sql.AddParameter("@Mostrar_Tablas_Sistema", 0);
//cmd.Parameters.Add("@Mensaje_Sistema_Id", 1);//.Direction = ParameterDirection.Output;
sql.AddParameter("@Mensaje_Sistema_Id", 1);
sql.AddParameter("@Simular", 0);
sql.AddParameter("@Trace", ""); //.Direction = ParameterDirection.Output;
sql.AddParameter("@Debug", 0);
sql.Parameters["@Mensaje_Sistema_Id"].Direction = ParameterDirection.Output;
sql.Parameters["@Trace"].Direction = ParameterDirection.Output;
//Definición de tamaños que soporten los tipos de datos de los parametros del salida del SP
sql.Parameters["@Mensaje_Sistema_Id"].Size = 4;
sql.Parameters["@Trace"].Size = 8000;
sql.ExecuteNonQuery();
if (sql.Parameters["RetVal"].Value.ToString() != "1")
{
String ErrorMessage = SqlText.ExecuteScalar("SELECT Mensaje_Amigable_Usuario FROM dbo.Mensaje_Sistema WHERE Mensaje_Sistema_Id=" + sql.Parameters["@Mensaje_Sistema_Id"].Value.ToString()).ToString();
Result.ShowAlert(ErrorMessage);
}
else
{
Result.Continue();
}
} // using (SqlProcedure sql = new SqlProcedure("dbo.sp_Revision_De_Solicitud"))
} //public void r104Implementation(Contratado_Solicitud_VacacionesModel instance)
public DataTable TaskAccountByManual(int TaskId)
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_WCS_Task_AccountByManual");
sp.AddParam("@IP", SqlDbType.NVarChar, Globals.DEF_ClientIP());
sp.AddParam("@Client", SqlDbType.NVarChar, Globals.DEF_ClientName());
sp.AddParam("@Loginer", SqlDbType.NVarChar, Loginer.CurrentUser.Account);
sp.AddParam("@TaskId", SqlDbType.Int, TaskId);
return(DataProvider.Instance.GetTable(_Loginer.DBName, sp.SqlCommand, tb_WMS_Task.__TableName));
}
public void r107Implementation(Contratado_Solicitud_VacacionesModel instance)
{
using (SqlProcedure sql = new SqlProcedure("dbo.sp_Administrar_Solicitud_Vacaciones"))
{
sql.Command.CommandTimeout = 60 * 10;
//Definición parametro especial para recibir el RETURN del SP
sql.AddParameter("RetVal", 0);
sql.Parameters["RetVal"].Direction = ParameterDirection.ReturnValue;
sql.AddParameter("@Tipo_Operacion_Id", 1); // 1. Significa INSERT/CREACION NUEVO
sql.AddParameter("@Contratado_Solicitud_Vacaciones_Id", 0); /// Este es el ID de la tabla de solicitudes.
sql.AddParameter("@Solicitud_Vacaciones_Key", "");
sql.AddParameter("@Contratado_Id", instance.Contratado_Id);
sql.AddParameter("@Fecha_Solicitud", instance.Fecha_Solicitud);
sql.AddParameter("@Dias_Solicitados", instance.Dias_Solicitados);
sql.AddParameter("@Fecha_Inicio_Vacaciones", instance.Fecha_Inicio_Vacaciones);
sql.AddParameter("@Fecha_Fin_Vacaciones", instance.Fecha_Fin_Vacaciones);
sql.AddParameter("@Estatus_Solicitud_Id", instance.Estatus_Solicitud_Id);
sql.AddParameter("@Fecha_Autorizacion_Rechazo", DateTime.Today);
sql.AddParameter("@Autorizado_Rechazado_Por_Id", UserId);
sql.AddParameter("@Notas_Autorizacion_Rechazo", instance.Notas_Autorizacion_Rechazo);
sql.AddParameter("@Dias_Autorizados", instance.Dias_Autorizados);
sql.AddParameter("@Fecha_Inicio_Vacaciones_Autorizado ", instance.Fecha_Inicio_Vacaciones);
sql.AddParameter("@Fecha_Fin_Vacaciones_Autorizado ", instance.Fecha_Fin_Vacaciones);
sql.AddParameter("@HTML ", "");
/*Parametros de sistema**/
sql.AddParameter("@Creado_Por_Id", UserId);
sql.AddParameter("@Transaccion_Id", DBNull.Value);
sql.AddParameter("@Fijar_Bitacora", 1);
sql.AddParameter("@Mostrar_Tablas_Sistema", 0);
sql.AddParameter("@Mensaje_Sistema_Id", 1);
sql.AddParameter("@Simular", 0);
sql.AddParameter("@Trace", "");
sql.AddParameter("@Debug", 0);
sql.Parameters["@Mensaje_Sistema_Id"].Direction = ParameterDirection.Output;
sql.Parameters["@Trace"].Direction = ParameterDirection.Output;
//Definición de tamaños que soporten los tipos de datos de los parametros del salida del SP
sql.Parameters["@Mensaje_Sistema_Id"].Size = 4;
sql.Parameters["@Trace"].Size = 8000;
sql.ExecuteNonQuery();
if (sql.Parameters["RetVal"].Value.ToString() != "1")
{
String ErrorMessage = SqlText.ExecuteScalar("SELECT Mensaje_Amigable_Usuario FROM dbo.Mensaje_Sistema WHERE Mensaje_Sistema_Id=" + sql.Parameters["@Mensaje_Sistema_Id"].Value.ToString()).ToString();
Result.ShowAlert(ErrorMessage);
}
else
{
Result.Continue();
}
} // using (SqlProcedure sql = new SqlProcedure("dbo.sp_Administrar_Solicitud_Vacaciones"))
} // public void r107Implementation(Contratado_Solicitud_VacacionesModel instance)
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (sqlProc != null)
{
sqlProc.Dispose();
sqlProc = null;
}
}
}
/// <summary>
/// Finalizes a chunked save operation.
/// </summary>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
/// <param name="fileId">File identifier.</param>
/// <param name="fullSize">Full size (stream length) of the binary value.</param>
/// <param name="source">Binary data containing metadata (e.g. content type).</param>
public void CommitChunk(int versionId, int propertyTypeId, int fileId, long fullSize, BinaryDataValue source = null)
{
// start a new transaction here if needed
var isLocalTransaction = !TransactionScope.IsActive;
if (isLocalTransaction)
{
TransactionScope.Begin();
}
try
{
// commit the process: set the final full size and checksum
using (var cmd = new SqlProcedure {
CommandText = CommitChunkScript, CommandType = CommandType.Text
})
{
cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = fileId;
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = fullSize;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = DBNull.Value;
cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = source != null ? source.ContentType : string.Empty;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = source != null
? source.FileName.FileNameWithoutExtension == null
? DBNull.Value
: (object)source.FileName.FileNameWithoutExtension
: DBNull.Value;
cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = source != null?ValidateExtension(source.FileName.Extension) : string.Empty;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
// rollback the transaction if it was opened locally
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Rollback();
}
throw new DataException("Error during committing binary chunk to file stream.", ex);
}
finally
{
// commit the transaction if it was opened locally
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Commit();
}
}
}
public DataTable TaskPriorityModify(int TaskId, int First, int Second)
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_WCS_Task_PriorityModify");
sp.AddParam("@IP", SqlDbType.NVarChar, Globals.DEF_ClientIP());
sp.AddParam("@Client", SqlDbType.NVarChar, Globals.DEF_ClientName());
sp.AddParam("@Loginer", SqlDbType.NVarChar, Loginer.CurrentUser.Account);
sp.AddParam("@TaskId", SqlDbType.Int, TaskId);
sp.AddParam("@First", SqlDbType.Int, First);
sp.AddParam("@Sencond", SqlDbType.Int, Second);
return(DataProvider.Instance.GetTable(_Loginer.DBName, sp.SqlCommand, tb_WMS_Task.__TableName));
}
public DataSet GetReportData_Checklist(string DocNoFrom, string DocNoTo, DateTime DateFrom, DateTime DateTo)
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_RptAP_Checklist");
sp.AddParam("@DocNoFrom", SqlDbType.VarChar, DocNoFrom.Trim());
sp.AddParam("@DocNoTo", SqlDbType.VarChar, DocNoTo.Trim());
sp.AddParam("@DateFrom", SqlDbType.VarChar, ConvertEx.ToCharYYYYMMDD(DateFrom));
sp.AddParam("@DateTo", SqlDbType.VarChar, ConvertEx.ToCharYYYYMMDD(DateTo));
DataSet ds = DataProvider.Instance.GetDataSet(_Loginer.DBName, sp.SqlCommand);
ds.Tables[0].TableName = tb_AP.__TableName;
return(ds);
}
/// <summary>
/// Método que retorna el valor en euros de un sku
/// </summary>
/// <remarks> Autor: Gabriel Herrera</remarks>
public TotalTransaction[] GetSkuValue(string sku)
{
string procedureName = "CalculateSku";
using (unitOfWork = new UnitOfWork())
{
SqlProcedure procedure = new SqlProcedure(procedureName, new SqlParameter {
ParameterName = "@Sku", Value = sku
});
TotalTransaction[] skuTotal = unitOfWork.TransactionRepository.ExecuteProcedure <TotalTransaction>(procedure);
return(skuTotal);
}
}
// ReSharper disable once SuggestBaseTypeForParameter
private static SqlProcedure GetWriteChunkToSqlProcedure(BlobStorageContext context, long offset, byte[] buffer)
{
// This is a helper method to aid both the sync and async version of the write chunk operation.
var cmd = new SqlProcedure {
CommandText = UpdateStreamWriteChunkFsScript, CommandType = CommandType.Text
};
cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = context.FileId;
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = context.VersionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = context.PropertyTypeId;
cmd.Parameters.Add("@Data", SqlDbType.VarBinary).Value = buffer;
cmd.Parameters.Add("@Offset", SqlDbType.BigInt).Value = offset;
return(cmd);
}
private static Tuple <VersionNumber, VersionNumber> GetLastVersions(string path)
{
VersionNumber majorVersion = null;
VersionNumber minorVersion = null;
SqlProcedure cmd = null;
SqlDataReader reader = null;
try
{
cmd = new SqlProcedure
{
CommandText = Scripts.LastVersions,
CommandType = CommandType.Text
};
cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = path;
reader = cmd.ExecuteReader();
while (reader.Read())
{
var majorNumber = reader.GetInt16(reader.GetOrdinal("MajorNumber"));
var minorNumber = reader.GetInt16(reader.GetOrdinal("MinorNumber"));
var statusCode = reader.GetInt16(reader.GetOrdinal("Status"));
var isLastMajor = reader.GetBoolean(reader.GetOrdinal("LastMajor"));
var isLastMinor = reader.GetBoolean(reader.GetOrdinal("LastMinor"));
var versionNumber = new VersionNumber(majorNumber, minorNumber, (VersionStatus)statusCode);
if (isLastMajor)
{
majorVersion = versionNumber;
}
if (isLastMinor)
{
minorVersion = versionNumber;
}
}
}
finally
{
reader?.Dispose();
cmd?.Dispose();
}
return(new Tuple <VersionNumber, VersionNumber>(majorVersion, minorVersion));
}
/// <summary>
/// Deletes file records that are marked as deleted from the metadata database and also from the blob storage.
/// </summary>
/// <returns>Whether there was at least one row that was deleted.</returns>
public bool CleanupFiles()
{
using (var proc = new SqlProcedure {
CommandText = CleanupFileScript, CommandType = CommandType.Text
})
{
proc.CommandType = CommandType.Text;
try
{
var deleted = false;
using (var reader = proc.ExecuteReader())
{
var fileId = 0;
var size = 0L;
string providerName = null;
string providerData = null;
// We do not care about the number of deleted rows,
// we only want to know if a row was deleted or not.
if (reader.Read())
{
deleted = true;
fileId = reader.GetSafeInt32(reader.GetOrdinal("FileId"));
size = reader.GetSafeInt64(reader.GetOrdinal("Size"));
providerName = reader.GetSafeString(reader.GetOrdinal("BlobProvider"));
providerData = reader.GetSafeString(reader.GetOrdinal("BlobProviderData"));
}
// delete bytes from the blob storage
// Delete algorithm is same in the BuiltIn and SqlFs providers.
var provider = BlobStorageBase.GetProvider(providerName);
var ctx = new BlobStorageContext(provider, providerData)
{
VersionId = 0, PropertyTypeId = 0, FileId = fileId, Length = size
};
ctx.Provider.Delete(ctx);
}
return(deleted);
}
catch (Exception ex)
{
throw new DataException("Error during binary cleanup.", ex);
}
}
}
private SqlProcedure _getAllIds(SqlProcedureNames item)
{
var procedure = new SqlProcedure {
FileName = item.FileName,
Catalog = item.ActionName,
ProcedureBody = string.Format(
"IF EXISTS (SELECT * FROM sys.objects WHERE name = '{1}') DROP PROCEDURE {0}.[{1}] \n" +
"GO \n\n" +
"CREATE PROCEDURE {0}.[{1}] AS\n" +
"BEGIN\n" +
" SELECT Id FROM {0}.[{2}]\n" +
"END \n" +
"GO \n\n\n\n", item.ShemeName, item.ProcedureName, item.TableName)
};
return(procedure);
}
private static void WriteSqlFileStream(Stream stream, int fileId, SqlFileStreamData fileStreamData = null)
{
SqlProcedure cmd = null;
try
{
// if we did not receive a path and transaction context, retrieve it now from the database
if (fileStreamData == null)
{
cmd = new SqlProcedure {
CommandText = UpdateBinaryPropertyFileStreamScript, CommandType = CommandType.Text
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = fileId;
string path;
byte[] transactionContext;
// Set Stream column to NULL and retrieve file path and
// transaction context for the Filestream column.
using (var reader = cmd.ExecuteReader())
{
reader.Read();
path = reader.GetString(0);
transactionContext = reader.GetSqlBytes(1).Buffer;
}
fileStreamData = new SqlFileStreamData {
Path = path, TransactionContext = transactionContext
};
}
stream.Seek(0, SeekOrigin.Begin);
using (var fs = new SqlFileStream(fileStreamData.Path, fileStreamData.TransactionContext, FileAccess.Write))
{
// default buffer size is 4096
stream.CopyTo(fs);
}
}
finally
{
cmd?.Dispose();
}
}
public DataTable FuzzySearch(string content)
{
if (content.ToLower().Contains("like"))
{
string sql = ("select * from ITEM where ISDELETED=0 ");
if (content != "")
{
sql += content;
}
return(DataProvider.Instance.GetTable(_Loginer.DBName, sql, _TableName));
}
else
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_FuzzySearchProduct");
sp.AddParam("@Content", SqlDbType.NVarChar, content);
return(DataProvider.Instance.GetTable(_Loginer.DBName, sp.SqlCommand, tb_ITEM.__TableName));
}
}
/// <summary>
/// Deletes a binary property value from the metadata database, making the corresponding blbo storage entry orphaned.
/// </summary>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
public void DeleteBinaryProperty(int versionId, int propertyTypeId)
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_Delete"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.ExecuteNonQuery();
}
finally
{
cmd?.Dispose();
}
}
internal static byte[] ReadRandom(BlobStorageContext context, long offset, int count)
{
byte[] result;
using (var cmd = new SqlProcedure {
CommandText = LoadBinaryFragmentFilestreamScript
})
{
cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = context.FileId;
cmd.Parameters.Add("@Position", SqlDbType.BigInt).Value = offset + 1;
cmd.Parameters.Add("@Count", SqlDbType.Int).Value = count;
cmd.CommandType = CommandType.Text;
result = (byte[])cmd.ExecuteScalar();
}
return(result);
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
filename = String.Format("TYM_{0}_FORCAST_{1}", DateTime.Now.ToString("yyyyMMddHHmmss"), filename);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40103010";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
string line = string.Empty;
using (StreamReader sr = new StreamReader(filename))
{
using (SqlProcedure sp = new SqlProcedure("sp_TYM_Forcast_ClearData"))
{
sp.ExecuteNonQuery();
}
while ((line = sr.ReadLine()) != null)
{
if (line.Length > 0)
{
MyCompany.Data.Objects.TYMForcastImport Order = new MyCompany.Data.Objects.TYMForcastImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = "";
Order.CustomerMatCode = line.Substring(0, 3) + "-" + line.Substring(3, 5) + "-" + line.Substring(8, 2) + "-" + line.Substring(10, 2) + "-" + line.Substring(12, 2) + "-" + line.Substring(14, 4) + "-" + line.Substring(18, 4);//line.Substring(0, 22);
Order.PartsDevision = "1";
Order.CustomerPO = "";
Order.ReliabilityDevision = "P";
Order.DeliveryDate = Convert.ToDateTime(line.Substring(22, 4).Trim() + "-" + line.Substring(26, 2).Trim() + "-" + line.Substring(28, 2).Trim());
Order.Quantity = float.Parse(line.Substring(30, 8)).ToString().Trim();
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
Order.Insert();
}
}
sr.Close();
}
}
}
public static string getMaterial(string CustMatCode, string OrderBy, string pc, string lc)
{
CustMatCode = CustMatCode.Replace(" ", "");
using (SqlProcedure sp = new SqlProcedure("sp_GetMaterial"))
{
sp.AddParameter("@CustomerItemNumber", CustMatCode);
sp.AddParameter("@OrderBy", OrderBy);
sp.AddParameter("@pc", pc);
sp.AddParameter("@lc", lc);
object sc = sp.ExecuteScalar();
if (sc == null)
{
return(string.Empty);
}
else
{
return(sc.ToString());
}
}
}
public int ExecuteProcedure(SqlProcedure procedure)
{
try
{
StringBuilder query = new StringBuilder();
query.AppendFormat("{0} {1}"
, procedure.ProcedureName
, string.Join(",", procedure.Parameters.Select(p => p.ParameterName + "=" + p.Value.ToString())));
var rowsAffected = context
.Database
.ExecuteSqlCommand(query.ToString());
return(rowsAffected);
}
catch (Exception ex)
{
throw new ExceptionOperation(HelperMessage.GetMessage(CodeMessage.GEN_001), (int)CodeHTTP.Error, ex.Message, ex.InnerException);
}
}
public T[] ExecuteProcedure <T>(SqlProcedure procedure)
{
try
{
StringBuilder query = new StringBuilder();
query.AppendFormat("{0} {1}"
, procedure.ProcedureName
, string.Join(",", procedure.Parameters.Select(p => p.ParameterName + "=" + p.Value.ToString())));
var selectedList = context
.Database
.SqlQuery <T>(query.ToString())
.ToArray <T>();
return((T[])selectedList);
}
catch (Exception ex)
{
throw new ExceptionOperation(HelperMessage.GetMessage(CodeMessage.GEN_001), (int)CodeHTTP.Error, ex.Message, ex.InnerException);
}
}
/// <summary>
/// Inserts a new binary record into the metadata database containing an already exising file id,
/// removing the previous record if the content is not new.
/// </summary>
/// <param name="value">Binary data to insert.</param>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
/// <param name="isNewNode">Whether this value belongs to a new or an existing node.</param>
public void InsertBinaryPropertyWithFileId(BinaryDataValue value, int versionId, int propertyTypeId, bool isNewNode)
{
SqlProcedure cmd = null;
int id;
try
{
cmd = new SqlProcedure
{
CommandText = isNewNode ? InsertBinaryPropertyWithKnownFileIdScript : DeleteAndInsertBinaryPropertyWithKnownFileId,
CommandType = CommandType.Text
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId != 0 ? (object)versionId : DBNull.Value;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId != 0 ? (object)propertyTypeId : DBNull.Value;
cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = value.FileId;
id = (int)cmd.ExecuteScalar();
}
finally
{
cmd?.Dispose();
}
value.Id = id;
}
public NewTechSqlDal(string connectionString)
{
sqlProc = new SqlProcedure(connectionString);
sqlProc.CommandTimeout = 60;
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (sqlProc != null)
{
sqlProc.Dispose();
sqlProc = null;
}
}
}
