The Install

First you will need to download and run the System.Data.SQLite installer. This is an open source ADO.NET provider for SQLite. The install package has the necessary assemblies and Visual Studio plugins to allow you to add a server connection to a SQLite database. Run the installer and follow the wizard.

Tools

The tool that comes with Visual Studio Integration is nice, but sometimes I don’t want to fire up Visual Studio to inspect my SQLite database. I use SQLite Manager as my external managment tool. It is a Firefox plugin that works very nicely and has all the commands necessary to create, update, and manage your SQLite instance.

Code

Time to write some code to access your newly created database. You should have used Visual Studio or SQLite Manager to create a database with a schema. SQLite does not support stored procedures so you are limited to LINQ or writing SQL within code. It kind of smells to have SQL in code, but if you seperate your concerns (three tier architecture) you should be able to contain the smell.

My repository takes a SQLiteConnection. A SQLiteConnection points directly to your file. To Initialize your connection it looks like this.

var connectionString = “Data Source=MyFile.sqlite;Version=3;”;
var connection = new SQLiteConnection(connectionString);
public SQLiteRepository(SQLiteConnection connection)
{
_connection = connection;
}

Now how about executing some sql against the database? Well let’s see how it’s done but you won’t be suprised much, it looks like regular ADO.NET. The variable SqlSelectAll is a regular Select query written in SQL syntax.

public IQueryable< Message > GetAll()
{
var messages = new List();
try
{
_connection.Open();
var command = new SQLiteCommand(SqlSelectAll, _connection);
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var message = new Message();
message.Id = (long) reader["Id"];
message.Subject = (string) reader["Subject"];
message.Text = (string) reader["Text"];
messages.Add(message);
}
}
}
finally
{
_connection.Close();
}
return messages.AsQueryable();
}

The one gotcha is that you must remember to Open the connection or else your attempt to retrieve data will fail miserably.

How about a parameterized query execution sample? Well I got one for that as well. Again the SqlDelete variable is just a simple delete in SQL syntax.

public void Remove(Message message)
{
try
{
_connection.Open();
var command = new SQLiteCommand(SqlDelete, _connection);
command.Parameters.Add(“@Id”, DbType.Int32).Value = message.Id;
command.ExecuteNonQuery();
}
finally
{
_connection.Close();
}
}

Conclusion

Compact databases are just the right tool in a lot of instances, especially when building windows based applications but that doesn’t mean they aren’t also great for the web. Look at your data saving needs and you’ll probably realize you could probably go with SQLite very easily.
Update – March 10th 2009

If you are developing for a x64 based system don’t forget to use the proper assemblies when deploying your projects. The current SQLite libraries are compiled for x86 systems and will not work in a x64 system. Read more about it at the SQLite ADO.NET Forums .

(From Microsoft SQL Server 2005 Database Encryption)

This is a how-to guide which will aims to help ms sql server developers and ms sql server administrators to implement Microsoft SQL Server 2005 Encryption methodologies.
This tutorial is a step-by-step guide for encryption and decryption in MS SQL Server 2005 and later.

Creating Master Key

Before using encryption algoritms in SQL Server 2005 and SQL Server 2008, a master key should be created in the database where encryption is going to be used.
Note that master key is created seperately for each database on a SQL Server database instance
Before creating a master key, sql developers or sql server database administers that has the required permissions can run the below t-sql select query to see if a master key is created before.

SELECT * FROM sys.symmetric_keys

If there has been created a master key, you will see a result that is similar to below if you are running the t-sql select from sys.symmetric_keys view in MS SQL Server 2005,
database master key from sys.symmetric_keys

It is important that for a database in MS SQL Server, there can be only one master key in other words a single master key can be created on a database. A second master key can not be created in a sql server database.
You can use the below t-sql script code in order to create a master key in the sql database.

/*************** CREATE MASTER KEY *********************************/
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N’##MS_DatabaseMasterKey##’
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$EncryptionPassword12′
GO

It is important that you keep the encryption password in a safe place or keep backups of your sql server database master key.
I’m going to deal with master key backup later in this tutorial.
You can drop or remove an existing master key using the DROP MASTER KEY t-sql command.
If you try to drop a master key which has been used for creating other database objects like certificates the DROP MASTER KEY sql command will fail.

