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