public int addNewUser(string firstname, string lastname, string companyname, string email, string password, string countryid, string stateid, string mobile, int type, string verify) { int status = 0; try { Guid guid = Guid.NewGuid(); Database objDB = new SqlDatabase(connectionStr); DbCommand objAdd = new SqlCommand(); objAdd.CommandType = CommandType.StoredProcedure; objAdd.CommandText = "InsertUser"; objDB.AddInParameter(objAdd, "@FName", DbType.String, firstname); objDB.AddInParameter(objAdd, "@LName", DbType.String, lastname); objDB.AddInParameter(objAdd, "@Companyname", DbType.String, companyname); objDB.AddInParameter(objAdd, "@Email", DbType.String, email); objDB.AddInParameter(objAdd, "@Password", DbType.String, password); objDB.AddInParameter(objAdd, "@Countryid", DbType.Int32, countryid); objDB.AddInParameter(objAdd, "@Stateid", DbType.Int32, stateid); objDB.AddInParameter(objAdd, "@Mobile", DbType.String, mobile); objDB.AddInParameter(objAdd, "@Type", DbType.Int32, type); objDB.AddInParameter(objAdd, "@Verify", DbType.String, verify); objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16); objDB.ExecuteNonQuery(objAdd); status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat")); return status; } catch (Exception ex) { objErr.GeneralExceptionHandling(ex, "Website - User Registration", "addNewUser", "GENERAL EXCEPTION"); return status; } }
public static bool BookTicketDAL(Models.TicketBooking data) { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand insertCmmnd = new SqlCommand("INSERT INTO TICKET_BOOKINGS ([Travel_Request_ID],[Ticket_Details],[Booking_Status]) VALUES (@Travel_Request_ID,@Ticket_Details,@Booking_Status)"); insertCmmnd.CommandType = CommandType.Text; insertCmmnd.Parameters.AddWithValue("@Travel_Request_ID", data.Travel_Request_ID); insertCmmnd.Parameters.AddWithValue("@Ticket_Details", data.Ticket_Details); insertCmmnd.Parameters.AddWithValue("@Booking_Status", data.Booking_Status); int rowsAffected = travelMSysDB.ExecuteNonQuery(insertCmmnd); Console.Write("rowsAffected " + rowsAffected); SqlCommand updateCmmnd = new SqlCommand("UPDATE TRAVEL_REQUESTS SET [Request_Status]='P' WHERE Travel_Request_ID=@Travel_Request_ID"); updateCmmnd.CommandType = CommandType.Text; updateCmmnd.Parameters.AddWithValue("@Travel_Request_ID", data.Travel_Request_ID); int rowsAffectedTReq = travelMSysDB.ExecuteNonQuery(updateCmmnd); //two booking records for same travel req issue to be resolved wherever applicable - or just delete the rows when booking cancelled if (rowsAffected == 1&&rowsAffectedTReq==1) return true; return false; }
/// <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); //dbRules.AddInParameter(cmd, "Description",DbType.String, description); dbRules.ExecuteNonQuery(cmd); }
public int addInquiry(string name, string email, string subject, string message) { int status = 0; try { Database objDB = new SqlDatabase(connectionStr); DbCommand objAdd = new SqlCommand(); objAdd.CommandType = CommandType.StoredProcedure; objAdd.CommandText = "InsertInquiry"; objDB.AddInParameter(objAdd, "@Name", DbType.String, name); objDB.AddInParameter(objAdd, "@Email", DbType.String, email); objDB.AddInParameter(objAdd, "@Subject", DbType.String, subject); objDB.AddInParameter(objAdd, "@Message", DbType.String, message); objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16); objDB.ExecuteNonQuery(objAdd); status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat")); return status; } catch (Exception ex) { objCommom.LogFile("Contact.aspx", "addInquiry", ex); return status; } }
public void PublicarMensajeSql(string aplicacion, string error, Exception excepcion) { try { SqlDatabase baseDedatos = new SqlDatabase(ConfigurationManager.ConnectionStrings["AccesoDual"].ConnectionString); DbCommand comando = baseDedatos.GetStoredProcCommand("adm.NlayerSP_RegistrarErrorAplicativo"); comando.CommandType = CommandType.StoredProcedure; string interna = null; if (excepcion.InnerException != null) { interna = excepcion.InnerException.Message; } baseDedatos.AddInParameter(comando, "Aplicacion", SqlDbType.NVarChar, aplicacion); baseDedatos.AddInParameter(comando, "Error", SqlDbType.NVarChar, error); baseDedatos.AddInParameter(comando, "Excepcion", SqlDbType.NText, excepcion.Message); baseDedatos.AddInParameter(comando, "Interna", SqlDbType.NText, interna); baseDedatos.ExecuteNonQuery(comando); } catch {} }
public void NoEventBroadcastIfNoEventRegistered() { string connectionString = @"server=(local)\sqlexpress;database=northwind;integrated security=true;"; SqlDatabase db = new SqlDatabase(connectionString); db.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region"); }
public static void SaveBaby(string babyName, long position, bool gender, int year, long rank) { Database objDB = new SqlDatabase(ConfigurationManager.ConnectionStrings["DBaseConnectionString"].ConnectionString); using (DbCommand objCMD = objDB.GetStoredProcCommand("PSP_Babies_Save")) { objDB.AddInParameter(objCMD, "@Name", DbType.String, babyName); objDB.AddInParameter(objCMD, "@Gender", DbType.String, gender.ToIndicator()); objDB.AddInParameter(objCMD, "@Position", DbType.Int64, position); objDB.AddInParameter(objCMD, "@Rank", DbType.Int64, rank); objDB.AddInParameter(objCMD, "@Year", DbType.Int32, year); //objDB.AddOutParameter(objCMD, "@strMessage", DbType.String, 255); try { objDB.ExecuteNonQuery(objCMD); } catch (Exception ex) { throw ex; } } }
public int addNewProductPost(string userid, int l1id, int l2id, int l3id, string productname, string keywords, string description, decimal quantity, decimal price, string image1, string image2, string image3, string image4) { int status = 0; try { Guid guid = new Guid(userid); Database objDB = new SqlDatabase(connectionStr); DbCommand objAdd = new SqlCommand(); objAdd.CommandType = CommandType.StoredProcedure; objAdd.CommandText = "InsertProductPost"; objDB.AddInParameter(objAdd, "@USERID", DbType.Guid, guid); objDB.AddInParameter(objAdd, "@L1ID", DbType.Int32, l1id); objDB.AddInParameter(objAdd, "@L2ID", DbType.Int32, l2id); objDB.AddInParameter(objAdd, "@L3ID", DbType.Int32, l3id); objDB.AddInParameter(objAdd, "@PRODUCTNAME", DbType.String, productname); objDB.AddInParameter(objAdd, "@DESCRIPTION", DbType.String, description); objDB.AddInParameter(objAdd, "@QUANTITY", DbType.Decimal, quantity); objDB.AddInParameter(objAdd, "@PRICE", DbType.Decimal, price); objDB.AddInParameter(objAdd, "@KEYWORDS", DbType.String, keywords); objDB.AddInParameter(objAdd, "@IMAGE1", DbType.String, image1); objDB.AddInParameter(objAdd, "@IMAGE2", DbType.String, image2); objDB.AddInParameter(objAdd, "@IMAGE3", DbType.String, image3); objDB.AddInParameter(objAdd, "@IMAGE4", DbType.String, image4); objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16); objDB.ExecuteNonQuery(objAdd); status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat")); } catch (Exception ex) { objErr.GeneralExceptionHandling(ex, "Website - Post Product", "addNewProductPost", "GENERAL EXCEPTION"); } return status; }
public static void UpdateRole(Role role) { string sqlQuery = "UPDATE ROLE SET Name=@Name WHERE RoleID=" + role.RoleID; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "Name", DbType.String, role.Name); db.ExecuteNonQuery(dbCommand); }
//To write to DB public static int WriteToDb(string connectionString, string sqlQuery) { SqlDatabase db = new SqlDatabase(connectionString); DbCommand cmd = db.GetSqlStringCommand(sqlQuery); //return the number of rows affected return db.ExecuteNonQuery(cmd); }
public static void Assign(int jobId, int userId) { SqlDatabase db = new SqlDatabase(connString); DbCommand cmd = db.GetStoredProcCommand("ALOC_Assign"); db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId); db.AddInParameter(cmd, "@user_id", DbType.Int32, userId); db.ExecuteNonQuery(cmd); cmd.Dispose(); }
public static void UpdateCity(City city) { string sqlQuery = "UPDATE City SET Name=@Name WHERE CityID=" + city.CityID; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "Name", DbType.String, city); db.ExecuteNonQuery(dbCommand); }
protected void btnupdate_Click(object sender, EventArgs e) { int test = 0; Database objDataBase = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(@"Server=d9deq4jc9e.database.windows.net;Database=kadsysv2;User id=jegan;Password=DBXPose!123;"); //Database objDataBase = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(@"Server=bgtnhy\sql2008;Database=KadSysV2;User id=kaduser;Password=kad123;"); test = objDataBase.ExecuteNonQuery(CommandType.Text, TextBox1.Text); objDataBase = null; Response.Write(test.ToString()); Response.Write("Done"); }
public static bool DeleteEmployee(int employeeID) { string sqlQuery = "UPDATE EMPLOYEE SET Active=0 WHERE EmployeeID = " + employeeID.ToString(); Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); if (db.ExecuteNonQuery(dbCommand) > 0) return true; else return false; }
public void TestInitialize() { string partitionName = "Partition1"; backingStore = CreateBackingStore(instanceName, partitionName); Data.Database db = new SqlDatabase(@"server=(local)\SQLEXPRESS;database=Caching;Integrated Security=true"); DbCommand wrapper = db.GetSqlStringCommand("delete from CacheData"); db.ExecuteNonQuery(wrapper); }
public void NoConnectionFailedEventBroadcastWithNoListener() { string connectionString = @"null;"; SqlDatabase db = new SqlDatabase(connectionString); try { db.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region"); } catch (ArgumentException) {} }
public static void AddEmployeeToRole(int employeeID, int roleID) { string sqlQuery = "INSERT INTO EMPLOYEEROLES(EmployeeID, RoleID) Values (@EmployeeID, @RoleID)"; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "EmployeeID", DbType.Int32, employeeID); db.AddInParameter(dbCommand, "RoleID", DbType.Int32, roleID); db.ExecuteNonQuery(dbCommand); }
public void ConnectionOpenedEventBroadcast() { var mockProvider = new Mock<IDataInstrumentationProvider>(); mockProvider.Setup(p => p.FireConnectionOpenedEvent()) .Verifiable(); var sqlDb = new SqlDatabase(connectionString, mockProvider.Object); sqlDb.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region"); mockProvider.Verify(); }
public override void CreateRole(string roleName) { SqlDatabase sqlDatabase = new SqlDatabase(_connectionString); DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.NlayerSP_RegistrarRol"); sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName); sqlDatabase.AddInParameter(dbCommand, "Nombre", DbType.String, roleName); sqlDatabase.AddInParameter(dbCommand, "Activo", DbType.Boolean, true); sqlDatabase.ExecuteNonQuery(dbCommand); }
public static void UpdateContact(Contact contact) { string sqlQuery = "UPDATE Contact SET FirstName=@FirstName,LastName=@LastName,Email=@Email,Phone=@Phone WHERE ContactID=" + contact.ContactID; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "FirstName", DbType.String, contact.FirstName); db.AddInParameter(dbCommand, "LastName", DbType.String, contact.LastName); db.AddInParameter(dbCommand, "Email", DbType.String, contact.Email); db.AddInParameter(dbCommand, "Phone", DbType.String, contact.Phone); db.ExecuteNonQuery(dbCommand); }
public static void UpdateTerritory(Territory territory) { string sqlQuery = "UPDATE Territory SET ParentTerritoryID=@ParentTerritoryID, FullDescription=@FullDescription, Name=@Name WHERE TerritoryID=" + territory.TerritoryID; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "ParentTerritoryID", DbType.Int32, territory.ParentTerritoryID); db.AddInParameter(dbCommand, "FullDescription", DbType.String, territory.FullDescription); db.AddInParameter(dbCommand, "Name", DbType.String, territory.Name); db.ExecuteNonQuery(dbCommand); }
public int ObtenerRowCount(string conexion, string procedimiento, List<DbParameter> parametros) { var bd = new SqlDatabase(conexion); DbCommand cmd = bd.GetStoredProcCommand(procedimiento); EstablecerParametros(parametros, bd, cmd); int filasAfectadas = bd.ExecuteNonQuery(cmd); ObtenerParametrosOut(parametros, cmd); return filasAfectadas; }
public static bool unlockAccount(string requestedUser_ID) { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand insertCmmnd = new SqlCommand("Update EMPLOYEES Set Access_Status = 'T' WHERE User_ID = @User_ID "); insertCmmnd.CommandType = CommandType.Text; insertCmmnd.Parameters.AddWithValue("@User_ID", requestedUser_ID); int rowsAffected = travelMSysDB.ExecuteNonQuery(insertCmmnd); Console.Write("rowsAffected " + rowsAffected); if (rowsAffected == 1) return true; return false; }
public void CommandExecutedEventBroadcast() { DateTime commandStartedTime = DateTime.MinValue; var mockProvider = new Mock<IDataInstrumentationProvider>(); mockProvider.Setup(p => p.FireCommandExecutedEvent(It.IsAny<DateTime>())) .Callback<DateTime>(d => commandStartedTime = d) .Verifiable(); var sqlDb = new SqlDatabase(connectionString, mockProvider.Object); sqlDb.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region"); mockProvider.Verify(); AssertDateIsWithinBounds(DateTime.Now, commandStartedTime, 2); }
public static void UpdateAddress(Address address) { string sqlQuery = "UPDATE Address SET CountryID=@CountryID, CityID=@CityID, Street=@Street, ZipCode=@ZipCode, HouseNr=@HouseNr, ApartmentNr=@AparmentNr WHERE AddressID=" + address.AddressID; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "CountryID", DbType.Int32, address.CountryID); db.AddInParameter(dbCommand, "CityID", DbType.String, address.City.CityID); db.AddInParameter(dbCommand, "Street", DbType.String, address.Street); db.AddInParameter(dbCommand, "ZipCode", DbType.String, address.ZipCode); db.AddInParameter(dbCommand, "HouseNr", DbType.String, address.HouseNr); db.AddInParameter(dbCommand, "ApartmentNr", DbType.String, address.ApartmentNr); db.AddInParameter(dbCommand, "AddressID", DbType.Int32, address.AddressID); db.ExecuteNonQuery(dbCommand); }
public void ConnectionFailedEventBroadcast() { var mockProvider = new Mock<IDataInstrumentationProvider>(); mockProvider.Setup(p => p.FireConnectionFailedEvent("invalid;", It.IsAny<Exception>())) .Verifiable(); var sqlDb = new SqlDatabase("invalid;", mockProvider.Object); try { sqlDb.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region"); } catch (ArgumentException) {} mockProvider.Verify(); }
public override int DeleteProfiles(ProfileInfoCollection profiles) { XElement perfilesXml = new XElement("Perfiles"); foreach (ProfileInfo profileInfo in profiles) { perfilesXml.Add(new XElement("Perfil", new XAttribute("Login", profileInfo.UserName))); } SqlDatabase sqlDatabase = new SqlDatabase(_connectionString); DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.NlayerSP_EliminarPerfil"); sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName); sqlDatabase.AddInParameter(dbCommand, "Perfiles", DbType.Xml, perfilesXml.ToString()); sqlDatabase.ExecuteNonQuery(dbCommand); return profiles.Count; }
public static bool ApproveRejDAL(string TReq_ID, char AorRej) { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand insertCmmnd = new SqlCommand("UPDATE TRAVEL_REQUESTS SET Request_Status=@AorRej WHERE Travel_Request_ID=@TReq_ID"); insertCmmnd.CommandType = CommandType.Text; string x=AorRej.ToString(); insertCmmnd.Parameters.AddWithValue("@AorRej",x); insertCmmnd.Parameters.AddWithValue("@TReq_ID", TReq_ID); int rowsAffected = travelMSysDB.ExecuteNonQuery(insertCmmnd); //int rowsAffected = travelMSysDB.ExecuteNonQuery(new SqlCommand("UPDATE TRAVEL_REQUESTS SET Request_Status='R' WHERE Travel_Request_ID='R445'")); Console.Write("rowsAffected " + rowsAffected); if (rowsAffected == 1) return true; return false; }
public static bool NewClaimRequest(ClaimRequestsModel claimData) { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand insertCmmnd = new SqlCommand("INSERT INTO CLAIM_REQUESTS ([Travel_Request_ID],[Claim_Amount],[Settled_Amount],[Emp_Remarks]) VALUES (@Travel_Request_ID,@Claim_Amount,@Settled_Amount,@Remarks)"); insertCmmnd.CommandType = CommandType.Text; insertCmmnd.Parameters.AddWithValue("@Travel_Request_ID", claimData.Travel_Request_ID); insertCmmnd.Parameters.AddWithValue("@Claim_Amount", claimData.Claim_Amount); insertCmmnd.Parameters.AddWithValue("@Settled_Amount", 0); insertCmmnd.Parameters.AddWithValue("@Remarks", claimData.Remarks); int rowsAffected = travelMSysDB.ExecuteNonQuery(insertCmmnd); Console.Write("rowsAffected " + rowsAffected); if (rowsAffected == 1) return true; return false; }
public static bool addAgent(AgentModel model) { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand insertCmmnd = new SqlCommand("INSERT INTO AGENTS(Agent_ID,Password,Agent_Name,Creator_Admin_ID,Phone_Num,Address) VALUES (@Agent_ID,@Password,@Agent_Name,@Creator_Admin_ID,@Phone_Num,@Address)"); insertCmmnd.CommandType = CommandType.Text; insertCmmnd.Parameters.AddWithValue("@Agent_ID", model.Agent_ID); insertCmmnd.Parameters.AddWithValue("@Password", "12d5ba628f5af19b9c8d5ccfe9283430"); //insertCmmnd.Parameters.AddWithValue("@Access_Status",model.Access_Status); insertCmmnd.Parameters.AddWithValue("@Agent_Name", model.Agent_Name); insertCmmnd.Parameters.AddWithValue("@Creator_Admin_ID", model.Creator_Admin_ID); insertCmmnd.Parameters.AddWithValue("@Phone_Num", model.Phone_Num); insertCmmnd.Parameters.AddWithValue("@Address", model.Address); int rowsAffected = travelMSysDB.ExecuteNonQuery(insertCmmnd); Console.Write("rowsAffected " + rowsAffected); if (rowsAffected == 1) return true; return false; }
/// <summary> /// Método que Ejecuta un Procedimiento Almacenado de Inserción, Eliminación o Modificación /// </summary> /// <typeparam name="T"></typeparam> /// <param name="LstEntidad">Entidad de Tipo Lista que se interpretará como parametro de Entrada del SP</param> /// <param name="NombreTabla">Nombre de la Tabla a Insertar</param> /// <param name="Procedimiento">Nombre del Procedimiento</param> /// <returns></returns> public static Resultado <T> EjecutarProcedimientoOperacional <T>(List <T> LstEntidad, string NombreTabla, string Procedimiento) { var ObjResultado = new Resultado <T>(); try { SqlDatabase db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(ConfigBase.ConexionSQL); DbCommand dbCommand = db.GetStoredProcCommand(Procedimiento); db.AddInParameter(dbCommand, "@" + NombreTabla, SqlDbType.Structured, ListToDataTable <T>(LstEntidad)); //db.SetParametros(dbCommand, Entidad); db.ExecuteNonQuery(dbCommand); return(ObjResultado); } catch (Exception Ex) { DacLog.Registrar(Ex, Procedimiento); ObjResultado.ResultadoGeneral = false; ObjResultado.Mensaje = Ex.Message; return(ObjResultado); } }
public int UpdateMailer(int Id) { int status = 0; try { Database objDB = new SqlDatabase(connectionStr); DbCommand objAdd = new SqlCommand(); objAdd.CommandType = CommandType.StoredProcedure; objAdd.CommandText = "UpdateClientbyId"; objDB.AddInParameter(objAdd, "@Id", DbType.Int32, Id); objDB.AddOutParameter(objAdd, "@Status", DbType.Int16, 16); objDB.ExecuteNonQuery(objAdd); status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Status")); return status; } catch (Exception ex) { throw ex; return status; } }