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