Getting error when trying to save data to database in console app

0

My app scans a folder for any changes made in it. Once it has detected a file being added it should the save that file or path in my database.

But at the moment this is the error I am getting:

File created: New Text Document - Copy (2).txt
Unhandled exception. System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the 
keyword 'File'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, 
Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean 
breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean 
callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, 
SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, 
Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, 
String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior 
runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, 
SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior 
runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, 
Boolean asyncWrite, String method)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, 
Boolean 
sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at FileWatcher.Program.FileSystemWatcher_Created(Object sender, FileSystemEventArgs e) in 
C:\Users\Dev\source\repos\FileWatcher\FileWatcher\Program.cs:line 44
at System.IO.FileSystemWatcher.NotifyFileSystemEventArgs(WatcherChangeTypes changeType, 
ReadOnlySpan`1 
name)
at System.IO.FileSystemWatcher.ParseEventBufferAndNotifyForEach(Byte[] buffer)
at System.IO.FileSystemWatcher.ReadDirectoryChangesCallback(UInt32 errorCode, UInt32 numBytes, 
NativeOverlapped* overlappedPointer)
at System.Threading.ThreadPoolBoundHandleOverlapped.CompletionCallback(UInt32 errorCode, UInt32 
numBytes, NativeOverlapped* nativeOverlapped)
at System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 
numBytes, NativeOverlapped* pNativeOverlapped)
ClientConnectionId:5ec97695-2345-4ecd-89b0-f77d4dc409ee
Error Number:156,State:1,Class:15

So this is the code I am using:

private static void MonitorDirectory(string path)
    {
        FileSystemWatcher fileSystemWatcher = new FileSystemWatcher();
        fileSystemWatcher.Path = path;
        fileSystemWatcher.Created += FileSystemWatcher_Created;
        fileSystemWatcher.Renamed += FileSystemWatcher_Renamed;
        fileSystemWatcher.Deleted += FileSystemWatcher_Deleted;
        fileSystemWatcher.EnableRaisingEvents = true;
    }

    private static void FileSystemWatcher_Created(object sender, FileSystemEventArgs e)
    {
        Console.WriteLine("File created: {0}", e.Name);

        string connectionString = @"Data Source=Development-PC\SQLEXPRESS;Initial 
        Catalog=FileDB;Integrated Security=True";
        FileStream stream = new FileStream(e.Name, FileMode.Open, FileAccess.ReadWrite);
        BinaryReader reader = new BinaryReader(stream);
        byte[] file = reader.ReadBytes((int)stream.Length);
        reader.Close();
        stream.Close();

        SqlCommand command;
        SqlConnection connection = new SqlConnection(connectionString);
        command = new SqlCommand("INSERT INTO FileTable (File, Date) VALUES (@File, @Date)", 
 connection);
        command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file;
        command.Parameters.Add("@Date", SqlDbType.DateTime, file.Length).Value.ToString();
        connection.Open();
        command.ExecuteNonQuery();
    }

I am not entirely sure what I am doing wrong with SQL command?

Please help

Thanks!

EDIT

It does read the directory and display the files in the console, but when it tries to save the data in the database the app then crashes with the above error code

EDIT

FULL CODE AFTER EDIT

 static void Main(string[] args)
    {
        string path = "C:\\Database";
        MonitorDirectory(path);
        Console.ReadKey();
    }

    private static void MonitorDirectory(string path)
    {
        FileSystemWatcher fileSystemWatcher = new FileSystemWatcher();
        fileSystemWatcher.Path = path;
        fileSystemWatcher.Created += FileSystemWatcher_Created;
        fileSystemWatcher.Renamed += FileSystemWatcher_Renamed;
        fileSystemWatcher.Deleted += FileSystemWatcher_Deleted;
        fileSystemWatcher.EnableRaisingEvents = true;
    }

    private static void FileSystemWatcher_Created(object sender, 
    FileSystemEventArgs e)
    {
        Console.WriteLine("File created: {0}", e.Name);

        string connectionString = @"Data Source=Development- 
        PC\SQLEXPRESS;Initial Catalog=FileDB;Integrated Security=True";
        FileStream stream = new FileStream(e.Name, FileMode.Open, 
        FileAccess.ReadWrite);
        BinaryReader reader = new BinaryReader(stream);
        byte[] file = reader.ReadBytes((int)stream.Length);
        reader.Close();
        stream.Close();

        SqlCommand command;
        SqlConnection connection = new SqlConnection(connectionString);
        command = new SqlCommand("INSERT INTO FileTable ([File], [Date]) 
        VALUES (@File, @Date)", connection);
        command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = 
   file;
        command.Parameters.Add("@Date", SqlDbType.DateTime, 
   file.Length).Value.ToString();
        connection.Open();
        command.ExecuteNonQuery();
    }

    private static void FileSystemWatcher_Renamed(object sender, 
    FileSystemEventArgs e)

    {
        Console.WriteLine("File renamed: {0}", e.Name);
    }

    private static void FileSystemWatcher_Deleted(object sender, 
    FileSystemEventArgs e)

    {
        Console.WriteLine("File deleted: {0}", e.Name);
    }
c#
asked on Stack Overflow Jan 24, 2020 by Keith King • edited Jan 24, 2020 by Keith King

3 Answers

3

In SQL, you need to escape user defined tokens that overlap keywords with square brackets, try replacing this line:

command = new SqlCommand("INSERT INTO FileTable (File, Date) VALUES (@File, @Date)", 
 connection);

with this:

command = new SqlCommand("INSERT INTO FileTable ([File], [Date]) VALUES (@File, @Date)", 
 connection);
answered on Stack Overflow Jan 24, 2020 by user1538301
0

File is a TSQL reserved key word and that's why you are getting an exception.

You need to change your query to the following to get it work:

command = new SqlCommand("INSERT INTO FileTable ([File], [Date]) VALUES (@File, @Date)", 
 connection);

Putting [] arround fields with the same names as TSQL keywords solves such issues

It would be also nice if you create your table as:

create table FileTable 
(
 [File] varbinary(MAX) null,
 [Date] nvarchar(50) null
)

And also change

command.Parameters.Add("@Date", SqlDbType.DateTime, file.Length).Value.ToString();

to:

command.Parameters.Add("@Date", SqlDbType.DateTime, file.Length).Value = DateTime.Now;
answered on Stack Overflow Jan 24, 2020 by Karamafrooz • edited Jan 24, 2020 by Karamafrooz
0

Just an advice, use using keyword when initializing an object of Sql connection and command, this tells GC to process it after the clause. Usage: Using(sqlconnection sql = new sqlconnection(connstring)) {} Any IDisposable class has this option

answered on Stack Overflow Jan 24, 2020 by Firixx

User contributions licensed under CC BY-SA 3.0