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