Need some C#/SQL Help!

[MajorMatt]

I have a code block that looks in a directory and stores the file path for each file in an array. I want to store each field in the array into a row in an SQL CE database I have setup.

Code: [Select]

string[] filePaths = Directory.GetFiles(@"C:\filepath");
That gets the files paths for each file in the directory 'filepath' and stores them in the 'filePaths' array.

I also want to extract the filename for each file, excluding the extension and store these in another column. So in the end I will have a table with an identifier column, a filePath column and a fileName column.

Hope you can understand what I'm trying to achieve, it's probably really simple but for some reason I just can't think of how to go about it.

If you need any more info just shout!

Thanks,
Matt

[rumborak]

What part do you have problems with? The parsing of the file names into directory, small file name etc? I think there is a C# function that does the parsing for you.

With SQL under C# I can't help, sorry.

rumborak

[MajorMatt]

What part do you have problems with? The parsing of the file names into directory, small file name etc? I think there is a C# function that does the parsing for you.

With SQL under C# I can't help, sorry.

rumborak

The part I am having trouble with is taking each value in the array and placing it into a field in my SQL table. Also, removing the directory/extension from each value in the array and placing each result into a separate field.

So, from an array containing these values where ".ext" is a file extension:

Code: [Select]

C:\Test Folder\file1.ext
C:\Test Folder\file2.ext
C:\Test Folder\file3.ext
C:\Test Folder\file4.ext
C:\Test Folder\file5.ext
C:\Test Folder\file6.ext

I want to inject each value into a field in a table and also inject the filename into another field so I end up with a table like this:

Code: [Select]

 ID   |              filePath           | fileName
0001  |    C:\Test Folder\file1.ext  |    file1
0002  |    C:\Test Folder\file2.ext  |    file2
0003  |    C:\Test Folder\file3.ext  |    file3
0004  |    C:\Test Folder\file4.ext  |    file4
0005  |    C:\Test Folder\file5.ext  |    file5
0006  |    C:\Test Folder\file6.ext  |    file6


200th Post

[MajorMatt]

Ok, so i discovered the 'foreach' statement is the way to go, so...

Code: [Select]

                foreach (string fP in filePaths)
                {
                    SqlCeConnection dbConnection = new SqlCeConnection(DataAccess.GetConnectionString("dbCon"));

                    SqlCeDataAdapter da = new SqlCeDataAdapter();
                    da.InsertCommand = new SqlCeCommand("INSERT INTO movieTable VALUES(@filePath)", dbConnection);
                    da.InsertCommand.Parameters.Add("@filePath", SqlDbType.VarChar).Value = fP;
                    dbConnection.Open();
                    da.InsertCommand.ExecuteNonQuery();
                    dbConnection.Close();
                }

But this throws an exception:

Code: [Select]

System.ArgumentException: VarChar
   at System.Data.SqlServerCe.SqlCeType.FromSqlDbType(SqlDbType type)
   at System.Data.SqlServerCe.SqlCeParameter.set_SqlDbType(SqlDbType value)
   at System.Data.SqlServerCe.SqlCeParameter..ctor(String name, SqlDbType dataType)
   at System.Data.SqlServerCe.SqlCeParameterCollection.Add(String parameterName, SqlDbType type)
   at movieLibrary_v1._0.movieBrowser.getMoviesBtn_Click(Object sender, EventArgs e) in C:\Users\Matt\Documents\Visual Studio 2008\Projects\movieLibrary_v1.0\movieLibrary_v1.0\movieBrowser.cs:line 146

I'm stuck I'll post over at some programming forums too, but I've seen some people around here seem to have some knowledge of this stuff.

Thanks,
Matt

[bremac]

Code: [Select]

System.ArgumentException: VarChar
   at System.Data.SqlServerCe.SqlCeType.FromSqlDbType(SqlDbType type)
   at System.Data.SqlServerCe.SqlCeParameter.set_SqlDbType(SqlDbType value)
   at System.Data.SqlServerCe.SqlCeParameter..ctor(String name, SqlDbType dataType)
   at System.Data.SqlServerCe.SqlCeParameterCollection.Add(String parameterName, SqlDbType type)
   at movieLibrary_v1._0.movieBrowser.getMoviesBtn_Click(Object sender, EventArgs e) in C:\Users\Matt\Documents\Visual Studio 2008\Projects\movieLibrary_v1.0\movieLibrary_v1.0\movieBrowser.cs:line 146

Well, that's a pretty unhelpful exception. I'm assuming your table only has the one column (ie. it doesn't match your first post)? Otherwise, my (rusty) SQL knowledge says you should be specifying the destination column, ex. "INSERT INTO movieTable (filePath) VALUES(@filePath)", unless you want to put the file path in your ID column.

[MajorMatt]

Code: [Select]

System.ArgumentException: VarChar
   at System.Data.SqlServerCe.SqlCeType.FromSqlDbType(SqlDbType type)
   at System.Data.SqlServerCe.SqlCeParameter.set_SqlDbType(SqlDbType value)
   at System.Data.SqlServerCe.SqlCeParameter..ctor(String name, SqlDbType dataType)
   at System.Data.SqlServerCe.SqlCeParameterCollection.Add(String parameterName, SqlDbType type)
   at movieLibrary_v1._0.movieBrowser.getMoviesBtn_Click(Object sender, EventArgs e) in C:\Users\Matt\Documents\Visual Studio 2008\Projects\movieLibrary_v1.0\movieLibrary_v1.0\movieBrowser.cs:line 146

Well, that's a pretty unhelpful exception. I'm assuming your table only has the one column (ie. it doesn't match your first post)? Otherwise, my (rusty) SQL knowledge says you should be specifying the destination column, ex. "INSERT INTO movieTable (filePath) VALUES(@filePath)", unless you want to put the file path in your ID column.

Aha, yes you are right, I don't get the exception, but the data does not appear in the table :/