public void RetrieveAll(Data_AppUserFile.SortType sort, d1_Data_AppUserFile d, IMyLog log)
{
string strCmdFmt = "SELECT * FROM {0} ORDER BY {1}";
string strCmd = "";
switch (sort)
{
case Data_AppUserFile.SortType.Date:
strCmd = string.Format(strCmdFmt, TABLE_NAME, "[CreationDateUtc]"); break;
case Data_AppUserFile.SortType.State:
strCmd = string.Format(strCmdFmt, TABLE_NAME, "[AccountStatus]"); break;
case Data_AppUserFile.SortType.Email:
strCmd = string.Format(strCmdFmt, TABLE_NAME, "[Email]"); break;
default: throw new NotSupportedException("Unknown sort - " + sort.ToString());
}
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
while (s.Reader.Read())
{
Data_AppUserFile r1 = readOneRecord(s.Reader);
d(r1);
}
}
}
private void lAny(IMyLog log, QuestionOption it)
{
Console.Clear();
string select = "SELECT [Date], [Level], [Logger], [Message] FROM [dbo].[Log] ";
string where = String.Format("WHERE [Logger] like '{0}'", it.OptionText);
if (!String.IsNullOrEmpty(limitQueryForDate))
{
where += limitQueryForDate;
}
string order = "ORDER BY [Date] ";
string cmd = select + where + order;
// String.Format(@"
//SELECT [Date], [Level], [Logger], [Message] FROM [dbo].[Log]
//WHERE [Logger] like '{0}'
//ORDER BY [Date]
//", it.OptionText);
using (SqlDisposable s = new SqlDisposable(SQLDBConfig.DBToUse.LogDB, cmd))
{
while (s.Reader.Read())
{
DateTime Date = (DateTime)s.Reader["Date"];
Date = Date.AddHours(4);//mg to get date in brasil
String Level = (String)s.Reader["Level"];
String Logger = (String)s.Reader["Logger"];
String Message = (String)s.Reader["Message"];
Console.WriteLine(String.Format("{0:dd/MM HH:mm} {1} {2} {3}",
Date, Level, Logger, Message));
}
}
}
public bool HasAccount(string email, IMyLog log)
{
String cmd = String.Format("SELECT * FROM {0} WHERE {1} like '{2}'",
TABLE_NAME,
"[Email]",
email);
using (SqlDisposable s = new SqlDisposable(Db, cmd))
{
return(s.Reader.HasRows);
}
}
public Data_AppUserFile RetrieveOne(string email, IMyLog log)
{
Data_AppUserFile ret = null;
var strCmd = String.Format("SELECT * FROM {0} WHERE [Email] like '{1}'", TABLE_NAME, email);
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.Read())
{
ret = readOneRecord(s.Reader);
}
}
return(ret);
}
public void UpdateStatus(string email, Data_AppUserFile.eUserStatus newStatus, IMyLog log)
{
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[AccountStatus]", newStatus.ToIntString());
string strCmd = cmd.GetSql_Update(string.Format("WHERE [Email] like '{0}'", email));
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("UpdateAll: *** unexpected RecordsAffected != 1");
}
}
}
public void UpdateCounters_Queued(string email, long timeNow, int howMany, IMyLog log)
{
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[LastMsgQueuedUtc]", SqlExtensions.SqlDateUtcNow().ToString());
cmd.AddInc("[NoOfQueuedMessages]", howMany);
cmd.AddDec("[RemainingMessages]", howMany);
cmd.Add("[AccountStatus]", String.Format("CASE WHEN {0}={1} THEN {2} ELSE {0} END",
"[AccountStatus]",
Data_AppUserFile.eUserStatus.verified_welcome_No_sent.ToIntString(),
Data_AppUserFile.eUserStatus.verified_welcome_queued.ToIntString()));
string strCmd = cmd.GetSql_Update(string.Format("WHERE [Email] like '{0}'", email));
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("StoreNew: *** unexpected RecordsAffected != 1");
}
}
}
public void UpdateAll(
string email,
string userName,
string password,
string creationIp,
string telNumbers,
Data_AppUserFile.eUserStatus status,
Int32 minDelayInSeconds,
Int64 noOfQueuedMessages,
Int64 noOfSentMessages,
Int64 noOfBytesSent,
Int64 remainingMsg,
IMyLog log)
{
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[UserName]", userName.Quote());
cmd.Add("[Password]", password.Quote());
cmd.Add("[CreationIp]", creationIp.Quote());
cmd.Add("[MobileNumbers]", telNumbers.Quote());
cmd.Add("[AccountStatus]", status.ToIntString());
cmd.Add("[MinDelayInSeconds]", minDelayInSeconds.ToString());
cmd.Add("[NoOfQueuedMessages]", noOfQueuedMessages.ToString());
cmd.Add("[NoOfSentMessages]", noOfSentMessages.ToString());
cmd.Add("[NoOfBytesSent]", noOfBytesSent.ToString());
cmd.Add("[RemainingMessages]", remainingMsg.ToString());
string strCmd = cmd.GetSql_Update(string.Format("WHERE [Email] like '{0}'", email));
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("UpdateAll: *** unexpected RecordsAffected != 1");
}
}
}
public void StoreNew(Data_AppUserFile data, out bool fileArleadyUsed, IMyLog log)
{
fileArleadyUsed = false;
if (HasAccount(data.Email, log))
{
fileArleadyUsed = true;
return;
}
SqlCmdBuilder cmd = new SqlCmdBuilder(TABLE_NAME);
cmd.Add("[UserName]", data.UserName.Quote());
cmd.Add("[MobileNumbers]", data.MoblieAllNumbers.Quote());
cmd.Add("[Email]", data.Email.Quote());
cmd.Add("[Password]", data.Password.Quote());
cmd.Add("[CreationDateUtc]", data.CreationDate.SqlDate().ToString());
cmd.Add("[CreationIp]", data.CreationIp.Quote());
cmd.Add("[ApiGuId]", data.ApiGuId.Quote());
cmd.Add("[NoOfQueuedMessages]", data.NoOfQueuedMessages.ToString());
cmd.Add("[NoOfSentMessages]", data.NoOfSentMessages.ToString());
cmd.Add("[NoOfBytesSent]", data.NoOfBytesSent.ToString());
cmd.Add("[RemainingMessages]", data.RemainingMessages.ToString());
cmd.Add("[MinDelayInSeconds]", data.MinDelayInSeconds.ToString());
cmd.Add("[AccountStatus]", data.AccountStatus.ToIntString());
string strCmd = cmd.GetSql_InsertSelect();
//System.Diagnostics.Debug.WriteLine(strCmd);//mg remove
using (SqlDisposable s = new SqlDisposable(Db, strCmd))
{
if (s.Reader.RecordsAffected != 1)
{
log.Error("StoreNew: *** unexpected RecordsAffected != 1");
}
}
}
