How to Parse a File Path to SQL Server at Runtime

I wish to include a file path in an SQL Server query which is executed from C#. The file path is obtained from a textBox, which in turn was provided by an open file dialog. The SQL server query is contained in the following string:

string strSqlAcctSelect = String.Format("SELECT Code AS dhAccountType,
                                                     Name as dhAcctName 
                                           INTO {0} " + "FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0; DATABASE = 
    {1}', 'SELECT * FROM " + "[Sheet1$]')", strAcctTabName, this.textBoxAcctPath.Text);

Where at runtime:

    this.textBoxAcctPath.Text = 开发者_JS百科"J:\\CCCDataVic\\RMH\\PE1006Data\\DHCC.xls";

When this string is parsed back to screen of course the file path string looks as it should: 'J:\CCCDataVic\RMH\PE1006Data\DHCC.xls'.

The problem is this: how do I include a string containing a path in an SQL Query in C# (as above) using the literal "\" (single back slash) without parsing the query "\" (the double back slash)?

Whatever you do, the string is parsed to SQL containing the double back slash which SQL doesn't like.

---

Try this

string path = new Uri(this.textBoxAcctPath.Text).AbsolutePath;

But this would throw exception if you supply invalid path, so I advise you to use below code

string validPath = string.Empty;

if (Uri.TryCreate(this.textBoxAcctPath.Text, UriKind.Absolute, out uri))
{
   validPath = uri.AbsolutePath;               
}
else
{
   throw new ArgumentException("Invalid Path");
}


string strSqlAcctSelect = String.Format("SELECT Code AS dhAccountType,
                                                     Name as dhAcctName 
                                           INTO {0} " + "FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0; DATABASE = 
    {1}', 'SELECT * FROM " + "[Sheet1$]')", strAcctTabName, validPath);

---

If I have understood the original post correctly, you may be getting confused because of the Visual Studio Debugger. e.g. consider this C# code :

    string str = "a\\b"; 

    MessageBox.Show(string.Format("string {0} has length {1}", str, str.Length));

This displays : string a\b has length 3

In Visual Studio, the debugger will show the contents of str as :

"a\\b"

, but it is stored in the computer at execution time (taking a very simplified view) as "a\b".

---

Give this a try:

string strSqlAcctSelect = String.Format("SELECT Code AS dhAccountType,
                                                     Name as dhAcctName 
                                           INTO {0} " + "FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0; DATABASE = 
    {1}', 'SELECT * FROM " + "[Sheet1$]')", strAcctTabName, this.textBoxAcctPath.Text.Replace(@"\\", @"\"));