Make a setup file from project which includes sqlite database! ! !

Question

0.00/5 (No votes)

See more:

I have a simple form with one button and two texrfield. i want to save data from the textfields to SQLite database by pressing the button. i created SQLite database and i wrote my code as below. when i run the code from Visual studio (Start), it works correctly But if i create a setup file from my project and installed it, then i can not even open the software which i published! when i double click on the icon of my software, nothing will happen! (it doesn't open!).I know The problem is ONLY because of SQLite part because if i delete the code from SQLite and install the setup file, the software Run correctly (but without database!). Does anyone know how can i fix this problem? i also uploaded my project in google drive below, if it helps... Thanks

simpleDatabase8 SQLite - Test.7z - Google Drive[^]

What I have tried:

<pre>and this is again my connection code:
<pre> public static SQLiteConnection con = new SQLiteConnection("Data Source= C:\\Users\\...\\sqliteDB.db3");

    private void button1_Click(object sender, EventArgs e)
    {


        con.Open();
        SQLiteCommand cmd = new SQLiteCommand("insert into [Table](name,code) VALUES ('" + textBox1.Text + "', '" + textBox2.Text + "')", con);


        cmd.Parameters.AddWithValue("@name", textBox1.Text);
        cmd.Parameters.AddWithValue("@code", textBox2.Text);

        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
        cmd.ExecuteNonQuery();

        con.Close();
        MessageBox.Show("Inserted");

    }

Posted 11-Dec-19 22:27pm

Member 10943256

Updated 11-Dec-19 22:47pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-12-11T22:47:00

Solution 1

Two major things wrong here:

1) Don't hard code connections - always read them from a configuration file, or you have to change you app in many places each time you release it - and that menas releasing untested code which is going to fail one day, or developing against a "live" database which is much, much worse. When you make a mistake in yoru code, you can mess up the production database far, far too easily. Always use a config file, so you change the connection in one location.

2) Never use strings to build a SQL command with parameter values. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you use strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

I'd also suggest you have a look here: Where should I store my data?[^] and use that to locate the SQLite DB - you can use the same code in your setup program to copy the file there.

Posted 11-Dec-19 22:47pm

OriginalGriff

Comments

Member 10943256 12-Dec-19 5:01am

Thanks for your good information.
about the first tip, what is the address of my configuration file?
i could't find it.
Thanks

OriginalGriff 12-Dec-19 5:35am

How do you expect me to know?
Google "Configuration file" and the type of your app and see what it comes back with. At a guess, you don't have one - or aren't aware you do - so look at the results to find out how to use one with your environment (Winapps and Web use totally different config mechanisms for example).

Member 10943256 12-Dec-19 5:40am

I thought you saw my windows application!
i uploaded it on google drive and shared it above the text :"What I have tried:"
https://drive.google.com/file/d/1XV0GtTODcVJQW1gyI6xvZXUWQ1JvS7ua/view?usp=sharing

OriginalGriff 12-Dec-19 5:56am

Nope. Very few of us go off to random sites to wade through gawd-knows-how-much code in search of anything that might be relevant. We're all volunteers, and this cuts into actual paying work. We're not going to waste our time wading through your code because you can't be bothered to show us just the relevant bits.

Member 10943256 12-Dec-19 5:43am

i guess, i should add the database physically to my visual studio first, then i can see Configuration file, ?

Member 10943256 12-Dec-19 6:06am

Sorry why you use "We" instead of "I" ?!
i have a problem and i wrote my question with complete information there. i even send the complete source file from my problem and i asked kindly for help. If anyone knows and helps, i will really appreciate it!
But you, if you don't want to help or you don't know the answer please dont write anything!

Richard MacCutchan 12-Dec-19 8:44am

Calm down, and go back and read the suggestions you have been given. We are here to help people with their problems, but we expect you to provide all the relevant information here in your question.

Member 10943256 12-Dec-19 8:53am

Dear Richard,
Thanks for your comment.
from my side, i think i wrote all relevant information, but if i missed sth or there is any vague part in my explanation, please tell me then i will write/add that.

Richard MacCutchan 12-Dec-19 9:07am

Well, OriginalGriff has already explained what is missing, so there is no point me repeating it.

Richard MacCutchan 12-Dec-19 9:13am

Just looking at your code I see two problems.

1. Using string concatenation, as OriginalGriff already pointed out has risks to your data integrity. But I notice you are also adding two parameter values to your command, without using value names in your insert statement.

2. You do not check the result of your ExecuteNonQuery command to see if it succeeded, but you still post a success message. So your code could run for weeks without ever inserting a record and you would never know.

I would suggest spending some serious study time learning how to use SQL correctly.

Member 10943256 12-Dec-19 9:34am

it get values from the textfileds and save them in two columns in database with the same name!

Richard MacCutchan 12-Dec-19 9:45am

