How to extract part of string from SQL query result using C#

Question

i have the following problem: i have a sql table ("pathtable") in which i save the paths to my archived xml files in a column called "path". i now want to extract the filename ("filename") from said file ("xml1.xml") in the path from the column i get when i use the query i mention in my code (SELECT path FROM pathtable).

in other words, let's say there's a file called "xml1.xml" in the folder xmlfiles (xmlfiles). and the path (C:\Users\xmlfiles) to said folder is saved in my sql table in the column "path".

In a perfect world the result of my code would be just the name of the file: "xml1" (without the file extension)

Thanks for your help, regards Brian

SqlConnection connection = new SqlConnection(@"Data Source=...\SQLEXPRESS;Initial Catalog=...;Integrated Security=SSPI");
{
    string pathname= "SELECT path FROM pathtable";
    SqlCommand cmd = new SqlCommand(pathname, connection);
    connection.Open();
    SqlDataReader reader = cmd.ExecuteReader();

    string strPath = pathname; //"C:\Users\xmlfiles\"; 
    string filename = null;
    filename = Path.GetFileName(strPath);

    while (reader.Read())
    {
        Console.Write(filename);
    }

   Console.ReadKey();
    reader.Close();
    connection.Close();
}

because there seems to be a lot of confusion on what i want to achieve (my bad :)), i try to explain it in a better way: this is my sql table: SQL Table

and in this path: "C:\Users\xmlfiles\" there is a file called "testxml.xml" and i want my code to return "testxml". and one more requirement is that it's hard-coded, in other words i can only change the path i want to use in my sql table, not in the C# code. so let's say i update my sql table from "C:\Users\xmlfiles\" to "C:\Users\docfiles\" then it should give me the filename from the folder docfiles.

i'm just getting started, so thanks for your patience

Your code looks a little confused. You can obtain the paths returned by the query inside the while loop like string path = reader.GetString(0); and the file names without extension like string file = Path.GetFileNameWithoutExtension(path); — René Vogt
– René Vogt, Commented Oct 10, 2019 at 13:12
I think there is a confusion in your code, do you mean the path you are getting from db is "C:\Users\xmlfiles\xml1.xml"? — Safeer Raees
– Safeer Raees, Commented Oct 10, 2019 at 13:28

garty · Accepted Answer · 2019-10-10 13:25:44Z

2

Not sure if you're struggling parsing the file name from the path or reading from the DB, so I've covered both.

With each iteration of the while loop you are effectively reading one row from the result set. You can then read each columns value for that row by index and type as such:

while (reader.Read())
{
    string fileName = reader.GetString(0);
}

IF you were to select two columns from the table you might do something like this:

SELECT path,FILENAME FROM pathtable

while (reader.Read())
{
        string fileName = reader.GetString(0);
        string filePAth = reader.GetString(1);
}

IF you were storing the entire path in the table you could parse out the filename using

System.IO.Path.GetFileName(fullPath) or for the file name without the extension System.IO.Path.GetFileNameWithoutExtension(fullPath)

Although I'd advise you do the above processing when you create the record initially to save you having to do further process later on down the line, storing both the path and the filename. That way you can select the filename if that is all you require.

edited Oct 10, 2019 at 13:25

answered Oct 10, 2019 at 13:18

garty

4264 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BrianF. Over a year ago

i think you're on the right track garty, i changed my code using your suggestion: .... while (reader.Read()) { string fileName = reader.GetString(0); Console.Write(fileName); } and now the code returns: "C:\Users\xmlfiles\" which is exactly what i have in the path column in my sql table. How do i now get the filename from the file which is in said folder (file: testxml.xml in folder: C:\Users\xmlfiles\ --> it should return "testxml")?

garty Over a year ago

You don't. You've lost that data at this point. What you need to do is store either the full path when you create the pathtable row or store the file name in a second column. Optionally, you can iterate all the files at the path

foreach(string fullPath in System.IO.Directory.GetFiles("")) { 			string fileName = System.IO.Path.GetFileNameWithoutExtension(fullPath); 		}

`

garty Over a year ago

If you've only stored C:\Users\xmlfiles\` for the path` column in the pathtable then you only have C:\Users\xmlfiles`. You should store the full path C:\Users\xmlfiles\testxml.xml`.

Shan9588 · Accepted Answer · 2019-10-10 13:15:28Z

1

If you want the filename without the extension, you could just use the GetFileNameWithoutExtension method :

string strPath = @"C:\Users\xmlfiles\xmlFile.xml"; 
string filename = Path.GetFileNameWithoutExtension(strPath);
Console.WriteLine(filename); // result will be xmlFile

answered Oct 10, 2019 at 13:15

Shan9588

586 bronze badges

Collectives™ on Stack Overflow

How to extract part of string from SQL query result using C#

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related