# GeneralSQL

**Repository Path**: sharpsoft/GeneralSQL

## Basic Information

- **Project Name**: GeneralSQL
- **Description**: 轻量数据库访问库
- **Primary Language**: C#
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 3
- **Forks**: 2
- **Created**: 2018-06-29
- **Last Updated**: 2021-11-03

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# GeneralSQL


#### 项目介绍
轻量数据库访问库
<p>已支持MySql、MsSql、Sqlite</p>
<p>
对基本结构化语法分析有兴趣的可以看看。</p>
<p>GSQL语法规则与MySql基本一致</p>
解析GSQL-DOM：

```
string sql=@"SELECT a.`Id` as AID,b.`Id` as BID FROM TableA as a
LEFT JOIN TableB as b ON a.Id=b.Id+1
WHERE a.Id>2
ORDER BY a.Name;

INSERT INTO TableA(Id,Name,`Time`)VALUES(1,'王二麻子',NOW());
";
GSQLAnalyzer analyzer=new GSQLAnalyzer(sql);
var statements=analyzer.ReadStatements();
```


数据访问层实例：

```
 public class AccountsDAL : DAL_Base
    {
        public AccountsDAL(Database p_db, int p_userid) : base(p_db, p_userid)
        {
        }
        public override void CreateSchema()
        {
            var stream1 = getResFile("ds-accountmanager.txt");
            using (StreamReader sr = new StreamReader(stream1, Encoding.UTF8))
            {
                string gsql = sr.ReadToEnd();
                db.ExecuteNonQuery(new GSQLCommandScript(gsql));
            }
        }
        #region Roles


        public JArray GetRoles()
        {
            string sql = $"SELECT * FROM `Roles`;";
            var array = db.ExecuteJArray(sql);
            return array;
        }
        
        public JObject GetRole(int rid)
        {
            string sql = $"SELECT * FROM `Roles` WHERE `Id`=={rid};";
            var role = db.ExecuteJObject(sql);
            return role;
        }

        public int InsertRole(string rolename)
        {
            string sql = "INSERT INTO `Roles`(`Name`)VALUES(@Name);SELECT LASTID();";
            var rid = db.ExecuteInt32(sql, new { Name = rolename });
            return rid;
        }

        public bool RoleNameExists(string rname)
        {
            string sql = "SELECT COUNT(`Id`) FROM `Roles` WHERE `Name`==@Name";
            var count = db.ExecuteInt32(sql, new { Name = rname });
            return count > 0;
        }

        public void UpdateRoleName(int rid, string rolename)
        {
            string sql = "UPDATE `Roles` SET `Name`=@Name WHERE `Id`=@Id;";
            db.ExecuteNonQuery(sql, new { Id = rid, Name = rolename });
        }

        public void DeleteRole(int roleid)
        {
            string sql = $"SELECT COUNT(`Id`) FROM `Users` WHERE `RoleId`=={roleid}";
            var count = db.ExecuteInt32(sql);
            if (count > 0)
            {//角色有被使用
                throw new Exception("角色有指定给用户，无法删除。");
            }
            string sqldel = $"DELETE FROM `Roles` WHERE `Id`=={roleid};";
            db.ExecuteNonQuery(sqldel);
        }

        #endregion

        #region Users

        public JArray GetUsers()
        {
            string sql = $"SELECT `Id`,`Name`,`RoleId`,`Status`,`CreateTime` FROM `Users`;";
            var array = db.ExecuteJArray(sql);
            return array;
        }
        public JArray GetUsers(int roleid)
        {
            string sql = $"SELECT `Id`,`Name`,`RoleId`,`Status`,`CreateTime` FROM `Users` WHERE `RoleId`=={roleid};";
            var array = db.ExecuteJArray(sql);
            return array;
        }

        public JObject GetUser(int userid)
        {
            var sql = 
$@"SELECT `Users`.`Id`,`Users`.`Name`,`Users`.`RoleId`,`Roles`.`Name` `RoleName`,`Users`.`Status`,`Users`.`CreateTime` FROM `Users`
LEFT JOIN `Roles` ON `Roles`.`Id`==`Users`.`RoleId`
WHERE `Users`.`Id`=={userid};";
            var user = db.ExecuteJObject(sql);
            return user;
        }
        /// <summary>
        /// 检查用户名是否已存在
        /// </summary>
        /// <param name="uname"></param>
        /// <returns></returns>
        public bool UserNameExists(string uname)
        {
            string sql = "SELECT COUNT(`Id`) FROM `Users` WHERE `Name`==@Name";
            var count = db.ExecuteInt32(sql, new { Name = uname });
            return count > 0;
        }

        public int InsertUser(string name, string pwd_md5, int roleid)
        {
            if (UserNameExists(name))
            {
                throw new Exception($"username\"{name}\" has exists.");
            }
            string sql = "INSERT INTO `Users`(`Name`,`Password`,`RoleId`)VALUES(@Name,@Password,@RoleId);SELECT LASTID();";
            int uid = db.ExecuteInt32(sql, new { Name = name, Password = pwd_md5, RoleId = roleid });
            return uid;
        }
        public void DeleteUser(int userid)
        {
            string sql = $"DELETE FROM `Users` WHERE `Id`=={userid};";
            db.ExecuteNonQuery(sql);
        }

        public void UpdateUserStatus(int uid, UserStatus userStatus)
        {
            string sql = $"UPDATE `Users` SET `Status`={(int)userStatus} WHERE `Id`={uid}";
            db.ExecuteNonQuery(sql);

        }
        public void UpdateUserPassword(int uid, string pwd_md5)
        {
            string sql = $"UPDATE `Users` SET `Password`=@Password WHERE `Id`={uid}";
            db.ExecuteNonQuery(sql, new { Password = pwd_md5 });

        }
        /// <summary>
        /// 匹配用户名密码
        /// </summary>
        /// <param name="uname">用户名</param>
        /// <param name="pwd_md5">密码</param>
        /// <returns>反回用户Id</returns>
        public int? MatchPassword(string uname, string pwd_md5)
        {
            string sql = "SELECT `Id` FROM `Users` WHERE `Name`==@Name & `Password`==@Password;";
            var userid = db.ExecuteScalar<int?>(sql, new { Name = uname, Password = pwd_md5 });
            return userid;
        }


        #endregion

        #region Permissions

        public JArray GetPermissions(int roleid)
        {
            string sql = $"SELECT * FROM `Permissions` WHERE `RoleId`=={roleid};";
            var array = db.ExecuteJArray(sql);
            return array;
        }
        /// <summary>
        /// 设置角色的权限
        /// </summary>
        /// <param name="roleid"></param>
        /// <param name="module"></param>
        /// <param name="controller"></param>
        /// <param name="action"></param>
        /// <param name="enabled"></param>
        public void SetPermission(int roleid, string module, string controller, string action, bool enabled)
        {
            var obj = new { RoleId = roleid, Module = module, Controller = controller, Action = action, Enabled = enabled };
            string sqlcount = "SELECT COUNT(*) FROM `Permissions` WHERE `Module`==@Module & `Controller`==@Controller & `Action`==@Action & `RoleId`=@RoleId;";
            var count = db.ExecuteInt32(sqlcount, obj);
            if (count == 0)
            {
                string sqlinsert = "INSERT INTO `Permissions`(`Module`, `Controller`, `Action`,`RoleId`,`Enabled`)VALUES(@Module,@Controller,@Action,@RoleId,@Enabled);";
                db.ExecuteNonQuery(sqlinsert, obj);
            }
            else
            {
                string sqlupdate = "UPDATE `Permissions` SET `Enabled`=@Enabled WHERE `Module`==@Module & `Controller`==@Controller & `Action`==@Action & `RoleId`=@RoleId;";
                db.ExecuteNonQuery(sqlupdate, obj);
            }
        }

        #endregion

    }
```