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