internal static DataTable AdapterFill(string sprocName)
{
DataTable dataTable = new DataTable();
try
{
using (SqlConnection con = new SqlConnection(_Connection))
{
SqlCommand cmd = new SqlCommand(sprocName, con);
cmd.CommandType = CommandType.StoredProcedure;
for (int index = 0; index < _ParamName.Count; index++)
{
cmd.Parameters.AddWithValue(_ParamName[index], _ParamValue[index]);
}
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dataTable);
}
}
}
catch (Exception ex)
{
DBCommands.RecordError(ex);
}
finally
{
EmptyParams();
}
return(dataTable);
}
internal static bool ExecuteNonQuery(string sprocName)
{
try
{
using (SqlConnection con = new SqlConnection(_Connection))
{
SqlCommand cmd = new SqlCommand(sprocName, con);
cmd.CommandType = CommandType.StoredProcedure;
for (int index = 0; index < _ParamName.Count; index++)
{
cmd.Parameters.AddWithValue(_ParamName[index], _ParamValue[index]);
}
con.Open();
cmd.ExecuteNonQuery();
return(true);
}
}
catch (Exception ex)
{
DBCommands.RecordError(ex);
return(false);
}
finally
{
EmptyParams();
}
}
public override bool UserInfoUpdate(UserInfo user)
{
DBCommands.PopulateParams("@UserName", user.UserName);
DBCommands.PopulateParams("@ProfileImage", user.ProfileImage);
DBCommands.PopulateParams("@RecoverCode", user.RecoverCode);
DBCommands.PopulateParams("@GroupUsers", MapGroupListToTable(user.GroupUsers));
return(DBCommands.ExecuteNonQuery("p_UserInfo_Update"));
}
public override UserInfo UserInfoGetByUser(string userName)
{
DBCommands.PopulateParams("@UserName", userName);
DataTable userData = DBCommands.AdapterFill("p_UserInfo_GetByUser");
UserInfo user = new UserInfo();
foreach (DataRow row in userData.Rows)
{
user.UserName = row["UserName"].ToString();
user.Email = row["Email"].ToString();
user.ProfileImage = (row["ProfileImage"] != DBNull.Value) ? row["ProfileImage"].ToString() : null;
}
return(user);
}
public override UserInfo UserInfoGetByCode(string code)
{
DBCommands.PopulateParams("@code", code);
DataTable userTable = DBCommands.AdapterFill("p_UserInfo_GetByCode");
UserInfo user = null;
foreach (DataRow row in userTable.Rows)
{
user = new UserInfo();
user.UserInfoID = Convert.ToInt32(row["UserInfoID"]);
user.UserName = row["UserName"].ToString();
user.Email = (row["Email"] != DBNull.Value) ? row["Email"].ToString() : null;
user.ProfileImage = (row["ProfileImage"] != DBNull.Value) ? row["ProfileImage"].ToString() : null;
user.RecoverCode = code;
}
return(user);
}
internal static string ExecuteScalar(string sprocName)
{
try
{
using (SqlConnection con = new SqlConnection(_Connection))
{
SqlCommand cmd = new SqlCommand(sprocName, con);
cmd.CommandType = CommandType.StoredProcedure;
for (int index = 0; index < _ParamName.Count; index++)
{
cmd.Parameters.AddWithValue(_ParamName[index], _ParamValue[index]);
}
con.Open();
var result = cmd.ExecuteScalar();
if (result != null)
{
return(result.ToString());
}
else
{
return(null);
}
}
}
catch (Exception ex)
{
DBCommands.RecordError(ex);
return(null);
}
finally
{
EmptyParams();
}
}
public override DataTable UserGroupsGetActive()
{
return(DBCommands.AdapterFill("p_UserGroup_GetActive"));
}
public override DataTable GroupUsersGetByUserName(string userName)
{
DBCommands.PopulateParams("@UserName", userName);
return(DBCommands.AdapterFill("p_GroupUsers_GetByUserName"));
}
public override DataTable UserInfoGetAll()
{
return(DBCommands.AdapterFill("p_UserInfo_GetAll"));
}