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