public TableInfo GetTableInfo(string tableName) //查询单表中字段的属性
{
if (_con == null)
{
return(null);
}
if (_con.State != ConnectionState.Open)
{
_con.Open();
}
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = _con;
cmd.CommandText = string.Format("PRAGMA table_info('{0}')", tableName);
SQLiteDataReader reader = cmd.ExecuteReader();
TableInfo retInfo = new TableInfo();
while (reader.Read())
{
TableField tmp = new TableField();
tmp.Cid = $"{reader[0]} "; //表字段id
tmp.Name = $"{reader[1]} "; //表字段名
tmp.Type = $"{reader[2]} "; //表字段类型
tmp.NotNull = $"{reader[3]} "; //表字段是否可空
tmp.Dflt_value = $"{reader[4]} "; //表字段默认值
tmp.pk = $"{reader[5]} "; //表字段是否为键值
retInfo.Add(tmp);
}
reader.Close();
_con.Close();
return(retInfo);
}
/// <summary>
/// sqlite内置表sqlite_master，使用B-tree管理数据库所有表的信息
///
/// CREATE TABLE sqlite_master(
/// type text,
/// name text,
/// tbl_name text,
/// rootpage integer,
/// sql text
/// );
/// sql语句用例"SELECT name FROM sqlite_master WHERE TYPE='table'"
/// </summary>
/// <returns></returns>
public List <TableInfo> GetTableInfos()
{
if (_con == null)
{
return(null);
}
if (_con.State != ConnectionState.Open)
{
_con.Open();
}
List <TableInfo> retList = new List <TableInfo>();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = _con;
cmd.CommandText = "SELECT name FROM sqlite_master WHERE TYPE='table'";
SQLiteDataReader sr = cmd.ExecuteReader();
List <string> tables = new List <string>();
while (sr.Read())
{
tables.Add(sr.GetString(0));
}
sr.Close();
foreach (string tab in tables)
{
cmd.CommandText = $"PRAGMA TABLE_INFO({tab})";
sr = cmd.ExecuteReader();
TableInfo info = new TableInfo();
info.TableName = tab;
while (sr.Read())
{
TableField tmp = new TableField();
tmp.Cid = $"{sr[0]}";
tmp.Name = $"{sr[1]}";
tmp.Type = $"{sr[2]}";
tmp.NotNull = $"{sr[3]}";
tmp.Dflt_value = $"{sr[4]}";
tmp.pk = $"{sr[5]} ";
info.Add(tmp);
}
retList.Add(info);
sr.Close();
}
_con.Close();
return(retList);
}
public void AddTableField(string tableName, TableField field)
{
assert();
_cmd.CommandText = $"ALTER TABLE {tableName} ADD COLUMN {field.Name} {field.Type}";
_cmd.ExecuteNonQuery();
}