예제 #1
0
 public IEnumerable <Database> GetAll(string serverName)
 {
     using (IDbConnection connection = new SqlConnection(ConnStrHelper.GetConnStr(serverName)))
     {
         const string queryDbName = @"SELECT name AS 'DatabaseName' FROM sys.sysdatabases WITH(NOLOCK) WHERE dbid > 4";
         connection.Open();
         var          resultDatabases = connection.Query <Database>(queryDbName).ToList();
         const string queryDbDesc     = @"SELECT value FROM sys.fn_listextendedproperty ('MS_Description', default, default, default, default, default, default)";
         const string queryDbOwner    = @"SELECT value FROM sys.fn_listextendedproperty ('JR_Owner', default, default, default, default, default, default)";
         foreach (var i in resultDatabases)
         {
             try
             {
                 connection.ChangeDatabase(i.DatabaseName);
             }
             catch
             {
                 i.ServerName          = serverName;
                 i.DatabaseDescription = @"<font color='red'>脱机</font>";
                 continue;
             }
             var dbDesc  = connection.Query <string>(queryDbDesc).FirstOrDefault();
             var dbOwner = connection.Query <string>(queryDbOwner).FirstOrDefault();
             i.ServerName          = serverName;
             i.DatabaseOwner       = dbOwner ?? "";
             i.DatabaseOwner       = i.DatabaseOwner.Trim().IsEmpty() ? "" : i.DatabaseOwner;
             i.DatabaseDescription = dbDesc ?? "";
             i.DatabaseDescription = i.DatabaseDescription.Trim().IsEmpty() ? "" : i.DatabaseDescription;
         }
         return(resultDatabases.AsEnumerable());
     }
 }
예제 #2
0
        public IEnumerable <Table> GetAll(string serverName, string databaseName, string schemaName)
        {
            using (IDbConnection connection = new SqlConnection(ConnStrHelper.GetConnStr(serverName)))
            {
                connection.Open();
                try
                {
                    connection.ChangeDatabase(databaseName);
                }
                catch
                {
                    return(new List <Table>());
                }
                const string query        = @"SELECT a.name AS 'TableName', e.ColCount AS 'ColCount', c.value AS 'TableOwner', b.value AS 'TableDescription' FROM sys.tables a LEFT JOIN sys.fn_listextendedproperty ('MS_Description', 'schema', @schemaname, 'table', default, default, default) b
 on a.name = b.objname COLLATE Chinese_PRC_CI_AS LEFT JOIN sys.fn_listextendedproperty ('JR_Owner', 'schema', @schemaname, 'table', default, default, default) c
 on a.name = c.objname COLLATE Chinese_PRC_CI_AS LEFT JOIN sys.schemas d ON a.schema_id = d.schema_id LEFT JOIN (select g.name AS 'TableName', COUNT(*) AS 'ColCount' from sys.syscolumns f left join sys.tables g on f.id = g.object_id group by g.name) e ON a.name = e.TableName WHERE d.name = @schemaname";
                var          resultTables = connection.Query <Table>(query, new { schemaname = schemaName }).ToList();
                foreach (var i in resultTables)
                {
                    i.ServerName       = serverName;
                    i.DatabaseName     = databaseName;
                    i.SchemaName       = schemaName;
                    i.TableOwner       = i.TableOwner ?? "";
                    i.TableOwner       = i.TableOwner.Trim().IsEmpty() ? "" : i.TableOwner;
                    i.TableDescription = i.TableDescription ?? "";
                    i.TableDescription = i.TableDescription.Trim().IsEmpty() ? "" : i.TableDescription;
                }
                return(resultTables.AsEnumerable());
            }
        }
