Solved

C# return 3D array from SQL query

Posted on 2014-03-05
8
506 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
crm development 2 37
SQl help with selection 14 44
Form design in vb.net 7 19
COnsume rest client 6 11
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now