public void createMember(Member enMember)
{
string sqlSentence = "";
try
{
sqlSentence += "INSERT INTO members (realName, displayName, email, password, language, isActive, createdAt) ";
sqlSentence += "VALUES (@realName, @displayName, @email, @password, @language, @isActive, @createdAt) ";
List <SqlParameter> sqlParameters = new List <SqlParameter>();
sqlParameters.Add(new SqlParameter("@realName", enMember.realName));
sqlParameters.Add(new SqlParameter("@displayName", enMember.displayName));
sqlParameters.Add(new SqlParameter("@email", enMember.email));
sqlParameters.Add(new SqlParameter("@password", enMember.password));
sqlParameters.Add(new SqlParameter("@language", enMember.language));
sqlParameters.Add(new SqlParameter("@isActive", true));
sqlParameters.Add(new SqlParameter("@createdAt", DateTime.Now.ToUniversalTime().ToString("yyyyMMdd HH:mm:ss")));
dbWrapper.Insert(sqlSentence, sqlParameters);
}
catch (Exception ex)
{
throw ex;
}
}
private static void RunSQLServer()
{
using (var dal = new DBWrapper("DBConnection"))
{
try
{
var user = new User
{
FirstName = "First",
LastName = "Last",
Dob = DateTime.Now.AddDays(-3000),
IsActive = true
};
int count = dal.GetScalarValue <int>("DAH_User_Scalar", CommandType.StoredProcedure);
Console.WriteLine("current total " + count.ToString());
AddLog("insert user with trans ---");
dal.BeginTransaction();
AddLog("begin trans ---");
dal.CreateParameter("@FirstName", 50, user.FirstName, DbType.String);
dal.CreateParameter("@LastName", user.LastName, DbType.String);
dal.CreateParameter("@Dob", user.Dob, DbType.DateTime);
dal.CreateParameter("@IsActive", 50, user.IsActive, DbType.Boolean);
int newId;
dal.Insert("DAH_User_Insert", CommandType.StoredProcedure, out newId);
dal.Commit();
AddLog("done commit ---");
AddLog("new user id " + newId.ToString());
dal.ClearParameters();
int count_1 = dal.GetScalarValue <int>("DAH_User_Scalar", CommandType.StoredProcedure);
AddLog("current total " + count_1.ToString());
dal.ClearParameters();
dal.CreateParameter("@Id", 12, DbType.Int32);
using (var reader = dal.GetDataReader("DAH_User_GetById", CommandType.StoredProcedure))
{
while (reader.Read())
{
AddLog(string.Format("user id 12 : firstname {0}", reader["FirstName"].ToString()));
}
}
}
catch (Exception ex)
{
try //https://docs.microsoft.com/en-us/archive/blogs/dataaccesstechnologies/zombie-check-on-transaction-error-this-sqltransaction-has-completed-it-is-no-longer-usable
{
dal.Rollback();
}
catch (Exception)
{
throw;
}
}
}
}
public int createContact(Contact enContact)
{
string sqlSentence = "";
try
{
sqlSentence += "INSERT INTO contacts (memberId, firstName, lastName, genre, email, mobileNumber, landlineNumber, isActive, picExtension, comments, createdAt) ";
sqlSentence += "VALUES (@memberId, @firstName, @lastName, @genre, @email, @mobileNumber, @landlineNumber, @isActive, @picExtension, @comments, @createdAt) ";
List <SqlParameter> sqlParameters = new List <SqlParameter>();
sqlParameters.Add(new SqlParameter("@memberId", memberInfo.id));
sqlParameters.Add(new SqlParameter("@firstName", enContact.firstName));
sqlParameters.Add(new SqlParameter("@lastName", enContact.lastName));
sqlParameters.Add(new SqlParameter("@genre", enContact.genre.ToUpper()));
sqlParameters.Add(new SqlParameter("@email", enContact.email));
sqlParameters.Add(new SqlParameter("@mobileNumber", enContact.mobileNumber));
sqlParameters.Add(new SqlParameter("@landlineNumber", enContact.landlineNumber));
sqlParameters.Add(new SqlParameter("@isActive", true));
sqlParameters.Add(new SqlParameter("@createdAt", DateTime.Now.ToUniversalTime().ToString("yyyyMMdd HH:mm:ss")));
if (enContact.picExtension == string.Empty)
{
sqlParameters.Add(new SqlParameter("@picExtension", DBNull.Value));
}
else
{
sqlParameters.Add(new SqlParameter("@picExtension", enContact.picExtension));
}
if (enContact.comments == string.Empty)
{
sqlParameters.Add(new SqlParameter("@comments", DBNull.Value));
}
else
{
sqlParameters.Add(new SqlParameter("@comments", enContact.comments));
}
return(dbWrapper.Insert(sqlSentence, sqlParameters));
}
catch (Exception ex)
{
throw ex;
}
}
