{"id":1926,"date":"2013-08-12T20:18:27","date_gmt":"2013-08-13T01:18:27","guid":{"rendered":"http:\/\/www.wiredprairie.us\/blog\/?p=1926"},"modified":"2022-06-29T10:26:41","modified_gmt":"2022-06-29T15:26:41","slug":"using-sqlite-and-c-to-determine-if-a-specified-table-exists","status":"publish","type":"post","link":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1926","title":{"rendered":"Using Sqlite and C# to determine if a specified table exists"},"content":{"rendered":"\n

I had need of a bit of code in C# to determine whether several tables in a Sqlite database had been created, so \u2026<\/p>\n\n\n\n

public<\/span> static<\/span> class<\/span> DbExtensions\n{\n    \/\/\/ <summary><\/span>\n    \/\/\/ Determines whether the table exists<\/span>\n    \/\/\/ <\/summary><\/span>\n    \/\/\/ <param name=\"connection\">Existing, opened, database connection<\/param><\/span>\n    \/\/\/ <param name=\"tableName\">The name of the table to test for.<\/param><\/span>\n    \/\/\/ <returns>True if table exists.<\/returns><\/span>\n    public<\/span> static<\/span> bool<\/span> TableExists(this<\/span> IDbConnection connection, string<\/span> tableName)\n    {\n        Debug.Assert(connection != null<\/span>);\n        Debug.Assert(!string<\/span>.IsNullOrWhiteSpace(tableName));\n\n        var cmd = connection.CreateCommand();\n        cmd.CommandText = @\"SELECT COUNT(*) FROM sqlite_master WHERE name=@TableName\"<\/a><\/span>;\n        var p1 = cmd.CreateParameter();\n        p1.DbType = DbType.String;\n        p1.ParameterName = \"TableName\"<\/span>;\n        p1.Value = tableName;\n        cmd.Parameters.Add(p1);\n\n        var result = cmd.ExecuteScalar();\n        return<\/span> ((long<\/span>)result) == 1;\n    }\n}<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Nothing too complex \u2026 only determining that the list of tables is stored in a <\/strong>system table named sqlite_master<\/strong>. If you want to find all indexes that have dependencies on a specific table, you can instead use the column tbl_name<\/strong>.<\/p>\n\n\n\n
If you\u2019re using Dapper and the DapperExtensions, you might find this useful:<\/p>\n\n\n\n
_connection = new<\/span> System.Data.SQLite.SQLiteConnection();\nDapperExtensions.DapperExtensions.SqlDialect = new<\/span> DapperExtensions.Sql.SqliteDialect();\n_connection.ConnectionString = new<\/span> DbConnectionStringBuilder()\n{\n    {\"Data Source\"<\/span>, \"thumbnailer.db\"<\/span>},\n    {\"Version\"<\/span>, \"3\"<\/span>},\n    {\"FailIfMissing\"<\/span>, \"False\"<\/span>},\n}.ConnectionString;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
I had need of a bit of code in C# to determine whether several tables in a Sqlite database had been created, so \u2026 public static class DbExtensions { \/\/\/ <summary> \/\/\/ Determines whether the table exists \/\/\/ <\/summary> \/\/\/ <param name=”connection”>Existing, opened, database connection<\/param> \/\/\/ <param name=”tableName”>The name of the table to test for.<\/param> […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[4],"tags":[82,145],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pd5QIe-v4","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":1597,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1597","url_meta":{"origin":1926,"position":0},"title":"Paging Data with SQL Server Compact","date":"April 1, 2012","format":false,"excerpt":"If you\u2019re using SQL Server Compact Editition version 4 or higher, there\u2019s finally a decent and efficient way to do data paging: var streets = db.Query(@\"SELECT * FROM StreetNames ORDER BY Id OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY\", new { offset = group * GroupSize, rows = GroupSize\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"image","src":"https:\/\/i0.wp.com\/www.wiredprairie.us\/blog\/wp-content\/uploads\/2012\/04\/image9.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1730,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1730","url_meta":{"origin":1926,"position":1},"title":"How to find an element in a DataTemplate in WinRT\/XAML.","date":"September 7, 2012","format":false,"excerpt":"Here\u2019s one way to find a named element in a DataTemplate in XAML in Windows 8 XAML. You might try FindName to discover it doesn\u2019t work. That\u2019s because it\u2019s not recursive. So, I created a simple extension method to do the same thing: public static class FrameworkElementExtensions { public static\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"image","src":"https:\/\/i0.wp.com\/www.wiredprairie.us\/blog\/wp-content\/uploads\/2012\/09\/image.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1835,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1835","url_meta":{"origin":1926,"position":2},"title":"How to rewrite a MongoDB C# LINQ with a Projection Requirement using a MongoCursor","date":"January 26, 2013","format":false,"excerpt":"The LINQ Provider for MongoDB does not currently take into account data projections efficiently when returning data. This could mean that you\u2019re unnecessarily returning more data from the database than is needed. So, I\u2019m going to show you the pattern I applied as a replacement for the LINQ queries when\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1345,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1345","url_meta":{"origin":1926,"position":3},"title":"Creating a simple Entity Reference system for Ember.js","date":"December 28, 2011","format":false,"excerpt":"I had some data stored in a structure similar to this: Nothing too fancy. A table of Gift(s) and a table of Person(s). Each gift had a foreign key relationship to a Person (the person who \u201cgave\u201d the gift). Since some people may give several gifts, I didn\u2019t want to\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1524,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1524","url_meta":{"origin":1926,"position":4},"title":"Alternative to ApplicationSettings in .NET","date":"February 1, 2012","format":false,"excerpt":"After dealing with lost settings, an unclear upgrade path, and my own confusion surrounding the magic of Settings in a .NET client application, I decided to build my own. You\u2019re probably familiar with this UI in Visual Studio. It hasn\u2019t changed much since it was first created: A list of\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"image","src":"https:\/\/i0.wp.com\/www.wiredprairie.us\/blog\/wp-content\/uploads\/2012\/02\/image.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1701,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1701","url_meta":{"origin":1926,"position":5},"title":"Windows 8 WinRT\/Metro Missing UpdateSourceTrigger","date":"August 5, 2012","format":false,"excerpt":"If you\u2019ve done WPF or Silverlight programming, you may have found an occasion where using the Binding property UpdateSourceTrigger set to PropertyChanged was extremely useful. (I know I have!) It may have looked something like this:  The key feature was the live updating of the\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1926"}],"collection":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/comments?post=1926"}],"version-history":[{"count":3,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1926\/revisions"}],"predecessor-version":[{"id":2387,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1926\/revisions\/2387"}],"wp:attachment":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/media?parent=1926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/categories?post=1926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/tags?post=1926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
First, it creates the SQLite connection (nuget<\/a>) and then configures the DapperExtensions to use the SqliteDialect. What that means is that the extensions will generate SQL that actually works! :) Finally, in my case, I wanted the DB to be created if it didn\u2019t exist, so I set FailIfMissing<\/strong> to False<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"