Solved

C# return 3D array from SQL query

Posted on 2014-03-05
8
517 Views
Last Modified: 2014-03-17
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?
0
Comment
Question by:MrDavidThorn
  • 5
  • 3
8 Comments
 

Author Comment

by:MrDavidThorn
ID: 39906174
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39906189
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
 

Author Comment

by:MrDavidThorn
ID: 39912565
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:MrDavidThorn
ID: 39912571
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
 

Author Comment

by:MrDavidThorn
ID: 39912572
I'm not sure what variable I need to use for the Array.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39912607
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
 

Author Comment

by:MrDavidThorn
ID: 39912659
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
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39917039
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

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question