Displaying data from SQL database in C# WinForm controls (textBox, comboBox, label)

Question

I have a performance problem with displaying data from an SQL database in my application. The problem is that I have a large number of parameters that I need to display (customers personal data, his current statistics etc.).

So far I've used either SqlCommand.ExecuteScalar (for single parameters), or DataTable.Rows[].ItemArray.GetValue() (for multiple parameters - I fill the DataTable with SqlDataAdapter whose query withdraws the necessary data from the database) and assigned their values to the appropriate control. Assuming that command is an SqlCommand type:

For single parameter

command.CommandText = "SELECT Parameter1 FROM MyTable WHERE Condition = Value";
 textBox1.Text = command.ExecuteScalar().ToString();

For multiple parameters (SDA is a SqlDataAdapter):

command.CommandText="SELECT Parameter1 - ParameterN FROM MyTable WHERE Condition = Value";
 SDA.SelectCommand = command;
 SDA.Fill(MyDataTable);
 textBox1.Text = MyDataTable.Rows[0].ItemArray.GetValue(0).ToString();
 comboBox1.Text = MyDataTable.Rows[0].ItemArray.GetValue(1).ToString();
/*
I repeat similar lines of code for each parameter and display it in the appropriate control.
*/

This approach works correctly but when I have a large number of parameters (20+), it works very slowly.

Is there a more efficient way to display these amounts of data, and how would I implement it?

Thank you

Seems a pretty simple job. However, if you don't show your code it is nearly impossible to help — Steve
– Steve, Commented May 7, 2013 at 8:46
Ok, I've edited my question. Like I said, I have a working solution, now I'm looking for a working and efficient solution. — NDraskovic
– NDraskovic, Commented May 7, 2013 at 8:57

Steve · Accepted Answer · 2013-05-07 09:10:45Z

2

Probably, with the second example, a SqlDataReader will perform better because you read the values just one time, while with a DataAdapter, you need to load the DataTable and then loop over the rows of the table (Effectively reading data two times).

command.CommandText="SELECT Field1,...,FieldN FROM MyTable WHERE Condition = Value";
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
    // Of course this works correctly just if your query returns one row....
    textBox1.Text = reader.GetString(0);
    comboBox1.Text = reader.GetString(n);
}

You could also try with the Field<T> extension for the DataRow

command.CommandText="SELECT Field1,...,FieldN FROM MyTable WHERE Condition = Value";
SqlDataAdapter SDA = new SqlDataAdapter(command);
SDA.Fill(MyDataTable);
textBox1.Text = MyDataTable.Rows[0].Field<string>("Field1");
comboBox1.Text = MyDataTable.Rows[0].Field<string>("FieldN");

However, I think that the real performance gain would be in the query that you submit to the database engine and in the correct working of indexes on your tables. Try to retrieve the minimun number of rows possible, search on indexed fields and/or change to a stored procedure.

answered May 7, 2013 at 9:10

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NDraskovic Over a year ago

Well, I don't think that the query is the problem because it is pretty straight forward - it takes the data about a specific customer (in the WHERE clause I filter by the customers unique number, so the query always returns only one row). Because I always have only one row, I do not need to loop trough the DataTable, the problem (in my opinion) is simply in the number of parameters I need to display (I need one line of code for each parameter).

Steve Over a year ago

Well, slowness is often a personal perception. There is no big difference in reading 5 values from reading 20 values. I think that your code is already good as is, however, to have a frame of reference, I think you need to measure the slowness (Use a Stopwatch class with 5 and 20 values and look at the results)

NDraskovic Over a year ago

Ok thanks, to be honest, I still haven't performed the real testing process, I only test the functionality by debugging so the process might work faster when I install it. Thanks anyway

brasofilo · Accepted Answer · 2014-04-28 10:17:27Z

here i had write sample stored procedure in wich you can get idea...

you can pass as amny parameter as you can in xml format and insert into temp table...

now you have table with value Name/value pair means Paramater name /value....

now you can do your furteher work...

    /*
    EXEC wa_TempGetDaya '<SampleXML>
    <tblXML><AccountID>3</AccountID><Code>11</Code><Description>Leptospiral infect NEC</Description></tblXML> 
    </SampleXML>'
    */
    CREATE PROCEDURE  wa_TempGetDaya
    (   
        @ParaXML NVARCHAR(MAX)  
    )  
    AS  
    SET NOCOUNT ON

    BEGIN

        DECLARE @AccountID INT
        DECLARE @MyXML XML
        SET @MyXML = @ParaXML  

        IF OBJECT_ID('tempdb..#TempData') IS NOT NULL
        DROP TABLE #TempData 

        SELECT * INTO #TempData 
        FROM (
               SELECT
                   Parse.value('(AccountID)[1]', 'INT') AS 'AccountID',
                   Parse.value('(Code)[1]', 'Varchar(100)') AS 'Code',
                   Parse.value('(Description)[1]', 'varchar(1000)') AS 'Description'
                FROM
                   @MyXML.nodes('/SampleXML/tblXML') AS YourData(Parse) 
              ) AS tbl



  declare @para1 varchar(20)
    declare @para2 varchar(20)
    declare @para3 varchar(20)


SELECT @para1 =AccountID ,@para2 =Code,@para3 =Description from #TempICD

    END

Collectives™ on Stack Overflow

Displaying data from SQL database in C# WinForm controls (textBox, comboBox, label)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related