[sqlite] 判断表、视图是否存在及常用C#操作语句

时间:2022-01-24 10:16:56

1,判断表是否存在:

SELECT name, sql FROM sqlite_master WHERE type="table" AND name = "Dom" 

结果如下:

[sqlite] 判断表、视图是否存在及常用C#操作语句

2.判断视图是否存在:

SELECT count(*) FROM sqlite_master WHERE type = "view" AND name = "myView"

结果如下:

[sqlite] 判断表、视图是否存在及常用C#操作语句

type='view'判断视图.结果>0就是有这个视图


另附C#操作的常用代码:

    DataTable table = conn.GetSchema("TABLES");
    if (table != null && table.Rows.Count > 0)
     {
     string tableName = table.Rows[0]["TABLE_NAME"].ToString();
     DataTable schemaTable = GetReaderSchema(tableName, conn);
     }

  

    private DataTable GetReaderSchema(string tableName, SQLiteConnection connection)
{
DataTable schemaTable = null;
IDbCommand cmd = new SQLiteCommand();
cmd.CommandText = string.Format("select * from [{0}]", tableName);
cmd.Connection = connection;
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
schemaTable = reader.GetSchemaTable();
}
return schemaTable;
}

  

    foreach (DataRow dr in schemaTable.Rows)
{
ColumnInfo info = new ColumnInfo();
info.Name = new NameElement(dr["ColumnName"].ToString());
info.Ordinal = Convert.ToInt32(dr["ColumnOrdinal"].ToString());
info.AllowDBNull = (bool)dr["AllowDBNull"];
info.MaxLength = Convert.ToInt32(dr["ColumnSize"].ToString());
info.DataTypeId = Convert.ToInt32(dr["ProviderType"].ToString());
info.DataType = dr["DataTypeName"].ToString().Trim();
info.AutoIncrement = (bool)dr["IsAutoIncrement"];
info.IsPrimaryKey = (bool)dr["IsKey"];
info.Unique = (bool)dr["IsUnique"];
info.IsReadOnly = (bool)dr["IsReadOnly"];
string netType = dr["DataType"].ToString(); list.Add(info.Name.Name.ToString(), info);
}