I have a web application that needs to push data to an SQLite db that is on the user machine. It is quite straight forward. It takes data from an Azure SQL db and pushes to the local SQLite db.
var tblBatchNumbers = new DataTable(); using (var da = new SqlDataAdapter("exec spBatchNumbers", connString)) //using (var da = new SqlDataAdapter("SELECT BatchNumber,Programme FROM BatchNumbers", connString)) { da.Fill(tblBatchNumbers); } SQLiteConnection cons = new SQLiteConnection(ConnectionStringDestination,true);
cons.Open(); SQLiteCommand Command = new SQLiteCommand("DELETE FROM BatchNumbers", cons); Command.ExecuteNonQuery(); cons.Close(); using (SQLiteConnection con = new SQLiteConnection(ConnectionStringDestination,true))
{ con.Open();
If I understand the problem correctly, you want to update a SQLite DB on a user's machine from a remote web application running on Azure. If so, the approach is not possible because the Azure web application cannot make requests to the user's machine.
Oh dear. Its a shame I cant do what I wanted! As an aside, why is it that a web app cannot access the local machine? Is this a security issue?
It's s TCP/IP fundamental. It's possible with some networking (the approach depends on your network topology) and crafting a listener on the client machine. Being that you have to write/install software on the client for either approach it easy to build
a client side application.
takes data from an Azure SQL db and pushes to the local SQLite db
Firstly, glad to hear that you get a solution with the help of mike and mgebhard.
Besides, based on your code and description, it seems that you'd like to query data from Azure SQL db and push the retrieved data to your local SQLite db after user click the button from your application, to achieve the requirement, here is another possible
solution:
Member
47 Points
143 Posts
Populate a local SQLite Database Using Web App
Jun 04, 2019 09:01 AM|Billson3000|LINK
Hello Experts,
I have a web application that needs to push data to an SQLite db that is on the user machine. It is quite straight forward. It takes data from an Azure SQL db and pushes to the local SQLite db.
The SQLite db is here:
string ConnectionStringDestination = @"Data Source=C:\TestSQLite\Testdb.db;Version=3;";And here is my code to push:
protected void Button1_Click(object sender, EventArgs e){string connString = @"My Conn";string ConnectionStringDestination = @"Data Source=C:\TestSQLite\Testdb.db;Version=3;";var tblBatchNumbers = new DataTable();using (var da = new SqlDataAdapter("exec spBatchNumbers", connString))//using (var da = new SqlDataAdapter("SELECT BatchNumber,Programme FROM BatchNumbers", connString)){da.Fill(tblBatchNumbers);}SQLiteConnection cons = new SQLiteConnection(ConnectionStringDestination,true);cons.Open();SQLiteCommand Command = new SQLiteCommand("DELETE FROM BatchNumbers", cons);Command.ExecuteNonQuery();cons.Close();using (SQLiteConnection con = new SQLiteConnection(ConnectionStringDestination,true)){con.Open();using (SQLiteTransaction transaction = con.BeginTransaction()){foreach (DataRow row in tblBatchNumbers.Rows){using (SQLiteCommand sqlitecommand = new SQLiteCommand("INSERT INTO BatchNumbers(RecordId,BatchNumber,ExpiryDate,Programme,Route,Dose) VALUES ('"+ Convert.ToString(row[0]) + "','"+ Convert.ToString(row[1]) + "','"+ Convert.ToString(row[2]) + "','"+ Convert.ToString(row[3]) + "','"+ Convert.ToString(row[4]) + "','"+ Convert.ToString(row[5]) + "')", con)){sqlitecommand.ExecuteNonQuery();}}transaction.Commit();}}}It all works brilliantly when running in Local Host. However, once I deploy the app to Azure then the process fails. Here is the error:
[SQLiteException (0xe): unable to open database file]System.Data.SQLite.SQLite3.Open(String strFilename, String vfsName, SQLiteConnectionFlags connectionFlags, SQLiteOpenFlagsEnum openFlags, Int32 maxPoolSize, Boolean usePool) +644System.Data.SQLite.SQLiteConnection.Open() +5579HTML5OfflineApplication.BulkCopy_DataTableToSQLLite.Button1_Click(Object sender, EventArgs e) in C:\Users\Me\OneDrive - Me\Visual Studio\source\repos\Offline\Offline\Secure\BulkCopy_DataTableToSQLLite.aspx.cs:32System.Web.UI.WebControls.Button.OnClick(EventArgs e) +109System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +108System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +31System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3470I think that the error is caused by the application not being able to reference/find the database. The file and folder has WRITE access.
Any ideas. I have drawn a blank on this now for 48hrs!!!!!!
Thanks very much always.
Billson3000
All-Star
47361 Points
20761 Posts
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 10:13 AM|mgebhard|LINK
If I understand the problem correctly, you want to update a SQLite DB on a user's machine from a remote web application running on Azure. If so, the approach is not possible because the Azure web application cannot make requests to the user's machine.
Member
47 Points
143 Posts
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 10:18 AM|Billson3000|LINK
Oh dear. That's a bit disappointing!!
Is there a work around?
Could I build a windows app that could call the Azure databases instead?
All-Star
191265 Points
27743 Posts
Moderator
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 10:20 AM|Mikesdotnetting|LINK
Further discussion on desktop application development is off topic here.
Member
47 Points
143 Posts
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 10:26 AM|Billson3000|LINK
Cheers Mike.
I have only developed web apps and aspnet.
Can you recommend a starting tutorial or learning space?
All-Star
191265 Points
27743 Posts
Moderator
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 10:46 AM|Mikesdotnetting|LINK
I would start here: https://docs.microsoft.com/en-us/dotnet/framework/wpf/getting-started/introduction-to-wpf-in-vs
All-Star
47361 Points
20761 Posts
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 10:50 AM|mgebhard|LINK
The "Docs" link above has links to all the different types of .NET projects you can build.
https://docs.microsoft.com/en-us/dotnet/
Member
47 Points
143 Posts
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 11:46 AM|Billson3000|LINK
Thanks very much Mike and MGE.
Oh dear. Its a shame I cant do what I wanted! As an aside, why is it that a web app cannot access the local machine? Is this a security issue?
All-Star
47361 Points
20761 Posts
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 12:32 PM|mgebhard|LINK
It's s TCP/IP fundamental. It's possible with some networking (the approach depends on your network topology) and crafting a listener on the client machine. Being that you have to write/install software on the client for either approach it easy to build a client side application.
Member
47 Points
143 Posts
Re: Populate a local SQLite Database Using Web App
Jun 04, 2019 03:04 PM|Billson3000|LINK
Thanks a lot MGE.
Back to the drawing board with a separate client side application with a local storage solution.
Thanks again. I appreciate your time and help!
All-Star
40485 Points
6233 Posts
Microsoft
Re: Populate a local SQLite Database Using Web App
Jun 05, 2019 02:55 AM|Fei Han - MSFT|LINK
Hi Billson3000,
Firstly, glad to hear that you get a solution with the help of mike and mgebhard.
Besides, based on your code and description, it seems that you'd like to query data from Azure SQL db and push the retrieved data to your local SQLite db after user click the button from your application, to achieve the requirement, here is another possible solution:
1) Create and insert a new message in queue in button click event. And if possible, you can use [Azure Queue service](https://docs.microsoft.com/en-us/azure/storage/queues/storage-dotnet-how-to-use-queues)
2) Create and run QueueTrigger [Azure WebJobs](https://docs.microsoft.com/en-us/azure/app-service/webjobs-sdk-how-to) on user's local to consume queue messages
3) In QueueTrigger Azure WebJobs code logic, you can query data from Azure SQL db and push the retrieved data to local SQLite db as you did before.
With Regards,
Fei Han
MSDN Community Support
Please remember to "Mark as Answer" the responses that resolved your issue.