If you run the DROP MASTER KEY sql server statement after a certificate is defined which we will see in the next step, the following error message is going to be informing the sql server programmer about the dependent certificate.

Msg 15580, Level 16, State 1, Line 2
Cannot drop master key because certificate ‘PasswordFieldCertificate’ is encrypted by it.
Creating a Certificate

The second step for using encryption in a SQL Server database is creating the certificates that will be used for creating symmetric keys and encrypting database table column fields.
So creating a certificate is still a preparation step in the encryption process.
Encrypting table column values or sql variables is still a few steps ahead.
You can also view existing certificates in a MS SQL Server database by running a select query over sys.certificates view.

SELECT * FROM sys.certificates

You can run the below t-sql script to create a certificate.

/*************** CREATE CERTIFICATE *********************************/
IF NOT EXISTS (
SELECT * FROM sys.certificates WHERE name = N’PasswordFieldCertificate’
)
CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = ‘Password Fields’;
GO

You can also drop or remove an existing certificate from a database using the DROP CERTIFICATE certificate_name tsql syntax.

If you try to drop a certificate that is used during the creation of a symmetric key, etc. the following error message is going to be thrown by the SQL Server engine.

Msg 15352, Level 16, State 1, Line 2
The certificate cannot be dropped because one or more entities are either signed or encrypted using it.
Creating a Symmetric Key

After the certifates are created in the sql database, next the symmetric key is being generated by executing a CREATE SYMMETRIC KEY SQL Server command.
Again, you can check sys.symmetric_keys view name fields whether a key already exists and declared in the current database.
The following sql code script is enough to create a symmetric key which will be used for encryption and for decryption in the sql database.

/*************** CREATE SYMMETRIC KEY *********************************/
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;

sys.symmetric_keys view

If you try to create symmetric key with a name that exists among the sys.symmetric_keys system view, the following error message will be displayed:

Msg 15282, Level 16, State 1, Line 1
A key with name ‘PasswordFieldSymmetricKey’ or user defined unique identifier already exists or you do not have permissions to create it.

In the above CREATE SYMMETRIC KEY t-sql statement one important point is the algorithm parameter that is specified in the command.
Microsoft SQL Server can use the following algorithms in encryption sensitive data.
DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 and AES_256
As system administrators or database admins, one important note for AES_128, AES_192 and AES_256 is AES encryption algorithm can not be used on Microsoft Windows 2000 Servers and Windows XP operating systems. If you have a MS SQL Server instance running on a Win2k server, then it is better to create the symmetric keys using the TRIPLE_DES algorithm, for instance. Otherwise, your script will fail when it is run on a sql server which is installed on Windows 2000 servers and Windows XP computers since AES is not supported on those operating systems. You should consider this point while choosing an encryption algorithm for your SQL Server database applications.
Preparing Database Tables to Store Encrypted Data

Now it is time to create a table column field which will store or keep the encrypted values in it for us.
Since I’m working on AdventureWorks database which I have downloaded and installed as a sample database for MS SQL Server 2008, I’ll try to alter a table in AdventureWorks db and add a new column for storing encrypted data.
Since password fields is not open or visible in Person.Contact table, I give up searching for a password field in any of tables in AdventureWorks. I want to encrypt EmailAddress data column values in Person.Contact table.
I’m adding a table column named EncryptedEmailAddress which is declared as varbinary data type and 256 bytes in length using the below ALTER TABLE sql command.
If this varbinary field is not enough in length or size to store the resultant encrypted data, the encryption sql commands will generate an error.

ALTER TABLE Person.Contact
ADD EncryptedEmailAddress varbinary(256);
GO

Now run the below select query and observe that the Encrypted e-mail address column is null. We have not updated this encrypted field yet.

SELECT EmailAddress, EncryptedEmailAddress FROM Person.Contact
Encrypting Sensitive Data

Here is the final step for encrypting a table field using MS SQL Server encryption algoritms and methods.
An encryption can be done on a string or binary value (in nvarchar, varchar, varbinary, nchar, char, binary sql data types) in SQL Server using the EncryptByKey t-sql function.
EncryptByKey encrypts a given data by using a symmetric key.
The necessary symmetric key information can be passed to the EncryptByKey function using the Key_GUID Transact-SQL function. Key_GUID returns the uniqueidentifier (GUID) of the symmetric key whose key name is specified in the Key_GUID function.
The output of the encryption function EncryptByKey is a varbinary with a maximum lenth 8000 bytes.

