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