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