?
Solved

C# return 3D array from SQL query

Posted on 2014-03-05
8
Medium Priority
?
553 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

840 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