c# program as a .NET SQLCLR stored procedure rewrite

Question

I am using Visual Studio 2015 & SQL Server 2014. I have the below C# program for which I have to pass the Table object name and the program generates the Create Table script. Now I am working on writing this as a SQLCLR Stored Procedure. Could you please give me an idea or direction to accomplish this?

public class CSHProgram
    {      
        static void Main()
        {
            Server dbservername = new Server(@"(local)");
            string tablename = "";

            try
            {
                dbservername.ConnectionContext.LoginSecure = true;
                savetbldeftofile(dbservername, tablename);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (dbservername.ConnectionContext.IsOpen)
                    dbservername.ConnectionContext.Disconnect();
                Console.ReadKey();
            }
        }

        public static void savetbldeftofile(Server dbservername, string tablename)
        {
            StringBuilder sb = new StringBuilder();

            string tblName = @"\b" + tablename + "\b";

            foreach (Database DBServer in dbservername.Databases)
            {
                Match db = Regex.Match(DBServer.Name, "UserTestDB", RegexOptions.IgnoreCase);
                if (db.Success)
                {
                    foreach (Table dbTbl in DBServer.Tables)
                    {
                        Match tabl = Regex.Match(dbTbl.Name, tblName, RegexOptions.IgnoreCase);
                        if (tabl.Success)
                        {
                            ScriptingOptions soptions = new ScriptingOptions();
                            soptions.ClusteredIndexes = true;
                            soptions.Default = true;
                            soptions.DriAll = true;
                            soptions.Indexes = true;
                            soptions.IncludeHeaders = true;
                            soptions.AppendToFile = true;

                            StringCollection SCollection = dbTbl.Script(soptions);
                            foreach (string str in SCollection)
                            {
                                sb.Append(str);
                                sb.Append(Environment.NewLine);
                            }
                        }

                        StreamWriter sw = File.CreateText(@"c:\temp\" + tablename + ".sql");
                        sw.Write(sb.ToString());
                        sw.Close();
                    }
                }
            }
        }
    }

How would I pass the result of the above C# program to the .NET SQLCLR stored procedure below?

public static void Scriptfile (string table)
{
    SqlPipe Procedure = SqlContext.Pipe;
    Procedure.Send("");
}

The following post from Devart is an excellent example on how to generate the Create T-SQL script from an existing table. http://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query — Edmond Quinton, Oct 12 '16 at 20:47

Solomon Rutzky · Answer 1 · 2016-10-12T19:28:03.747

I am not entirely sure what you mean by:

pass the result of the above C# program to the .NET SQLCLR stored procedure

The code for the console app you have at the moment cannot be placed within a SQLCLR assembly and work as you are using SMO and that is specifically disabled within SQL Server's CLR host.

Generally speaking, you pass values into a SQLCLR object via input parameters, just like a T-SQL stored procedure or function.

Technically, you can keep the SMO code working in the console app and call that .EXE as an external process via Process.Start() within the SQLCLR object, but that requires:

Setting your assembly to PERMISSION_SET = UNSAFE,

and
setting this up to use a work-flow similar to:
1. create a tempfile name in the SQLCLR code (so that concurrently running processes don't accidentally share the same file) using Path.GetTempFileName
2. call the console app, passing in the tempfile name
3. read the tempfile in the SQLCLR object into a string variable
4. delete the tempfile (i.e. cleanup)
5. return the string variable as an out parameter

I'm not sure that it's worth the trouble to do all of that, but that is what it would take to accomplish.

Well, I am trying to script out Create Table by passing the name of the table in the C# Console App but I want to do this in SQL Server. That was the reason why I went down the path of .net SQL CLR Stored Procedure. Is there a better way to accomplish this. "pass the result of the above C# program to the .NET SQLCLR stored procedure" I actually meant if there was a way to send the results of savetbldeftofile() function in the .net SQL CLR Stored Procedure. If there's no other best way to accomplish this I will follow your work-flow. — ITHelpGuy, Oct 12 '16 at 18:59
@user25407 No, there is absolutely no way to use the `SMO` class within SQLCLR. So if you simply must initiate this from a T-SQL stored procedure or function, then you need to run the .EXE as an external process, which will save to a file, and then you read the file. I will update my answer to include deleting the tempfile for cleanup. — Solomon Rutzky, Oct 12 '16 at 19:26

c# program as a .NET SQLCLR stored procedure rewrite

1 Answers1