Can excel dna return a full table or column from sql to excel?

May 2, 2014 at 2:55 PM
I have this code of a UDF that displays in excel "Hello " + the first name from the column of names from an sql database table named "person", is there a way that i can modify it in a way that excel will show me the first 3 or 5 names, or even the full column?
    [ExcelFunction(Description = "Test")]
    public static string Test(string hello)
    {
        String ConnStr = @"Data Source=*****; Initial Catalog=*****; User ID=*****; Password=*****;
        Trusted_Connection=False";

        SqlConnection conn = new SqlConnection(ConnStr);
        conn.Open();

        DataSet d = new DataSet();
        SqlCommand com = new SqlCommand(@"SELECT TOP 1 FirstName + ' ' + LastName AS Name FROM Person", conn);

        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = com;
        sda.Fill(d);

        DataTable data = d.Tables[0];

        foreach (DataRow dr in data.Rows)
        {
            return hello + ' ' + dr[0].ToString();
        }
        return "unsuccesfull";
    }
Thanks in advance.
May 2, 2014 at 8:05 PM
Fixed my problem using an array as the return value, selecting the cells in Excell and using (ctrl + shift + enter) to enter the formula, i'm kind of new to this, thanks anyways.
Marked as answer by maximushuertus on 5/2/2014 at 1:06 PM