DJUKA

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Web;

namespace WebFormsProjekt.Persistence
{
    public abstract class SqlLiteBaseRepository
    {
        private string _dbPath = HttpContext.Current.Server.MapPath("\\db.sqlite");

        private void InitializeDatabase()
        {
            SQLiteConnection.CreateFile(_dbPath);


            using (var connection = new SQLiteConnection("Data Source =" + _dbPath))
            {
                connection.Open();

                var cmd = new SQLiteCommand("CREATE TABLE users (" +
                        "id integer primary key autoincrement, " +
                        "username text, " +
                        "firstName text," +
                        "lastName text," +
                        "email text," +
                        "password text," +
                        "authority integer" +
                    ");",
                    connection);


                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO users(username, firstName, lastName, email, password, authority) values " +
                    "('Djuro105', 'Tomislav', 'Jansky', 'tjansky@vub.hr', '1234', 2)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO users(username, firstName, lastName, email, password, authority) values " +
                    "('Pero12', 'Janko', 'Grgic', 'grga@vub.hr', 'test', 1)";
                cmd.ExecuteNonQuery();




                var cmd1 = new SQLiteCommand("CREATE TABLE groups (" +
                        "id integer primary key autoincrement, " +
                        "id_prof integer, " +
                        "name text," +
                        "course integer," +
                        "year integer," +
                        "subject text" +
                    ");",
                    connection);

                cmd1.ExecuteNonQuery();

                cmd1.CommandText = "INSERT INTO groups(id_prof, name, course, year, subject) values " +
                    "(1, 'Grupa1-OIP', 0, 1, 'Oip')";
                cmd1.ExecuteNonQuery();
                cmd1.CommandText = "INSERT INTO groups(id_prof, name, course, year, subject) values " +
                     "(1, 'Grupa2-OIP', 0, 1, 'Oip')";
                cmd1.ExecuteNonQuery();
            }
        }

        public SqlLiteBaseRepository()
        {
            if (!File.Exists(_dbPath))
            {
                InitializeDatabase();
            }
        }

        public SQLiteConnection GetDbConnection()
        {
            return new SQLiteConnection("Data Source =" + _dbPath);
        }

    }
}