/// <summary>
/// 表的栏位列表
/// </summary>
/// <param name="TableName">表名</param>
public List <DataColumnModel> ColumnList(string TableName, string DBConnectionString = null)
{
List <DataColumnModel> reVal = new List <DataColumnModel>();
if (DBConnectionString.IsNullOrEmpty())
{
DBConnectionString = Config.SqlServerConnectionString;
}
string SelectSQL = @"SELECT
[DisplayOrder]=[A].[COLORDER],
[Name]=[A].[Name],
[IsIdentity]=CASE WHEN COLUMNPROPERTY([A].[ID], [A].[Name],'IsIdentity')=1 THEN '1'ELSE '0' END,
[IsPrimaryKey]=CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ=A.ID AND NAME IN (
SELECT [Name] FROM SYSINDEXES WHERE [INDID] IN(
SELECT [INDID] FROM SYSINDEXKEYS WHERE [ID]=[A].[ID] AND [COLID]=[A].[COLID]
))) THEN '1' ELSE '0' END,
[DataType]=[B].[Name],
[OccupancyByte]=[A].[length],
[Length]=COLUMNPROPERTY(A.ID,A.name,'PRECISION'),
[DecimalDigits]=isnull(COLUMNPROPERTY(A.ID,A.name,'Scale'),0),
[IsAllowNull]=CASE WHEN [A].[IsNullAble]=1 THEN '1' ELSE '0' END,
[DefaultValue]=ISNULL(E.text,''),
[Description]=ISNULL(g.[value],'')
FROM SYSCOLUMNS [A]
LEFT JOIN systypes [B] ON [A].[XUSERTYPE]=[B].[XUSERTYPE]
INNER JOIN SYSOBJECTS [D] ON [A].[ID]=[D].[ID] AND [D].[XTYPE]='U' AND [D].[Name]<>'dtproperties'
LEFT JOIN syscomments [E] ON [A].[CDEFAULT]=[E].[ID]
LEFT JOIN SYS.EXTENDED_PROPERTIES [G] ON [A].[ID]=[G].[MAJOR_ID] AND [A].[COLID]=[G].[MINOR_ID]
LEFT JOIN SYS.EXTENDED_PROPERTIES [F] ON [D].[ID]=[F].[MAJOR_ID] AND [F].[MINOR_ID]=0
WHERE [D].[NAME]='@TableName'".Replace("@TableName", TableName);
SqlHelper DbHelper = new SqlHelper(DBConnectionString);
DataTable SelectDT = DbHelper.ExecuteDataTable(SelectSQL);
DataColumnModel DataColumnM = null;
foreach (DataRow dw in SelectDT.Rows)
{
DataColumnM = new DataColumnModel()
{
Name = dw["Name"].ToString(),
DataType = dw["DataType"].ToString(),
Length = dw["Length"].ToString().ToInt(),
IsAllowNull = dw["IsAllowNull"].ToString().ToBool(),
DisplayName = dw["Description"].ToString(),
IsIdentity = dw["IsIdentity"].ToString().ToBool(),
IsPrimaryKey = dw["IsPrimaryKey"].ToString().ToBool(),
DecimalDigits = dw["DecimalDigits"].ToString().ToInt(),
DefaultValue = dw["DefaultValue"].ToString()
};
if (!reVal.Contains(DataColumnM))
{
reVal.Add(DataColumnM);
}
}
return(reVal);
}
/// <summary>
/// 加载数据文件
/// </summary>
/// <param name="XmlFilePath">数据文件路径</param>
/// <returns>返回值：加载是否成功</returns>
public bool LoadData(string _DirPath = "")
{
bool reVal = true;
if (_DirPath.IsNullOrEmpty())
{
_DirPath = SuperManager.FileFullPath("~/DataModel/");
}
if (!_DirPath.EndsWith("\\"))
{
_DirPath = _DirPath + "\\";
}
if (Directory.Exists(_DirPath))
{
#region 加载表数据
List <string> _DTMF = SuperIO.SubFileName(_DirPath, ".dtml");
List <string> _DTList = new List <string>();
foreach (string _FMF in _DTMF)
{
string _FilePath = _DirPath + _FMF;;
List <DataColumnModel> _DCMList = new List <DataColumnModel>();
DataTableModel _DTM = new DataTableModel();
XmlNode _Node = XMLHelper.SingleNode(_FilePath, "//root/Model");
_DTM.Name = _Node.AttriteValue("DataTable");
_DTM.DisplayName = _Node.AttriteValue("DName");
XmlNodeList _NodeList = XMLHelper.NodeList(_FilePath, "//root/Model/Property");
foreach (XmlNode _FXN in _NodeList)
{
if (_FXN.Attributes["DBColumn"] != null && _FXN.Attributes["Type"] != null)
{
DataColumnModel _DCM = new DataColumnModel()
{
Name = _FXN.AttriteValue("DBColumn"),
Length = _FXN.AttriteValue("Length").ToInt(),
IsPrimaryKey = _FXN.AttriteValue("IsPrimaryKey").ToBool(),
DisplayName = _FXN.AttriteValue("DName"),
DataType = _FXN.AttriteValue("Type"),
DecimalDigits = _FXN.AttriteValue("DecimalDigits").ToInt(),
DefaultValue = _FXN.AttriteValue("DefaultValue"),
IsAllowNull = _FXN.AttriteValue("IsAllowNull").ToBool(),
IsIdentity = _FXN.AttriteValue("IsIdentity").ToBool()
};
_DCMList.Add(_DCM);
}
}
_DTM.ColumnList = _DCMList;
DTMList.Add(_DTM);
}
#endregion 加载表数据
}
return(reVal);
}
/// <summary>
/// 表的栏位列表
/// </summary>
/// <param name="TableName">表名</param>
public List <DataColumnModel> ColumnList(string TableName, SQLiteHelper DbHelper = null)
{
List <DataColumnModel> reVal = new List <DataColumnModel>();
string SelectSQL = @"PRAGMA table_info([@TableName]) ".Replace("@TableName", TableName);
if (DbHelper == null)
{
DbHelper = new SQLiteHelper(Config.SQLiteConnectionString);
}
DataTable SystemDT = DbHelper.ExecuteDataTable(SelectSQL);
DataColumnModel DataColumnM = null;
foreach (DataRow dw in SystemDT.Rows)
{
int length = 0;
string DataType = "";
Regex Rx = new Regex(@"^\w+", RegexOptions.IgnoreCase);
var MC = Rx.Matches(dw["type"].ToString());
DataType = MC[0].Value;
Rx = new Regex(@"(\d+)");
if (Rx.IsMatch(dw["type"].ToString()))
{
MC = Rx.Matches(dw["type"].ToString());
length = MC[0].Value.ToInt();
}
bool IsIdentity = false;
if (dw["pk"].ToString() == "1" && DataType == "INTEGER")
{
IsIdentity = true;
}
DataColumnM = new DataColumnModel()
{
Name = dw["name"].ToString(),
DataType = DataType,
Length = length,
IsAllowNull = !dw["notnull"].ToString().ToBool(),
DisplayName = string.Empty,
IsIdentity = IsIdentity,
IsPrimaryKey = dw["pk"].ToString().ToBool(),
DecimalDigits = 0,
DefaultValue = dw["dflt_value"].ToString()
};
if (!reVal.Contains(DataColumnM))
{
reVal.Add(DataColumnM);
}
}
return(reVal);
}