DataTable CallStoreProcedure(Job job, DataTable filterTable, IdpeKey connectionStringKey) { DatabaseTypes databaseType = connectionStringKey.GetDatabaseType(); string actualConnectionString = connectionStringKey.Value; DataTable table = new DataTable(); string xmlInputData = ToXml(filterTable); IDbConnection con = null; IDal myDal = new DataAccessLayer(databaseType).Instance; try { con = myDal.CreateConnection(actualConnectionString); StoredProcedure sp = new StoredProcedure(myDal); sp.ConnectionString = actualConnectionString; IDbDataParameter dp1 = sp.CreateParameter(); dp1.ParameterName = "@dataSourceId"; dp1.DbType = DbType.Int16; dp1.Value = job.DataSource.Id; IDbDataParameter dp2 = sp.CreateParameter(); dp2.ParameterName = "@inputData"; dp2.DbType = DbType.Xml; dp2.Value = xmlInputData; sp.CacheDisconnectData = true; sp.Name = "SreDuplicateCheck"; sp.AddParameter(dp1); sp.AddParameter(dp2); con.Open(); IDataReader reader = sp.CreateDataReader(); table.Load(reader); } catch (Exception ex) { string errorMessage = "Error while checking duplicate (database communication)! " + Environment.NewLine + ex.Message; AddErrorMessage(job, errorMessage); } finally { if (con != null) { if (con.State != System.Data.ConnectionState.Closed) con.Close(); } con.Close(); con.Dispose(); } return table; }
public static async Task <Account> GetFromToken(string token) { using (var p_User_GetByToken = new StoredProcedure("p_User_GetByToken")) { p_User_GetByToken.AddParameter("sToken", token, 32); using (var reader = await p_User_GetByToken.RunReader()) { if (reader.HasRows) { while (await reader.ReadAsync()) { return(new Account { ID = reader.GetInt32(0), Email = reader.GetString(1), Password = reader.GetString(2), Role = (AccountRole)reader.GetInt32(3) }); } } } } return(null); }
internal static StoredProcedure GenerateDocumentCode(string transactionType) { //TODO : XML config for SP name StoredProcedure sp = new StoredProcedure("GenerateDocumentCode"); sp.AddParameter("@TransactionType", StoredProcedure.ParameterType.String, 30, transactionType); return sp; }
internal static void Login(string userName, string password, out int userNo, out bool blocked, out bool canLogin) { using (var spo = new StoredProcedure("usp_User_loginGame", DB.GetDatabaseClient(DatabaseType.Account).mConnection)) { spo.AddParameter("userID", userName, 20); spo.AddParameter("userPW", password, 32); spo.AddOutput <int>("userNo"); spo.AddOutput <byte>("authID"); spo.AddOutput <int>("block"); spo.AddOutput <int>("isLoginable"); spo.Run(); userNo = spo.GetOutput <int>("userNo"); blocked = spo.GetOutput <int>("block") == 1; canLogin = spo.GetOutput <int>("isLoginable") == 1; } }
public static string GetUserName(string user) { string userName; StoredProcedure sp; sp = new StoredProcedure("SP_GetUserName"); sp.AddParameter("app_user", user, SqlDbType.VarChar, 30); userName = sp.GetStringValue(); return userName; }
public static async Task <string> Login(string email, string password) { using (var p_User_Login = new StoredProcedure("p_User_Login")) { p_User_Login.AddParameter("sEmail", email, 50); p_User_Login.AddParameter("sPassword", password, 32); using (var reader = await p_User_Login.RunReader()) { if (reader.HasRows) { while (await reader.ReadAsync()) { // TODO: Also return role. return(reader.GetString(0)); } } } } return(null); }
public Dictionary <string, string> GenerateParameters(int applicationId) { Dictionary <string, string> returnParams = new Dictionary <string, string>(); IDbConnection con = null; IDal myDal = new DataAccessLayer(Information.EyediaCoreConfigurationSection.Database.DatabaseType).Instance; try { //this will be called using default connectionstring of the application con = myDal.CreateConnection(_ConnectionString); StoredProcedure sp = new StoredProcedure(myDal); sp.ConnectionString = _ConnectionString; IDbDataParameter dp1 = sp.CreateParameter(); dp1.ParameterName = "@applicationId"; dp1.DbType = DbType.Int16; dp1.Value = applicationId; sp.CacheDisconnectData = true; sp.Name = "SRE_GENERATE_PARAMETERS"; sp.AddParameter(dp1); con.Open(); IDataReader reader = sp.CreateDataReader(); reader.Read(); for (int i = 0; i < reader.FieldCount; i++) { returnParams.Add(reader.GetName(i), reader.GetValue(i).ToString()); } } catch (Exception ex) { Trace.TraceError("Error while generating parameters " + Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace); } finally { if (con != null) { if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } con.Close(); con.Dispose(); } return(returnParams); }
public static async Task <bool> Exists(string desc) { using (var p_Proposal_Exists = new StoredProcedure("p_Proposal_Exists")) { p_Proposal_Exists.AddParameter("sDescription", desc); using (var reader = await p_Proposal_Exists.RunReader()) { if (reader.HasRows) { return(true); } } } return(false); }
public static bool IsValidUser(string user, string pwd) { string md5Pwd; string md5DbPwd; StoredProcedure spPwd; StoredProcedure spUser; bool existUser; if (user == null) { throw new ArgumentNullException("key"); } if (user.Length == 0) { throw new ArgumentException("\"user\" cannot be an empty string", "user"); } if (pwd == null) { throw new ArgumentNullException("key"); } if (pwd.Length == 0) { throw new ArgumentException("\"pwd\" cannot be an empty string", "pwd"); } spUser = new StoredProcedure("SP_ExistUser"); spUser.AddParameter("user", user, SqlDbType.VarChar, 30); existUser = spUser.GetBooleanValue(); if (!existUser) { return false; } spPwd = new StoredProcedure("SP_GetPwd"); spPwd.AddParameter("user", user, SqlDbType.VarChar, 30); md5DbPwd = spPwd.GetStringValue(); md5Pwd = Cryptography.MD5(pwd); if (md5Pwd == md5DbPwd) { return true; } else { return false; } }
private void ProcessProcedure(string proc, IProcedureRepository procedureRepo) { var procedure = new StoredProcedure <SqlParameter>(_schema, proc); using (var command = Connection.CreateCommand()) { var sb = new StringBuilder(Resources.SQL_PARAMETER_QUERY); sb = sb.Replace("@Catalog", _catalog); sb = sb.Replace("@Schema", _schema); sb = sb.Replace("@ProcName", proc); command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; var reader = command.ExecuteReader(); while (reader.Read()) { procedure.AddParameter(CreateParameter(reader)); } reader.Dispose(); } procedureRepo.Add(new ProcedureKey(_schema, proc), procedure); }
public static async Task <bool> Create(CreateProposalModel model) { using (var p_Proposal_Create = new StoredProcedure("p_Proposal_Create")) { p_Proposal_Create.AddParameter("sStudyFields", string.Join(';', model.Study)); p_Proposal_Create.AddParameter("sDescription", model.Description); p_Proposal_Create.AddParameter("sToolsUsed", string.Join(';', model.Environment)); p_Proposal_Create.AddParameter("sToolsInformation", model.TechnicalDescription); p_Proposal_Create.AddParameter("sNecessities", model.ExtraRequirements); p_Proposal_Create.AddParameter("sTheme", model.Theme); p_Proposal_Create.AddParameter("sActivities", string.Join(';', model.Activities)); p_Proposal_Create.AddParameter("nRequiredStudents", model.AmountOfStudents); p_Proposal_Create.AddParameter("sPreferedStudents", string.Join(';', model.Names)); p_Proposal_Create.AddParameter("sOptionalComment", model.Remarks); p_Proposal_Create.AddParameter("sPeriods", string.Join(';', model.Period)); p_Proposal_Create.AddOutput <int>("nID"); p_Proposal_Create.Run(); return(true); // F**k checking temporarily. //return (await p_Proposal_Create.Run()).GetOutput<int>("nID") != 0; } }