Since EncryptByKey t-sql command requires a symmetric key, if you execute the EncryptByKey command without openning the symmetric key, the EncryptByKey function will return NULL values during the encrypting calls.
Once the symmetric key is opened in a session, EncryptByKey will function properly in that session. This means as a sql server developer after opening the symmetric key in a session, you can then call the encryption and also the decryption functions more than once successfully.
Also, if you plan to encrypt data and write it on a table or store encrypted data in a stored procedure or in a user defined function (udf), you can open the related symmetric key once at the beginning of the stored procedure or user-defined function t-sql codes, and then in the following lines of sql codes you can execute the EncryptByKey and the DecryptByKey Transact-SQL commands.

/*************** ENCRYPT SENSITIVE DATA *********************************/
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
UPDATE Person.Contact SET EncryptedEmailAddress = EncryptByKey(Key_GUID(‘PasswordFieldSymmetricKey’), EmailAddress);
SELECT EmailAddress, EncryptedEmailAddress FROM Person.Contact
GO

Encrypted Email Address field in AdventureWorks sample SQL Server database
Decrypting Encrypted Data

Of course, if you are encrypting your data in Microsoft SQL Server 2005 or the new version SQL2008 aka Katmai, you may also want to decrypt the encrypted data.
As well as encrypting, decrypting can be handled using t-sql commands and functions in SQL Server. DecryptByKey t-sql function decrypts data using a symmetric key definen in the current sql database.
You can call the DecryptByKey function passing the encrypted data in varbinary data type. Just as similar to EncryptByKey requires an opened symmetric key in the current session, DecryptByKey function also requires a symmetric key that has been opened in the current session before it is called.

Therefore, the t-sql codes below are executed before making any call to EncryptByKey or DecryptByKey t-sql functions.

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
/*************** DECRYPT *********************************/
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
SELECT
EmailAddress,
EncryptedEmailAddress,
CONVERT(nvarchar, DecryptByKey(EncryptedEmailAddress)) AS ‘Decrypted Email Address’
FROM Person.Contact;
GO

Original, Encrypted and Decrypted Email Address column values

One last important point for decrypting encrypted data on SQL Server is that as a sql programmer or administrator, you should take care for the original data type that is encrypted and the target data type that the decrypted data is going to be converted.
Since DecryptByKey function returns data in varbinary data type up to 8000 bytes, if you convert this decrypted varbinary data to nvarchar sql data type you get different result when compared to decrypted varbinary data converted to varchar.
So, if you are encrypting nvarchar data, decrypt and convert it back to nvarchar. Same for varchar data type also. if you are encrypting varchar data, decrypt and convert it back to varchar. Otherwise, you will not get expected results from implemented decryption algorithm.

Let’s look at the following select script where encryption and decryption takes place for both varchar and nvarchar data types.
You will see if nvarchar data is converted back to nvarchar then result is correct. Same is true for also varchar data.

SELECT
– Encrypt ‘Varchar’ string and then decrypt encrypted data
CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID(‘PasswordFieldSymmetricKey’), ‘Varchar’))),
CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID(‘PasswordFieldSymmetricKey’), ‘Varchar’))),
– Encrypt N’NVarchar’ string and then decrypt encrypted data
CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID(‘PasswordFieldSymmetricKey’), N’NVarchar’))),
CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID(‘PasswordFieldSymmetricKey’), N’NVarchar’)))

converting decrypted data back to original encrypted data type
Using Parameters in EncryptByKey and DecryptByKey T-SQL Functions

I have used string values or database table column names in the previous examples. But sql variables can also be used for encryption and decryption functions.

DECLARE @sample_data nvarchar(MAX)
SET @sample_data = N’This is a step-by-step guide summarizing SQL Server Encryption’
SELECT
CONVERT(nvarchar(MAX), DecryptByKey(EncryptByKey(Key_GUID(‘PasswordFieldSymmetricKey’), @sample_data)))

Using Parameters in EncryptByKey and DecryptByKey T-SQL Functions

In this step-by-step guide or SQL Server encryption and decryption tutorial, we have implemented SQL Server data encryption and encryption metodologies with sample t-sql codes in a simple application in Microsoft SQL Server 2005 and MS SQL Server 2008. Encryption and Decryption is an important aspect for database security in sql database development.