예제 #3
0
 public void UpdateTableDesc(Table table, string dealer)
 {
     using (IDbConnection connection = new SqlConnection(ConnStrHelper.GetConnStr(table.ServerName)))
     {
         connection.Open();
         connection.ChangeDatabase(table.DatabaseName);
         const string query            = "SELECT value FROM sys.fn_listextendedproperty ('MS_Description', 'schema', @schemaname, 'table', @tablename, default, default)";
         var          tableDescription =
             connection.Query <string>(query, new { schemaname = table.SchemaName, tablename = table.TableName }).FirstOrDefault();
         const string queryOwner = "SELECT value FROM sys.fn_listextendedproperty ('JR_Owner', 'schema', @schemaname, 'table', @tablename, default, default)";
         var          tableOwner =
             connection.Query <string>(queryOwner, new { schemaname = table.SchemaName, tablename = table.TableName }).FirstOrDefault();
         if (tableDescription.IsNull() && tableOwner.IsNull() && table.TableDescription.IsNull() && table.TableOwner.IsNull())
         {
             return;
         }
         if (!tableDescription.IsNull() && !tableOwner.IsNull() && !table.TableDescription.IsNull() && !table.TableOwner.IsNull())
         {
             if (tableDescription == table.TableDescription && tableOwner == table.TableOwner)
             {
                 return;
             }
         }
         var logDal = new LogDal();
         var log    = new Log()
         {
             ColName    = "",
             DbName     = table.DatabaseName,
             Dealer     = dealer,
             DealTime   = DateTime.Now,
             NewOwner   = table.TableOwner ?? "",
             OldOwner   = tableOwner ?? "",
             NewDesc    = table.TableDescription ?? "",
             OldDesc    = tableDescription ?? "",
             SchemaName = "dbo",
             SvrName    = table.ServerName,
             TblName    = table.TableName
         };
         logDal.Add(log);
         var p = new DynamicParameters();
         p.Add("@name", "MS_Description");
         p.Add("@level0type", "schema");
         p.Add("@level0name", table.SchemaName);
         p.Add("@level1type", "table");
         p.Add("@level1name", table.TableName);
         p.Add("@value", table.TableDescription ?? "");
         connection.Execute(tableDescription.IsNull() ? "sys.sp_addextendedproperty" : "sys.sp_updateextendedproperty", p, commandType: CommandType.StoredProcedure);
         p = new DynamicParameters();
         p.Add("@name", "JR_Owner");
         p.Add("@level0type", "schema");
         p.Add("@level0name", table.SchemaName);
         p.Add("@level1type", "table");
         p.Add("@level1name", table.TableName);
         p.Add("@value", table.TableOwner ?? "");
         connection.Execute(tableOwner.IsNull() ? "sys.sp_addextendedproperty" : "sys.sp_updateextendedproperty", p, commandType: CommandType.StoredProcedure);
     }
 }
예제 #4
0
 public void UpdateFieldDesc(Field field, string dealer)
 {
     using (IDbConnection connection = new SqlConnection(ConnStrHelper.GetConnStr(field.ServerName)))
     {
         connection.Open();
         connection.ChangeDatabase(field.DatabaseName);
         const string query            = "SELECT value FROM sys.fn_listextendedproperty ('MS_Description', 'schema', @schemaname, 'table', @tablename, 'column', @fieldname)";
         var          fieldDescription =
             connection.Query <string>(query, new { schemaname = field.SchemaName, tablename = field.TableName, fieldname = field.FieldName }).FirstOrDefault();
         if (fieldDescription.IsNull() && field.FieldDescription.IsNull())
         {
             return;
         }
         if (!fieldDescription.IsNull() && !field.FieldDescription.IsNull())
         {
             if (fieldDescription == field.FieldDescription)
             {
                 return;
             }
         }
         if (!field.FieldDescription.IsNull())
         {
             field.FieldDescription = HttpUtility.UrlDecode(field.FieldDescription);
         }
         var logDal = new LogDal();
         var log    = new Log()
         {
             ColName    = "",
             DbName     = field.DatabaseName,
             Dealer     = dealer,
             DealTime   = DateTime.Now,
             NewDesc    = field.FieldDescription ?? "",
             OldDesc    = fieldDescription ?? "",
             SchemaName = "dbo",
             SvrName    = field.ServerName,
             TblName    = field.TableName,
             OldOwner   = "",
             NewOwner   = ""
         };
         logDal.Add(log);
         var p = new DynamicParameters();
         p.Add("@name", "MS_Description");
         p.Add("@level0type", "schema");
         p.Add("@level0name", field.SchemaName);
         p.Add("@level1type", "table");
         p.Add("@level1name", field.TableName);
         p.Add("@level2type", "column");
         p.Add("@level2name", field.FieldName);
         p.Add("@value", field.FieldDescription ?? "");
         connection.Execute(fieldDescription.IsNull() ? "sys.sp_addextendedproperty" : "sys.sp_updateextendedproperty", p, commandType: CommandType.StoredProcedure);
     }
 }
