Quick insert from C# CLR

Question

I am working on a huge set of data, and using CLR for processing it. The CLR processing is working quick, but I need a quick way to move the processed data to the database(through CLR).

For example, see the following clr code

protected static string Normalize(string s) // space and special character remover
    {
        char[] arr = s.ToCharArray();
        arr = Array.FindAll<char>(arr, (c => char.IsLetterOrDigit(c)));
        return new string(arr).ToLower();
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void udpNormStr ()
    {
        SqlConnection con = new SqlConnection("context connection = true");
        SqlCommand cmd = new SqlCommand("Select cName from NamesTable", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataTable fill = new DataTable();
        fill.Columns.Add("NormName", typeof(string));
        da.Fill(dt);
        cmd.CommandText = "insert into NormTable values (@nName)";
        cmd.Parameters.Add("@nName", SqlDbType.VarChar);
        foreach (DataRow row in dt.Rows)
        {
            fill.Rows.Add(Normalize(row[0].ToString()));
        }
        con.Open();
        foreach (DataRow row in fill.Rows)
        {
            cmd.Parameters["@nName"].Value = row[0];            
            cmd.ExecuteNonQuery();            
        }
        con.Close();
}

It is taking lot of time to execute, and is wasting 90% of that time in the insert operations. Please suggest a better way of moving processed data to database(through CLR).

Answer 1

If this work is SQL/CLR, then that is tricky. One idea might be to make that method only return the data, for example as a CLR Table-Valued Function, and then do the INSERT back in TSQL pulling from the table-valued function.