C# return 3D array from SQL query

MrDavidThorn
MrDavidThorn used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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[];
        }
    }
Carl TawnSenior Systems and Integration Developer

Commented:
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

Author

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()
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

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();

Author

Commented:
I'm not sure what variable I need to use for the Array.
Carl TawnSenior Systems and Integration Developer

Commented:
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.

Author

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
Senior Systems and Integration Developer
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial