Using C# to interface with SQLite

If you need to interface with SQLite there are a couple of steps.

1) You need to get the .net provider for SQLite from sourceforge.net
2) Then add a reference to System.Data.SQLite to your project.
3) You need to make sure the reference is marked to be copied locally.

Here is a C# class to for doing select, insert, update, and delete.

 public class SqlLiteHelper
    {
        String dbConnection;

        /// <summary>
        ///     Single Param Constructor for specifying the DB file.
        /// </summary>
        /// <param name="inputFile">The File containing the DB</param>
        public SqlLiteHelper(String inputFile)
        {
            dbConnection = String.Format("Data Source={0}", inputFile);
        }

        /// <summary>
        ///     Single Param Constructor for specifying advanced connection options.
        /// </summary>
        /// <param name="connectionOpts">A dictionary containing all desired options and their values</param>
        public SqlLiteHelper(Dictionary<String, String> connectionOpts)
        {
            String str = "";
            foreach (KeyValuePair<String, String> row in connectionOpts)
            {
                str += String.Format("{0}={1}; ", row.Key, row.Value);
            }
            str = str.Trim().Substring(0, str.Length - 1);
            dbConnection = str;
        }


        /// <summary>
        ///     Allows the programmer to run a query against the Database.
        /// </summary>
        /// <param name="sql">The SQL to run</param>
        /// <returns>A DataTable containing the result set.</returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();
                    using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        using (SQLiteDataReader reader = mycommand.ExecuteReader())
                        {
                            dt.Load(reader);
                            reader.Close();
                        }
                    }
                    cnn.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return dt;
        }

        /// <summary>
        ///     Allows the programmer to interact with the database for purposes other than a query.
        /// </summary>
        /// <param name="sql">The SQL to be run.</param>
        /// <returns>An Integer containing the number of rows updated.</returns>
        public int ExecuteNonQuery(string sql)
        {
            int rowsUpdated = 0;

            using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
            {
                cnn.Open();
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    mycommand.CommandText = sql;
                    rowsUpdated = mycommand.ExecuteNonQuery();
                }
                cnn.Close();
            }
            return rowsUpdated;
        }

        /// <summary>
        ///     Allows the programmer to retrieve single items from the DB.
        /// </summary>
        /// <param name="sql">The query to run.</param>
        /// <returns>A string.</returns>
        public string ExecuteScalar(string sql)
        {
            using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
            {
                cnn.Open();
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    mycommand.CommandText = sql;
                    object value = mycommand.ExecuteScalar();
                    cnn.Close();
                    if (value != null)
                    {
                        return value.ToString();
                    }
                }
            }
            return "";
        }

        /// <summary>
        ///     Allows the programmer to easily update rows in the DB.
        /// </summary>
        /// <param name="tableName">The table to update.</param>
        /// <param name="data">A dictionary containing Column names and their new values.</param>
        /// <param name="where">The where clause for the update statement.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete rows from the DB.
        /// </summary>
        /// <param name="tableName">The table from which to delete.</param>
        /// <param name="where">The where clause for the delete.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Delete(String tableName, String where)
        {
            Boolean returnCode = true;
            try
            {
                this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
            }
            catch (Exception ex)
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily insert into the DB
        /// </summary>
        /// <param name="tableName">The table into which we insert the data.</param>
        /// <param name="data">A dictionary containing the column names and data for the insert.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
            }
            catch (Exception ex)
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete all data from the DB.
        /// </summary>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearDB()
        {
            DataTable tables;
            try
            {
                tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
                foreach (DataRow table in tables.Rows)
                {
                    this.ClearTable(table["NAME"].ToString());
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        ///     Allows the user to easily clear all data from a specific table.
        /// </summary>
        /// <param name="table">The name of the table to clear.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearTable(String table)
        {
            try
            {

                this.ExecuteNonQuery(String.Format("delete from {0};", table));
                return true;
            }
            catch
            {
                return false;
            }
        }
    }

How to get a list of all identity columns in a database

Here is some code to get a list of identity columns in database.

select  so.name as TableName,  + o.list as IdentityColumnName 
from    sysobjects so
cross apply
    (SELECT 
        column_name
         + 
           case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        ''
        end + ' '
     from information_schema.columns where table_name = so.name
    ) o (list)
where   xtype = 'U'
AND o.list is not null
AND name    NOT IN ('dtproperties')

Check if a database exists on a server

Here is some code to use to check if a database exists.
Just replace the string 'DATABASE NAME’.

SELECT * FROM [master].[sys].[databases] WHERE name='DATABASE NAME'

How to switch Entity Framework database connected to

If you have a connection to a database through entity framework and you need to switch it to another database (with the exact same structure) you just need to set the Connection.ConnectionString (as seen below).
I had an application where we created a copy of the Master database when setting up a new client.  So using Entity Framework I switched from the master database to the client (depending on what the admin was doing).

using (MasterEntities aEntities = new MasterEntities())
{

//Switch db connected to

aEntities.Database.Connection.ConnectionString = aEntities.Database.Connection.ConnectionString.Replace("OldDatabaseName", "NewDatabaseName"); //Some Query

}

Get list of all default values for every table

This script will list all default values for every table.

SELECT ao.name AS TABLE_NAME, dc.name AS CONSTRAINT_NAME, dc.definition AS DEFAULT_VALUE, ac.name AS COLUMN_NAME 
FROM sys.default_constraints AS dc 
INNER JOIN sys.all_columns AS ac ON dc.parent_object_id = ac.object_id 
AND dc.parent_column_id = ac.column_id 
INNER JOIN sys.all_objects AS ao ON ac.object_id = ao.object_id


List all indexes in a database

This script will list all indexes and their types (clustered/nonclustered).

SELECT  so.name AS TableName
            , si.name AS IndexName
            , si.type_desc AS IndexType
FROM            sys.indexes si 
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE so.type = 'U'    --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY so.name, si.type 

List all stored procedures in a database

This script will provide a list of all stored procedures in a database along with the create script for each.

SELECT specific_name, routine_definition 
  FROM information_schema.routines 
 WHERE routine_type = 'PROCEDURE'
 

List all synonyms in a database

This script will list all synonyms you have in a database.  It will tell you the name, the server connecting to, the database, schema, and object name your connecting to.

This is helpful to figure out if you have your synonyms pointing to the wrong environment.

SELECT name, 
COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName, 
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName, 
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName, 
PARSENAME(base_object_name,1) AS objectName 
FROM sys.synonyms 
ORDER BY serverName,dbName,schemaName,objectName

Summary of last night’s job run

Here is a script that will provide a summary of jobs that ran last night.
This is helpful if you want a summary of your jobs instead of an email from each job.

SELECT
    j.name, 
    last_run_outcome = 
    CASE 
        WHEN js.last_run_outcome = 0 THEN 'Failed' 
        WHEN js.last_run_outcome = 1 THEN 'Succeeded' 
        WHEN js.last_run_outcome = 2 THEN 'Retry' 
        WHEN js.last_run_outcome = 3 THEN 'Canceled' 
        ELSE 'Unknown' 
    END, 
    last_run_datetime = msdb.dbo.agent_datetime( 
        CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
        CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) 
FROM msdb.dbo.sysjobs j 
JOIN msdb.dbo.sysjobsteps js 
ON j.job_id = js.job_id
WHERE msdb.dbo.agent_datetime(CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
                            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) > DATEADD (dd , -1 , GETDATE())
ORDER BY Name

List all Primary Keys in a database

This script will list all primary keys in a database.

SELECT ST.name AS TABLE_NAME, SKC.name AS CONSTRAINT_NAME, SC.name AS CONSTRAINT_COLUMN_NAME, SC.column_id AS COLUMN_ORDER 
FROM sys.key_constraints AS SKC 
INNER JOIN sys.tables AS ST ON ST.object_id = SKC.parent_object_id 
INNER JOIN sys.index_columns AS SIC ON SIC.object_id = ST.object_id AND SIC.index_id = SKC.unique_index_id 
INNER JOIN sys.columns AS SC ON SC.object_id = ST.object_id AND SC.column_id = SIC.column_id WHERE (LEFT(ST.name, 1) = N't') AND (ST.type = N'U')