예제 #5
0
 public void UpdateDbDesc(Database db, string dealer)
 {
     using (IDbConnection connection = new SqlConnection(ConnStrHelper.GetConnStr(db.ServerName)))
     {
         connection.Open();
         connection.ChangeDatabase(db.DatabaseName);
         const string queryDbDesc  = @"SELECT value FROM sys.fn_listextendedproperty ('MS_Description', default, default, default, default, default, default)";
         var          dbDesc       = connection.Query <string>(queryDbDesc).FirstOrDefault();
         const string queryDbOwner = @"SELECT value FROM sys.fn_listextendedproperty ('JR_Owner', default, default, default, default, default, default)";
         var          dbOwner      = connection.Query <string>(queryDbOwner).FirstOrDefault();
         if (dbDesc.IsNull() && db.DatabaseDescription.IsNull() && dbOwner.IsNull() && db.DatabaseOwner.IsNull())
         {
             return;
         }
         if (!dbDesc.IsNull() && !db.DatabaseDescription.IsNull() && !dbOwner.IsNull() && !db.DatabaseOwner.IsNull())
         {
             if (dbDesc == db.DatabaseDescription && dbOwner == db.DatabaseOwner)
             {
                 return;
             }
         }
         var logDal = new LogDal();
         var log    = new Log()
         {
             ColName    = "",
             DbName     = db.DatabaseName,
             Dealer     = dealer,
             DealTime   = DateTime.Now,
             NewDesc    = db.DatabaseDescription ?? "",
             OldDesc    = dbDesc ?? "",
             SchemaName = "",
             SvrName    = db.ServerName,
             TblName    = "",
             OldOwner   = dbOwner ?? "",
             NewOwner   = db.DatabaseOwner ?? ""
         };
         logDal.Add(log);
         var p = new DynamicParameters();
         p.Add("@name", "MS_Description");
         p.Add("@value", db.DatabaseDescription ?? "");
         connection.Execute(dbDesc.IsNull() ? "sys.sp_addextendedproperty" : "sys.sp_updateextendedproperty", p,
                            commandType: CommandType.StoredProcedure);
         p = new DynamicParameters();
         p.Add("@name", "JR_Owner");
         p.Add("@value", db.DatabaseOwner ?? "");
         connection.Execute(dbOwner.IsNull() ? "sys.sp_addextendedproperty" : "sys.sp_updateextendedproperty", p,
                            commandType: CommandType.StoredProcedure);
     }
 }
