SQLite + C#

    Недавно произошел мой первый опыт работы с Базами данных (БД). И в этой статье хотел бы рассказать о работе с SQLite в C#.

1. Что нужно знать?

    Во-первых нужно быть знакомым с языком SQL. Могу посоветовать хорошую справку с примерами по SQL - http://www.w3schools.com/sql .
    Во-вторых иметь представление о том что такое БД.


2. Что нам понадобится?

    Использовать мы будем SQLite - легковесная встраиваемая реляционная база данных. Слово «встраиваемый» означает, что SQLite не использует парадигму клиент-сервер, то есть движок SQLite не является отдельно работающим процессом, с которым взаимодействует программа, а предоставляет библиотеку, с которой программа компонуется и движок становится составной частью программы

    Ссылка на SQLite - SQLite-1.0.66.0 (Скачать и установить).


3. Приступаем к работе.

    Предлагаю написать простенькую программу - Телефонный справочник. В нём будут храниться имена людей и ихние номера телефонов.
    С помощью Sqliteman-1.2.1, вы можете легко создать файл БД, и таблицу.
    Структура таблицы:

    Класс Item описывает структуру элемента в таблице:

    Листинг Item.cs:
using System;

namespace TBook
{
    public class Item
    {
        string _name;
        int _phoneNumber;
        int _id;

        public Item(int id, string name, int phoneNumber)
        {
            _id = id;
            _name = name;
            _phoneNumber = phoneNumber;
        }

        public int ID
        {
            get
            {
                return _id;
            }
        }

        public string Name
        {
            get
            {
                return _name;
            }
        }

        public int PhoneNumber
        {
            get
            {
                return _phoneNumber;
            }
        }
    }
}

    Для того чтобы работать с SQLite, добавляем в проект ссылку "System.Data.SQLite". (Проект-> Добавить ссылку). 
    Теперь напишем класс TelephoneBook, в котором реализуем основные методы - Добавление в БД, удаление из БД, очистка таблицы, получение данных из БД.

    Листинг TelephoneBook.cs :
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

namespace TBook
{
    class TelephoneBook
    {
        const string TABLENAME = "telbook"; // имя моей таблицы

        string _dbConnection;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="path">Path to database file.</param>
        public TelephoneBook(string path)
        {
            _dbConnection = "Data Source=" + path;
        }

        /// <summary>
        /// Method to add item to database
        /// </summary>
        /// <param name="name">Name.</param>
        /// <param name="number">Phone number.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Add(string name, int number)
        {
            try
            {
                string sqlCommand = "INSERT INTO " + TABLENAME
                    + " (name, number) VALUES (@name, @number)";
                SQLiteConnection cnn = new SQLiteConnection(_dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(sqlCommand, cnn);
                mycommand.Parameters.Add("@name", DbType.String).Value = name;
                mycommand.Parameters.Add("@number", DbType.Int32).Value = number;
                mycommand.ExecuteNonQuery();
                cnn.Close();
            }
            catch(Exception)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Method to delete item from database
        /// </summary>
        /// <param name="id">ID of item</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Delete(int id)
        {
            try
            {
                string sqlCommand = "delete from " + TABLENAME + " where id=@id";
                SQLiteConnection cnn = new SQLiteConnection(_dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(sqlCommand, cnn);
                mycommand.Parameters.Add("@id", DbType.Int32).Value = id;
                mycommand.ExecuteNonQuery();
                cnn.Close();
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Method gets table of items by name
        /// </summary>
        /// <param name="name">Name.</param>
        /// <returns></returns>
        public DataTable GetTable(string name)
        {
            DataTable dt = new DataTable();
            try
            {
                string sqlCommand = "SELECT * FROM " + TABLENAME
                    + " WHERE name like @name";
                SQLiteConnection cnn = new SQLiteConnection(_dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(sqlCommand, cnn);
                mycommand.Parameters.Add("@name", DbType.String).Value = '%' + name + '%';
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                cnn.Close();
            }
            catch (Exception)
            {
                throw new SQLiteException();
            }
            return dt;
        }

        /// <summary>
        /// Method gets items by name
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<Item> GetItemList(string name)
        {
            return ConvertToItemList(GetTable(name).Rows);
        }

        /// <summary>
        /// Method to clear table
        /// </summary>
        public void ClearTable()
        {
            string sqlCommand = "delete from " + TABLENAME;
            SQLiteConnection cnn = new SQLiteConnection(_dbConnection);
            cnn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(sqlCommand, cnn);
            mycommand.ExecuteNonQuery();
            cnn.Close();
        }

        /// <summary>
        /// Method to convert DataRowCollection to List of Item
        /// </summary>
        /// <param name="rows"></param>
        /// <returns></returns>
        private List<Item> ConvertToItemList(DataRowCollection rows)
        {
            List<Item> itemList = new List<Item>();

            for (int i = 0; i < rows.Count; i++)
            {
                itemList.Add(new Item(
                    Convert.ToInt32(rows[i]["id"]),
                    rows[i]["name"].ToString(),
                    Convert.ToInt32(rows[i]["number"])));
            }

            return itemList;
        }
    }
}


   Теперь можно пользоваться классом  TelephoneBook  :
    

TelephoneBook pbook = new TelephoneBook("database");
pbook.Add("Sergey Parakhin", 22321312); // Добавляем запись в БД
pbook.Delete(0); // Удаляем запись с ID равным 0

// Получаем коллекцию всех записей по где встречаеться имя Parakhin:
List<Item> itemList = pbook.GetItemList("Parakhin");
pbook.ClearTable();// Очистка таблицы



    Важно обратить внимание на то как формируется строка  SQL запроса. Во избежания «SQL Injection» атак,  используются параметризованные запросы:
...
string sqlCommand = "INSERT INTO " + TABLENAME
                    + " (name, number) VALUES (@name, @number)";
 ...                
 mycommand.Parameters.Add("@name", DbType.String).Value = name;
 mycommand.Parameters.Add("@number", DbType.Int32).Value = number;
... 

    Собирать строку запроса таким образом не безопасно: 

string sqlCommand = "INSERT INTO " + TABLENAME
        + " (name, number) VALUES (" + name +", " + number + ")";


Я продемонстрировал самый простой пример взаимодействия с БД SQLite на C#.
SQLite очень удобна для прикладных программ с целью хранения данных, т.к. она  не использует парадигму клиент-сервер.  

Так что мы видим, что работать с базами данных совсем несложно, желаю удачи в освоении SQLite, и... да прибудет с вами Сила=)