public static bool CheckUserExists(string username)
{
bool exists = false;
ProcessDBEvent dbEvent = delegate(ref SqlTransaction transaction)
{
using (SqlCommand command = DBCONN.CreateCommand())
{
//command.Parameters.Clear();
command.Transaction = transaction;
//command.CommandText = GenerateSelectExistsCommand("dbo.tblRegister", "CName");
command.CommandText = DBCommands.get_register_params_withCName;
command.Parameters.Add(new SqlParameter("CName", username));
exists = (int)command.ExecuteScalar() > 0;
if (exists)
{
//Debug.Log("User already exists.");
//DebugLog.Log("User already exists.");
}
else
{
//Debug.Log("User does not exist.");
//DebugLog.Log("User does not exist.");
}
}
return(exists);
};
ProcessDB(dbEvent);
return(exists);
}
public static bool CheckPostalExists(string code)
{
bool exists = false;
ProcessDBEvent dbEvent = delegate(ref SqlTransaction transaction)
{
using (SqlCommand command = DBCONN.CreateCommand())
{
command.Parameters.Clear();
command.Transaction = transaction;
command.CommandText = GenerateSelectExistsCommand("dbo.tblPostalcode", "code");
command.Parameters.Add(new SqlParameter("code", code));
exists = (int)command.ExecuteScalar() > 0;
if (exists)
{
//Debug.Log("Postal code exists.");
//DebugLog.Log("Postal code exists.");
}
else
{
//Debug.Log("Postal code does not exist.");
//DebugLog.Log("Postal code does not exist.");
}
}
return(exists);
};
ProcessDB(dbEvent);
return(exists);
}
public static LocationData[] GetAllPostalCodeData()
{
List <LocationData> data = new List <LocationData>();
ProcessDBEvent dbEvent = delegate(ref SqlTransaction transaction)
{
using (SqlCommand command = DBCONN.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = DBCommands.get_all_postalcode_params;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
LocationData ld = new LocationData();
ReadPostalCodeData(reader, ref ld);
data.Add(ld);
}
}
}
return(true);
};
ProcessDB(dbEvent);
return(data.ToArray());
}
public static bool InsertSalesInfo(SalesInfoData infoData)
{
ProcessDBEvent dbEvent = delegate(ref SqlTransaction transaction)
{
using (SqlCommand command = DBCONN.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = DBCommands.insert_salesinfo_params;
command.Parameters.AddWithValue("SName", infoData.SName); // string
command.Parameters.AddWithValue("PostalCod", infoData.PostalCod); // decimal
command.Parameters.AddWithValue("LoginTime", infoData.LoginTime); // DateTime
command.Parameters.AddWithValue("PhotoCopierModel", infoData.PhotoCopierModel); // string
command.Parameters.AddWithValue("DemoDuration", infoData.DemoDuration); // DateTime
command.Parameters.AddWithValue("Frequency", infoData.Frequency); // int
bool result = false;
try
{
result = (command.ExecuteNonQuery() != 0);
}
catch (SqlException ex)
{
//Debug.Log(ex.Message);
}
catch (Exception ex)
{
//Debug.Log(ex.Message);
}
return(result);
}
};
return(ProcessDB(dbEvent));
}
public static RegistrationData GetRegistrationData(int id)
{
RegistrationData data = new RegistrationData();
ProcessDBEvent dbEvent = delegate(ref SqlTransaction transaction)
{
using (SqlCommand command = DBCONN.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = DBCommands.get_register_params_withCID;
command.Parameters.Add(new SqlParameter("CID", id));
using (SqlDataReader reader = command.ExecuteReader())
{
//while (reader.Read())
//{
// data = new RegistrationData();
// ReadRegistrationData(reader, ref data);
// break;
//}
// New implementation
if (!reader.Read())
{
throw new InvalidOperationException("No records were returend");
}
}
}
return(false);
};
ProcessDB(dbEvent);
return(data);
}
static bool ProcessDB(ProcessDBEvent dbEvent)
{
Initialize();
bool result = false;
try
{
SqlTransaction transaction = DBCONN.BeginTransaction(IsolationLevel.Serializable);
if (dbEvent(ref transaction))
{
//Debug.Log("Committing db transaction...");
transaction.Commit();
result = true;
}
else
{
//Debug.Log("Db transaction failed: Rolling back...");
transaction.Rollback();
}
}
catch (SqlException e)
{
//Debug.Log(e.Message);
}
catch (Exception e)
{
//Debug.Log(e.Message);
}
Uninitialize();
return(result);
}
public static RegistrationData GetRegistrationData(string name)
{
RegistrationData data = new RegistrationData();
ProcessDBEvent dbEvent = delegate(ref SqlTransaction transaction)
{
using (SqlCommand command = DBCONN.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = DBCommands.get_register_params_withCName;
command.Parameters.Add(new SqlParameter("CName", name));
using (SqlDataReader reader = command.ExecuteReader())
{
//while (reader.Read())
//{
// data = new RegistrationData();
// ReadRegistrationData(reader, ref data);
// Debug.Log(string.Format("ReadData > {0} {1}", data.CName, data.CPwd));
// break;
//}
int ordName = reader.GetOrdinal("CName");
int ordPwd = reader.GetOrdinal("CPwd");
//Debug.Log(string.Format("ordName: {0}, ordPwd: {1}", ordName, ordPwd));
if (!reader.Read())
{
throw new InvalidOperationException("No records were returend");
}
//ReadRegistrationData(reader, ref data);
data.CName = reader.GetString(ordName);
data.CPwd = reader.GetString(ordPwd);
//Debug.Log(string.Format("ReadData > {0} {1}", data.CName, data.CPwd));
if (reader.Read())
{
throw new InvalidOperationException("Multiple records were returned");
}
}
}
return(false);
};
ProcessDB(dbEvent);
return(data);
}
public static bool CreateUser(RegistrationData data)
{
int n = 0;
ProcessDBEvent dbEvent = delegate(ref SqlTransaction transaction)
{
using (SqlCommand command = DBCONN.CreateCommand())
{
command.Transaction = transaction;
command.CommandType = CommandType.Text;
command.CommandText = DBCommands.insert_register_params;
command.Parameters.AddWithValue("CName", data.CName);
command.Parameters.AddWithValue("Company", data.Company);
command.Parameters.AddWithValue("CPwd", data.CPwd);
command.Parameters.AddWithValue("Email", data.Email);
try
{
n = Convert.ToInt32(command.ExecuteScalar());
}
catch (SqlException ex)
{
//Debug.Log(ex.Message);
}
catch (Exception ex)
{
//Debug.Log(ex.Message);
}
//Debug.Log(n == 0 ? "Registration failed: Unable to insert row" : "Registration success: " + n);
// Commit transaction
transaction.Commit();
transaction = DBCONN.BeginTransaction(IsolationLevel.Serializable);
}
return(n != 0);
};
return(ProcessDB(dbEvent));
}
