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:
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
48 Points
149 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
52961 Points
23563 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
48 Points
149 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
194428 Points
28073 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
48 Points
149 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
194428 Points
28073 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
52961 Points
23563 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
48 Points
149 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
52961 Points
23563 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
48 Points
149 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
40565 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