GET DATA FROM SQLITE AND MYSQL DATABASES

GET DATA FROM SQLITE AND MYSQL DATABASE

Today we will see how to get data from sqlite database and mysql database .

1. DATA FROM SQLITE DATABASE.

To connect sqlite to asp.net we need to download the dll, you can down load the updated dll from the 

web site of sqlite :  http://www.sqlite.org/download.html

OR

Finisar.SQLite

- An ADO.NET Data Provider for SQLite

http://adodotnetsqlite.sourceforge.net/documentation/csharp_example.php

OR

http://sourceforge.net/projects/sqlite-dotnet2/files/SQLite%20for%20ADO.NET%202.0/

Put the dll in your bin folder.

NOTE*: check the version of your sqlite and your framework you are using as well as the 32bit or 64 bit dll you need to download as per your application and database.

THE CODE BEHIND PAGE: 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

//using Finisar.SQLite;

using System.Data.SQLite;

   using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page

{

 public static string GetConnectionString()

{

  string connStr = String.Format("server={0};user id={1}; password={2};" + 

    "database=TESTDB; pooling=false", "localhost",

    "root", "youdbpassword");

  return connStr;

}

    protected void Page_Load(object sender, EventArgs e)

    {

        //connection to sqlite database

        System.Data.SQLite.SQLiteConnection cn = new System.Data.SQLite.SQLiteConnection("Data Source=C:/sqlite/testdb.db;Version=3;New=False;Compress=True;"); 

        try

        {

                      cn.Open();

            SQLiteCommand cmd = new SQLiteCommand("select * from emp", cn);

            DataTable dt = new DataTable();

            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

            da.Fill(dt);

            GridView1.DataSource = dt;

            GridView1.DataBind();

           

        }

        catch

        {

            //display any exeptions  

        }

        finally

        {

            cn.Close();

        }  

  

    }

}

2.CONNECT TO MYSQL DATABASE:

Download the mysql from oracle site :

http://dev.mysql.com/downloads/mysql/

Install as the complete type and you will have the connector/dll required to connect to the asp.net .Or else you need to download the connector from the same above site seperate.

the connector location will be :

C:\Program Files (x86)\MySQL\Connector NET 6.8.3\Assemblies\v4.0

in your PC.

INCLUDE THE mysql.data.dll in your application and rest is done...............

            //connection to mysql database

            MySql.Data.MySqlClient.MySqlConnection mycon =

            new MySqlConnection(GetConnectionString());

            if (mycon.State != ConnectionState.Open)

                try

                {

                    mycon.Open();

                    MySqlCommand cmdmysql = new MySqlCommand("select * from empsql", mycon);

                    MySqlDataAdapter damysql = new MySqlDataAdapter(cmdmysql);

                    DataTable dtmysql = new DataTable();

                    damysql.Fill(dtmysql);

                    GridView2.DataSource = dtmysql;

                    GridView2.DataBind();

                }

                catch (MySqlException ex)

                {

                    throw (ex);

                }