System.Data.SQLite

Hi all,

 although i read the articles found here i am still not sure about the situatuin concerning SilverLight and this wrapper.

 As far as i can see i have no chance to use the Sqlite dll in my clientside, that's clear,

 but using it by a webservice(not runnin on clientside) should be possible, right ?

 

I tried to do it by

1) Adding the .sqlite file

2) Creating a Connection with the Connectionstring to the .slite file

3) executing some SqliteCommands....

 but however it didn't work

 

Is it possible in this scenario at all ?

 

Lookin forward to your answers, i would be really thankful

 

Best Regards,

Tobi

 Hi Tobi,

I recently tried this, and this does work fine.

I use this with nhibernate on the server.

The problems i encountered were:  (I did not have any experience with a windows website)

- Finding the correct path where the data is on the server

- i needed to give the correct rights to the databases on the server.

 

What problems do you have?  Can you explain this a little bit more?

Regards

Luc

 

Hi Luc, Thanks for your reply. At the Moment i just have a vs2010 project where i added an wcf Service on the Server Side to Access the sqlite Database . I have already done it using sql2008 Server and didnt have any Problems. (i do Not use linqtosqlsclasses, just Standard classes like sqlconnection, sqlcommand etc....) Can u Tell me how to find out the correct path for my sqlitefile ? I just added it in vs on the Server Side and tried different paths but since a Database is generated automatically if it doesnot exist i cannot really tell if the path is correct.... (always getting Model.CommunicationException or sth) How did u do it ? Thanks in Advance, i Hope this Information helps. Regards, Tobi

 Hi again, some additional information:

 i can read from the sqlite file like this

 TextReader reader = new StreamReader("C:/.... .sqlite");

So if think,i dont have a problem with user permissions, right ?

 

But if i try the following:

SqlConnection con = new SqlConnection();

 con.ConnectionStrig = "**same path as up there**"

and then executing a command i get System.ServiceModel.CommunicationException and System.Net.WebException

 

The sqlite file seems to be ok as my sqliteviewer shows everything correctly,

 

i would really be VERY thankful for any ideas

 

Regards,

Tobi

It's not enough to use a streamreader and read from the file.  You need to open the file using a FileStream object, for read/write, and then also be able to open another (new) file in the same folder for read/write/create/delete access.

 

You can find the real path where your site is by using this:

this.Server.MapPath(@"~/Data")

 In this case, i have a subpath Data where my sqlite files are.

 

 Hi again & thank you for the answers

 @Robert

i have done the following which also works:

StreamReader stream = new StreamReader(new FileStream("C:\\Users\\Shorty\\documents\\visual studio 2010\\Projects\\...client.sqlite", FileMode.Open)

string a = stream.ReadLine() + " gelesen";
FileStream f = File.Create("C:\\Users\\Shorty\\documents\\visual studio 2010\\Projects\\....Test.txt");
StreamWriter writer = new StreamWriter(f);
writer.WriteLine("testing");
writer.Close();

 The only problem i encountered here was that i couldn't flush the streams without getting System.ServiceModel.CommunicationException & System.Net.WebException again.....

 

EDIT: Just tried to open a File to append some data.....works also

@lucwuyts

I think you wanted to point me at 

HttpServerUtility u = HttpContext.Current.Server;

string a = u.MapPath(@"~/ClientBin");

right ? 

However i'm getting  System.ServiceModel.CommunicationException & System.Net.WebException at the first line.

 

 

I know it's difficult to help her, so if you have any question that could help you i'll just answer as fast as i can...

A general question: Can i use my sqlite file for example from the desktop or do i have to add it to my App_Data folder and adress it anywhere in my webproject ?

 

 

If somebody has already used sqlite with Silverlight 4., i would be very thankful for a step to step description and information about the used folders (concerning  user permissions etc.)

 

Regards,

Tobi

 

 Hi again,

i got it working with SQLiteDataReader now but still i'm having trouble with SQLiteDataAdapter & dataset....

DataSet dataset = new DataSet();
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(dataset);

throws  System.ServiceModel.CommunicationException & System.Net.WebException in the last line. Any idea ?

SQLiteCommand is just the same as i use with SqliteReader(Works there)

 

Regards

 

 

 okay the problem with dataset is related to the DateTime to string problem.

I have read other Threads about this issue & i just have one short question.

 

I would prefer to convert all DateTimefields to Textfields, do i have to do it manually wit my Sqliteadmin or is there anyway to do this just by code when opening the database ?

 

Regards

Are you running this on a website?

I only used sqlite from a webservice running in a website. 

I don't think you can use this version of sqlite directly from silverlight.   

Yes i am using all of this on a webservice...

 

The only problem i still have are the DateTimeFields now, i cannot change the DateTimeColumns to TextColumns because i have sqlitequeries in my database which rely on DateTimeComparison...and if i change the format i am getting empty results :(

 Damn that's a real difficult situation...

 

 

And if you use the universal date-time format?

That's what i use for the moment.

 u mean ticks, right ? Which format do i have to take then for the column ? long ?

 Thanks again,

Tobi

No, in text format you can use  year-month-day hh:mm:ss   this will always work (i think)

You could also use a double.  I've seen this before, but never used it myself because it is not readable in another tool.

i already did this and it works perfectly smooth for me. i don't understand why it wouldn't for you. maybe a little reading off the manual or 'read me' will help you a lot.