by sweisfeld via Shawn Weisfeld [MVP] on 6/11/2009 5:12:49 AM
Today was a good day for questions, got one asking how to query the file system from sql server. See they have a list of network shares that they want to find out what folders are in each. SQL CLR to the rescue. . .
First lets write a Table Valued Function to get a list of folders. I chose a Table Valued Function so I can join it to a list of paths to search in sql server. Remember that since we are leaving the confines of the SQL Server to get data from the disk we need to elevate our permission, we are also impersonating a domain account as they have access to external resources.
here is the code for the Table Valued Function:
1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4: using System.Data.SqlTypes;
5: using Microsoft.SqlServer.Server;
6: using System.Collections;
7: using System.Collections.Generic;
8: using System.Security.Principal;
9:
10: public partial class UserDefinedFunctions
11: {
12: /// <summary>
13: /// Get a list of all the folders in a given path, for a given number of levels deep
14: /// </summary>
15: /// <param name="path"></param>
16: /// <param name="levels"></param>
17: /// <returns></returns>
18: [SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "directory nvarchar(4000)", DataAccess=DataAccessKind.Read)]
19: public static IEnumerable Folders(String path, int levels)
20: {
21: WindowsImpersonationContext impersonatedIdentity = null;
22: List<string> results = new List<string>();
23:
24: try
25: {
26: //impersonate the user that is executing me in sql server
27: impersonatedIdentity = SqlContext.WindowsIdentity.Impersonate();
28:
29: //Get the data
30: GetDirectories(results, path, levels);
31: }
32: finally
33: {
34: //undo the impersonation
35: if (impersonatedIdentity != null)
36: {
37: impersonatedIdentity.Undo();
38: }
39: }
40:
41: return results;
42: }
43:
44: /// <summary>
45: /// Recursivly dig through the file system getting data
46: /// </summary>
47: /// <param name="results">List of the results</param>
48: /// <param name="path">Current Path we are looking into</param>
49: /// <param name="levels">Number of levels left to dig down</param>
50: private static void GetDirectories(List<string> results, string path, int levels)
51: {
52: //are we at the bottom of the query
53: if (levels > 0)
54: {
55: //append on the slash if we dont have it
56: if (!path.EndsWith("\\"))
57: {
58: path = path + "\\";
59: }
60:
61: try
62: {
63: //get a list of all the folders under the current path
64: foreach (string folder in System.IO.Directory.GetDirectories(path, "*", System.IO.SearchOption.TopDirectoryOnly))
65: {
66: //add each to the results, then dig down one more level
67: results.Add(folder);
68: GetDirectories(results, folder, levels - 1);
69: }
70: }
71: catch (UnauthorizedAccessException ex)
72: {
73: //If we dont have access to a folder, just report that back in the result set
74: results.Add(ex.Message);
75: }
76: }
77: }
78:
79: /// <summary>
80: /// convert one of our Enumerable objects into a data row
81: /// </summary>
82: /// <param name="obj"></param>
83: /// <param name="directory"></param>
84: public static void FillRow(Object obj, out SqlChars directory)
85: {
86: directory = new SqlChars(obj.ToString());
87: }
88: };
I created a table (SearchPaths) that contains a list of all the paths that I want to search:
Now I can just join the list of paths to search with the function and I have everything I was looking for:
If you are getting security exceptions and the like take a look at the slides from the CLR talk I gave a PASS Summit 2008, they should get you moving in the right direction.
http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2008/11/23/sql-pass-2008-talk.aspx
Original Post: SQL CLR: Query the file system to get a list of folders
The content of the postings is owned by the respective author. CSharpFeeds is not responsible for the contents of the postings. This site is automatically generated and cannot be reviewed for abusive content. If you find abusive content on CSharpFeeds, please contact us. Designated trademarks and brands are the property of their respective owners. All rights reserved.
