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);
}
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);
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;
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
User contributions licensed under CC BY-SA 3.0