0

I have a file named query.sql containing a TRANSACTION, and I want to execute that file using sqlite command line from my C# code. Its works fine, but I cannot get the affected rows from the command line output. I want to get the affected rows and show it using a MessageBox.

My C# code using Process :

ProcessStartInfo startinfo = new ProcessStartInfo("sqlite3.exe");
startinfo.Arguments = string.Format("\"{0}\" \".read '{1}'\"",DatabasePath, Path.Combine(Path.GetTempPath(), "query.sql"));
//startinfo.WindowStyle = ProcessWindowStyle.Normal;
Process.Start(startinfo);

view from commandline :

sqlite3.exe "C:\Users\me\AppData\Local\app\database.db" ".read 'C:\Users\me\AppData\Local\Temp\query.sql'"

example of my sql file:

I executed the command and the db got changed, but I cannot capture the affected rows. How to capture the affected rows from that Process?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Wind Cat
  • 5
  • 2
  • 2
    Why use from command line instead of the [Sqlite .NET library](https://www.nuget.org/packages/Microsoft.Data.Sqlite)? – Magnetron Oct 05 '21 at 14:03
  • Yeah, this would be a very good use case for SQLite.NET. – Robert Harvey Oct 05 '21 at 14:30
  • INSERT commands don't return rows. – Joel Coehoorn Oct 05 '21 at 17:08
  • @Magnetron I tried using the library for import case but the performance so different. with library i need to read the sql file with stream reader to assign it to a string and if the file is very huge, it takes very long time to process and use big memory when i see in the diagnostic tools. but i have little doubt if i doing that in wrong way. – Wind Cat Oct 06 '21 at 00:14

1 Answers1

0

First you need to add Select Changes(); to the end of your SQL.

https://www.sqlite.org/lang_corefunc.html#changes

Then you need to setup your process to redirect the output to your app.

startinfo.UseShellExecute = false;
startinfo.RedirectStandardOutput = true;
var proc = Process.Start(startinfo);
while (!proc.StandardOutput.EndOfStream)
{
    string output = proc.StandardOutput.ReadLine();
    Console.WriteLine(output);
}

https://stackoverflow.com/a/4291965/7182460

While the above should work there is an easier way. I'm not sure what your reason is for using the command line. You might want to take a look at using an ORM.

https://github.com/praeclarum/sqlite-net

Neil B
  • 2,096
  • 1
  • 12
  • 23
  • I tried using the library for import case but the performance so different. with library i need to read the sql file with stream reader to assign it to a string and if the file is very huge, it takes very long time to process and use big memory when i see in the diagnostic tools. but i have little doubt if i doing that in wrong way. – Wind Cat Oct 06 '21 at 00:46
  • thanks your code works fine. but i changed the `SELECT changes();` because it always return `1`, i changed to `SELECT total_changes();` and append to end of my sql file. and i will take a look to the link you provide. – Wind Cat Oct 06 '21 at 01:05