none
Trying to access values in row.ItemArray RRS feed

  • Question

  • I have a C# WinForms application, connecting to a SQL Server 2012 Express database.
    I also have SQL Server Management Studio.
    I pull data from 2-separate tables and insert them into a temporary table.
    I then try to access the data in the temp table for usage in a report.
    I can "See" the data in SSMS. However, I am unable to access the data via my application.

    When I hover over the dataRow.ItemArray I see the desired data (5-columns of data), but have not been able to successfully assign them to my variables. Each time I iterate through the rows, no data is visible.

    Below, I show one (of several) coding suggestions I found on line, but cannot extract any data.
    BTW, the SQL Server forum thought this to be a .Net issue and not SQL.

    Could someone suggest a practical way to actually extract the values from the ItemArray. Other suggestions online have proven unsuccessful. Also, they were showing how to ADD values to an ItemArray, whereas I need to extract values.

    It may be helpful to mention that 2-of my tables are for inserting AssignmentsIN and another for inserting AssignmentsOUT. As stated above, I pull data from both of these tables and populate a temporary table, from which I populate a report.

    However, if 2-persons both have an assignment Out on the same date, my code updates the temp table with the 2nd AssignmentOUT. This leaves the first few columns of the row pertaining to the AssignmentIN empty, containing a DBNull.Value.

    Also, the inserted values appear to show up at the bottom of SSMS all by themselves.

    Therefore, I'm trying to retrieve those values and, after retrieving the 2nd AssignmentOUT, to also retrieve the original AssignmentIN values to update the beginning columns of the row with the same values that the AssignmentIN contained.

    I hope that explanation makes some sense. That is why I'm trying so hard to access the data I can see (5-columns worth) from the row.ItemArray.

    I need a workable method to actually Access, retrieve values from the row.ItemArray.
        private void btnGetInfo_Click(object sender, EventArgs e)
        {
            string dateOUT = "";
            string subject = "";
            string person = "";
            string location = "";
                
             try
             {
                 using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\AppDB\Database.mdf;Database=MyDatabase;Integrated Security=True;Connection Timeout=30;User Instance=False"))
                 {
                     conn.Open();
                     SqlDataAdapter adap = new SqlDataAdapter();
                     SqlCommand cmdGetInfo = new SqlCommand(
                         "SELECT DateOUT, Subject, Person, Location, DateIN FROM ReportData WHERE DateIN IS NULL AND DateOUT IS NOT NULL ORDER BY DateIN DESC", conn);
     
                     adap.SelectCommand = cmdGetInfo;
                     DataTable dt = new DataTable();
                     adap.Fill(dt);
    
                     int rowCount = dt.Rows.Count;
                     int columnCount = dt.Columns.Count;
                     foreach (DataRow row in dt.Rows)
                     {
                         for (int i = 0; i < columnCount; i++)
                         {
                             DataColumn dcol = dt.Columns[i];
                             for (int iRow = 0; iRow < rowCount; iRow++)
                             {
                                 object cell = dt.Rows[iRow].ItemArray[i];
    			    //How can I assign ItemArray’s values to above variables?
                             }
                          }
                     }
                 }
             }
             catch (SqlException ex) { MessageBox.Show(ex.Message); }
             catch (System.Exception ex) { MessageBox.Show(ex.Message); }
        }




    • Edited by CodingMann Wednesday, September 11, 2019 11:44 PM Clarification of attempt
    Wednesday, September 11, 2019 12:25 AM

Answers

  • Hello Avik Das

    First off, I would like to thank everyone who provided coding examples to try. I now understand how difficult working with ItemArray really is.

    Therefore, I have taken a different approach to the problem. As stated in my earlier comments, I pull data from 2-tables into a temp table, from which I then create a report. Originally, I found that if a 2nd person rec'd an assignment OUT on the same date, the update of the 2nd assignment overwrote the first one. After manipulating the code, I modified an Update statement, adding an Insert, allowing the 2nd assignment to be inserted into the temp table.

    However, this orphaned the ending columns of the assignment Out, leaving the beginning columns of an assignment IN NULL.The report again, only reflected the first assignment OUT.

    To address this issue, I reworked my code to look for those Null values in an assignment IN. I then capture the original assignment IN and update the record containing the assignment OUT with that data.

    This successfully allows the report to "See" both assignments OUT by the same date.

    I use a SqlDataReader to pull the original assignment IN data, followed by an Update.

    I appreciate the input I rec'd and realize that a row.ItemArray is NOT the method to use in the future.

    • Marked as answer by CodingMann Sunday, September 15, 2019 2:45 PM
    Sunday, September 15, 2019 2:44 PM

