SQLITE and Windows Forms

(Sqlite)


Hi,

Recently i got an project from client on windows forms and sqlite database. I share that project in this article, if you are looking for an windows form and sqlite database solution. This article might help you.

I place the connectionstring app.config file the connection string is


<appSettings>
    <add key="constring" value="data source=C:\DATABASE\Configdb.s3db; Version=3;"/>
  </appSettings>


I refer this connectionstring in dal methods using the following code


public static string ConnectionString = ConfigurationSettings.AppSettings["constring"];


Like the sql server management studio, To manipulate sqlite3 db i used SQLITE Administrator, I attached that in this article, you can download it


You can download the sample project which has the following codes and sqlitedb.


To run this project copy sqlitedb database attached in this project to the following location

C:\DATABASE



Example Code

         private int GetTotalRows()
        {
            string sql = "select count(*) from serviceconfig";

            int rowCount = SQLiteDatabase.GetRowCount(sql);

            return rowCount;
        }


        private void InsertConfig()
        {
            string sqlCommand = "INSERT INTO ServiceConfig(FolderPath, Interval) VALUES (@FolderPath, @Interval)";

            SQLiteParameter[] p = new SQLiteParameter[2];

            p[0] = new SQLiteParameter("@FolderPath", txt_folderpath.Text);

            p[1] = new SQLiteParameter("@Interval", txt_interval.Text);

            int rowAdded = SQLiteDatabase.Insert(sqlCommand, p);

            if (rowAdded == 1)
                label3.Text = "Config Updated Successfully";
            else
                label3.Text = "Error Occurred";
        }


        private void UpdateConfig()
        {
            string sqlCommand = @"UPDATE ServiceConfig SET FolderPath = @FolderPath,
            Interval = @Interval WHERE ID=@ID";

            SQLiteParameter[] p = new SQLiteParameter[3];

            p[0] = new SQLiteParameter("@FolderPath", txt_folderpath.Text);

            p[1] = new SQLiteParameter("@Interval", txt_interval.Text);

            p[2] = new SQLiteParameter("@ID", 2);

            int rowAdded = SQLiteDatabase.Update(sqlCommand, p);

            if (rowAdded == 1)
                label3.Text = "Config Updated Successfully";
            else
                label3.Text = "Error Occurred";
        }

 //SQLITE Class

class SQLiteDatabase
    {
        public static string ConnectionString = ConfigurationSettings.AppSettings["constring"];

        public static int GetRowCount(string sql)
        {
            int result = 0;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
                {
                    result = Convert.ToInt32(cmd.ExecuteScalar());
                }

                connection.Close();
            }
            return result;
        }

        public static int Insert(string sql, SQLiteParameter[] parameters)
        {
            int result = 0;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
                {
                    cmd.Parameters.AddRange(parameters);
                    result = cmd.ExecuteNonQuery();
                }

                connection.Close();
            }
            return result;
        }

        public static int Update(string sql,SQLiteParameter[] parameters)
        {
            int result = 0;
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
                {
                    cmd.Parameters.AddRange(parameters);
                    result = cmd.ExecuteNonQuery();
                }

                connection.Close();
            }
            return result;
        }

        public static Dictionary GetData(string sql)
        {
            Dictionary dic = new Dictionary();
             string path="C:\temp";
                    int interval = 2;

            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
                {
                    SQLiteDataReader rdr = cmd.ExecuteReader();
                    while(rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                            path = rdr.GetValue(0).ToString();

                        string intstr = rdr.GetValue(1).ToString();

                        if(!string.IsNullOrEmpty(intstr))
                        interval = Convert.ToInt32(rdr.GetValue(1));
                    }
                }

                dic.Add(interval, path);

                connection.Close();
            }


            return dic;
          
        }


Ratings


Average Rating: 0.00 by 0 users
Giri Prasad
4/5/2014
4/5/2014
Download PDF
Download Project

Comments






Tom

good one


John

very nice!!


Woofy

ajdkfajsfkasdjfasdklfjalsjfdalskfjlaksjflkjlkjakjjlkjkljja;jsdfklasfjaskfjas;dfkjas;lfjas;lfjas;fdjas;lfjas;fas;f;asfaslkfjaskfjsalkfjlskjflsajfklsajflasjflasjdflkasjfljaljflasjflasjdflakoiwuoiwruowqu


scoofy

good one


tommy

good


test

test


test1

test2


test3

test3


test 4

test 4


test5

test5


test 6

test 7


test8

test8


test9

test9


test 10

test 10