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!"); } }