Database for windows form application .NET

Hello All,

Are you creating a windows application in .NET? Have you ever thought that how we will manage our database in windows application because we are not sure that SQL Server will be there[on client machine]? Do you want to keep application’s data with in that system offline and provide control?

Yes, There is a way to achieve the same.

HOW?

In .NET there are several ways to maintain your data on machine itself where your application is installed. Actually there are few software whose requirement is to have there database online[Central] and sync them to local Database. SO lets Discuss More about Local Database.

Majorly, there are 2 popular DB systems are in place:-

  • SQL SEVER COMPACT EDITION
  • SQLITE

Both are having there respective Pros and Cons but I would go with sQLITE here.

  1. First of all Download SQLITE extension from https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki.
  2. Now open Visual Studio and open you windows form application.
  3. Add reference System.Data.SQLITE form .NET component.
  4. create a Class which will be communicating with DB as well as with your application. A Sample is as follows
  5. using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.SQLite;
    
    
    namespace sqllitetest
    {
        class SQLiteDatabase
        {
            String dbConnection;
    
    	/// <summary>
    	///     Default Constructor for SQLiteDatabase Class.
    	/// </summary>
    	public SQLiteDatabase()
    	{
    		dbConnection = "Data Source=sqlDB";
    	}
    
    	/// <summary>
    	///     Single Param Constructor for specifying the DB file.
    	/// </summary>
    	/// <param name="inputFile"/>The File containing the DB
    	public SQLiteDatabase(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
    	public SQLiteDatabase(Dictionary<string , String> connectionOpts)
    	{
    		String str = "";
    		foreach (KeyValuePair</string><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
    	/// <returns>A DataTable containing the result set.</returns>
    	public DataTable GetDataTable(string sql)
    	{
    		DataTable dt = new DataTable();
    		try
    		{
    			SQLiteConnection cnn = new SQLiteConnection(dbConnection);
    			cnn.Open();
    			SQLiteCommand mycommand = new SQLiteCommand(cnn);
    			mycommand.CommandText = sql;
    			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.
    	/// <returns>An Integer containing the number of rows updated.</returns>
    	public int ExecuteNonQuery(string sql)
    	{
    		SQLiteConnection cnn = new SQLiteConnection(dbConnection);
    		cnn.Open();
    		SQLiteCommand mycommand = new SQLiteCommand(cnn);
    		mycommand.CommandText = sql;
    		int 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.
    	/// <returns>A string.</returns>
    	public string ExecuteScalar(string sql)
    	{
    		SQLiteConnection cnn = new SQLiteConnection(dbConnection);
    		cnn.Open();
    		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 name="data"/>A dictionary containing Column names and their new values.
    	/// <param name="where"/>The where clause for the update statement.
    	/// <returns>A boolean true or false to signify success or failure.</returns>
    	public bool Update(String tableName, Dictionary</string><string , String> data, String where)
    	{
    		String vals = "";
    		Boolean returnCode = true;
    		if (data.Count >= 1)
    		{
    			foreach (KeyValuePair</string><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 name="where"/>The where clause for the delete.
    	/// <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 fail)
    		{
    			//MessageBox.Show(fail.Message);
    			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 name="data"/>A dictionary containing the column names and data for the insert.
    	/// <returns>A boolean true or false to signify success or failure.</returns>
    	public bool Insert(String tableName, Dictionary</string><string , String> data)
    	{
    		String columns = "";
    		String values = "";
    		Boolean returnCode = true;
    		foreach (KeyValuePair</string><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 fail)
    		{
    			//MessageBox.Show(fail.Message);
    			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.
    	/// <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;
    		}
    	}       
        }
    }
    
    

You are Done!! Waiting for your Queries and Feedback.

Happy Coding!

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>