All replies

  • Hello,

    Using a DataAdapter and DataTable is overkill for this type of operation. A more appropriate method to access data is to use a SqlConnection to connect to the database and a SqlCommand to run the SELECT query. From there use ExecuteReader off the command then in a while loop access each field. In the example below a instance of a class is populated and added to a list (which is not what you are doing of course but the pattern is sound), for you instead of creating an instance of a class use the same GetString, GetInt32 (and there are more e.g. GetDate etc). Ignore the mHasException and mLastException as the are part of a larger code sample.

    /// <summary>
    /// List of countries
    /// </summary>
    /// <returns>All countries</returns>
    public List<Country> Countries()
    {
    	var countryList = new List<Country>(); 
    
    	var selectStatement = "SELECT id , CountryName FROM dbo.Countries;";
    
    	using (var cn = new SqlConnection { ConnectionString = ConnectionString })
    	{
    		using (var cmd = new SqlCommand { Connection = cn })
    		{
    			cmd.CommandText = selectStatement;
    			try
    			{
    				cn.Open();
    				var reader = cmd.ExecuteReader();
    				while (reader.Read())
    				{
    					countryList.Add(new Country()
    					{
    						Id = reader.GetInt32(0),
    						CountryName = reader.GetString(1)
    					});
    				}
    			}
    			catch (Exception e)
    			{
    				mHasException = true;
    				mLastException = e;
    			}
    		}
    	}
    
    	return countryList;
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by D Avik Wednesday, September 11, 2019 5:47 AM
    • Unproposed as answer by CodingMann Wednesday, September 11, 2019 11:05 PM
    Wednesday, September 11, 2019 12:52 AM
    Moderator
  • Hi CodingMann,

    Thank you for posting here.

    You could try the following code to assign value to variable in datatable.

                DataTable table = dataset.Tables[0];
                string dateOUT = "";
                string subject = "";
                string person = "";
                string location = "";
                foreach (DataRow row in table.Rows)
                {
                    dateOUT = row["DateOUT"].ToString();
                    subject = row["Subject"].ToString();
                    person = row["Person"].ToString();
                    location = row["Location"].ToString();
                }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 11, 2019 5:25 AM
    Moderator
  • If I were you, I'll check and see if the SSMS is really connecting database mounted at C:\AppDB\Database.mdf instead of some other location.

    If you just inserted your data, also check and see if you're not under uncommitted transaction. (Easy way to check is to simply run "commit" on the SSMS window and see if any error message come up)

    Wednesday, September 11, 2019 5:46 AM
    Answerer
  • Hi,

    You are using three for loop, instead of one which is actually require; only first one in this context.

    foreach row

    -- for every column

    -- -- for every row

    One last point is that, the variable declared inside the for loop will not available; after execution of this block.

    var items = new List<CustomClass>();
    
    foreach(DataRow row in table.Rows)
    {
        items.add(new CustomClass(){
            DateDt = row[0],
            Name = row[1],
            ...
        });
    }

    You can use the DataTable instance directly into other section of project. So, I don't understand that why you like to assign the value in different variable.

    Anyway, using DataReader is always preferable for this context.



    Avik Das



    • Edited by D Avik Wednesday, September 11, 2019 6:08 AM
    Wednesday, September 11, 2019 5:46 AM
  • I have already tried using a DataReader to pull back the data from my temp table.

    However, this approach does not work. It doesn't give me my data.

    Also, while drilling down into the DataRow, I first discovered the row.ItemArray. This DOES show my 5-columns of data I'm trying to retrieve.

    If I can see the data I'm after by hovering my cursor over row.ItemArray, I just need a concrete method of accessing that data, which clearly shows up in the row.ItemArray.

    Please suggest how to access the row.ItemArray in a practical way.

    Also, this is one database, which is part of a distributed application. I am successfully connecting and  writing to it and retrieving data in other parts of the App.

    Please bear in mind that, my original question was about how to access the ItemArray. The code I provided was but one example I found on line, but doesn't retrieve data. I eagerly await a better suggestion on accessing the data I can see in the row.ItemArray.

    Wednesday, September 11, 2019 11:05 PM
  • Hello Jack. I've already tried this approach, but it doesn't give me my data.

    Also, while drilling down into the DataRow, I first discovered the row.ItemArray. This DOES show my 5-columns of data I'm trying to retrieve.

    If I can see the data I'm after by hovering my cursor over row.ItemArray, I just need a concrete method of accessing that data, which clearly shows up in the row.ItemArray.

    Please suggest how to access the row.ItemArray in a practical way.

    Also, this is one database, which is part of a distributed application. I am successfully connecting and  writing to it and retrieving data in other parts of the App.

    Please bear in mind that, my original question was about how to access the ItemArray. The code I provided was but one example I found on line, but doesn't retrieve data. I eagerly await a better suggestion on accessing the data I can see in the row.ItemArray.

    Wednesday, September 11, 2019 11:07 PM
  • Hello cheong00

    My database is one database, which is part of a distributed application. Yes, SSMS is really connecting to this database at the above location.

    My App is successfully writing to and retrieving data in other parts of the App.

    As stated above, I've discovered that by drilling down into the DataRow, the row.ItemArray, my data is visible when I hover my cursor over row.ItemArray.

    That is why I'm looking for a good method (not to write to) but to Retrieve data from the row.ItemArray.

    Wednesday, September 11, 2019 11:15 PM
  • Hello D Avik

    The example I posted was only one suggestion I found online, for attempting to access the DataRow's row.ItemArray. Obviously, the posted example doesn't work for me.

    One method for accessing row.ItemArray

    I need to retrieve the data for performing a SQL Update.

    I'm looking for a practical example of accessing values in the row.ItemArray because when I drill down into the DataRow I can actually see the value of the 5-columns I'm looking for.

    If anyone can provide a concrete example of Accessing values in a row.ItemArray, I would appreciate it.

    Wednesday, September 11, 2019 11:23 PM
  • I have already tried using a DataReader to pull back the data from my temp table.

    However, this approach does not work. It doesn't give me my data.

    Also, while drilling down into the DataRow, I first discovered the row.ItemArray. This DOES show my 5-columns of data I'm trying to retrieve.

    If I can see the data I'm after by hovering my cursor over row.ItemArray, I just need a concrete method of accessing that data, which clearly shows up in the row.ItemArray.

    Please suggest how to access the row.ItemArray in a practical way.

    Also, this is one database, which is part of a distributed application. I am successfully connecting and  writing to it and retrieving data in other parts of the App.

    Please bear in mind that, my original question was about how to access the ItemArray. The code I provided was but one example I found on line, but doesn't retrieve data. I eagerly await a better suggestion on accessing the data I can see in the row.ItemArray.

    If using a DataReader as I recommended than you are doing something wrong. This is the proper way to get at your data, not using ItemArray.

    Using the method I recommended provides strong typed data while a ItemArray does not even though it[s strong typed they are seen as objects. If you look at Jack's reply below everything is cast to string type while if the field type is say a date or numeric they need to be converted to a date or numeric etc. my recommendation uses methods to get at the correct type for the fields.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, September 12, 2019 12:06 AM
    Moderator
  • Okay, let's look at how much work is involved for working with a ItemArray. Here a table is read into a DataTable then looped through rows passing ItemArray to an instance of a person class to populate a person for each row returned in the DataTable.

    Person class

    public class Person
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string State { get; set; }
        public Person(object[] itemArray)
        {
            if (itemArray.Length == 4)
            {
                Id = Convert.ToInt32(itemArray[0]);
                if (itemArray[1] != DBNull.Value && !string.IsNullOrWhiteSpace(itemArray[1].ToString()))
                {
                    FirstName = itemArray[1].ToString();
                }
    
                if (itemArray[2] != DBNull.Value && !string.IsNullOrWhiteSpace(itemArray[2].ToString()))
                {
                    LastName = itemArray[2].ToString();
                }
    
                if (itemArray[3] != DBNull.Value && !string.IsNullOrWhiteSpace(itemArray[3].ToString()))
                {
                    State = itemArray[3].ToString();
                }
    
            }
        }
    
        public override string ToString()
        {
            return $"{Id}, {FirstName},{LastName},{State}";
        }
    }


    Class to read data without exception handling.

    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace RowItemArrayDemo
    {
        public class Operations
        {
            private string ConnectionString = 
                "Data Source=KARENS-PC;" + 
                "Initial Catalog=MasterDetailSimple;" +
                "Integrated Security=True";
    
    
            public List<Person> LoadData()
            {
                var dt = new DataTable();
                var personList = new List<Person>();
                using (var cn = new SqlConnection {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand() { Connection = cn})
                    {
                        cmd.CommandText = "SELECT id,FirstName,LastName,State FROM Customer";
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
                        foreach (DataRow row in dt.Rows)
                        {
                            personList.Add(new Person(row.ItemArray));
                        }
                    }
                }
    
                return personList;
            }
        }
    }
    

    Bare bones form code

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    
        private void ReadButton_Click(object sender, EventArgs e)
        {
            var ops = new Operations();
            var personList = ops.LoadData();
    
            foreach (var person in personList)
            {
                Console.WriteLine(person);
            }
        }
    }

    Or perhaps using a BindingNavigator

    public partial class Form1 : Form
    {
        private BindingSource _bindingSource = new BindingSource();
        public Form1()
        {
            InitializeComponent();
        }
    
        private void ReadButton_Click(object sender, EventArgs e)
        {
            var ops = new Operations();
    
            _bindingSource.DataSource = ops.LoadData();
            bindingNavigator1.BindingSource = _bindingSource;
    
            FirstNameTextBox.DataBindings.Add("Text", _bindingSource, "FirstName");
            LastNameTextBox.DataBindings.Add("Text", _bindingSource, "LastName");
            KeyTextBox.DataBindings.Add("Text", _bindingSource, "id");
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, September 12, 2019 1:55 AM
    Moderator
  • Hi CodingMann,

    Thanks for the feedback.

    As usual, my solution should be correct. If you want to use row.ItemArray to return data, you could use the following code.

      DataTable table = new DataTable();
                table.Columns.Add("Name");
                table.Columns.Add("Age");
                table.Columns.Add("Id");
                table.Rows.Add("test1", 22, 1001);
                table.Rows.Add("test2", 23, 1002);
                table.Rows.Add("test3", 24, 1003);
                string name = string.Empty;
                string age = string.Empty;
                string id = string.Empty;
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    name = table.Rows[i].ItemArray[0].ToString();
                    age = table.Rows[i].ItemArray[1].ToString();
                    id = table.Rows[i].ItemArray[2].ToString();
                    Console.WriteLine(name+" "+age+" "+id+" ");
                }

    Result:

    If it still doesn't solve your problem, I think your datatable may have some problems. Therefore, I suggest that you could check it before writing the code.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 12, 2019 2:36 AM
    Moderator
  • Working off my last example (sorry for the delay, had other things to attend too) here is the slim down version without a DataTable LoadDataBetter.

    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace RowItemArrayDemo
    {
        public class Operations
        {
            private string ConnectionString = 
                "Data Source=KARENS-PC;" + 
                "Initial Catalog=MasterDetailSimple;" +
                "Integrated Security=True";
    
    
            public List<Person> LoadData()
            {
                var dt = new DataTable();
                var personList = new List<Person>();
                using (var cn = new SqlConnection {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand() { Connection = cn})
                    {
                        cmd.CommandText = "SELECT id,FirstName,LastName,State FROM Customer";
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
                        foreach (DataRow row in dt.Rows)
                        {
                            personList.Add(new Person(row.ItemArray));
                        }
                    }
                }
    
                return personList;
            }
            public List<Person> LoadDataBetter()
            {
                var personList = new List<Person>();
                using (var cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (var cmd = new SqlCommand() { Connection = cn })
                    {
                        cmd.CommandText = "SELECT id,FirstName,LastName,State FROM Customer";
                        cn.Open();
    
                        var reader = cmd.ExecuteReader();
                        if (!reader.HasRows) return personList;
    
                        while (reader.Read())
                        {
                            var values = new object[reader.FieldCount];
                            reader.GetValues(values);
                            personList.Add(new Person(values));
    
                        }
    
                    }
                }
    
                return personList;
            }
        }
    }
    
    The same Person class used in my last reply is used here.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, September 12, 2019 2:37 AM
    Moderator
  • Don't even bother with the .ItemArray! I never use it, it's not necessary to. Keep it simple and do this:
     
    foreach (DataRow row in dt.Rows)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            object cell = row[i]; 
        }
    }




    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, September 12, 2019 5:00 AM
    Moderator
  • Hi CodingMann,

    Finally I understood that you prefer to use DataRow.ItemArray; because you can see some data as below.

    Jack J Jun already shared a sample code, which is relevant as per your requirement. Only one point I like to add that everything might not be a string, and not null.

    foreach(DataRow row in dt.Rows)
    {
        DateTime? dateOut = row.ItemArray[0] as DateTime?;
        String subject = row.ItemArray[1] as string;
    }

    This is tested from my end, and you can see below-


    Avik Das

    Thursday, September 12, 2019 8:31 PM
  • Hello Avik Das

    First off, I would like to thank everyone who provided coding examples to try. I now understand how difficult working with ItemArray really is.

    Therefore, I have taken a different approach to the problem. As stated in my earlier comments, I pull data from 2-tables into a temp table, from which I then create a report. Originally, I found that if a 2nd person rec'd an assignment OUT on the same date, the update of the 2nd assignment overwrote the first one. After manipulating the code, I modified an Update statement, adding an Insert, allowing the 2nd assignment to be inserted into the temp table.

    However, this orphaned the ending columns of the assignment Out, leaving the beginning columns of an assignment IN NULL.The report again, only reflected the first assignment OUT.

    To address this issue, I reworked my code to look for those Null values in an assignment IN. I then capture the original assignment IN and update the record containing the assignment OUT with that data.

    This successfully allows the report to "See" both assignments OUT by the same date.

    I use a SqlDataReader to pull the original assignment IN data, followed by an Update.

    I appreciate the input I rec'd and realize that a row.ItemArray is NOT the method to use in the future.

    • Marked as answer by CodingMann Sunday, September 15, 2019 2:45 PM
    Sunday, September 15, 2019 2:44 PM