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());
}
}
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());
}
}
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);
}
}
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);
}
}
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);
}
}
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());
}
}
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));
}