/// <summary> /// 获取数据库中所有用户表的名称列表 /// </summary> /// <returns>返回数据库表名集合</returns> public static List <string> GetTableNames() { //DBHelper.ConnectionString = String.Format("Server={0};Database={1};uid={2};pwd={3}", GlobalConfig.Item.Server, GlobalConfig.Item.DataBase, GlobalConfig.Item.UID, GlobalConfig.Item.PWD); List <string> tables = new List <string>(); string sql = String.Empty; if (GlobalConfig.Item.DataBaseType == ConfigItem.DataBaseTypeEnum.SQLServer) { if (GlobalConfig.Item.IsAllowView) { sql = "select name from sysobjects where xtype in('U','V') order by crdate"; } else { sql = "select name from sysobjects where xtype='U' order by crdate"; } using (SqlDataReader reader = DBHelper.GetReader(DBHelper.ConnectionString, CommandType.Text, sql, null)) { while (reader.Read()) { tables.Add(reader["name"] as string); } reader.Close(); } } else if (GlobalConfig.Item.DataBaseType == ConfigItem.DataBaseTypeEnum.SQLite) { if (GlobalConfig.Item.IsAllowView) { sql = "SELECT name FROM sqlite_master WHERE type in ('table','view') ORDER BY name"; } else { sql = "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name"; } using (SQLiteDataReader reader = SQLiteHepler.GetReader(SQLiteHepler.ConnectionString, CommandType.Text, sql, null)) { while (reader.Read()) { tables.Add(reader["name"] as string); } reader.Close(); } } return(tables); }
/// <summary> /// 获取一个表的主键字段列表 /// </summary> /// <param name="tableName">表名</param> /// <returns>返回主键字段列表</returns> public static List <string> GetPrimaryKeys(string tableName) { List <string> lst = new List <string>(); string sql = "sp_help"; DataSet ds = new DataSet(); if (GlobalConfig.Item.DataBaseType == ConfigItem.DataBaseTypeEnum.SQLServer) { SqlParameter[] values = { new SqlParameter("@objname", tableName) }; ds = DBHelper.GetDataSet(DBHelper.ConnectionString, CommandType.StoredProcedure, sql, values); if (ds.Tables.Count >= 7) { foreach (DataRow row in ds.Tables[6].Rows) { //if (row["constraint_type"].ToString() == "PRIMARY KEY (clustered)") //2011-9-9修正,增加了对非聚集主键的解析支持 if (row["constraint_type"].ToString().StartsWith("PRIMARY KEY")) { string[] keys = row["constraint_keys"].ToString().Split(new char[] { ',' }); foreach (string key in keys) { lst.Add(key.Trim()); } break; } } } } else if (GlobalConfig.Item.DataBaseType == ConfigItem.DataBaseTypeEnum.SQLite) { //SQLiteParameter[] values = { new SQLiteParameter("@objname", tableName) }; sql = $"pragma table_info ('{tableName}') "; ds = SQLiteHepler.GetDataSet(SQLiteHepler.ConnectionString, CommandType.Text, sql, null); var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { var dataRow = dt.Select("pk = '1'"); foreach (var dr in dataRow) { var primaryKey = dr["name"].ToString(); lst.Add(primaryKey); } } } return(lst); }
/// <summary> /// 根据表名获取表的字段和字段类型的集合 /// </summary> /// <param name="tableName">表名</param> /// <returns>字段和字段类型的集合</returns> public static Dictionary <string, string> GetColumnAndCType(string tableName) { Dictionary <string, string> dic = new Dictionary <string, string>(); string sql = String.Format("select * from {0} where 1<>1", tableName); DataSet ds = new DataSet(); if (GlobalConfig.Item.DataBaseType == ConfigItem.DataBaseTypeEnum.SQLServer) { ds = DBHelper.GetDataSet(DBHelper.ConnectionString, CommandType.Text, sql, null); } else if (GlobalConfig.Item.DataBaseType == ConfigItem.DataBaseTypeEnum.SQLite) { ds = SQLiteHepler.GetDataSet(SQLiteHepler.ConnectionString, CommandType.Text, sql, null); } foreach (DataColumn col in ds.Tables[0].Columns) { dic.Add(col.ColumnName, GetCSharpVariableType(col.DataType)); } return(dic); }