Problem med database connect i c# sqlite
Hejsa!Jeg sidder og leger lidt med at connecte til min sqllite database fra c#
Jeg har fundet nedenstående klasse hvor jeg stort set for alt koden foræret til at arbejde med databasen.
Jeg har et problem med at initiere klassen
hvis jeg skriver
db = new SQLiteDatabase()
Så for jeg fejlen The name db does not exist in the current context
Jeg har på fornemmelsen at det er en eller anden reference jeg skal bruge udover System.Data.SQLite men jeg kan ikke lige greje det.
Jeg kører visuel Studio 10
c# console applikations projekt
sqlite
Når det virker for mig vil jeg bruge klassen i asp.net og vise db udtræk via website.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
class Consoleapplication2
{
static void Main(string[] args)
{
db = new SQLiteDatabase();
DataTable recipe;
String query = "select * from services";
// The results can be directly applied to a DataGridView control
// Or looped through for some other reason
foreach (DataRow r in recipe.Rows)
{
Console.WriteLine((r["cpr"].ToString()));
}
}
}
class SQLiteDatabase
{
String dbConnection;
/// <summary>
/// Default Constructor for SQLiteDatabase Class.
/// </summary>
public SQLiteDatabase()
{
dbConnection = "Data Source='c:\\sqllite\\dbRIS_2010.db3'";
}
/// <summary>
/// Single Param Constructor for specifying the DB file.
/// </summary>
/// <param name="inputFile">The File containing the DB</param>
public SQLiteDatabase(String inputFile)
{
dbConnection = String.Format("Data Source={0}", inputFile);
}
public SQLiteDatabase(Dictionary<String, String> connectionOpts)
{
String str = "";
foreach (KeyValuePair<String, String> row in connectionOpts)
{
str += String.Format("{0}={1}; ", row.Key, row.Value);
}
str = str.Trim().Substring(0, str.Length - 1);
dbConnection = str;
}
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;
}
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;
}
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 "";
}
public bool Update(String tableName, Dictionary<String, String> data, String where)
{
String vals = "";
Boolean returnCode = true;
if (data.Count >= 1)
{
foreach (KeyValuePair<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;
}
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)
{
Console.WriteLine(fail.Message);
returnCode = false;
}
return returnCode;
}
public bool Insert(String tableName, Dictionary<String, String> data)
{
String columns = "";
String values = "";
Boolean returnCode = true;
foreach (KeyValuePair<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)
{
Console.WriteLine(fail.Message);
returnCode = false;
}
return returnCode;
}
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;
}
}
public bool ClearTable(String table)
{
try
{
this.ExecuteNonQuery(String.Format("delete from {0};", table));
return true;
}
catch
{
return false;
}
}
}
