public bool Insert(Photo photo, Database db, DbTransaction transaction = null)
{
DbCommand command = db.GetStoredProcCommand("usp_PhotoInsert");
photo.PhotoId = Guid.NewGuid();
db.AddInParameter(command, "PhotoId", DbType.Guid, photo.PhotoId);
db.AddInParameter(command, "FileName", DbType.String, photo.FileName);
db.AddInParameter(command, "FilePath", DbType.String, photo.FilePath);
db.AddInParameter(command, "ContextId", DbType.Guid, photo.ContextId);
db.AddInParameter(command, "Description", DbType.String, photo.Description);
db.AddInParameter(command, "ContextTypeId", DbType.Int32, (int)photo.ContextType);
db.AddInParameter(command, "ContextSubTypeId", DbType.Int32, photo.ContextSubTypeId);
db.AddInParameter(command, "PhotoCategoryId", DbType.Int32, (int)photo.PhotoCategory);
db.AddInParameter(command, "IsDeleted", DbType.String, photo.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, photo.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
photo.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
photo.UpdatedDate = photo.CreatedDate;
return true;
}
public bool Insert(Option Option, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionInsert");
db.AddInParameter(command, "Name", DbType.String, Option.Name);
db.AddInParameter(command, "Description", DbType.String, Option.Description);
db.AddInParameter(command, "OptionCategoryId", DbType.Int16, Option.OptionCategoryId);
db.AddInParameter(command, "ParentOptionId", DbType.Int16, Option.ParentOptionId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, Option.IsDeleted);
db.AddInParameter(command, "IsMultiSelect", DbType.Boolean, Option.IsMultiSelect);
db.AddInParameter(command, "Points", DbType.Int16, Option.Points);
db.AddOutParameter(command, "OptionId", DbType.Int16, 3);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
Option.OptionId = Convert.ToInt16(db.GetParameterValue(command, "OptionId").ToString());
return true;
}
public bool Insert(OptionItem OptionItem, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionItemInsert");
db.AddInParameter(command, "Name", DbType.String, OptionItem.Name);
db.AddInParameter(command, "Description", DbType.String, OptionItem.Description);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, OptionItem.IsDeleted);
db.AddInParameter(command, "OptionId", DbType.Int16, OptionItem.OptionId);
db.AddInParameter(command, "CreatedBy", DbType.Guid, OptionItem.CreatedBy);
db.AddOutParameter(command, "OptionItemId", DbType.Int16, 3);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
OptionItem.OptionItemId = Convert.ToInt16(db.GetParameterValue(command, "OptionItemId").ToString());
return true;
}
public bool Insert(School school, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SchoolInsert");
db.AddInParameter(command, "SchoolId", DbType.Guid, Guid.NewGuid());
db.AddInParameter(command, "Name", DbType.String, school.Name);
db.AddInParameter(command, "StreetAddress", DbType.String, school.StreetAddress);
db.AddInParameter(command, "City", DbType.String, school.City);
db.AddInParameter(command, "State", DbType.String, school.State);
db.AddInParameter(command, "Zip", DbType.String, school.Zip);
db.AddInParameter(command, "ContactNumber", DbType.String, school.ContactNumber);
db.AddInParameter(command, "Email", DbType.String, school.Email);
db.AddInParameter(command, "Location", DbType.String, school.Location);
db.AddInParameter(command, "WebsiteURL", DbType.String, school.WebsiteURL);
db.AddInParameter(command, "RatingValue", DbType.Decimal, school.RatingValue);
db.AddInParameter(command, "CreatedBy", DbType.Guid, school.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
school.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
school.UpdatedDate = school.CreatedDate;
return true;
}
public bool Insert(Student student, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentInsert");
db.AddInParameter(command, "StudentId", DbType.Guid, Guid.NewGuid());
db.AddInParameter(command, "UserId", DbType.Guid, student.StudentUser.UserId);
db.AddInParameter(command, "SchoolId", DbType.Guid, student.School.SchoolId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, student.IsDeleted);
db.AddInParameter(command, "Year", DbType.String, student.School.Year);
db.AddInParameter(command, "StartYear", DbType.String, student.StartYear);
db.AddInParameter(command, "StartMonth", DbType.String, student.StartMonth);
db.AddInParameter(command, "Status", DbType.String, student.Status);
db.AddInParameter(command, "PreviousSchoolInfo", DbType.String, student.PreviousSchoolInfo);
db.AddInParameter(command, "PreviousSchool", DbType.String, student.PreviousSchool);
db.AddInParameter(command, "MajorId", DbType.Int16, student.MajorId);
db.AddInParameter(command, "CreatedBy", DbType.Guid, student.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
student.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
student.UpdatedDate = student.CreatedDate;
return true;
}
public bool Insert(PartialUser partialUser, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_PartialUserInsert");
partialUser.PartialUserId = Guid.NewGuid();
db.AddInParameter(command, "PartialUserId", DbType.Guid, partialUser.PartialUserId);
db.AddInParameter(command, "Email", DbType.String, partialUser.Email);
db.AddInParameter(command, "FirstName", DbType.String, partialUser.FirstName);
db.AddInParameter(command, "MiddleName", DbType.String, partialUser.MiddleName);
db.AddInParameter(command, "LastName", DbType.String, partialUser.LastName);
db.AddInParameter(command, "Contact", DbType.String, partialUser.Contact);
db.AddInParameter(command, "RoleId", DbType.Guid, partialUser.RoleId);
db.AddInParameter(command, "UserId", DbType.Guid, partialUser.UserId);
db.AddInParameter(command, "PartialHouseId", DbType.Guid, partialUser.PartialHouseId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, partialUser.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, partialUser.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
partialUser.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
partialUser.UpdatedDate = partialUser.CreatedDate;
return true;
}
public bool DeleteRoleRightsByRoleId(Role roles, Database db, DbTransaction transaction)
{
DbCommand dbCommand = db.GetStoredProcCommand("usp_RoleRightDelete");
db.AddInParameter(dbCommand, "RoleId", DbType.Guid, roles.RoleId);
db.ExecuteNonQuery(dbCommand, transaction);
return true;
}
//Funcion para registrar autos
public int RegistrarAutos(Autos DatosA, DbTransaction tran, Database db)
{
string sqlCommand = "dbo.insertar_autos";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
try {
db.AddInParameter(dbCommand, "@INTplaca", DbType.Int32, Utilerías.ObtenerValor(DatosA.Placa));
db.AddInParameter(dbCommand, "@STRmarca", DbType.String, Utilerías.ObtenerValor(DatosA.Marca));
db.AddInParameter(dbCommand, "@STRmodelo", DbType.String, Utilerías.ObtenerValor(DatosA.Modelo));
db.AddInParameter(dbCommand, "@INTanno", DbType.Int32, Utilerías.ObtenerValor(DatosA.Año));
db.AddInParameter(dbCommand, "@INTnumero_vin", DbType.Int32, Utilerías.ObtenerValor(DatosA.Vin));
db.AddInParameter(dbCommand, "@STRcolor", DbType.String, Utilerías.ObtenerValor(DatosA.Color));
db.AddOutParameter(dbCommand, "@nStatus", DbType.Int16, 2);
db.AddOutParameter(dbCommand, "@strMessage", DbType.String, 250);
db.AddOutParameter(dbCommand, "@INTid_Auto", DbType.Int32, 4);
db.ExecuteNonQuery(dbCommand, tran);
if (int.Parse(db.GetParameterValue(dbCommand, "@nStatus").ToString()) > 0)
throw new Exception(db.GetParameterValue(dbCommand, "@strMessage").ToString());
//retorna el id del auto que acaba de registrar
return int.Parse(db.GetParameterValue(dbCommand, "@INTid_Auto").ToString());
} catch (Exception ex) {
throw new Exception(ex.Message);
}
}
public bool Delete(OptionCategory ropertyOptionCategory, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionCategoryDelete");
db.AddInParameter(command, "OptionCategoryId", DbType.Guid, ropertyOptionCategory.OptionCategoryId);
db.ExecuteNonQuery(command, transaction);
return true;
}
internal static Int32 ExecuteNonQueryOutWithOutDBX(String CommandName, DbParameter[] param, ref DbCommand cmdx, ref Database dbx, DbTransaction transaction = null) //out List<DbParameter> outputParameters
{
try
{
Int32 result = 0;
using (DbCommand cmd = db.GetStoredProcCommand(CommandName))
{
cmd.Parameters.AddRange(param);
if (transaction != null)
{
result = db.ExecuteNonQuery(cmd, transaction);
}
else
{
result = db.ExecuteNonQuery(cmd);
}
dbx = db;
cmdx = cmd;
return(result);
}
}
catch (Exception ex)
{
throw new Exception("Error Inesperado =>" + ex.Message, ex);
}
}
public static bool Insert(SurveyEntity survey, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SurveyInsert");
db.AddInParameter(command, "MyUniversity", DbType.String, survey.MyUniversity);
db.AddInParameter(command, "UniversityName", DbType.String, survey.UniversityName);
db.AddInParameter(command, "UniversityAddress", DbType.String, survey.UniversityAddress);
db.AddInParameter(command, "TypeOfResidence", DbType.String, survey.TypeOfResidence);
db.AddInParameter(command, "TypeOfResidenceOption", DbType.String, survey.TypeOfResidenceOption);
db.AddInParameter(command, "NameOfResidence", DbType.String, survey.NameOfResidence);
db.AddInParameter(command, "AddressOfResidence", DbType.String, survey.AddressOfResidence);
db.AddInParameter(command, "PropertyOwnerComment", DbType.String, survey.PropertyOwnerComment);
db.AddInParameter(command, "Email", DbType.String, survey.Email);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
public bool Insert(PartialHouse partialHouse, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_PartialHouseInsert");
partialHouse.PartialHouseId = Guid.NewGuid();
db.AddInParameter(command, "PartialHouseId", DbType.Guid, partialHouse.PartialHouseId);
db.AddInParameter(command, "PartialUserId", DbType.Guid, partialHouse.PartialUserId);
db.AddInParameter(command, "StateId", DbType.Int32, partialHouse.StateId);
db.AddInParameter(command, "ZipCode", DbType.String, partialHouse.ZipCode);
db.AddInParameter(command, "City", DbType.String, partialHouse.City);
db.AddInParameter(command, "Address", DbType.String, partialHouse.Address);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, partialHouse.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, partialHouse.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
partialHouse.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
partialHouse.UpdatedDate = partialHouse.CreatedDate;
return true;
}
internal static Int32 ExecuteNonQueryOut(String CommandName, Int32 tipo, DbParameter[] param, ref DbCommand cmdx, ref Database dbx, DbTransaction transaction = null) //out List<DbParameter> outputParameters
{
try
{
// DatabaseProviderFactory factory = new DatabaseProviderFactory();
//Database db = factory.Create(tipo == 1 ? cadConexion : cadConexion2);
db = SetEnviroment(tipo);
Int32 result = 0;
using (DbCommand cmd = db.GetStoredProcCommand(CommandName))
{
cmd.Parameters.AddRange(param);
if (transaction != null)
{
result = db.ExecuteNonQuery(cmd, transaction);
}
else
{
result = db.ExecuteNonQuery(cmd);
}
dbx = db;
cmdx = cmd;
return(result);
}
}
catch (Exception ex)
{
throw new Exception("Error Inesperado =>" + ex.Message, ex);
}
}
public bool Insert(StudentHouseLeave studentHouseLeave, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentHouseLeaveInsert");
db.AddInParameter(command, "HouseId", DbType.Guid, studentHouseLeave.HouseId);
db.AddInParameter(command, "BaseHouseRoomId", DbType.Guid, studentHouseLeave.BaseHouseRoomId);
db.AddInParameter(command, "RequestBy", DbType.Guid, studentHouseLeave.RequestBy);
db.AddInParameter(command, "RequestTo", DbType.Guid, studentHouseLeave.RequestTo);
db.AddInParameter(command, "status", DbType.Int16, studentHouseLeave.status);
db.AddOutParameter(command, "RequestDate", DbType.DateTime, 30);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
studentHouseLeave.RequestDate = Convert.ToDateTime(db.GetParameterValue(command, "RequestDate").ToString());
studentHouseLeave.ResponseDate = studentHouseLeave.RequestDate;
return true;
}
internal static Int32 ExecuteNonQuery(String CommandName, Int32 tipo, DbParameter[] param = null, DbTransaction transaction = null)
{
try
{
db = SetEnviroment(tipo);
Int32 result = 0;
using (DbCommand cmd = db.GetStoredProcCommand(CommandName))
{
if (param != null)
{
cmd.Parameters.AddRange(param);
}
if (transaction != null)
{
result = db.ExecuteNonQuery(cmd, transaction);
}
else
{
result = db.ExecuteNonQuery(cmd);
}
return(result);
}
}
catch (Exception ex)
{
throw new Exception("Error Inesperado =>" + ex.Message, ex);
}
}
public void btnInsert_Click(System.Object sender, System.EventArgs e)
{
//GetStoredProcCommand
DbCommand cmd = db.GetStoredProcCommand("InsertSampleData");
db.AddInParameter(cmd, "name", DbType.String, "Insert");
db.AddInParameter(cmd, "value", DbType.String, "New");
db.ExecuteNonQuery(cmd);
}
public static void CreateStoredProcedures(Database db)
{
DbCommand command;
string sql;
try
{
DeleteStoredProcedures(db);
}
catch { }
sql = "CREATE OR REPLACE PACKAGE PKGENTLIB AS " +
"TYPE T_CURSOR IS REF CURSOR; " +
"PROCEDURE RegionSelect (CUR_OUT OUT T_CURSOR); " +
"END PKGENTLIB;";
command = db.GetSqlStringCommand(sql);
db.ExecuteNonQuery(command);
sql = "CREATE OR REPLACE PACKAGE BODY PKGENTLIB AS " +
"PROCEDURE RegionSelect(CUR_OUT OUT T_CURSOR) IS " +
"V_CURSOR T_CURSOR; " +
"BEGIN " +
"OPEN V_CURSOR FOR " +
"SELECT * FROM Region ORDER BY RegionID; " +
"CUR_OUT := V_CURSOR; " +
"END RegionSelect; " +
"END PKGENTLIB;";
command = db.GetSqlStringCommand(sql);
db.ExecuteNonQuery(command);
sql = "create procedure RegionInsert (pRegionID IN Region.RegionID%TYPE, pRegionDescription IN Region.RegionDescription%TYPE) as " +
"BEGIN " +
" insert into Region values(pRegionID, pRegionDescription); " +
"END;";
command = db.GetSqlStringCommand(sql);
db.ExecuteNonQuery(command);
sql = "create procedure RegionUpdate (pRegionID IN Region.RegionID%TYPE, pRegionDescription IN Region.RegionDescription%TYPE) as " +
"BEGIN " +
" update Region set RegionDescription = pRegionDescription where RegionID = pRegionID; " +
"END;";
command = db.GetSqlStringCommand(sql);
db.ExecuteNonQuery(command);
sql = "create procedure RegionDelete (pRegionID IN Region.RegionID%TYPE) as " +
"BEGIN " +
" delete from Region where RegionID = pRegionID; " +
"END;";
command = db.GetSqlStringCommand(sql);
db.ExecuteNonQuery(command);
}
/// <summary>
/// Inserts a rule into the database
/// </summary>
/// <param name="name">The name of the rule</param>
/// <param name="expression">The expression defining the rule</param>
public void InsertRule(string name, string expression, string description)
{
DbCommand cmd = dbRules.GetStoredProcCommand("dbo.InsertRule");
dbRules.AddInParameter(cmd, "Name", DbType.String, name);
dbRules.AddInParameter(cmd, "Expression", DbType.String, expression);
//TODO:要在表中加入Description这一字段
dbRules.AddInParameter(cmd, "Description", DbType.String, description);
dbRules.ExecuteNonQuery(cmd);
}
public int GetSchemaVersion(string name)
{
int version = 0;
using (DbCommand dbCmd = db.GetStoredProcCommand("chpt09_GetSchemaVersion"))
{
db.AddInParameter(dbCmd, "@Name", DbType.String, name);
db.AddOutParameter(dbCmd, "@Version", DbType.Int32, 0);
db.ExecuteNonQuery(dbCmd);
version = (int)db.GetParameterValue(dbCmd, "@Version");
}
return(version);
}
public bool Delete(HouseOption house, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_HouseOptionDeleteByHouseId");
db.AddInParameter(command, "HouseId", DbType.Guid, house.HouseId);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
/// <summary>
/// Create a new record in the database.
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
/// <remarks></remarks>
public int CreateDatafile(Datafile file)
{
int newid = 0;
DbCommand cmd = store.GetStoredProcCommand("CreateDatafile");
store.AddInParameter(cmd, "category", DbType.String, file.Category);
store.AddInParameter(cmd, "group", DbType.String, file.Group);
store.AddInParameter(cmd, "filename", DbType.String, file.Filename);
store.AddInParameter(cmd, "extension", DbType.String, file.Extension);
store.AddInParameter(cmd, "content", DbType.Binary, file.Content);
store.AddOutParameter(cmd, "newid", DbType.Int32, newid);
store.ExecuteNonQuery(cmd);
newid = System.Convert.ToInt32(cmd.Parameters[5].Value);
return(newid);
}
public static int Insert(Database db, TaskInfo taskInfo)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO TaskInfo(");
sbValue.Append("values(");
sbField.Append("TaskId");
sbValue.AppendFormat("{0}", taskInfo.TaskId);
sbField.Append(",CameraId");
sbValue.AppendFormat(",{0}", taskInfo.CameraId);
sbField.Append(",DecoderId");
sbValue.AppendFormat(",{0}", taskInfo.DecoderId);
sbField.Append(",Status");
sbValue.AppendFormat(",{0}", taskInfo.Status);
sbField.Append(",HappenDateTime");
sbValue.AppendFormat(",'{0}')", taskInfo.HappenDateTime == null ? DateTime.Now : taskInfo.HappenDateTime);
string cmdText = sbField.ToString() + " " + sbValue.ToString();
try
{
cmdText = cmdText.Replace("\r\n", "");
return db.ExecuteNonQuery(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Delete SAS_ExportData Data...
/// <summary>
/// <param name=sender></param>
/// <param name= e></param>
public bool Delete(ExportDataEN argEn)
{
bool lbRes = false;
string sqlCmd = "DELETE FROM SAS_ExportData WHERE InterfaceID = @InterfaceID";
Microsoft.Practices.EnterpriseLibrary.Data.Database coDb = DatabaseFactory.CreateDatabase(csConnectionStr);
try
{
using (DbCommand cmd = coDb.GetSqlStringCommand(sqlCmd))
{
coDb.AddInParameter(cmd, "@InterfaceID", DbType.String, argEn.InterfaceID);
int liRowAffected = coDb.ExecuteNonQuery(cmd);
if (liRowAffected > -1)
{
lbRes = true;
}
else
{
throw new Exception("Deletion Failed! No Row has been deleted...");
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lbRes);
}
public static int Insert(Database db, MapInfo mapInfo)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO MapInfo(");
sbValue.Append("values(");
sbField.Append("Name");
sbValue.AppendFormat("'{0}'", mapInfo.Name);
sbField.Append(",Width");
sbValue.AppendFormat(",{0}", mapInfo.Width);
sbField.Append(",Height");
sbValue.AppendFormat(",{0}", mapInfo.Height);
sbField.Append(",FileName)");
sbValue.AppendFormat(",'{0}')", mapInfo.FileName);
string cmdText = sbField + " " + sbValue;
try
{
return db.ExecuteNonQuery(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
public bool Insert(Spotlight spotlight, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SpotlightInsert");
db.AddInParameter(command, "UserId", DbType.Guid, spotlight.UserId);
db.AddInParameter(command, "Awards", DbType.String, spotlight.Awards);
db.AddInParameter(command, "Achievements", DbType.String, spotlight.Achievements);
db.AddInParameter(command, "CurentGPA", DbType.String, spotlight.CurentGPA);
db.AddInParameter(command, "OraganizationId", DbType.Int16, spotlight.OraganizationId);
db.AddInParameter(command, "Involvments", DbType.String, spotlight.Involvments);
db.AddInParameter(command, "FraternityId", DbType.Int16, spotlight.FraternityId);
db.AddInParameter(command, "SoroityId", DbType.Int16, spotlight.SoroityId);
db.AddInParameter(command, "GreekHonorSocitiesId", DbType.Int16, spotlight.GreekHonorSocitiesId);
db.AddInParameter(command, "GreakOrganizationId", DbType.Int16, spotlight.GreakOrganizationId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, spotlight.IsDeleted);
db.AddInParameter(command, "CreatedBy", DbType.Guid, spotlight.CreatedBy);
db.AddOutParameter(command, "CreatedDate", DbType.DateTime, 30);
db.ExecuteNonQuery(command, transaction);
spotlight.CreatedDate = Convert.ToDateTime(db.GetParameterValue(command, "CreatedDate").ToString());
spotlight.UpdatedDate = spotlight.CreatedDate;
return true;
}
public static int Insert(Database db, EventRect oEventRect)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO [EvenRectInfo](");
sbValue.Append("values (");
//sbField.Append("[FaceID]");
//sbValue.AppendFormat("'{0}'", oFace.FaceID);
sbField.Append("[x]");
sbValue.AppendFormat("{0}", oEventRect.x);
sbField.Append(",[y]");
sbValue.AppendFormat(",{0}", oEventRect.y);
sbField.Append(",[w]");
sbValue.AppendFormat(",{0}", oEventRect.w);
sbField.Append(",[h]");
sbValue.AppendFormat(",{0}", oEventRect.h);
sbField.Append(",[ObjectId])");
sbValue.AppendFormat(",{0})", oEventRect.ObjectId);
string cmdText = sbField.ToString() + " " + sbValue.ToString();
try
{
cmdText = cmdText.Replace("\r\n", "");
db.ExecuteNonQuery(CommandType.Text, cmdText);
int id = int.Parse(db.ExecuteScalar(CommandType.Text, "SELECT ident_current('EventRectInfo')").ToString());
return id;
}
catch (Exception ex)
{
throw ex;
}
}
public static int Insert(Database db, REct oRect)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO [REct](");
sbValue.Append("values (");
//sbField.Append("[RectID]");
//sbValue.AppendFormat("'{0}'", oRect.RectID);
sbField.Append("[X]");
sbValue.AppendFormat("{0}", oRect.X);
sbField.Append(",[Y]");
sbValue.AppendFormat(",{0}", oRect.Y);
sbField.Append(",[W]");
sbValue.AppendFormat(",{0}", oRect.W);
sbField.Append(",[H])");
sbValue.AppendFormat(",{0})", oRect.H);
string cmdText = sbField.ToString() + " " + sbValue.ToString();
try
{
cmdText = cmdText.Replace("\r\n", "");
db.ExecuteNonQuery(CommandType.Text, cmdText);
int id = int.Parse(db.ExecuteScalar(CommandType.Text, "SELECT ident_current('REct')").ToString());
return id;
}
catch (Exception ex)
{
throw ex;
}
}
public static int UpdateParameter(Database db, SystemParameter systemParameter)
{
StringBuilder sb = new StringBuilder();
if (IsExistRow(db, systemParameter.Name))
{
sb.Append("update systemParameter set");
sb.AppendFormat(" Value='{0}',Type='{1}' where Name='{2}'", systemParameter.Name, systemParameter.Type,systemParameter.Value);
}
else
{
sb.Append("insert into systemParameter(name,type,value) ");
sb.AppendFormat("values('{0}','{1}','{2}')", systemParameter.Name, systemParameter.Type, systemParameter.Value);
}
string cmdText = sb.ToString();
try
{
return db.ExecuteNonQuery(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
private static void DeleteAttach(Guid id, Database db, DbTransaction trans)
{
DbCommand dbCommand = db.GetStoredProcCommand("Usp_DeleteAttachment", id);
try
{
if (trans == null)
db.ExecuteNonQuery(dbCommand);
else
db.ExecuteNonQuery(dbCommand, trans);
}
catch (System.Data.SqlClient.SqlException sex) // 只捕获SqlException，其余抛出继续传播
{
DBHelper.ParseSqlException(sex, true);
}
}
public static int Insert(Database db, CapturePicture ocapturePicture)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO [CapturePicture](");
sbValue.Append("values (");
//sbField.Append("[PictureID]");
//sbValue.AppendFormat("'{0}'", ocapturePicture.PictureID);
sbField.Append("[CameraID]");
sbValue.AppendFormat("{0}", ocapturePicture.CameraID);
sbField.Append(",[Datetime]");
//sbValue.AppendFormat(",'{0}'", ocapturePicture.Datetime);
sbValue.AppendFormat(",'{0}'", ocapturePicture.Datetime);
sbField.Append(",[FilePath])");
sbValue.AppendFormat(",'{0}')", ocapturePicture.FilePath);
string cmdText = sbField.ToString() + " " + sbValue.ToString();
try
{
cmdText = cmdText.Replace("\r\n", "");
db.ExecuteNonQuery(CommandType.Text, cmdText);
//string cmdText2 = "select max(PictureID) from CapturePicture";
//return int.Parse(db.ExecuteScalar(CommandType.Text, cmdText2).ToString());
int id = int.Parse(db.ExecuteScalar(CommandType.Text, "SELECT ident_current('CapturePicture')").ToString());
return id;
}
catch (Exception ex)
{
throw ex;
}
}
public bool Delete(School school, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_SchoolDelete");
db.AddInParameter(command, "SchoolId", DbType.Guid, school.SchoolId);
db.AddInParameter(command, "UpdatedBy", DbType.Guid, school.UpdatedBy);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
public static int Delete(Database db, int id)
{
try
{
StringBuilder sb = new StringBuilder();
DataSet dataSet = GetVideoInfoById(db, id);
if (dataSet.Tables[0].Rows.Count == 0)
{
return 0;
}
//删除相应的文件
VideoInfo videoInfo;
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
videoInfo = new VideoInfo(dr);
if (File.Exists(videoInfo.FilePath))
{
File.Delete(videoInfo.FilePath);
}
}
//删除出数据库中的记录
sb.Append("delete from VideoInfo ");
sb.AppendFormat(" where Id={0}", id);
string cmdText = sb.ToString();
return db.ExecuteNonQuery(CommandType.Text, cmdText);
}
catch (Exception ex)
{
throw ex;
}
}
private static void SaveSysPara(string key, string bu, string value, Database db, DbTransaction trans)
{
DbCommand dbCommand = db.GetStoredProcCommand("Usp_UpdateSysPara", key, bu, value);
try
{
if (trans == null)
db.ExecuteNonQuery(dbCommand);
else
db.ExecuteNonQuery(dbCommand, trans);
}
catch (System.Data.SqlClient.SqlException sex) // 只捕获SqlException，其余抛出继续传播
{
DBHelper.ParseSqlException(sex, false);
}
}
/// <summary>
/// Descripción: Metodo para guardar y actualizar un registro con los datos de la clase Informacion sin el manejo de la transaccion.
/// </summary>
/// <param name="oclsTblpreguntasInformacion">Instancia de la clase que se guardara.</param>
/// <param name="pdb">Instancia de la Base de Datos.</param>
public void m_Save(BC.Modelos.Informacion.clsTblpreguntasInformacion oclsTblpreguntasInformacion, Microsoft.Practices.EnterpriseLibrary.Data.Database pdb)
{
string vstrSP = string.Empty;
try
{
if (oclsTblpreguntasInformacion.bInsert)
{
vstrSP = "sva_Tblpreguntas_Ins";
}
else
{
vstrSP = "sva_Tblpreguntas_Upd";
}
DbCommand oCmd = pdb.GetStoredProcCommand(vstrSP);
pdb.AddInParameter(oCmd, "piIdpreguntas", DbType.Int32, oclsTblpreguntasInformacion.iIdpreguntas);
pdb.AddInParameter(oCmd, "psNombre", DbType.String, oclsTblpreguntasInformacion.sNombre);
pdb.AddInParameter(oCmd, "psParam", DbType.String, oclsTblpreguntasInformacion.sParam);
pdb.AddInParameter(oCmd, "piOrden", DbType.Int32, oclsTblpreguntasInformacion.iOrden);
pdb.AddInParameter(oCmd, "pdtFechacreacion", DbType.DateTime, oclsTblpreguntasInformacion.dtFechacreacion);
pdb.AddInParameter(oCmd, "pdtFechamodificacion", DbType.DateTime, oclsTblpreguntasInformacion.dtFechamodificacion);
pdb.AddInParameter(oCmd, "pdtFechabaja", DbType.DateTime, oclsTblpreguntasInformacion.dtFechabaja);
pdb.AddInParameter(oCmd, "pbActivo", DbType.Boolean, oclsTblpreguntasInformacion.bActivo);
pdb.AddInParameter(oCmd, "pbBaja", DbType.Boolean, oclsTblpreguntasInformacion.bBaja);
pdb.ExecuteNonQuery(oCmd);
oclsTblpreguntasInformacion.bInsert = false;
}
catch (Exception ex)
{
throw ex;
}
}
public static int Insert(Database db, Track oTrack)
{
StringBuilder sbField = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
sbField.Append("INSERT INTO [Track](");
sbValue.Append("values (");
//sbField.Append("[Id]");
//sbValue.AppendFormat("'{0}'", oTrack.Id);
sbField.Append("[REct])");
sbValue.AppendFormat("'{0}')", oTrack.REct);
string cmdText = sbField.ToString() + " " + sbValue.ToString();
try
{
cmdText = cmdText.Replace("\r\n", "");
db.ExecuteNonQuery(CommandType.Text, cmdText);
int id = int.Parse(db.ExecuteScalar(CommandType.Text, "SELECT ident_current('Track')").ToString());
return id;
}
catch (Exception ex)
{
throw ex;
}
}
public bool Delete(StudentHouse studentHouse, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_StudentHouseDelete");
db.AddInParameter(command, "StudentHouseId", DbType.Int16, studentHouse.StudentHouseId);
db.AddInParameter(command, "UpdatedBy", DbType.Guid, studentHouse.UpdatedBy);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
public bool Delete(Option Option, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionDelete");
db.AddInParameter(command, "OptionId", DbType.Int16, Option.OptionId);
db.AddInParameter(command, "IsDeleted", DbType.Boolean, Option.IsDeleted);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
public void ChangeUserInfo(string userName, string firstName, string lastName, int deptId)
{
Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
this.ValidateParam("userName", userName);
DbCommand dbCommand = db.GetStoredProcCommand("ChangePersonalInformation", userName, firstName, lastName, deptId);
db.ExecuteNonQuery(dbCommand);
}
public static int ExecuteNonQuery(EntLib.Database db, DbCommand cmd)
{
var f = new Func <int>(() =>
{
return(db.ExecuteNonQuery(cmd));
});
return(RetryIt(f, cmd));
}
public bool Delete(OptionItem OptionItem, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_OptionItemDelete");
db.AddInParameter(command, "OptionItemId", DbType.Guid, OptionItem.OptionItemId);
db.AddInParameter(command, "IsDeleted", DbType.Guid, OptionItem.IsDeleted);
db.ExecuteNonQuery(command, transaction);
return true;
}
public bool Delete(Comment comment, Database db, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("usp_CommentDelete");
db.AddInParameter(command, "CommentId", DbType.Int32, comment.CommentId);
db.AddInParameter(command, "UpdatedBy", DbType.Guid, comment.UpdatedBy);
if (transaction == null)
{
db.ExecuteNonQuery(command);
}
else
{
db.ExecuteNonQuery(command, transaction);
}
return true;
}
public Usuario Registro(Usuario usuario, string password)
{
byte[] passwordHash, passwordSalt;
CreatePasswordHash(password, out passwordHash, out passwordSalt);
usuario.PasswordHash = passwordHash;
usuario.PasswordSalt = passwordSalt;
stringBuilder = new StringBuilder();
Database.ExecuteNonQuery("DbSistemas.dbo.UPIUsuario", usuario.IdUsuario, usuario.NumeroDeEmpleado, usuario.UsuarioAcceso, usuario.Descripcion, usuario.PasswordHash, usuario.PasswordSalt);
return(usuario);
}
public bool ActualizarPersonalTiemposDeAlimentacion(PersonalTiemposDeAlimentacion personalTiemposDeAlimentacion)
{
stringBuilder = new StringBuilder();
stringBuilder.AppendLine("UPDATE TblPersonalTiemposDeAlimentacion SET NumeroDeEmpleado=@NumeroDeEmpleado,Fecha=@Fecha,TiemposDeAlimentacionID=@TiemposDeAlimentacionID,ModuloHabitacional=@ModuloHabitacional");
stringBuilder.AppendLine(" WHERE PersonalTiemposDeAlimentacionID=@PersonalTiemposDeAlimentacionID");
DbCommand cmd = Database.GetSqlStringCommand(stringBuilder.ToString());
Database.AddInParameter(cmd, "NumeroDeEmpleado", DbType.String, personalTiemposDeAlimentacion.NumeroDeEmpleado);
Database.AddInParameter(cmd, "Fecha", DbType.DateTime, personalTiemposDeAlimentacion.Fecha);
Database.AddInParameter(cmd, "TiemposDeAlimentacionID", DbType.Int16, personalTiemposDeAlimentacion.TiemposDeAlimentacionID);
Database.AddInParameter(cmd, "ModuloHabitacional", DbType.Int16, personalTiemposDeAlimentacion.ModuloHabitacional);
Database.AddInParameter(cmd, "PersonalTiemposDeAlimentacionID", DbType.Int32, personalTiemposDeAlimentacion.PersonalTiemposDeAlimentacionID);
if (Database.ExecuteNonQuery(cmd) > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 通用插入
/// </summary>
/// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
/// <param name="row">DataRow</param>
/// <param name="_table">插入表名称</param>
/// <param name="_isIDENTITY_INSERT_OFF">是否关闭主键列插入功能</param>
/// <param name="_identity">主键列名称</param>
public static int UniversalizationInsert(Microsoft.Practices.EnterpriseLibrary.Data.Database db, DataRow row, string _table, bool _isIDENTITY_INSERT_OFF, string _identity)
{
try
{
var sql = new StringBuilder();
var sqlend = new StringBuilder();
sql.Append("INSERT INTO " + _table + " (");
sqlend.Append(" VALUES (");
var cmd = db.DbProviderFactory.CreateCommand();
//获取所有的row的属性（获取列集合）
var cols = row.GetType().GetProperties();
foreach (var col in cols)
{
//insert去掉主键列插入（仅当数据库IDENTITY_INSERT=OFF时适用；为ON时不用该判断）
if (_isIDENTITY_INSERT_OFF && col.Name == _identity)
{
continue;
}
//获取某列的值
var val = Try2String(GetRowValue(row, col.Name));
//去掉空属性
if (string.IsNullOrEmpty(val))
{
continue;
}
//时间属性初始化时未赋值会变为默认最小值
if (col.PropertyType == typeof(DateTime))
{
DateTime dt;
DateTime.TryParse(val, out dt);
if (dt <= SqlDateTime.MinValue.Value)
{
continue;
}
}
sql.Append(" " + col.Name + ",");
sqlend.Append(" '" + val + "',");
}
var start = sql.ToString();
start = start.Substring(0, start.Length - 1) + ")";
var end = sqlend.ToString();
end = end.Substring(0, end.Length - 1) + ")";
cmd.CommandText = start + end;
return(db.ExecuteNonQuery(cmd));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 通用更新
/// </summary>
/// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
/// <param name="row">DataRow</param>
/// <param name="_table">更新表名称</param>
/// <param name="_identity">主键列名称</param>
/// <returns></returns>
public static int UniversalizationUpdate(Microsoft.Practices.EnterpriseLibrary.Data.Database db, DataRow row, string _table, string _identity)
{
try
{
var cmd = db.DbProviderFactory.CreateCommand();
var sql = new StringBuilder();
var sqlend = new StringBuilder();
sql.Append("UPDATE " + _table + " set");
sqlend.Append("WHERE");
//获取所有的row的属性（获取列集合）
var cols = row.GetType().GetProperties();
foreach (var col in cols)
{
//主键列不更新
if (col.Name == _identity)
{
continue;
}
//获取某列的值
var val = Try2String(GetRowValue(row, col.Name));
//去掉空属性
if (string.IsNullOrEmpty(val))
{
continue;
}
//时间属性初始化时未赋值会变为默认最小值
if (col.PropertyType == typeof(DateTime))
{
DateTime dt;
DateTime.TryParse(val, out dt);
if (dt <= SqlDateTime.MinValue.Value)
{
continue;
}
}
sql.Append(" " + col.Name + " = '" + val + "',");
}
sqlend.Append(" " + _identity + "= '" + Try2String(GetRowValue(row, _identity)) + "'");
var start = sql.ToString();
start = start.Substring(0, start.Length - 1) + " ";
var end = sqlend.ToString();
end = end.Substring(0, end.Length) + " ";
cmd.CommandText = start + end;
return(db.ExecuteNonQuery(cmd));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Executes the <paramref name="dbCommand"/> and returns the number of rows affected.
/// </summary>
/// <param name="database">The database to execute the command within.</param>
/// <param name="dbCommand">The command that contains the query to execute.</param>
/// <returns>The number of rows affected.</returns>
/// <exception cref="System.Exception">The command could not be executed.</exception>
/// <exception cref="System.Data.Common.DbException">The command could not be executed.</exception>
public static int ExecuteNonQuery(Enterprise.Database database, DbCommand dbCommand)
{
int results = 0;
try
{
results = database.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
throw new BusinessException(ex);
}
return(results);
}
public int ExeDbCommand(List <DbParameter> mList, string StoredProcName, ref string Msg)
{
try
{
DataSet ds = new DataSet();
Open();
mDbCommand = mDatabase.GetStoredProcCommand(StoredProcName);
mDbCommand.Connection = Connection;
//mDatabase.AddInParameter(mDbCommand, "@TableName", DbType.AnsiString, tblName);
//mDatabase.AddInParameter(mDbCommand, "@SelectFields", DbType.AnsiString, fldName);
//mDatabase.AddInParameter(mDbCommand, "@Where", DbType.AnsiString, where);
//mDatabase.AddInParameter(mDbCommand, "@OrderField", DbType.AnsiString, fldSort);
//mDatabase.AddInParameter(mDbCommand, "@PageSize", DbType.Int32, pageSize);
//mDatabase.AddInParameter(mDbCommand, "@PageIndex", DbType.Int32, page);
//mDatabase.AddOutParameter(mDbCommand, "@RowCount", DbType.Int32, Counts);
int i = 0;
foreach (DbParameter mItem in mList)
{
i++;
if (i < mList.Count)
{
mDatabase.AddInParameter(mDbCommand, mItem.ParameterName, mItem.DbType, mItem.Value);
}
else
{
mDatabase.AddOutParameter(mDbCommand, mItem.ParameterName, mItem.DbType, 50);
}
}
//ds = mDatabase.ExecuteDataSet(mDbCommand);
int mCount = mDatabase.ExecuteNonQuery(mDbCommand);
//pageCount = Convert.ToInt32(mDatabase.GetParameterValue(mDbCommand, "@pageCount"));
//int Counts = Convert.ToInt32(mDatabase.GetParameterValue(mDbCommand, "@RowCount"));
//return ModelHelper.ModelListInTable<T>(ds.Tables[0]);
string mMsg = mDatabase.GetParameterValue(mDbCommand, mList[mList.Count - 1].ParameterName).ToString();
Msg = mMsg;
return(mCount);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
Close();
}
}
/// <summary>
/// Takes a command object and returns an integer for the number of rows affected
/// </summary>
/// <param name="sqlCommand">SQLCommand</param>
/// <returns>SqlDataReader</returns>
public int ExecuteNonQuery(SqlCommand command)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
int timeout = CommandTimeOut;
if (timeout > 0)
{
command.CommandTimeout = timeout;
}
//Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDatabase();
return(db.ExecuteNonQuery((DbCommand)command));
}
/// <summary>
/// 根据条件where单表删除记录
/// </summary>
/// <param name="_db"></param>
/// <param name="_tablename">注意表名要用数据库全称</param>
/// <param name="_sqlwhere">限定语句，必须and开头，不能为空！</param>
/// <returns></returns>
public static int UniversalizationDeleteWhere(Microsoft.Practices.EnterpriseLibrary.Data.Database _db, string _tablename, string _sqlwhere)
{
if (string.IsNullOrEmpty(_sqlwhere))
{
return(0); //防止误操作，禁止无条件删除
}
try
{
var sql = "delete from " + _tablename + " where 1=1 " + _sqlwhere;
var cmd = _db.DbProviderFactory.CreateCommand();
cmd.CommandText = sql;
return(_db.ExecuteNonQuery(cmd));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 通用删除
/// </summary>
/// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
/// <param name="row">DataRow</param>
/// <param name="_table">表名称</param>
/// <param name="_identity">主键列名称</param>
/// <returns></returns>
public static int UniversalizationDelete(Microsoft.Practices.EnterpriseLibrary.Data.Database db, DataRow row, string _table, string _identity)
{
var idval = Try2String(row[_identity, DataRowVersion.Original]);
if (idval == "")
{
return(0);
}
var Sql = "delete from " + _table + " where " + _identity + "='" + idval + "';";
try
{
var cmd = db.DbProviderFactory.CreateCommand();
cmd.CommandText = Sql;
return(db.ExecuteNonQuery(cmd));
}
catch (Exception ex)
{
throw ex;
}
}
public int ExecuteNonQuery(string query, CommandType commandType, List <IDbDataParameter> parameters)
{
DbConnection _connection;
_connection = _database.CreateConnection();
var cmd = commandType == CommandType.StoredProcedure ? _database.GetStoredProcCommand(query) : _database.GetSqlStringCommand(query);
cmd.CommandTimeout = 300;
cmd.Connection = _connection;
_connection.Open();
using (cmd)
{
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
return(_database.ExecuteNonQuery(cmd));
}
}
/// <summary>
/// Descripción: Metodo para guardar y actualizar un registro con los datos de la clase Informacion con el manejo de la transacción.
/// </summary>
/// <param name="oclsTblcatladaInformacion">Instancia de la clase que se guardara.</param>
/// <param name="pdb">Instancia de la Base de Datos.</param>
/// <param name="poTrans">Instancia de la Transacción.</param>
public void m_Save(BC.Modelos.Informacion.clsTblcatladaInformacion oclsTblcatladaInformacion, Microsoft.Practices.EnterpriseLibrary.Data.Database pdb, System.Data.Common.DbTransaction poTrans)
{
string vstrSP = string.Empty;
try
{
if (oclsTblcatladaInformacion.bInsert)
{
vstrSP = "sva_Tblcatlada_Ins";
}
else
{
vstrSP = "sva_Tblcatlada_Upd";
}
DbCommand oCmd = pdb.GetStoredProcCommand(vstrSP);
pdb.AddInParameter(oCmd, "piIdlada", DbType.Int32, oclsTblcatladaInformacion.iIdlada);
pdb.AddInParameter(oCmd, "psNombre", DbType.String, oclsTblcatladaInformacion.sNombre);
pdb.AddInParameter(oCmd, "psDescripcion", DbType.String, oclsTblcatladaInformacion.sDescripcion);
pdb.AddInParameter(oCmd, "pbActivo", DbType.String, oclsTblcatladaInformacion.bActivo);
pdb.AddInParameter(oCmd, "pbBaja", DbType.String, oclsTblcatladaInformacion.bBaja);
pdb.AddInParameter(oCmd, "piIdusuariocreacion", DbType.String, oclsTblcatladaInformacion.iIdusuariocreacion);
pdb.AddInParameter(oCmd, "pdTfechacreacion", DbType.String, oclsTblcatladaInformacion.dTfechacreacion);
pdb.AddInParameter(oCmd, "piIdusuariomodificacion", DbType.String, oclsTblcatladaInformacion.iIdusuariomodificacion);
pdb.AddInParameter(oCmd, "pdTfechamodificacion", DbType.String, oclsTblcatladaInformacion.dTfechamodificacion);
pdb.AddInParameter(oCmd, "piIdusuarioabaja", DbType.String, oclsTblcatladaInformacion.iIdusuarioabaja);
pdb.AddInParameter(oCmd, "pdTfechabaja", DbType.String, oclsTblcatladaInformacion.dTfechabaja);
pdb.ExecuteNonQuery(oCmd, poTrans);
oclsTblcatladaInformacion.bInsert = false;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Descripción: Metodo para guardar y actualizar un registro con los datos de la clase Informacion con el manejo de la transacción.
/// </summary>
/// <param name="oclsTblcodigopostalInformacion">Instancia de la clase que se guardara.</param>
/// <param name="pdb">Instancia de la Base de Datos.</param>
/// <param name="poTrans">Instancia de la Transacción.</param>
public void m_Save(BC.Modelos.Informacion.clsTblcodigopostalInformacion oclsTblcodigopostalInformacion, Microsoft.Practices.EnterpriseLibrary.Data.Database pdb, System.Data.Common.DbTransaction poTrans)
{
string vstrSP = string.Empty;
try
{
if (oclsTblcodigopostalInformacion.bInsert)
{
vstrSP = "sva_Tblcodigopostal_Ins";
}
else
{
vstrSP = "sva_Tblcodigopostal_Upd";
}
DbCommand oCmd = pdb.GetStoredProcCommand(vstrSP);
pdb.AddInParameter(oCmd, "piIdcodigopostal", DbType.Int32, oclsTblcodigopostalInformacion.iIdcodigopostal);
pdb.AddInParameter(oCmd, "psCodigo", DbType.String, oclsTblcodigopostalInformacion.sCodigo);
pdb.AddInParameter(oCmd, "psAsentamiento", DbType.String, oclsTblcodigopostalInformacion.sAsentamiento);
pdb.AddInParameter(oCmd, "psTipoasentamiento", DbType.String, oclsTblcodigopostalInformacion.sTipoasentamiento);
pdb.AddInParameter(oCmd, "psMunicipio", DbType.String, oclsTblcodigopostalInformacion.sMunicipio);
pdb.AddInParameter(oCmd, "psEstado", DbType.String, oclsTblcodigopostalInformacion.sEstado);
pdb.AddInParameter(oCmd, "psCiudad", DbType.String, oclsTblcodigopostalInformacion.sCiudad);
pdb.AddInParameter(oCmd, "pdtFechacreacion", DbType.DateTime, oclsTblcodigopostalInformacion.dtFechacreacion);
pdb.AddInParameter(oCmd, "pdtFechamodificacion", DbType.DateTime, oclsTblcodigopostalInformacion.dtFechamodificacion);
pdb.AddInParameter(oCmd, "pdtFechabaja", DbType.DateTime, oclsTblcodigopostalInformacion.dtFechabaja);
pdb.AddInParameter(oCmd, "pbBaja", DbType.Boolean, oclsTblcodigopostalInformacion.bBaja);
pdb.ExecuteNonQuery(oCmd, poTrans);
oclsTblcodigopostalInformacion.bInsert = false;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Update SAS_ExportData Data...
/// <summary>
/// <param name=sender></param>
/// <param name= e></param>
public bool Update(ExportDataEN argEn)
{
bool lbRes = false;
int iOut = 0;
string sqlCmd = "Select count(*) as cnt From SAS_ExportData WHERE InterfaceID = '" + argEn.InterfaceID + "'";
try
{
Microsoft.Practices.EnterpriseLibrary.Data.Database loDbSel = DatabaseFactory.CreateDatabase(csConnectionStr);
using (DbCommand cmdSel = loDbSel.GetSqlStringCommand(sqlCmd))
{
using (IDataReader dr = loDbSel.ExecuteReader(cmdSel))
{
if (dr.Read())
{
iOut = GetValue <int>(dr, "cnt");
}
if (iOut < 0)
{
throw new Exception("Update Failed! No Record Exist!");
}
}
if (iOut != 0)
{
sqlCmd = "UPDATE SAS_ExportData SET InterfaceID = @InterfaceID, FileFormat = @FileFormat, Interface = @Interface, Frequency = @Frequency, TimeofExport = @TimeofExport, Filepath = @Filepath, PreviousData = @PreviousData,DateRange = @DateRange, DateFrom = @DateFrom, DateTo = @DateTo, LastUpdatedBy = @LastUpdatedBy, LastUpdatedDateTime = @LastUpdatedDateTime WHERE InterfaceID = @InterfaceID";
Microsoft.Practices.EnterpriseLibrary.Data.Database loDbUpd = DatabaseFactory.CreateDatabase(csConnectionStr);
using (DbCommand cmd = loDbUpd.GetSqlStringCommand(sqlCmd))
{
loDbUpd.AddInParameter(cmd, "@InterfaceID", DbType.String, argEn.InterfaceID);
loDbUpd.AddInParameter(cmd, "@FileFormat", DbType.String, argEn.FileFormat);
loDbUpd.AddInParameter(cmd, "@Interface", DbType.String, argEn.Interface);
loDbUpd.AddInParameter(cmd, "@Frequency", DbType.String, argEn.Frequency);
loDbUpd.AddInParameter(cmd, "@TimeofExport", DbType.String, argEn.TimeofExport);
loDbUpd.AddInParameter(cmd, "@Filepath", DbType.String, argEn.Filepath);
loDbUpd.AddInParameter(cmd, "@PreviousData", DbType.Boolean, argEn.PreviousData);
loDbUpd.AddInParameter(cmd, "@DateRange", DbType.Boolean, argEn.DateRange);
loDbUpd.AddInParameter(cmd, "@DateFrom", DbType.DateTime, argEn.DateFrom);
loDbUpd.AddInParameter(cmd, "@DateTo", DbType.DateTime, argEn.DateTo);
loDbUpd.AddInParameter(cmd, "@LastUpdatedBy", DbType.String, argEn.LastUpdatedBy);
loDbUpd.AddInParameter(cmd, "@LastUpdatedDateTime", DbType.DateTime, argEn.LastUpdatedDateTime);
int liRowAffected = loDbUpd.ExecuteNonQuery(cmd);
if (liRowAffected > -1)
{
System.Messaging.Message mm = new System.Messaging.Message(argEn, new System.Messaging.XmlMessageFormatter(new Type[] { typeof(ExportDataEN), typeof(string) }));
mm.Label = argEn.InterfaceID;
MessageQueueTransaction Transaction = new MessageQueueTransaction();
Transaction.Begin();
mq.Send(mm, Transaction);
Transaction.Commit();
lbRes = true;
}
else
{
throw new Exception("Update Failed! No Row has been updated...");
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lbRes);
}
//**********UploadExcelFile**************************************************
//NAME : UploadExcelFile
//PURPOSE : Uploading the data from calendar table and excel sheet into product catalog
//PARAMETERS : IncludeExclude,Locale
//RETURN VALUE : boolean
//USAGE :
//CREATED ON : 11-02-2006
//CHANGE HISTORY :Auth Date Description
//***********************************************************************
public bool UploadExcelFile()
{
string sConnectionOleString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "AssetData.xls;Extended Properties=Excel 8.0;";
OleDbDataReader assetOleDataReader = null;
SqlDataReader assetDataReader = null;
string strProducts = "";
string splitCharater;
splitCharater = ",";
string strMode = "A";
string sql = "";
string PID = "";
int iResult;
DataLibrary.Database oSiteWideDB = null;
OleDbConnection objOleConn = new OleDbConnection(sConnectionOleString);
try
{
objOleConn.Open();
}
catch (Exception Ex)
{
Console.WriteLine(Ex.Message);
return(false);
}
sql = "select distinct Title,Category_ID,SubGroups,'' as Industry,calendar.Description,iso2,[File_Name]," +
" File_Size,BDate,calendar.Code,'' ProductIds,Clone,File_Name_POD,Revision_Code,item_number,calendar.Id" +
" from calendar,Language where site_id=82 and file_name is not null " +
" and calendar.language=language.code " +
" order by calendar.id";
try
{
//Create database object using enterprise library i.e(Dataconfig.config)
oSiteWideDB = DataLibrary.DatabaseFactory.CreateDatabase("FlukeSitewide");
//Get the data reader for assets
assetDataReader = (SqlDataReader)oSiteWideDB.ExecuteReader(System.Data.CommandType.Text, sql);
}
catch (Exception ex)
{
Console.Write("Unable to execute the reader");
}
string[] productArray = null;
string assetFilename;
string assetPODName;
string strcategory = "";
long lngpid = 0;
string clonePID = "";
string strupdatequery = "";
while (assetDataReader.Read())
{
assetFilename = assetDataReader.GetValue(6).ToString();
assetPODName = assetDataReader.GetValue(12).ToString();
fnWriteLog("Id = " + assetDataReader.GetValue(15).ToString() + "*******" + "\n", false);
sql = "SELECT IndustryID,ProductID FROM [Sheet1$]" +
" where Revision_Code='" + assetDataReader.GetValue(13).ToString() +
"' and item_number=" + assetDataReader.GetValue(14).ToString();
OleDbCommand objCmdOleSelect = new OleDbCommand(sql, objOleConn);
OleDbDataAdapter objOleAdapter = new OleDbDataAdapter();
objOleAdapter.SelectCommand = objCmdOleSelect;
try
{
assetOleDataReader = objCmdOleSelect.ExecuteReader();
}
catch (Exception ex)
{
//Console.Write("Row not found in excel sheet " + ex.Message + "\n");
fnWriteLog("Row not found in excel sheet " + "\n", false);
}
try
{
assetOleDataReader.Read();
try
{
strProducts = assetOleDataReader.GetValue(1).ToString();
if (strProducts.Length > 5)
{
//Console.WriteLine("Stop");
strProducts = strProducts.Substring(1, 0);
}
productArray = strProducts.Split(splitCharater.ToCharArray()[0]);
if (assetOleDataReader.GetValue(0).ToString() == "1")
{
strcategory = "DCCA";
}
else if (assetOleDataReader.GetValue(0).ToString() == "2")
{
strcategory = "INET";
}
else if (assetOleDataReader.GetValue(0).ToString() == "3")
{
strcategory = "TELE";
}
}
catch (Exception Ex)
{
fnWriteLog("Product or Industry Id value not present for this row" + "\n", false);
}
clonePID = "0";
strMode = "A";
if (Convert.ToBoolean(assetDataReader.GetValue(11)) == true)
{
sql = "SELECT PID FROM calendar" +
" where id=" + assetDataReader.GetValue(11).ToString();
SqlDataReader Clonedatareader = null;
Clonedatareader = (SqlDataReader)oSiteWideDB.ExecuteReader(System.Data.CommandType.Text, sql);
strMode = "U";
try
{
clonePID = Convert.ToString(Clonedatareader.GetValue(0));
Clonedatareader.Close();
Clonedatareader = null;
}
catch (Exception ex)
{
fnWriteLog("Parent Row not found for this asset " + "\n", false);
}
}
PID = CreateModifyAsset(Convert.ToBoolean(assetDataReader.GetValue(11)), clonePID, assetDataReader.GetValue(0).ToString(),
assetDataReader.GetValue(4).ToString(), assetFilename, assetDataReader.GetValue(7).ToString(), Convert.ToDateTime(assetDataReader.GetValue(8).ToString()), productArray,
assetDataReader.GetValue(5).ToString(), strMode, assetDataReader.GetValue(1).ToString(),
assetDataReader.GetValue(14).ToString(), assetDataReader.GetValue(2).ToString(),
strcategory, "none");
assetOleDataReader.Close();
Console.Write("Id and PID = " + assetDataReader.GetValue(15).ToString() + " - " + PID + "\n");
fnWriteLog("Id and PID = " + assetDataReader.GetValue(15).ToString() + " - " + PID + "\n", false);
XmlDocument objxml = new XmlDocument();
XmlNode objcol;
objxml.LoadXml(PID);
if (objxml != null)
{
objcol = objxml.SelectSingleNode("ProductId");
PID = objcol.InnerText;
}
try
{
lngpid = Convert.ToInt32(PID);
PID = Convert.ToString(lngpid);
}
catch (Exception ex)
{
PID = "0";
fnWriteLog("Relationship no set " + "\n", false);
}
if (PID != "0")
{
strupdatequery = "update calendar set PID=" + PID +
" where id=" + assetDataReader.GetValue(15).ToString();
iResult = (int)oSiteWideDB.ExecuteNonQuery(System.Data.CommandType.Text, strupdatequery);
fnWriteLog(assetDataReader.GetValue(15).ToString() + "," + PID + "\n", false);
}
fnWriteLog("*******************" + "\n", false);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
objCmdOleSelect = null;
objOleAdapter = null;
assetOleDataReader.Close();
}
assetDataReader = null;
return(true);
}
protected int ExecuteNonQuery(SqlCommand command)
{
return(_db.ExecuteNonQuery(command));
}
