Click here to Skip to main content
15,879,326 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am trying to create a new SQLite database file if it doesn't exist,But every time it is throwing an exception that access is denied.


I tried path of different drive,but then it creates an empty database file without any table.
and the user may not have the another drive,hence was trying to create it in C drive only.

Can anyone please tell how to solve this issue?

Below is the code that I have tried.

What I have tried:

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace tthhSensorCalibrator
{
   public class DatabaseOperations
    {


       // OutputOfPort portOutput = new OutputOfPort();
        
        public void databaseMethod(string s1l, string s1h, string s2l, string s2h)
        {
            // This is the query which will create a new table in our database file with three columns. An auto increment column called "ID".
            string createTableQuery = @"CREATE TABLE IF NOT EXISTS [Log] (
                          [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                          [Date] VARCHAR(2048)  NULL,
                          [Time] VARCHAR(2048)  NULL,
                          [S1L] VARCHAR(2048) NULL,
                          [S1H] VARCHAR(2048) NULL,
                          [S2L] VARCHAR(2048) NULL,
                          [S2H] VARCHAR(2048) NULL
                          )";

            string path = "C:\\";
            if (!System.IO.File.Exists(path + "databaseFile.db3"))
            {
                SQLiteConnection.CreateFile(path + "databaseFile.db3");        // Create the file which will be hosting our database
            }
              
            else
            {
                using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"Data source="+ path + "databaseFile.db3"))
                {
                    using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                    {
                        con.Open();                             // Open the connection to the database

                        com.CommandText = createTableQuery;     // Set CommandText to our query that will create the table
                        com.ExecuteNonQuery();                  // Execute the query

                        string Date = DateTime.Now.ToShortDateString();
                        string Time = DateTime.Now.ToShortTimeString();

                        com.CommandText = "INSERT INTO Log (Date,Time,S1L,S1H,S2L,S2H) Values (@param1,@param2,@param3,@param4,@param5,@param6)";     // Add the first entry into our database 
                        com.CommandType = System.Data.CommandType.Text;
                        com.Parameters.Add(new SQLiteParameter("@param1", Date));
                        com.Parameters.Add(new SQLiteParameter("@param2", Time));
                        com.Parameters.Add(new SQLiteParameter("@param3", s1l));
                        com.Parameters.Add(new SQLiteParameter("@param4", s1h));
                        com.Parameters.Add(new SQLiteParameter("@param5", s2l));
                        com.Parameters.Add(new SQLiteParameter("@param6", s2h));
                        com.ExecuteNonQuery();      // Execute the query



                        com.CommandText = "Select * FROM Log";      // Select all rows from our database table

                        using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine(reader["Date"] + " : " + reader["Time"]);     // Display the value
                            }
                        }
                        con.Close();        // Close the connection to the database
                    }
                }
            }
         
        }

       
    }
}
Posted
Updated 10-Jun-18 23:39pm
Comments
F-ES Sitecore 11-Jun-18 5:25am    
I'm not a Windows expert but I believe you need some kind of special rights to create files in the root folder, so Microsoft are basically trying to discourage the practice. Create your database in a folder instead.
Nishikant Tayade 11-Jun-18 5:29am    
Tried a folder but still no results
F-ES Sitecore 11-Jun-18 5:41am    
That's the problem you need to focus on fixing, if it didn't work in a folder it's not going to work in root, you're just masking your actual problem with this permissions problem.
[no name] 11-Jun-18 14:03pm    
You never check any of the return codes.

How do you expect to know what happening?

You don't.

To add to what Thaddeus Jones - very correctly - said, see here: Where should I store my data?[^] - it shows some better places, and how to use them.
 
Share this answer
 
The root path of the Windows drive is owned by the system and has restricted access.

Select a different path where the user running your application has write access. For application related data these are the predefined application data folders for all (common) or each user (depending if the databse should be accessible by all users or each user has its own database). The Application.UserAppDataPath Property (System.Windows.Forms)[^] and the Application.CommonAppDataPath Property (System.Windows.Forms)[^] provide you with the corresponding pathes for your application.
 
Share this answer
 
Comments
Nishikant Tayade 11-Jun-18 8:30am    
string logFilename = System.IO.Path.Combine(Environment.GetFolderPath(
Environment.SpecialFolder.Favorites), "databaseFile.db3");

I tried this and it successfully creates database file,but it is empty,no table.
Nishikant Tayade 11-Jun-18 8:41am    
It worked,that was some if-else problem.
The special folder list also contains Program files ,how can i get access to that folder or can i not.
Jochen Arndt 11-Jun-18 9:00am    
The "Program Files" folder is where programs get installed. Normal users have no write access to that folder and it should not be used to store data that are changed after installation or update.

Use the folders provided by the functions from my answer or use GetFolderPath() with CommonApplicationData (all users), ApplicationData (roaming user), or LocalApplicationData (local user) and append the name of your application (you have to create the directory initially).

That is the recommended way to do it (using an application specific sub directory of one the application data folders). Using any other directory might make your application not running properly on other systems because writing is denied.
Nishikant Tayade 12-Jun-18 2:02am    
DirectoryInfo di=System.IO.Directory.CreateDirectory("C:\\Users\\admin\\AppData\\Roaming\\tthhSensorCalibrator");
string logFilename = System.IO.Path.Combine(Environment.GetFolderPath(
Environment.SpecialFolder.ApplicationData), di+"\\databaseFile.db3");

This is working on my machine.Database file in folder specified is getting created.
But when i try to run it on other machine then it shows
Could not found part of path.
C:\\Users\\ACCEL\\AppData\\Roaming\\tthhSensorCalibrator\\databaseFile.db3
Jochen Arndt 12-Jun-18 2:53am    
Don't use hard coded paths!

Use the app data paths returned by the Windows functions. That ensures that it works on all systems (after the directory has been created when using GetFolderPath() and appending your application name).

Use only one method:

GetFolderPath():
append application name and create that directory

Application.XxxDataPath property:
Directory is ...\CompanyName\ProductName\ProductVersion and is created when using that the first time and the directory does not exist.
Windows doesn't allow you to write to the root of the C:\ drive. You could put your database in %appdata% or the user's Documents folder, depending on what makes sense.
 
Share this answer
 
Comments
OriginalGriff 11-Jun-18 5:30am    
Down vote countered - you are absolutely right!
Windows won't let normal users write to the root of any boot drive (and isn't fond of changes to the root of any drive for very good reasons)
Nishikant Tayade 11-Jun-18 5:39am    
I tried the different folder (Documents folder specifically) but it creates the file but still there is no table.
[no name] 11-Jun-18 5:42am    
That's because your database creation happens only in the else, i.e. if the file already existed to begin with. You could remove the else and always execute the code in there.
OriginalGriff 11-Jun-18 5:48am    
:thumbsup:

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900