Summary sript:

/*************** CREATE MASTER KEY *********************************/
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N’##MS_DatabaseMasterKey##’
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$EncryptionPassword12′
GO
/*************** CREATE CERTIFICATE *********************************/
IF NOT EXISTS (
SELECT * FROM sys.certificates WHERE name = N’PasswordFieldCertificate’
)
CREATE CERTIFICATE PasswordFieldCertificate WITH SUBJECT = ‘Password Fields’;
GO
/*************** CREATE SYMMETRIC KEY *********************************/
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE PasswordFieldCertificate;
GO
SELECT * FROM sys.certificates
GO
CREATE TABLE #temp
(
ID int NOT NULL identity(1,1),
Data nvarchar(100) NOT null,
EncryptedData varbinary(256) NOT null
)

/*************** ENCRYPT SENSITIVE DATA *********************************/
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;

INSERT INTO #temp (Data, EncryptedData)
SELECT N’test data’
, EncryptByKey(Key_GUID(‘PasswordFieldSymmetricKey’), N’test data’)

INSERT INTO #temp (Data, EncryptedData)
SELECT N’another test data’
, EncryptByKey(Key_GUID(‘PasswordFieldSymmetricKey’), N’another test data’)

SELECT * FROM #temp

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
/*************** DECRYPT *********************************/
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
SELECT Data, EncryptedData,
CONVERT(nvarchar, DecryptByKey(EncryptedData)) AS ‘Decrypted Data’
FROM #temp

DROP TABLE #temp

GO
rollback

GRANT CREATE ANY DATABASE to Developer
- If you have CREATE ANY DATABASE permission then you can create databases but only drop/alter the databases you own.

1. Clear buffers and caches before execution:
   DBCC FREEPROCCACHE
   DBCC DROPCLEANBUFFERS
2. Enable statistics to get every query execution time:
   SET STATISTICS TIME ON
   – your queries
   SET STATISTICS TIME OFF
3. For total execution time (when you have multiple queries):
   DECLARE @startTime DateTime,
   DECLARE @endTime DateTime
   SET @startTime = CURRENT_TIMESTAMP
   – your queries here
   SET @endTime = CURRENT_TIMESTAMP
   SELECT DATEDIFF(MS, @startTime, @endTime) Time

Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc…

With the introduction of DMV’s in SQL Server 2005 getting this information is quite easy with this query:

 SELECT  L.request_session_id AS SPID,
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName,
        P.object_id AS LockedObjectId,
        L.resource_type AS LockedResource,
        L.request_mode AS LockType,
        ST.text AS SqlStatementText, ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

 Let’s look at it one DMV at a time from top to bottom:

sys.dm_tran_locks:

Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.

sys.partitions:

Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values ‘HOBT’, ‘Page’, ‘RID’  and ‘Key’. With this join we get the object_id of our locked table.

sys.objects:

Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.

sys.dm_exec_sessions:

Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.

sys.dm_tran_session_transactions:

Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.

sys.dm_tran_active_transactions:

Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.

sys.dm_exec_connections:

Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.

sys.dm_exec_sql_text:

Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.

 By applying the filter in the where clause you get the answers to questions like:

- What SQL Statement is causing the lock?

- Which user has executed the SQL statement that’s holding the locks?

- What objects/tables are being locked?

- What kinds of locks are being held and on which pages, keys, RID’s?

- etc…

This is a pretty common situation that comes up when performing database operations.  A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.  If we refer to the Books Online documentation, it gives examples that are similar to:

IF EXISTS (SELECT * FROM Table1 WHERE Column1=’SomeValue’)
UPDATE Table1 SET (…) WHERE Column1=’SomeValue’
ELSE
INSERT INTO Table1 VALUES (…)

This approach does work, however it might not always be the best approach.  This will do a table/index scan for both the SELECT statement and the UPDATE statement.  In most standard approaches, the following statement will likely provide better performance.  It will only perform one table/index scan instead of the two that are performed in the previous approach.

UPDATE Table1 SET (…) WHERE Column1=’SomeValue’
IF @@ROWCOUNT=0
INSERT INTO Table1 VALUES (…)

The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way.  Also (as I re-learned recently), with any database operation, it is good to performance test the different approaches that you take.  Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way.