How to convert Data
Set to the SQL Lite DB file in Asp.net C#
Introduction:-
Actually the article
is use full for both Android and C# users ,because all the android devices are
working with open source DB SQL Lite .All the android users are usually
use to create database from SQL Lite .From that we are processing the
operation (pushing that data in to main Data base).
For overcome all these
situation we tackled this code.
Step1:-
Download the SQL Lite DLL file and add the reference
using System.Data.SQLite;
Step2:- Make
sure all name spaces are mandatory
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 System.IO;
using System.Data.SQLite;
using System.Data.Common;
Step3:-Create one Dynamic data set for same purpose
passing to that method
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("Sno");
dt.Columns.Add("Name");
dt.Columns.Add("Marks");
dt.Columns.Add("Status");
dt.Rows.Add(1, "Lokesh", "B.Tech",
10);
dt.Rows.Add(2, "Vijay", "Msc",
50);
dt.Rows.Add(3, "Balaji", "MS",
61);
dt.Rows.Add(4, "Kishore", "B.Tech",
90);
ds.Tables.Add(dt);
Step4:-Create the SQL Lite Conversion Method
private static string
SaveAsSqlite(DataSet ds)
and also add the
supported methods
private static string
GetColumnType(Type type)
private static DbType
GetColumnDbType(Type type)
In this code we gave one sample
path for saving that DB file in some particular location
string
directoryPath = HttpContext.Current.Server.MapPath("~/TempSql");
The who .cs file is
look like
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 System.IO;
using System.Data.SQLite;
using System.Data.Common;
namespace SampleTestingCodes
{
public partial
class CreateSQLiteFileFromDataSet
: System.Web.UI.Page
{
/// <summary>
/// Saves as sqlite.
/// </summary>
/// <param
name="fileName">Name of the file.</param>
/// <returns></returns>
private
static string
SaveAsSqlite(DataSet ds)
{
string
tableQuery = string.Empty, insertQuery = string.Empty, valueQuery = string.Empty;
try
{
//You
can access this path also like this way
//string
directoryPath = HttpContext.Current.Server.MapPath("../TempSql");
//
string
directoryPath = HttpContext.Current.Server.MapPath("~/TempSql");
DirectoryInfo
sqlFolder = new DirectoryInfo(directoryPath);
if
(!sqlFolder.Exists)
{
sqlFolder.Create();
}
string
fileName = string.Format("SampleSQLite_{0}{1}{2}{3}{4}{5}_{6}.db",
DateTime.Now.Day, DateTime.Now.Month,
DateTime.Now.Year, DateTime.Now.Hour,
DateTime.Now.Minute, DateTime.Now.Second, "Lokesh");
SQLiteConnection
con = new SQLiteConnection();
con.ConnectionString = new DbConnectionStringBuilder()
{
{"Data
Source", HttpContext.Current.Server.MapPath("~/TempSql") + "/"
+ fileName},
{"Version", "3"},
{"FailIfMissing", "False"},
}.ConnectionString;
con.Open();
//
Create a table in the database to receive the information from the DataSet
SQLiteCommand
cmd = new SQLiteCommand(con);
foreach
(DataTable table in
ds.Tables)
{
cmd.CommandText = "DROP TABLE IF EXISTS " +
table.TableName;
cmd.ExecuteNonQuery();
tableQuery = "CREATE TABLE " + table.TableName + "(";
insertQuery = string.Format("INSERT
INTO {0} (", table.TableName);
valueQuery = " VALUES(";
foreach
(DataColumn column in
table.Columns)
{
tableQuery += string.Format("{0}
{1},", column.ColumnName, GetColumnType(column.DataType));
insertQuery += string.Format("{0},",
column.ColumnName);
valueQuery += string.Format("@{0},",
column.ColumnName);
}
tableQuery = tableQuery.Substring(0,
tableQuery.Length - 1);
tableQuery += ")";
cmd.CommandText =
tableQuery;
cmd.ExecuteNonQuery();
insertQuery =
insertQuery.Substring(0, insertQuery.Length - 1);
insertQuery += ")";
valueQuery =
valueQuery.Substring(0, valueQuery.Length - 1);
valueQuery += ")";
cmd.CommandTimeout = 0;
cmd.CommandText =
insertQuery + valueQuery;
cmd.Parameters.Clear();
foreach
(DataColumn column in
table.Columns)
{
cmd.Parameters.Add("@" + column.ColumnName,
GetColumnDbType(column.DataType));
}
foreach
(DataRow row in
table.Rows)
{
for (int i = 0; i <
row.ItemArray.Length; i++)
{
cmd.Parameters[i].Value = row[i];
}
cmd.ExecuteNonQuery();
}
}
cmd.Dispose();
con.Close();
con.Dispose();
return
"FILENAME:" + fileName.Replace(".db", string.Empty);
}
catch
{
throw;
}
}
/// <summary>
/// Gets the type of the column.
/// </summary>
/// <param
name="type">The type.</param>
/// <returns>Returns the sqlite data type</returns>
private
static string
GetColumnType(Type type)
{
string
dataType = string.Empty;
if
(type.Equals(typeof(int)))
{
dataType = "INTEGER";
}
else
if (type.Equals(typeof(float)) || type.Equals(typeof(double)) || type.Equals(typeof(decimal)))
{
dataType = "REAL";
}
else
if (type.Equals(typeof(byte[])))
{
dataType = "BLOB";
}
else
{
dataType = "TEXT";
}
return
dataType;
}
/// <summary>
/// Gets the type of the column.
/// </summary>
/// <param
name="type">The type.</param>
/// <returns>Returns the sqlite data type</returns>
private
static DbType
GetColumnDbType(Type type)
{
if
(type.Equals(typeof(int)))
{
return
DbType.Int32;
}
else
if (type.Equals(typeof(float)) || type.Equals(typeof(double)) || type.Equals(typeof(decimal)))
{
return
DbType.Decimal;
}
else
if (type.Equals(typeof(byte[])))
{
return
DbType.Binary;
}
else
{
return
DbType.String;
}
}
}
}