Convert SqlDataSource to DataTable and DataView

Question

I have the following SqlDataSource and I want to convert it to DataView and read a column from it:

SELECT     
    dbo.Divisions.DivisionShortcut, 
    COUNT(DISTINCT dbo.UserQuiz.Username) AS [Number of Participants]
FROM         
    dbo.Divisions 
INNER JOIN
    dbo.employee ON dbo.Divisions.SapCode = dbo.employee.DivisionCode 
INNER JOIN
    dbo.UserQuiz ON dbo.employee.Username = dbo.UserQuiz.Username 
INNER JOIN
    dbo.Quiz ON dbo.UserQuiz.QuizID = dbo.Quiz.QuizID
WHERE     
    (dbo.Quiz.QuizID = @QuizID)
GROUP BY 
    dbo.Divisions.DivisionShortcut

This SqlDataSource lets the user enter the number of the Quiz, and it will retrieve the total number of participants in that quiz. I want to convert this SqlDataSource to a DataTable and read a column from it.

So how to do that?

What you have is a SQL statement, not a SqlDataSource. Do you have any code for the creation of your SqlDataSource that is not working? — Metro Smurf
– Metro Smurf, Commented Feb 26, 2012 at 16:42
Reading a columns is not the issue after conversion into datatable — Sami
– Sami, Commented Nov 16, 2012 at 7:37

Sami · Accepted Answer · 2023-09-26 04:38:30Z

19

To get datasource

SqlDataSource SqlDataSource1 = new SqlDataSource(connectionString, selectQuery);

After you got a data source say SqlDataSource1

DataSourceSelectArguments args = new DataSourceSelectArguments();
DataView view = (DataView)SqlDataSource1.Select(args);
DataTable dt = view.ToTable();

Now you can read a column easily from this dt(DataTable) e.g.

int columnNumber = 0;
for(int i=0;i<dt.Rows.Count;i++)
{
    MessageBox.Show(dt.Rows[i][columnNumber].ToString());
    //Console.log(dt.Rows[i][columnNumber].ToString());
}

Reference

edited Sep 26, 2023 at 4:38

answered Nov 16, 2012 at 7:23

Sami

8,4599 gold badges72 silver badges104 bronze badges

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

Comments

7wp · Accepted Answer · 2016-07-19 12:24:31Z

6

The SqlDataSource has a Select method which you can call to get back a DataView from your SqlDataSource - see the MSDN documentation on that.

SqlDataSource dataSource = new SqlDataSource(connectionString, selectSql);
DataView view = (DataView)dataSource.Select(args);

DataTable table = view.ToTable();

Is that what you're looking for??

edited Jul 19, 2016 at 12:24

7wp

12.7k20 gold badges80 silver badges108 bronze badges

answered Feb 26, 2012 at 15:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

Convert SqlDataSource to DataTable and DataView

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related