loop folder get and insert doc file to sql db one by one

I need help. I have to insert doc files into mssql with their folder names using c#.

Here are some example directories:

\gpvmsrv01\INPUT\Scan\Patent\2017\2\I AP 20170115\Formula\1.doc \gpvmsrv01\INPUT\Scan\Patent\2016\1\I AP 20160248\Description\2_u.doc

And the number of files are more than thousand.

Table name: patent

[app_number] [int] NOT NULL,

[type] [nchar](20) NULL,

[filename] [nchar](10) NULL,

[data] [varbinary](max) NULL,

I should iterate through all directories.

SO my first row values

app_number=20170115 type=Formula filename=1 data=1.doc file

My second row values

app_number=20160248 type=Description filename=2_u data=2_u.doc file

I would be very grateful if someone helps me with some example code, please.

static void Main(string[] args)
{
const string baseFolder = "\\gpvmsrv01\\INPUT\\Scan\\Patent\\";
const string query =
"INSERT INTO Patent([app_number], [type], [filename], [data]) " +
"VALUES ({0}, {1}, {2}, {3})"
;
var fileList = Directory
.EnumerateFiles(baseFolder, "*", SearchOption.AllDirectories)
.ToArray()
;
using (var conn = new SqlConnection("your connection string goes here"))
using (var cmd = conn.CreateCommand())
{
foreach (var fileX in fileList)
{
var parts = fileX.Substring(baseFolder.Length).Split('\\');
// extract only the number
var appNum = Regex.Replace(parts[2], "[^0-9]", string.Empty);
var typ = parts[3];
var fn = parts[4];
var contents = File.ReadAllBytes(fileX);
cmd.CommandText = string.Format(query, appNum, typ, fn, contents) + Environment.NewLine;
}
cmd.ExecuteNonQuery();
}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.