public int CreateTable(string tableName, int qtyTable)
{
var properties = tmpTable.GetType().GetProperties();
string query = string.Empty;
string columns = string.Empty;
int result = 0;
for (int i = 1; i <= qtyTable; i++)
{
string name = tableName + i.ToString("0000");
columns = string.Format("[{0}] [int] IDENTITY(1,1) Primary Key not null ,\n", name + properties[0].Name);
for (int j = 1; j < properties.Length; j++)
{
columns += string.Format("[{0}] [nvarchar](max) null,\n", name + properties[j].Name);
}
query = string.Format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", name);
mESSQL.OpenConnect();
try
{
string existTableName = mESSQL.DbCommand(query).ExecuteScalar().ToString();
qtyTable++;
}
catch
{
query = string.Format("CREATE TABLE {0} ({1})", name, columns);
query.Remove(query.Length - 1, 1);
result += mESSQL.DbCommand(query).ExecuteNonQuery();
}
mESSQL.CloseConnect();
}
return(result);
}
/// <summary>
/// Get list all user
/// </summary>
/// <returns></returns>
public IEnumerable <UserDetail> GetAll()
{
Users = new List <UserDetail>();
string query = "SELECT * FROM dbo.UserDetail";
mESSQL.OpenConnect();
IDataReader reader = mESSQL.DbCommand(query).ExecuteReader();
while (reader.Read())
{
Users.Add(new UserDetail()
{
user_id = (int)reader["user_id"],
user_cd = reader["user_cd"].ToString(),
user_name = reader["user_name"].ToString(),
user_position = reader["user_position"].ToString(),
user_dept = reader["user_dept"].ToString(),
user_email = reader["user_email"].ToString(),
user_tel = reader["user_tel"].ToString(),
factory_cd = reader["factory_cd"].ToString(),
start_date = (DateTime)reader["start_date"],
reg_date = (DateTime)reader["reg_date"]
});
}
reader.Close();
mESSQL.CloseConnect();
return(Users);
}
// GET: All user
public IHttpActionResult Get()
{
string query = "SELECT * FROM [mesdb].[dbo].[m_mes_user]";
IList <m_mes_user> users = new List <m_mes_user>();
IDbConnection dbConnection = mESSQL.DbContext();
dbConnection.Open();
IDbCommand dbCommand = mESSQL.DbCommand(query, dbConnection);
IDataReader reader = dbCommand.ExecuteReader();
while (reader.Read())
{
users.Add(new m_mes_user
{
user_id = (int)reader["user_id"],
user_cd = reader["user_cd"].ToString(),
user_name = reader["user_name"].ToString(),
user_email = reader["user_email"].ToString(),
user_phone = reader["user_phone"].ToString(),
user_password = reader["user_password"].ToString(),
user_is_active = (bool)reader["user_is_active"],
user_is_online = (bool)reader["user_is_online"],
reg_date = (DateTime)reader["reg_date"],
});
}
reader.Close();
dbConnection.Close();
if (users.Count == 0)
{
return(NotFound());
}
return(Ok(users));
}
public UserLog Get(UserLog user)
{
try
{
string query = "SELECT userlog_id, user_cd, user_active, user_online, last_ipadd, last_login, reg_date ";
query += string.Format("FROM dbo.UserLog WHERE user_cd = '{0}' AND user_password = '******'",
user.user_cd, user.user_password);
mESSQL.OpenConnect();
IDataReader reader = mESSQL.DbCommand(query).ExecuteReader();
reader.Read();
user = new UserLog()
{
userlog_id = (int)reader["userlog_id"],
user_cd = reader["user_cd"].ToString(),
user_active = (bool)reader["user_active"],
user_online = (bool)reader["user_online"],
last_ipadd = reader["last_ipadd"].ToString(),
last_login = (DateTime)reader["last_login"],
reg_date = (DateTime)reader["reg_date"]
};
reader.Close();
List <string> userRoles = new List <string>();
query = "SELECT [control_cd] FROM [dbo].[MesControl] WHERE[parent_cd] in ";
query += "(SELECT[control_cd] FROM[dbo].[ControlRoles] WHERE[role_cd] in ";
query += string.Format("(SELECT[role_cd] FROM[dbo].[UserRoles] WHERE[user_cd] = '{0}'))", user.user_cd);
reader = mESSQL.DbCommand(query).ExecuteReader();
while (reader.Read())
{
userRoles.Add(reader["control_cd"].ToString());
}
reader.Close();
mESSQL.CloseConnect();
user.userRoles = string.Join(",", userRoles.ToArray());
}
catch (Exception ex)
{
System.Diagnostics.Debug.Print(ex.Message);
throw new Exception("Invalid user or password!");
}
if (user.user_active)
{
return(user);
}
else
{
throw new Exception("This user hasn't been actived!");
}
}
/// <summary>
/// Create new user user log
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public UserLog Create(UserLog user)
{
if (user == null)
{
throw new ArgumentNullException("user");
}
string query = "INSERT INTO dbo.UserLog ";
query += string.Format("(user_cd, user_password) VALUES ('{0}','{1}')", user.user_cd, user.user_password);
mESSQL.OpenConnect();
int result = mESSQL.DbCommand(query).ExecuteNonQuery();
mESSQL.CloseConnect();
if (result > 0)
{
return(user);
}
else
{
throw new Exception("Can't create new user!");
}
}
public ControlRoles Create(ControlRoles item)
{
if (item == null)
{
throw new ArgumentNullException("item");
}
string query = "INSERT INTO dbo.ControlRoles ";
query += "(control_cd, role_cd, reg_user) VALUES ";
query += string.Format("('{0}','{1}','{2}')", item.control_cd, item.role_cd, item.reg_user);
mESSQL.OpenConnect();
int result = mESSQL.DbCommand(query).ExecuteNonQuery();
mESSQL.CloseConnect();
if (result > 0)
{
return(item);
}
else
{
throw new Exception("Can't create new role!");
}
}
public MesFactory Create(MesFactory item)
{
if (item == null)
{
throw new ArgumentNullException("item");
}
string query = "INSERT INTO dbo.MesFactory ";
query += "(factory_cd, factory_name, reg_user) VALUES ";
query += string.Format("('{0}','{1}','{2}')", item.factory_cd, item.factory_name, item.reg_user);
mESSQL.OpenConnect();
int result = mESSQL.DbCommand(query).ExecuteNonQuery();
mESSQL.CloseConnect();
if (result > 0)
{
return(item);
}
else
{
throw new Exception("Can't create new factory!");
}
}
public MesControl Create(MesControl item)
{
if (item == null)
{
throw new ArgumentNullException("item");
}
string query = "INSERT INTO dbo.MesControl ";
query += "(control_cd, control_name, parent_cd) VALUES ";
query += string.Format("('{0}','{1}','{2}')", item.control_cd, item.control_name, item.parent_cd);
mESSQL.OpenConnect();
int result = mESSQL.DbCommand(query).ExecuteNonQuery();
mESSQL.CloseConnect();
if (result > 0)
{
return(item);
}
else
{
throw new Exception("Can't create new control!");
}
}
