예제 #1
0
        private int ExecuteSqlTran2(DBConnect connect, List <SqlObject> sqlobj)
        {
            switch (connect.Type)
            {
            case "sqlserver":
            {
                var dbHelperMySql = new DbHelperSqlServer(connect.ConnectString);
                return(dbHelperMySql.ExecuteSqlTran2(sqlobj.Select(
                                                         m => new SqlItem <SqlParameter>(m.Sql, m.Parameters.Select(
                                                                                             p => new SqlParameter(p.Name, SqlDbType.NVarChar)
                    {
                        Value = p.Value
                    }
                                                                                             ).ToArray())
                                                         ).ToList()));
            }
            break;

            default:
            {
                var dbHelperMySql = new DbHelperMySql(connect.ConnectString);
                return(dbHelperMySql.ExecuteSqlTran2(sqlobj.Select(
                                                         m => new SqlItem(m.Sql, m.Parameters.Select(
                                                                              p => new MySqlParameter(p.Name, MySqlDbType.VarChar)
                    {
                        Value = p.Value
                    }
                                                                              ).ToArray())
                                                         ).ToList()));
            }
            break;
            }
            return(0);
        }
예제 #2
0
        /// <summary>
        /// 获取数据库表列表
        /// </summary>
        /// <returns></returns>
        public ResponseMessage Tables(long ProjectID, int connectid, string databasename)
        {
            // 判断是否有权限
            var userproject = _UserManage.GetUserProject(_TenantManage.user.ID, ProjectID);

            if (userproject == null)
            {
                return(new ResponseMessage(MessageResult.Error, "项目不存在"));
            }
            var connect = GetConnectList(ProjectID).FirstOrDefault(m => m.ID == connectid);

            if (connect == null)
            {
                return(new ResponseMessage(MessageResult.Error, "连接不存在"));
                //return Content(Result(1, "连接不存在", null).ToString());
            }

            var list = new List <string>();

            switch (connect.Type)
            {
            case "sqlserver":
            {
                var connectstring = Regex.Replace(connect.ConnectString, @"initial catalog=([^;]*)", "initial catalog=" + databasename);
                DbHelperSqlServer dbHelperMySql = new DbHelperSqlServer(connectstring);
                var sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name;";
                var ds  = dbHelperMySql.Query(sql);
                foreach (DataRow _row in ds.Tables[0].Rows)
                {
                    list.Add(_row["name"].ToString());
                }
            }
            break;

            default:
            {
                var sql = string.Format(
                    "select table_name from information_schema.tables where table_schema='{0}' and table_type='base table';", databasename);
                DbHelperMySql dbHelperMySql = new DbHelperMySql(connect.ConnectString);
                var           ds            = dbHelperMySql.Query(sql);
                foreach (DataRow _row in ds.Tables[0].Rows)
                {
                    list.Add(_row["table_name"].ToString());
                }
            }
            break;
            }


            return(new ResponseMessage(MessageResult.Success, "成功", list));
            //return Content(JsonConvert.SerializeObject(list));
            // return Json(list, JsonRequestBehavior.AllowGet);
        }
예제 #3
0
        ///// <summary>
        ///// 获取数据库列表
        ///// </summary>
        ///// <param name="connectid"></param>
        ///// <returns></returns>
        //public ResponseMessage Connects(long ProjectID)
        //{
        //    var connect = GetConnectList(ProjectID);
        //    return new ResponseMessage(MessageResult.Success, "成功");
        //}

        /// <summary>
        /// 获取数据库列表
        /// </summary>
        /// <param name="connectid"></param>
        /// <returns></returns>
        public ResponseMessage Databases(long ProjectID, int connectid)
        {
            // 判断是否有权限
            var userproject = _UserManage.GetUserProject(_TenantManage.user.ID, ProjectID);

            if (userproject == null)
            {
                return(new ResponseMessage(MessageResult.Error, "项目不存在"));
            }
            var connect = GetConnectList(ProjectID).FirstOrDefault(m => m.ID == connectid);

            if (connect == null)
            {
                return(new ResponseMessage(MessageResult.Error, "连接不存在"));
            }
            var list = new List <string>();

            switch (connect.Type)
            {
            case "sqlserver":
            {
                DbHelperSqlServer dbHelperMySql = new DbHelperSqlServer(connect.ConnectString);
                var sql = "Select Name FROM Master.dbo.SysDatabases orDER BY Name  ;";
                var ds  = dbHelperMySql.Query(sql);
                foreach (DataRow _row in ds.Tables[0].Rows)
                {
                    list.Add(_row[0].ToString());
                }
            }
            break;

            default:
            {
                DbHelperMySql dbHelperMySql = new DbHelperMySql(connect.ConnectString);
                var           sql           = "show databases;";
                var           ds            = dbHelperMySql.Query(sql);
                foreach (DataRow _row in ds.Tables[0].Rows)
                {
                    list.Add(_row[0].ToString());
                }
            }
            break;
            }

            return(new ResponseMessage(MessageResult.Success, "成功", list));
        }
