/// <summary>
/// 获得存储过程参数
/// </summary>
/// <returns></returns>
public IDbParms GetParameters()
{
SqlParameterProvider parameters = new SqlParameterProvider();
var properties = this.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
foreach (var p in properties)
{
CommandParameterAttribute attr = (CommandParameterAttribute)p.GetCustomAttributes(typeof(CommandParameterAttribute), false).FirstOrDefault();
if (attr == null)
{
continue;
}
string SPName = attr.SPName;
if (String.IsNullOrWhiteSpace(SPName))
{
SPName = p.Name;
}
object value = p.GetValue(this, null);
parameters.AddParameter("@" + SPName, attr.DbType, attr.Size, value, attr.Direction);
}
return(parameters);
}
/// <summary>
/// 获得数据库对象，包含大小
/// </summary>
/// <param name="objectType"></param>
/// <returns></returns>
public List <ModelObjectItem2> GetObjects_Size(ObjectType objectType)
{
List <string> xtype = new List <string>();
switch (objectType)
{
case ObjectType.Table:
xtype.Add("U");
break;
case ObjectType.View:
xtype.Add("V");
break;
case ObjectType.Procedure:
xtype.Add("P");
break;
}
if (xtype.Count > 0)
{
string inStr = string.Join(",", xtype.Select(a => "'" + a + "'"));
string sql = $@"SELECT a.object_id as ObjectID,a.name as ObjectName,a.type as ObjectType,a.create_date as CreateDate,a.modify_date as ModifyDate,b.value as Description
FROM sys.objects AS a LEFT JOIN (SELECT * FROM sys.extended_properties WHERE name='MS_Description') AS b ON a.object_id=b.major_id AND b.minor_id=0
Where a.type in ({inStr}) ORDER BY a.type, a.name";
var data = db.ExecuteDataList <ModelObjectItem2>(sql, null);
db.Excute(_db =>
{
foreach (var obj in data)
{
obj.ObjectType = obj.ObjectType.Trim();
if (obj.ObjectType == "U")
{
SqlParameterProvider paramers = new SqlParameterProvider();
paramers.AddParameter("@objname", SqlDbType.VarChar, obj.ObjectName);
_db.ExecuteDataReaderSP("sp_spaceused", paramers, row =>
{
obj.rows = int.Parse(row.GetFieldValue <string>("rows").Trim());
obj.reserved = row.GetFieldValue <string>("reserved");
obj.data = row.GetFieldValue <string>("data");
obj.index_size = row.GetFieldValue <string>("index_size");
obj.unused = row.GetFieldValue <string>("unused");
});
}
}
});
return(data);
}
else
{
return(null);
}
}
/// <summary>
/// 获得存储过程或函数的参数
/// </summary>
/// <param name="FunctionName"></param>
/// <returns></returns>
public List <ModelParameterItem> GetFunctionParameters(string FunctionName)
{
string sql = @"SELECT sp.object_Id as ObjectID, sp.name as FunctionName,
isnull(param.name,'')as ObjectName,isnull(usrt.name,'') AS [DataType],
ISNULL(baset.name, '') AS [SystemType], CAST(CASE when baset.name is null then 0 WHEN baset.name IN ('nchar', 'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Size],
'' as ParamReamrk,isnull(parameter_id,0) as SortId,
is_output=case when param.is_output is null then 0 else param.is_output end
FROM sys.objects AS sp INNER JOIN sys.schemas b ON sp.schema_id = b.schema_id
left outer JOIN sys.all_parameters AS param ON param.object_id=sp.object_Id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
LEFT OUTER JOIN sys.extended_properties E ON sp.object_id = E.major_id
WHERE sp.TYPE in ('FN', 'IF', 'TF','P') AND ISNULL(sp.is_ms_shipped, 0) = 0 AND ISNULL(E.name, '') <> 'microsoft_database_tools_support' and sp.name=@SPName";
SqlParameterProvider parameters = new SqlParameterProvider();
parameters.AddParameter("@SPName", SqlDbType.VarChar, FunctionName);
var data = db.ExecuteDataList <ModelParameterItem>(sql, parameters);
return(data);
}
/// <summary>
/// 获得表结构
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public List <ModelObjectColumnItem> GetObjectColumns(string TableName)
{
string sql = @"
SELECT a.object_id AS ObjectID,
OBJECT_NAME(a.object_id) AS ObjectName,
a.system_type_id AS TypeID,
a.name AS ColumnName,
c.name AS TypeName,
a.max_length AS [MaxLength],
a.[precision] AS [Precision],
a.scale AS Scale,
b.value AS [Description],
IsIdentity = a.is_identity
--Flag_Edit=CASE WHEN a.is_identity=1 OR c.name='timestamp' THEN 'N' ELSE 'Y' END,
--Flag_TS=CASE WHEN c.name='timestamp' THEN 'Y' ELSE 'N' END
FROM sys.columns a LEFT JOIN sys.extended_properties b ON b.major_id = a.object_id AND b.minor_id = a.column_id AND b.name = 'MS_Description'
LEFT JOIN sys.types AS c ON c.user_type_id = a.user_type_id
WHERE a.object_id =(select object_id from sys.objects where name=@tableName)
ORDER BY OBJECT_NAME(a.object_id), a.column_id";
SqlParameterProvider parameters = new SqlParameterProvider();
parameters.AddParameter("@tableName", SqlDbType.VarChar, TableName);
var data = db.ExecuteDataList <ModelObjectColumnItem>(sql, parameters);
foreach (var obj in data)
{
if (string.Equals(obj.TypeName, "timestamp", System.StringComparison.OrdinalIgnoreCase))
{
obj.IsTimestamp = true;
}
}
string sqlKeys = @" SELECT a.TABLE_NAME,a.COLUMN_NAME,b.type
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS a INNER JOIN sys.objects AS b ON a.CONSTRAINT_NAME=b.name
WHERE TABLE_NAME=@tableName " ;
SqlParameterProvider parameters2 = new SqlParameterProvider();
parameters2.AddParameter("@tableName", SqlDbType.VarChar, TableName);
db.ExecuteDataReader(sqlKeys, parameters2, row =>
{
string type = row.GetFieldValue <string>("type");
string columnName = row.GetFieldValue <string>("COLUMN_NAME");
if (type == "PK")
{
var obj = data.Where(w => w.ColumnName == columnName).FirstOrDefault();
obj.IsPK = true;
}
if (type == "F")
{
var obj = data.Where(w => w.ColumnName == columnName).FirstOrDefault();
obj.IsFK = true;
}
});
return(data);
}