You are mixing methods. If you use string concatenation (which you are doing) then adding parameters serves no purpose. Your command should be:

SQLiteCommand cmd = new SQLiteCommand("insert into [Table](name,code) VALUES (@name, @code)", con);
cmd.Parameters.AddWithValue("@name", textBox1.Text);
cmd.Parameters.AddWithValue("@code", textBox2.Text);

And you still need to address point 2.

Member 10943256 12-Dec-19 9:53am

Thanks for explanation.
About last sentece, i didn't get your point. could you please tell me what is address point2...?

Richard MacCutchan 12-Dec-19 10:18am

I mean point number 2 in my message above. You must check the results of system calls that return a value which tells you whether a method was successful or not; do not just assume it was successful.

Member 10943256 12-Dec-19 10:30am

in this case, i wrote like it :)
Is it correct? Thanks
int i = 0;
i = cmd.ExecuteNonQuery();
if (i > 0)
{

MessageBox.Show("Inserted");
DisplayData();
}
else
{
MessageBox.Show("Not Inserted");
}
con.Close();

Richard MacCutchan 12-Dec-19 10:40am

Almost. You should move the con.Close outside the if block, so it always gets called.

Member 10943256 12-Dec-19 10:51am

Thanks. i edited it.
about solving my main problem to run the software after installing, could you please guide me more?
I dont know how should i find the address of my configuration file to fix the problem.

Richard MacCutchan 12-Dec-19 11:06am

See Using Visual Studio to find a database connection string[^], by our good friend @OriginalGriff.

Member 10943256 12-Dec-19 11:35am

Thanks Richard and also thanks @OriginalGriff.
i added the SQLite into sql connection but the connection String is still the same as address of the file location. but if i understood correctly, you meant i should change it(not to be in C/desktop...), yes?
2) and still it doesnt work after i installed it.(like before. i just click on shortcut and nothing happend)

OriginalGriff 12-Dec-19 11:47am

To go back the the first thing I told you:
"Don't hard code connections - always read them from a configuration file, or you have to change you app in many places each time you release it - and that menas releasing untested code which is going to fail one day, or developing against a "live" database which is much, much worse. When you make a mistake in yoru code, you can mess up the production database far, far too easily. Always use a config file, so you change the connection in one location."

Then the first reply:
"Google "Configuration file" and the type of your app and see what it comes back with. At a guess, you don't have one - or aren't aware you do - so look at the results to find out how to use one with your environment"

Have you tried Google yet? a very simple search "winforms configuration file c#" would have given you loads of information, like this for example:
https://docs.microsoft.com/en-us/dotnet/framework/winforms/advanced/how-to-read-settings-at-run-time-with-csharp
And then - six hours ago - you'd have the info you needed ...

Member 10943256 13-Dec-19 4:13am

@OriginalGriff: Thanks for helping again.
i did what you mentioned, and now it works again like before.i mean when i made a setup file and installed it, with double clicking on shortcut, nothing will happend!
Could you please tell what i missed?
Thanks.

1)My code in App.config is like below:

<configuration>
<startup>
<supportedruntime version="v4.0" sku=".NETFramework,Version=v4.6.1">

<connectionstrings>

<add name="connection_string" providername="System.Data.sqlclient" connectionstring="Data Source=C:\\Users\\MYUSER\\Desktop\\simpleDatabase8 SQLite - Copy\\simpleDatabase7\\bin\\Debug\\sqliteDB.db3;Version=3">

2) And i wrote like it in my Form:
static string conn_string = System.Configuration.ConfigurationManager.ConnectionStrings["connection_string"].ConnectionString;
public static SQLiteConnection con = new SQLiteConnection(conn_string);

OriginalGriff 13-Dec-19 5:45am

Because your connection string on the system you install it onto isn't going to have a user called "MYUSER" and the file probably wouldn't exist on his desktop if he did. Plus your app probably wouldn't have access permission to it if it's a different user anyway.

That's the point of a config file: it can be changed to reflect the local situation without changing the EXE.
There is also that you app isn't going to be there, if your setup program is "Normal" it'll be under Program Files and you can't write to files there at all for security reasons.

And don't store data files on the desktop: Windows provides a wealth of "sensible" places to put it.
https://www.codeproject.com/Tips/370232/Where-should-I-store-my-data

Member 10943256 13-Dec-19 9:54am

Thanks for the explaination.
Regarding to the first paragraph in your comment, can u please tell what should i do to solve it?

OriginalGriff 13-Dec-19 10:20am

Change the setup program to write the right path into the config file when it copies the DB over ...

Member 10943256 12-Dec-19 9:31am

Richard your answer was not correct!
As i mentioned in my question, if you run this from visual studio it works well and all data would be saved in database correctly. i don't know why you wrote this code will not run for weeks.....

Richard MacCutchan 12-Dec-19 9:46am

That is not what I said.