예제 #4
0
        private DataSet Query(DBConnect connect, string sql, List <Parameter> Parameters)
        {
            switch (connect.Type)
            {
            case "sqlserver":
            {
                var dbHelperMySql = new DbHelperSqlServer(connect.ConnectString);
                return(dbHelperMySql.Query(sql, Parameters.Select(p => new SqlParameter(p.Name, SqlDbType.NVarChar)
                    {
                        Value = p.Value
                    }).ToArray()));
            }

            default:
            {
                var dbHelperMySql = new DbHelperMySql(connect.ConnectString);
                return(dbHelperMySql.Query(sql, Parameters.Select(p => new MySqlParameter(p.Name, MySqlDbType.VarChar)
                    {
                        Value = p.Value
                    }).ToArray()));
            }
            }
        }
예제 #5
0
        /// <summary>
        /// 获取字段列表
        /// </summary>
        /// <param name="connectid"></param>
        /// <param name="databasename"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public ResponseMessage Columns(long ProjectID, int connectid, string databasename, string tablename)
        {
            // 判断是否有权限
            var userproject = _UserManage.GetUserProject(_TenantManage.user.ID, ProjectID);

            if (userproject == null)
            {
                return(new ResponseMessage(MessageResult.Error, "项目不存在"));
            }
            var connect = GetConnectList(ProjectID).FirstOrDefault(m => m.ID == connectid);

            if (connect == null)
            {
                return(new ResponseMessage(MessageResult.Error, "连接不存在"));
            }

            var list = new List <object>();

            switch (connect.Type)
            {
            case "sqlserver":
            {
                var connectstring = Regex.Replace(connect.ConnectString, @"initial catalog=([^;]*)", "initial catalog=" + databasename);
                var sql           = string.Format(@"
select sc.name,st.name type from syscolumns sc,systypes st where sc.xusertype=st.xusertype and sc.id in(select id from sysobjects where xtype='U' and name='{0}');
EXEC sp_pkeys @table_name='{0}';
", tablename);
                // var sql = string.Format("Select name FROM SysColumns Where id=Object_Id('{0}');", tablename, databasename);
                var dbHelperMySql = new DbHelperSqlServer(connectstring);
                var ds            = dbHelperMySql.Query(sql);
                foreach (DataRow _row in ds.Tables[0].Rows)
                {
                    var ispk = false;
                    foreach (DataRow pkrow in ds.Tables[1].Rows)
                    {
                        if (pkrow["column_name"].ToString() == _row["name"].ToString())
                        {
                            ispk = true;
                        }
                    }
                    list.Add(new
                        {
                            column_name = _row["name"].ToString(),
                            data_type   = _row["type"].ToString(),
                            primary_key = ispk,
                            display     = true
                        });
                }
            }
            break;

            default:
            {
                var sql =
                    string.Format("select * from information_schema.columns where table_schema='{1}' and table_name='{0}';", tablename, databasename);
                DbHelperMySql dbHelperMySql = new DbHelperMySql(connect.ConnectString);
                var           ds            = dbHelperMySql.Query(sql);
                foreach (DataRow _row in ds.Tables[0].Rows)
                {
                    list.Add(new
                        {
                            column_name = _row["column_name"].ToString(),
                            data_type   = _row["data_type"].ToString(),
                            primary_key = _row["column_key"].ToString().ToUpper() == "PRI",
                            display     = true
                        });
                }
            }
            break;
            }


            return(new ResponseMessage(MessageResult.Success, "成功", list));
            // return Json(list, JsonRequestBehavior.AllowGet);
        }