C# return 3D array from SQL query

Hi Experts

I'm new to C#. I'm migrating some code from VBA to a C# program, I want to pass a SQL query to a class and return an array of results.

so far I have the connection string and it's connecting the SQL db fine, I'm then passing a SQL query to a data reader, however I'm not sure on what I should do with the results.

I'm assuming there is a better process then simply looping through the recordset and building the array?
MrDavidThornAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MrDavidThornAuthor Commented:
class SQL_Array
    {

        string SQL;

         public SQL_Array()
        {

        }

        public SQL_Array(string strSQL )
        {

            SQL = strSQL;
        }

        public string[] SQL_Results()
        {
            string SQL_Results[];


            SqlConnection cnnDIT  = new SqlConnection ("user id=DMT_UAT_WRITER;" +
                                       "password=Bez$p1rachka;server=UUKDMTS01.systems.uk.hsbc;" +
                                       "Trusted_Connection=yes;" +
                                       "database=dmt; " +
                                       "connection timeout=30");
            cnnDIT.Open();

            SqlDataReader DIT_Reader = null;
            SqlCommand DIT_Command = new SqlCommand(SQL,cnnDIT);

            DIT_Reader = DIT_Command.ExecuteReader();


            cnnDIT.Close();

            return SQL_Results[];
        }
    }
0
Carl TawnSystems and Integration DeveloperCommented:
You can call GetValues() on the SqlDataReader to pull values from the current row into an array. But you will still need to read each row from the reader in turn.

Reference: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getvalues(v=vs.110).aspx
0
MrDavidThornAuthor Commented:
For some reason I'm struggling with this ā€“ Iā€™m replicated this VBA code to C#, I want the code accessible to the whole project so am building a class

This is the VBA code ā€“ the user passes a SQL string to the function and a string array of results is returned

Public Function SQLResults_DIT(strSQL As String) As String()
    Set cnt_DIT = New ADODB.Connection
    With cnt_DIT
           .CursorLocation = adUseClient
        .Open cnnDIT
        .CommandTimeout = 600
        Set rst_DIT = .Execute(strSQL)
    End With

        ReDim DITResults(0 To rst_DIT.RecordCount - 1, 0 To rst_DIT.Fields.Count - 1) As String
        Do Until rst_DIT.EOF
            For i = 0 To rst_DIT.Fields.Count - 1        
                    If Not IsNull(rst_DIT.Fields.Item(i)) Then DITResults(j, i) = rst_DIT.Fields.Item(i)              
            Next i
        Loop
        rst_DIT.Close
        Set rst_DIT = Nothing
SQLResults_DIT = DITResults()
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

MrDavidThornAuthor Commented:
So far in C# I have

 // string[] SQL_Results;
            string SQL = "Select * from dmt.dbo.DIT_tblADUpdateFails where txtstaffid is not null";

            SqlConnection cnnDIT = new SqlConnection("user id=DMT_UAT_WRITER;" +
                                       "password=Bez$p1rachka;server=UUKDMTS01.systems.uk.hsbc, 10100;" +
                                       "Trusted_Connection=yes;" +
                                       "database=dmt; " +
                                       "connection timeout=30");
            cnnDIT.Open();

            SqlDataReader DIT_Reader = null;
            SqlCommand DIT_Command = new SqlCommand(SQL, cnnDIT);

            DIT_Reader = DIT_Command.ExecuteReader();

            while (DIT_Reader.Read())
            {
                txtSQL.Text = txtSQL.Text + DIT_Reader.GetString(0) + Environment.NewLine;

            }

           
            cnnDIT.Close();
0
MrDavidThornAuthor Commented:
I'm not sure what variable I need to use for the Array.
0
Carl TawnSystems and Integration DeveloperCommented:
Do you actually need to return an array, or were you just doing that because that was the only option with VBA? A neater approach would be to load you data into a collection of custom objects and return that, instead of just returning a raw array.
0
MrDavidThornAuthor Commented:
Yes I'm only taking this approach as it's the current method. When you say custom objects do you mean a defined variable? - Because I'm passing a SQL statement that can return any amounts of fields
0
Carl TawnSystems and Integration DeveloperCommented:
No, i meant a set of custom classes to hold your data - but that may not work quite so well if you intend to execute arbitary SQL commands.

In your scenario I would simply return a DataTable instead. That will hold all of the data from your query and make it easier to work the with data once you have retrieved it:
string SQL = "Select * from dmt.dbo.DIT_tblADUpdateFails where txtstaffid is not null";

SqlConnection cnnDIT = new SqlConnection("user id=DMT_UAT_WRITER;" +
    "password=Bez$p1rachka;server=UUKDMTS01.systems.uk.hsbc, 10100;" +
    "Trusted_Connection=yes;" +
    "database=dmt; " +
    "connection timeout=30");

// create a new SqlDataAdapter
SqlDataAdaper adap = new SqlDataAdapter(SQL, cnnDIT);

// create an empty DataTable to hold the results
DataTable results = new DataTable();

// populate the DataTable. Note: the SqlDataAdapter will take care of opening/closing the connection for you
adap.Fill(results);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.