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