/// <summary> /// 获取指定表的字段信息 /// </summary> /// <param name="serverAddress">服务器地址</param> /// <param name="dbName">要获取信息的数据库名称</param> /// <param name="tabName">要获取信息的表名称</param> /// <param name="userName">数据库登录用户名</param> /// <param name="passWord">密码</param> /// <returns></returns> public static List <StObjField> GetTableFieldInfo(string serverAddress, string dbName, string tabName, string userName, string passWord) { List <StObjField> res = new List <StObjField>(); SqlDataReader dr = null; try { con = new SqlConnection(GetLineStr(serverAddress, dbName, userName, passWord)); con.Open(); string sql = GetDBFieldInfoSQL(false, tabName); SqlCommand cmd = new SqlCommand(sql, con); dr = cmd.ExecuteReader(); StObjField obj = null; object o = null; string[] isS = { "u", "v" }; string[] isI = { "u" }; string[] isU = { "u" }; string[] isD = { "u" }; StObjectType stobjtype = default(StObjectType); while (dr.Read()) { stobjtype = default(StObjectType); obj = new StObjField(); obj.ObjName = dr["objname"].ToString().Replace("(", "_").Replace(")", "_"); obj.ColID = Convert.ToInt32(dr["colid"]); obj.ColName = dr["name"].ToString(); obj.ColTypeName = dr["typename"].ToString(); o = dr["defaultvalue"]; obj.DefaultValue = o is DBNull ? null : o.ToString(); o = dr["describe"]; obj.Describe = o is DBNull ? null : o.ToString().Replace("\\r\\n", "").Replace("\r\n", ""); obj.IsComputed = Convert.ToBoolean(dr["iscomputed"]); obj.IsIdentity = Convert.ToBoolean(dr["isidentity"]); obj.IsNull = Convert.ToBoolean(dr["isnullable"]); obj.IsOutParam = Convert.ToBoolean(dr["isoutparam"]); obj.IsPrimaryKey = Convert.ToBoolean(dr["isprimarykey"]); obj.MaxLength = Convert.ToInt32(dr["length"]); obj.ObjID = Convert.ToInt32(dr["objid"]); o = dr["prec"]; obj.OldLength = o is DBNull ? -1 : Convert.ToInt32(o); o = dr["parentTabID"]; obj.PrientTableID = o is DBNull ? -1 : Convert.ToInt32(o); o = dr["parentTabName"]; obj.PrientTableName = o is DBNull ? null : o.ToString(); o = dr["scale"]; obj.Scale = o is DBNull ? -1 : Convert.ToInt32(o); if (Enum.TryParse <StObjectType>(dr["scale"] + "", false, out stobjtype)) { obj.ObjType = stobjtype; } string objtype = obj.ObjType.ToString().ToLower(); obj.IsObjDelete = isD.Contains(objtype); obj.IsObjInsert = isI.Contains(objtype); obj.IsObjSelect = isS.Contains(objtype); obj.IsObjUpdate = isU.Contains(objtype); obj.IsColDelete = obj.IsObjDelete; obj.IsColInsert = obj.IsObjInsert && !obj.IsPrimaryKey && !obj.IsIdentity; obj.IsColSelect = obj.IsObjSelect; obj.IsColUpdate = obj.IsObjUpdate && !obj.IsPrimaryKey && !obj.IsIdentity && !obj.IsComputed; res.Add(obj); } } catch (Exception ex) { throw ex; } finally { if (dr != null) { dr.Close(); } if (con != null) { con.Close(); } } return(res); }
/// <summary> /// 获取指定数据库的所有表的字段信息 /// </summary> /// <param name="serverAddress">服务器地址</param> /// <param name="dbName">要获取信息的数据库名称</param> /// <param name="userName">数据库登录用户名</param> /// <param name="passWord">密码</param> /// <returns></returns> public static Dictionary <string, StTable> GetAllTableFieldInfo2(string serverAddress, string dbName, string userName, string passWord, List <string> types, string reStr = "_", params string[] replyStr) { Dictionary <string, StTable> res = new Dictionary <string, StTable>(); SqlCommand cmd = null; try { string sql = GetLineStr(serverAddress, dbName, userName, passWord); con = new SqlConnection(sql); con.Open(); sql = GetDBFieldInfoSQL2(true, "", types); cmd = new SqlCommand(sql, con); DataSet ds = new DataSet(); using (DbDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(ds); } StTable obj = null; object o = null; bool e = replyStr != null && replyStr.Length > 0; string[] isS = { "u", "v" }; string[] isI = { "u" }; string[] isU = { "u" }; string[] isD = { "u" }; StObjectType stobjtype = default(StObjectType); StField field = new StField(); DataTable dt1 = ds.Tables[0]; foreach (DataRow dr in dt1.Rows) { stobjtype = default(StObjectType); field = new StField(); string tabName = dr["objname"].ToString(); if (!res.ContainsKey(tabName)) { obj = new StTable() { Fields = new List <StField>() }; obj.Name = dr["objname"].ToString(); if (e) { foreach (var item in replyStr) { obj.Name = obj.Name.Replace(item, reStr); } } o = dr["objdescribe"]; obj.Describe = o is DBNull ? null : o.ToString(); obj.ID = Convert.ToInt32(dr["objid"]); string objtype = obj.Type.ToString().ToLower(); if (Enum.TryParse <StObjectType>(dr["scale"] + "", false, out stobjtype)) { obj.Type = stobjtype; } obj.IsDelete = isD.Contains(objtype); obj.IsInsert = isI.Contains(objtype); obj.IsSelect = isS.Contains(objtype); obj.IsUpdate = isU.Contains(objtype); } field.ColID = Convert.ToInt32(dr["colid"]); field.ColName = dr["name"].ToString(); field.ColTypeName = dr["typename"].ToString(); o = dr["defaultvalue"]; field.DefaultValue = o is DBNull ? null : o.ToString(); o = dr["describe"]; field.Describe = o is DBNull ? null : o.ToString().Replace("\r\n", ""); field.IsComputed = Convert.ToBoolean(dr["iscomputed"]); field.IsIdentity = Convert.ToBoolean(dr["isidentity"]); field.IsNull = Convert.ToBoolean(dr["isnullable"]); field.IsOutParam = Convert.ToBoolean(dr["isoutparam"]); field.IsPrimaryKey = Convert.ToBoolean(dr["isprimarykey"]); field.MaxLength = Convert.ToInt32(dr["length"]); o = dr["prec"]; field.OldLength = o is DBNull ? -1 : Convert.ToInt32(o); o = dr["parentTabID"]; field.PrientTableID = o is DBNull ? -1 : Convert.ToInt32(o); o = dr["parentTabName"]; field.PrientTableName = o is DBNull ? null : o.ToString(); o = dr["scale"]; if (!(o is DBNull)) { field.Scale = Convert.ToInt32(o); } o = dr["identity_seed"];//种子 if (!(o is DBNull)) { field.IDENTITY_SEED = Convert.ToInt32(o); } o = dr["identity_increase"];//增量 if (!(o is DBNull)) { field.IDENTITY_INCREASE = Convert.ToInt32(o); } o = dr["unique_text"];//增量 if (!(o is DBNull)) { field.UniqueText = o is DBNull ? null : o.ToString(); } field.IsColDelete = obj.IsDelete; field.IsColInsert = obj.IsInsert && !field.IsPrimaryKey && !field.IsIdentity; field.IsColSelect = obj.IsSelect; field.IsColUpdate = obj.IsUpdate && !field.IsPrimaryKey && !field.IsIdentity && !field.IsComputed; if (!res.ContainsKey(obj.Name)) { res.Add(obj.Name, obj); } res[obj.Name].Fields.Add(field); } #region Check约束 if (ds.Tables.Count > 1) { dt1 = ds.Tables[1]; StObject sto = null; foreach (DataRow dr in dt1.Rows) { sto = new StObject(); sto.Type = StObjectType.C; o = dr["colid"]; sto.ColID = o is DBNull ? -1 : Convert.ToInt32(o.ToString()); o = dr["definition"]; sto.CreateText = o is DBNull ? "" : o.ToString(); o = dr["name"]; sto.Name = o is DBNull ? "" : o.ToString(); o = dr["tabid"]; sto.ObjID = Convert.ToInt32(o.ToString()); o = dr["tabname"]; sto.ObjName = o is DBNull ? null : o.ToString(); StField sf = res[sto.ObjName].Fields.Where(i => i.ColID == sto.ColID).FirstOrDefault(); if (sf != null) { //该约束是针对列的 sf.UniqueText = sto.CreateText; } if (res[sto.ObjName].StObjects == null) { res[sto.ObjName].StObjects = new List <StObject>(); } res[sto.ObjName].StObjects.Add(sto); } } #endregion } catch (Exception ex) { throw ex; } finally { if (cmd != null) { cmd.Clone(); } if (con != null) { con.Close(); } } return(res); }
/// <summary> /// 获取指定数据库的所有表的字段信息 /// </summary> /// <param name="serverAddress">服务器地址</param> /// <param name="dbName">要获取信息的数据库名称</param> /// <param name="userName">数据库登录用户名</param> /// <param name="passWord">密码</param> /// <returns></returns> public static Dictionary <string, List <StObjField> > GetAllTableFieldInfo(string serverAddress, string dbName, string userName, string passWord, List <string> types, DateTime?sTime, string reStr = "_", params string[] replyStr) { Dictionary <string, List <StObjField> > res = new Dictionary <string, List <StObjField> >(StringComparer.OrdinalIgnoreCase); SqlDataReader dr = null; try { string sql = GetLineStr(serverAddress, dbName, userName, passWord); con = new SqlConnection(sql); con.Open(); sql = GetDBFieldInfoSQL(true, "", types, sTime); SqlCommand cmd = new SqlCommand(sql, con); dr = cmd.ExecuteReader(); StObjField obj = null; object o = null; bool e = replyStr != null && replyStr.Length > 0; string[] isS = { "u", "v" }; string[] isI = { "u" }; string[] isU = { "u" }; string[] isD = { "u" }; StObjectType stobjtype = default(StObjectType); while (dr.Read()) { stobjtype = default(StObjectType); obj = new StObjField(); obj.ObjName = dr["objname"].ToString(); if (e) { foreach (var item in replyStr) { obj.ObjName = obj.ObjName.Replace(item, reStr); } } if (obj.ObjName == "vi_AccessIO") { int a = 0; } obj.ColID = Convert.ToInt32(dr["colid"]); o = dr["objdescribe"]; obj.ObjDescribe = o is DBNull ? null : o.ToString(); obj.ColName = dr["name"].ToString(); obj.CreTime = Convert.ToDateTime(dr["creTime"]); obj.UpdTime = Convert.ToDateTime(dr["updTime"]); obj.ColTypeName = dr["typename"].ToString(); o = dr["defaultvalue"]; obj.DefaultValue = o is DBNull ? null : o.ToString(); o = dr["defaultid"]; if (!(o is DBNull)) { obj.DefaultID = Convert.ToInt32(o); } o = dr["describe"]; obj.Describe = o is DBNull ? null : o.ToString().Replace("\r\n", ""); obj.IsComputed = Convert.ToBoolean(dr["iscomputed"]); o = dr["computed_text"]; obj.ComputedText = o is DBNull ? null : o.ToString(); obj.IsIdentity = Convert.ToBoolean(dr["isidentity"]); obj.IsNull = Convert.ToBoolean(dr["isnullable"]); obj.IsOutParam = Convert.ToBoolean(dr["isoutparam"]); obj.IsPrimaryKey = Convert.ToBoolean(dr["isprimarykey"]); obj.MaxLength = Convert.ToInt32(dr["length"]); obj.ObjID = Convert.ToInt32(dr["objid"]); o = dr["prec"]; obj.OldLength = o is DBNull ? -1 : Convert.ToInt32(o); o = dr["parentTabID"]; obj.PrientTableID = o is DBNull ? -1 : Convert.ToInt32(o); o = dr["parentTabName"]; obj.PrientTableName = o is DBNull ? null : o.ToString(); o = dr["parColId"]; obj.ParentColID = o is DBNull ? -1 : Convert.ToInt32(o); o = dr["parColName"]; obj.ParentColName = o is DBNull ? null : o.ToString(); o = dr["scale"]; if (!(o is DBNull)) { obj.Scale = Convert.ToInt32(o); } if (Enum.TryParse <StObjectType>(dr["objtype"] + "", false, out stobjtype)) { obj.ObjType = stobjtype; } obj.IsUnique = dr["isunion"] + "" == "1"; o = dr["identity_seed"];//种子 if (!(o is DBNull)) { obj.IDENTITY_SEED = Convert.ToInt32(o); } o = dr["identity_increase"];//增量 if (!(o is DBNull)) { obj.IDENTITY_INCREASE = Convert.ToInt32(o); } obj.IsUnique = dr["ischeck"] + "" == "1"; o = dr["check_text"];//增量 if (!(o is DBNull)) { obj.CheckText = o is DBNull ? null : o.ToString(); } string objtype = obj.ObjType.ToString().ToLower(); obj.IsObjDelete = isD.Contains(objtype); obj.IsObjInsert = isI.Contains(objtype); obj.IsObjSelect = isS.Contains(objtype); obj.IsObjUpdate = isU.Contains(objtype); obj.IsColDelete = obj.IsObjDelete; obj.IsColInsert = obj.IsObjInsert && !obj.IsPrimaryKey && !obj.IsIdentity; obj.IsColSelect = obj.IsObjSelect; obj.IsColUpdate = obj.IsObjUpdate && !obj.IsPrimaryKey && !obj.IsIdentity && !obj.IsComputed; if (!res.ContainsKey(obj.ObjName)) { res.Add(obj.ObjName, new List <StObjField>()); } res[obj.ObjName].Add(obj); if (GetDBTableInfohander != null) { GetDBTableInfohander(res.Count); } } } catch (Exception ex) { throw ex; } finally { if (dr != null) { dr.Close(); } if (con != null) { con.Close(); } } return(res); }