예제 #6
0
        public IEnumerable <Field> GetAll(string serverName, string databaseName, string schemaName, string tableName)
        {
            using (IDbConnection connection = new SqlConnection(ConnStrHelper.GetConnStr(serverName)))
            {
                connection.Open();
                connection.ChangeDatabase(databaseName);
                const string query        = @"SELECT
     c.name AS 'FieldName',
     c.colorder AS 'FieldOrder',
     t.name AS 'FieldType',
     CONVERT(BIT, c.IsNullable) AS 'IsFieldNullable',
     CONVERT(BIT,CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype='PK' AND parent_obj=c.id AND name IN (
         SELECT name FROM sysindexes WHERE indid IN(
             SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) THEN 1 ELSE 0 END) 
                 AS 'IsFieldPrimaryKey'
     ,CONVERT(BIT,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) AS 'IsFieldAutoIncrement',
     c.length AS 'FieldMaxLength',
     ISNULL(cm.text,'') AS 'FieldDefaultValue',
     ISNULL(etp.value,'') AS 'FieldDescription',
     c.colid AS 'FieldPosition'
FROM sys.syscolumns c
INNER JOIN sys.systypes t ON c.xusertype = t.xusertype 
LEFT JOIN sys.extended_properties etp ON etp.major_id = c.id AND etp.minor_id = c.colid AND etp.name = 'MS_Description' 
LEFT JOIN sys.syscomments cm ON c.cdefault = cm.id
LEFT JOIN sys.tables tb ON C.id = tb.object_id
LEFT JOIN sys.schemas sc ON sc.schema_id = tb.schema_id
WHERE c.id = OBJECT_ID(@tablename) AND sc.name = @schemaname";
                var          resultFields = connection.Query <Field>(query, new { schemaname = schemaName, tablename = tableName }).ToList();
                foreach (var i in resultFields)
                {
                    i.ServerName       = serverName;
                    i.DatabaseName     = databaseName;
                    i.SchemaName       = schemaName;
                    i.TableName        = tableName;
                    i.FieldDescription = i.FieldDescription ?? "";
                    i.FieldDescription = i.FieldDescription.Trim().IsEmpty() ? "" : i.FieldDescription;
                }
                return(resultFields.AsEnumerable());
            }
        }
예제 #7
0
        public IEnumerable <Field> GetField(string serverName, string databaseName, string tableName, string fieldNameKeyword, string fieldDescriptionKeyword, int pageIndex, int pageSize, out int totalRecords)
        {
            if (string.IsNullOrWhiteSpace(fieldNameKeyword) && string.IsNullOrWhiteSpace(fieldDescriptionKeyword))
            {
                totalRecords = 0;
                return(new List <Field>());
            }
            if (serverName != _advServerName || databaseName != _advDatabaseName || tableName != _advTableName ||
                fieldNameKeyword != _advFieldNameKeyword || fieldDescriptionKeyword != _advFieldDescriptionKeyword)
            {
                _fields = new List <Field>();
                using (IDbConnection connection = new SqlConnection(ConnStrHelper.GetConnStr(serverName)))
                {
                    connection.Open();
                    var databases = new List <Database>();
                    if (string.IsNullOrWhiteSpace(databaseName))
                    {
                        const string queryDbName =
                            @"SELECT name AS 'DatabaseName' FROM sys.sysdatabases WITH(NOLOCK) WHERE dbid > 4";
                        databases = connection.Query <Database>(queryDbName).ToList();
                    }
                    else
                    {
                        databases.Add(new Database()
                        {
                            DatabaseName = databaseName
                        });
                    }
                    foreach (var i in databases)
                    {
                        try
                        {
                            connection.ChangeDatabase(i.DatabaseName);
                        }
                        catch
                        {
                            continue;
                        }
                        var sb = new StringBuilder();
                        sb.Append(@"SELECT
     c.name AS 'FieldName',
     c.colorder AS 'FieldOrder',
     t.name AS 'FieldType',
     CONVERT(BIT, c.IsNullable) AS 'IsFieldNullable',
     CONVERT(BIT,CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype='PK' AND parent_obj=c.id AND name IN (
         SELECT name FROM sysindexes WHERE indid IN(
             SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) THEN 1 ELSE 0 END) 
                 AS 'IsFieldPrimaryKey'
     ,CONVERT(BIT,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) AS 'IsFieldAutoIncrement',
     c.length AS 'FieldMaxLength',
     ISNULL(cm.text,'') AS 'FieldDefaultValue',
     ISNULL(etp.value,'') AS 'FieldDescription',
     c.colid AS 'FieldPosition',
     tb.name AS 'TableName'
FROM sys.syscolumns c
INNER JOIN sys.systypes t ON c.xusertype = t.xusertype 
LEFT JOIN sys.extended_properties etp ON etp.major_id = c.id AND etp.minor_id = c.colid AND etp.name = 'MS_Description' 
LEFT JOIN sys.syscomments cm ON c.cdefault = cm.id
LEFT JOIN sys.tables tb ON C.id = tb.object_id
LEFT JOIN sys.schemas sc ON sc.schema_id = tb.schema_id
WHERE sc.name = 'dbo'");
                        if (!string.IsNullOrWhiteSpace(tableName))
                        {
                            sb.Append(@" AND c.id = OBJECT_ID('");
                            sb.Append(tableName);
                            sb.Append(@"')");
                        }
                        if (!string.IsNullOrWhiteSpace(fieldNameKeyword))
                        {
                            string[] fieldNameKeywords = Regex.Split(fieldNameKeyword, @"\s+");
                            foreach (var fieldName in fieldNameKeywords)
                            {
                                sb.Append(@" AND c.name LIKE '%");
                                sb.Append(fieldName.Trim());
                                sb.Append(@"%'");
                            }
                        }
                        if (!string.IsNullOrWhiteSpace(fieldDescriptionKeyword))
                        {
                            string[] fieldDescriptionKeywords = Regex.Split(fieldDescriptionKeyword, @"\s+");
                            foreach (var fieldDescription in fieldDescriptionKeywords)
                            {
                                sb.Append(@" AND CONVERT(VARCHAR(MAX),ISNULL(etp.value,'')) LIKE '%");
                                sb.Append(fieldDescription.Trim());
                                sb.Append(@"%'");
                            }
                        }

                        var query        = sb.ToString();
                        var resultFields =
                            connection.Query <Field>(query).ToList();
                        foreach (var j in resultFields)
                        {
                            j.DatabaseName     = i.DatabaseName;
                            j.FieldDescription = j.FieldDescription ?? "";
                            j.FieldDescription = j.FieldDescription.Trim().IsEmpty() ? "" : j.FieldDescription;
                            _fields.Add(j);
                        }
                    }
                }
                _advServerName              = serverName;
                _advDatabaseName            = databaseName;
                _advTableName               = tableName;
                _advFieldNameKeyword        = fieldNameKeyword;
                _advFieldDescriptionKeyword = fieldDescriptionKeyword;
            }
            totalRecords = _fields.Count;
            return(_fields.Skip((pageIndex - 1) * pageSize).Take(pageSize));
        }