public static IDataReader GetIDataReader(string connectionString, string sqlQuery) { SqlDatabase sqlServerDB = new SqlDatabase(connectionString); DbCommand cmd = sqlServerDB.GetSqlStringCommand(sqlQuery); //return an IDataReader. return sqlServerDB.ExecuteReader(cmd); }
public static IDataReader Departments() { SqlDatabase db = new SqlDatabase(connString); DbCommand command = db.GetSqlStringCommand("SELECT DeptId, Name FROM AllDepartments D WHERE D.Active=1 AND D.NormBillsTime=1 ORDER BY Name"); command.CommandType = CommandType.Text; return db.ExecuteReader(command); }
public IDataReader ToDataReader(string storedProcedureName, Dictionary<string, string> parameters) { using (SqlCommand cmd = new SqlCommand(storedProcedureName)) { cmd.CommandType = CommandType.StoredProcedure; foreach (string key in parameters.Keys) { SqlParameter parameter = new SqlParameter(key, parameters[key]); if (parameter.Value == null) parameter.Value = DBNull.Value; if (parameter.SqlDbType == SqlDbType.DateTime && parameter.Value is DateTime && (DateTime)parameter.Value == default(DateTime)) parameter.Value = SqlDateTime.MinValue.Value; cmd.Parameters.Add(parameter); } SqlDatabase database = new SqlDatabase(ConfigurationManager.ConnectionStrings["MyAppsLocal"].ConnectionString); try { IDataReader reader = database.ExecuteReader(cmd); return reader; } catch (Exception e) { Console.WriteLine(e.ToString()); return null; } } }
public override CustomerList GetCustomer(string vipCode) { SqlDatabase database = new SqlDatabase(ConnectionString); DbCommand command = database.GetStoredProcCommand("rmSP_WSPOS_GetCustomer"); command.CommandTimeout = 300; database.AddInParameter(command, "@VipCode", DbType.String, vipCode); List<Customer> customers = new List<Customer>(); using (IDataReader reader = database.ExecuteReader(command)) { while (reader.Read()) { Customer customer = new Customer(); customer.CustomerId = Convert.ToInt32(reader["VIPCode_id"]); customer.CustomerCode = reader["VipCode"] as string; customer.FirstName = reader["VIPGName"] as string; customer.LastName = reader["VIPName"] as string; customer.Telephone = reader["VIPTel"] as string; if (reader["VIPBDay"] != DBNull.Value) customer.BirthDate = Convert.ToDateTime(reader["VIPBDay"]); customers.Add(customer); } } CustomerList customerList = new CustomerList(); customerList.Customers = customers; customerList.TotalCount = customers.Count; return customerList; }
public static UserAuthResult Authenticate(string userName, string password, string providerKey) { string Auth_GetUserByCredentials = @"SELECT u.ID,u.Name,u.Surname,u.Email,u.Password,u.About,u.BirthDate,u.DateCreated,u.LastLogin,u.DateUpdated,ul.LoginProvider FROM User AS u INNER JOIN UserLogin AS ul ON u.ID = ul.UserID WHERE u.Email = '{1}' WHERE ul.Providerkey = '{1}'"; string connStr = ConfigurationManager.AppSettings["MasterSQLConnection"]; SqlDatabase db = new SqlDatabase(connStr); UserAuthResult result = new UserAuthResult(); result.AuthSuccess = false; User user = new User(); string dbPassword = string.Empty; try { string query = String.Format(Auth_GetUserByCredentials, userName); using (DbCommand command = db.GetSqlStringCommand(query)) { using (IDataReader reader = db.ExecuteReader(command)) { if (reader.Read()) { //Users.ID,Users.Name,Surname,IsAdmin,IsSuperAdmin,LoginType,Users.ActiveDirectoryDomain,Password user.ID = int.Parse(reader["ID"].ToString()); user.Password = reader["Password"].ToString(); user.Name = reader["Name"].ToString(); user.Surname = reader["Surname"].ToString(); } else { result.AuthSuccess = false; result.ErrorMsg = "Username or password is wrong"; } } } } finally { } if (!string.IsNullOrEmpty(password) && user.ID > 0 && password.Equals(user.Password)) { result.User = user; result.AuthSuccess = true; } else { result.ErrorMsg = "Username or password is wrong"; } return result; }
public static IDataReader Employees(int deptId) { SqlDatabase db = new SqlDatabase(connString); DbCommand command = db.GetSqlStringCommand("SELECT FirstName + ' ' + LastName AS FullName, UserId FROM AllocableUsers WHERE DeptId=" + deptId + " AND Active=1 ORDER BY FullName"); command.CommandType = CommandType.Text; return db.ExecuteReader(command); }
/// <summary> /// Retrieves a rule from the database /// </summary> /// <param name="Name">The name of the rule</param> /// <returns>An AuthorizationRuleData object</returns> public AuthorizationRuleData GetRule(string name) { AuthorizationRuleData rule = null; DbCommand cmd = dbRules.GetStoredProcCommand("dbo.GetRuleByName"); dbRules.AddInParameter(cmd, "Name", DbType.String, name); using (IDataReader reader = dbRules.ExecuteReader(cmd)) { if (reader.Read()) { rule = GetRuleFromReader(reader); } } return(rule); }
public static IDataReader Deparments(int clientId) { SqlDatabase db = new SqlDatabase( connString ); DbCommand command = db.GetStoredProcCommand( "ALOC__DepartmentSelectList" ); db.AddInParameter( command, "@ClientId", DbType.Int32, clientId ); command.CommandType = CommandType.StoredProcedure; return db.ExecuteReader( command ); }
public static IDataReader lockedAccounts() { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand queryCmmnd = new SqlCommand("SELECT * FROM EMPLOYEES WHERE Access_Status = 'F'"); queryCmmnd.CommandType = CommandType.Text; return travelMSysDB.ExecuteReader(queryCmmnd); }
public static IDataReader JobsAssignedTo(int clientId, int userId) { SqlDatabase db = new SqlDatabase(connString); DbCommand command = db.GetStoredProcCommand("ALOC_JobsAssignedTo"); command.CommandType = CommandType.StoredProcedure; db.AddInParameter(command, "@ClientId", DbType.Int32, clientId); db.AddInParameter(command, "@UserId", DbType.Int32, userId); return db.ExecuteReader(command); }
public static IDataReader AllJobs(int clientId) { SqlDatabase db = new SqlDatabase(connString); DbCommand command = db.GetSqlStringCommand(@" SELECT JobId, Name FROM AllOpenJobs J WHERE Active=1 AND ClientId=@clientId ORDER BY Name"); db.AddInParameter(command, "@clientId", DbType.Int32, clientId); return db.ExecuteReader(command); }
public static IDataReader ViewClaimRequests() { SqlDatabase travelMSysDB = new SqlDatabase(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\TravelMS_Sep16.mdf;Integrated Security=True"); SqlCommand reqListCmmnd = new SqlCommand("SELECT [Claim_ID],[Travel_Request_ID],[Claim_Amount],[Settled_Amount],[Emp_Remarks],[Admin_Remarks],[Claim_Status],[Admin_ID] FROM CLAIM_REQUESTS WHERE Travel_Request_ID IN (SELECT Travel_Request_ID FROM TRAVEL_REQUESTS WHERE Emp_ID = (SELECT Emp_ID FROM EMPLOYEES WHERE User_ID = @CurUser_ID))"); reqListCmmnd.Parameters.AddWithValue("@CurUser_ID", WebSecurity.CurrentUserName); return travelMSysDB.ExecuteReader(reqListCmmnd); }
public static IDataReader Clients( string type, int userId ) { SqlDatabase db = new SqlDatabase( connString ); DbCommand command = db.GetStoredProcCommand( "ALOC__ClientSelectList" ); command.CommandType = CommandType.StoredProcedure; db.AddInParameter( command, "@Type", DbType.String, type ); db.AddInParameter( command, "@UserId", DbType.Int32, userId ); return db.ExecuteReader( command ); }
public static IDataReader populateTravelRequests(string currUserID) { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand queryCmmnd = new SqlCommand("SELECT Travel_Request_ID FROM TRAVEL_REQUESTS WHERE Emp_ID = (SELECT Emp_ID FROM EMPLOYEES WHERE User_ID = @User_ID)"); queryCmmnd.CommandType = CommandType.Text; queryCmmnd.Parameters.AddWithValue("@User_ID", currUserID); return travelMSysDB.ExecuteReader(queryCmmnd); }
private static bool SelectUser(ref User user) { try { SqlDatabase db = new SqlDatabase(Properties.Settings.Default.connString); using (DbCommand cmd = db.GetStoredProcCommand("SelectUser")) { db.AddInParameter(cmd, "authcode", DbType.String, user.AuthCode); using (IDataReader dr = db.ExecuteReader(cmd)) { bool first = true; while (dr.Read()) { if (first) { user.Age = dr["age"] as string; user.AuthCode = dr["authcode"] as string; user.Gender = dr["gender"] as string; first = false; } int? tid = dr["testid"] as int?; if (tid != null) { Test t = null; if (user.Tests.ContainsKey(tid.Value)) t = user.Tests[tid.Value]; else { t = new Test() { ID = tid.Value, TimeEst = (int)dr["timeest"], MaxArraySize = (int)dr["maxarraysize"], DelayPeriod = (int)dr["delayperiod"] }; user.Tests.Add(tid.Value, t); } t.ImageArrays.Add(new ImageArray() { Index = (int)dr["index"], ImagesDisplayed = (int)dr["imagesdisplayed"], UserInput = (int)dr["userinput"], ImageFile = (string)dr["imagefile"] }); } } } return true; } } catch { return false; } }
public static IDataReader User_IDCheckDAL(string requestedUser_ID) { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand queryCmmnd = new SqlCommand("SELECT User_ID FROM EMPLOYEES WHERE User_ID=@User_ID"); queryCmmnd.CommandType = CommandType.Text; queryCmmnd.Parameters.AddWithValue("@User_ID", requestedUser_ID); return travelMSysDB.ExecuteReader(queryCmmnd); }
private IDataReader User_IDCheckDAL(string userName) { SqlDatabase travelMSysDB = new SqlDatabase(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\TravelMS_Sep16.mdf;Integrated Security=True");//new SqlDatabase(System.Configuration.ConfigurationManager.ConnectionStrings["TravelMSConnString"].ConnectionString); SqlCommand queryCmmnd = new SqlCommand("SELECT User_ID FROM EMPLOYEES WHERE User_ID=@User_ID"); queryCmmnd.CommandType = CommandType.Text; queryCmmnd.Parameters.AddWithValue("@User_ID", userName); IDataReader resReader = travelMSysDB.ExecuteReader(queryCmmnd); return resReader; }
public IDataReader ObtenerDataReader(string conexion, string procedimiento, List<DbParameter> parametros) { var bd = new SqlDatabase(conexion); DbCommand cmd = bd.GetStoredProcCommand(procedimiento); EstablecerParametros(parametros, bd, cmd); IDataReader resultado = bd.ExecuteReader(cmd); ObtenerParametrosOut(parametros, cmd); return resultado; }
private static Role GetRoleFromQuery(string sqlQuery) { Role role = null; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { while (dataReader.Read()) { role = GetRoleFromReader(dataReader); } } return role; }
private static City GetCityFromQuery(string sqlQuery) { City city = null; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { while (dataReader.Read()) { city = GetCityFromReader(dataReader); } } return city; }
private static Address GetAddressFromQuery(string sqlQuery) { Address address = null; Database db = new SqlDatabase(DBHelper.GetConnectionString()); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { while (dataReader.Read()) { address = GetAddressFromReader(dataReader); } } return address; }
public static List<NewTravelRequestModel> GetAgentRequestList() { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand reqListCmmnd = new SqlCommand("SELECT [Travel_Request_ID],[Emp_ID],[Trip_Name],[Travel_Type_Purpose],[Travel_Date],[Mode_of_Travel],[Travel_Class],[Source_City],[Destination_City],[Travel_Time_hh],[Travel_Time_mm],[First_Level_Approver],[Agent_ID],[Request_Status],[Acco_Status],[Remarks] FROM TRAVEL_REQUESTS WHERE Agent_ID = @CurAgent_ID AND (REQUEST_STATUS='A' OR REQUEST_STATUS='P')"); reqListCmmnd.Parameters.AddWithValue("@CurAgent_ID", WebSecurity.CurrentUserName); IDataReader nReader = travelMSysDB.ExecuteReader(reqListCmmnd); List<Models.NewTravelRequestModel> rList = new List<Models.NewTravelRequestModel>(); /*Object[] values = new Object[16]; nReader.GetValues(values);*/ while (nReader.Read()) { //rList.Add(nReader.Get ); rList.Add(new Models.NewTravelRequestModel { Travel_Request_ID = (string)nReader[0], Emp_ID = Int32.Parse((string)nReader[1]), Trip_Name = (string)nReader[2], Travel_Type_Purpose = (string)nReader[3], Travel_Date = (DateTime)nReader[4], Mode_of_Travel = (string)nReader[5], Travel_Class = (string)nReader[6], Source_City = (string)nReader[7], Destination_City = (string)nReader[8], Travel_Time_hh = (int)nReader[9], Travel_Time_mm = (int)nReader[10], First_Level_Approver = (string)nReader[11], Agent_ID = (string)nReader[12], Request_Status = StatusDetail.TravelRequestStatus((string)nReader[13]), Acco_Status =StatusDetail.AccoStatus( (string)nReader[14]), Remarks = (string)nReader[15] }); } nReader.Close(); return rList; }
//System.Web.Mvc vs. System.Web.WebPages.Html stackoverflow public static IEnumerable<SelectListItem> AgentListDAL() { SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString); SqlCommand reqListCmmnd = new SqlCommand("SELECT [Agent_ID],[Agent_Name] FROM AGENTS"); IDataReader nReader = travelMSysDB.ExecuteReader(reqListCmmnd); List<SelectListItem> rList = new List<SelectListItem>(); while (nReader.Read()) { rList.Add(new SelectListItem { Text = (string)nReader[1], Value=(string)nReader[0] }); } nReader.Close(); return (IEnumerable < SelectListItem > )rList; }
public static int[] Update( int employeeId, int jobId, int weekNum, int allocationId, int mins, int doNotAssign) { int[] retval = new int[] { -1, -1 }; SqlDatabase db = new SqlDatabase( connString ); DbCommand command = db.GetStoredProcCommand( "ALOC__AllocationUpdate" ); command.CommandType = CommandType.StoredProcedure; db.AddInParameter( command, "@EmployeeId", DbType.Int32, employeeId ); db.AddInParameter( command, "@JobId", DbType.Int32, jobId ); db.AddInParameter( command, "@WeekNum", DbType.Int32, weekNum ); db.AddInParameter(command, "@DoNotAssign", DbType.Int32, doNotAssign); //db.AddInParameter( command, "@AllocationId", DbType.Int32, allocationId ); db.AddInParameter( command, "@Mins", DbType.Int32, mins ); //object val = db.ExecuteScalar(command); IDataReader rr = db.ExecuteReader(command); //if (rr.NextResult()) if (rr.Read()) { retval[0] = Convert.ToInt32(rr["AllocId"].ToString()); retval[1] = Convert.ToInt32(rr["NoteLength"].ToString()); } command.Dispose(); return retval; }
private List<string> GetTables() { string q = @"SELECT table_schema+'.'+table_name as tn FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'"; var res= new List<string>(); var db =new SqlDatabase(_cs); using (var r = db.ExecuteReader(CommandType.Text, q)) { while (r.Read()) { res.Add(r.GetString(0)); } } return res; }
public override SiteMapNode BuildSiteMap() { lock (this) { if (!IsInitialized) { throw new Exception("El proveedor de SiteMap no ha sido inicializado."); } // TODO: _nodoRaiz == null if (true) { Clear(); SqlDatabase sqlDatabase = new SqlDatabase(_connectionString); DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.SCISP_ObtenerSiteMap"); sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName); using (IDataReader dataReader = sqlDatabase.ExecuteReader(dbCommand)) { dataReader.Read(); int siteMapId = dataReader.IsDBNull(0) ? 0 : dataReader.GetInt32(0); string titulo = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1); string descripcion = dataReader.IsDBNull(2) ? string.Empty : dataReader.GetString(2); string roles = dataReader.IsDBNull(4) ? "NULL" : dataReader.GetString(4); string url = dataReader.IsDBNull(3) ? null : dataReader.GetString(3); string[] rolList = roles.Split(','); _nodoRaiz = new SiteMapNode(this, siteMapId.ToString(CultureInfo.InvariantCulture), url, titulo, descripcion, rolList, null, null, null); _nodos.Add(siteMapId, _nodoRaiz); AddNode(_nodoRaiz); while (dataReader.Read()) { siteMapId = dataReader.GetInt32(0); titulo = dataReader.IsDBNull(1) ? string.Empty : dataReader.GetString(1); descripcion = dataReader.IsDBNull(2) ? string.Empty : dataReader.GetString(2); url = dataReader.IsDBNull(3) ? null : dataReader.GetString(3); roles = dataReader.IsDBNull(4) ? "NULL" : dataReader.GetString(4); int padreId = dataReader.IsDBNull(5) ? 0 : dataReader.GetInt32(5); rolList = roles.Split(','); SiteMapNode nodoHijo = new SiteMapNode(this, siteMapId.ToString(CultureInfo.InvariantCulture), url, titulo, descripcion, rolList, null, null, null); _nodos.Add(siteMapId, nodoHijo); if (_nodos.ContainsKey(padreId)) { AddNode(nodoHijo, _nodos[padreId]); } else { AddNode(nodoHijo); } } } } return _nodoRaiz; } }
public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords) { SqlDatabase sqlDatabase = new SqlDatabase(_connectionString); DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.SCISP_ObtenerUsuariosPorEmail"); sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName); sqlDatabase.AddInParameter(dbCommand, "Email", DbType.String, emailToMatch); sqlDatabase.AddInParameter(dbCommand, "Inicio", DbType.Int32, pageIndex); sqlDatabase.AddInParameter(dbCommand, "Cantidad", DbType.Int32, pageSize); MembershipUserCollection users = new MembershipUserCollection(); using (IDataReader dataReader = sqlDatabase.ExecuteReader(dbCommand)) { dataReader.Read(); totalRecords = dataReader.GetInt32(0); dataReader.NextResult(); while (dataReader.Read()) { users.Add(GetUserFromDataReader(dataReader)); } } return users; }
private static void ExecuteReader(SqlDatabase database, DbCommand command, MethodType type) { switch (type) { case MethodType.One: { using (var reader = database.ExecuteReader(command)) { // Process the results } return; } case MethodType.Two: { using (var reader = database.ExecuteReader(CommandType.Text, "SELECT * FROM Employees")) { // Process the results } return; } case MethodType.Three: { using (var reader = database.ExecuteReader("SalesByCategory", "Beverages", "1998")) { while (reader.Read()) { Console.WriteLine("Product name: {0}, Total purchase: {1}", reader[reader.GetOrdinal("ProductName")], reader[reader.GetOrdinal("TotalPurchase")]); } } return; } default: throw new NotSupportedException(); } }
/// <summary> /// Executes the reader. /// </summary> /// <param name="commandType">Type of the command.</param> /// <param name="commandText">The command text.</param> /// <returns></returns> public override IDataReader ExecuteReader(CommandType commandType, string commandText) { SqlDatabase database = new SqlDatabase(this._connectionString); return database.ExecuteReader(commandType, commandText); }
/// <summary> /// Executes the reader. /// </summary> /// <param name="commandWrapper">The command wrapper.</param> /// <returns></returns> public override IDataReader ExecuteReader(DbCommand commandWrapper) { SqlDatabase database = new SqlDatabase(this._connectionString); return database.ExecuteReader(commandWrapper); }
/// <summary> /// Executes the reader. /// </summary> /// <param name="storedProcedureName">Name of the stored procedure.</param> /// <param name="parameterValues">The parameter values.</param> /// <returns></returns> public override IDataReader ExecuteReader(string storedProcedureName, params object[] parameterValues) { SqlDatabase database = new SqlDatabase(this._connectionString); return database.ExecuteReader(storedProcedureName, parameterValues); }