[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to get datagridview in C sharp to display a relational sql query from SQL Server connection?

Posted on 2011-04-26
3
Medium Priority
?
710 Views
Last Modified: 2012-05-11
OK.  New to C Sharp and data connections.  Still trying to forget ADO and absorb ADO.NET and its table adapters, datasets, etc.    Many training samples provided online have one table (if lucky maybe two) but according to the following SQL statement I have three that have a relationship between such and also has expressions - like Database Name parses out the fieldname DBName and gives only the file name and not the entire path.  

So how do I tie such to a datagridview using Visual Studio 2010 C Sharp?

I did make an attempt using a data reader as the first code snippet show -  but do not know how to take my datagridview (called dgvLogger) and apply each column as getting the data.

I did also try to do the dataset via the wizard as the image shows but after I go to the dsLogger dataset and make that the datasource for the datagridview - when I go to the datapropertyname no fields show up like they are supposed to?

Any help is appreciated!!!
string sSQL = "";

            // had to adjust CHARINDEX('\\' 

            sSQL = "SELECT REPLACE(REVERSE(LEFT(REVERSE(DBName),CHARINDEX('\\', REVERSE(DBName),1)-1)), '.accdb', '') AS [Database Name], Access_USERS.[Full Name] as [User], Access_USERS.EXt as Ext, Access_USERS.CPU, substring(convert(varchar(20), Access_LOGGER.InDate, 9), 13, 5) as [Time In]";
            sSQL = sSQL + " FROM (Access_LOGGER INNER JOIN Access_DBLIST ON Access_LOGGER.fDBID = Access_DBLIST.ID) INNER JOIN Access_USERS ON Access_LOGGER.fUserID = Access_USERS.ID";
            sSQL = sSQL + " WHERE Access_USERS.WinSign<>'slecompte' AND Access_LOGGER.OutDate Is Null";
            sSQL = sSQL + " ORDER BY [Time In]";

            // do not put \\EC-SQL front of

            string conn = "Data Source=EC-SQL;Initial Catalog=NEWEQ;User Id=NEWEQ;Password=n3w3@!;";

            using (SqlConnection connection =
                        new SqlConnection(conn))
            {

                
            

                SqlCommand command =
                    new SqlCommand(sSQL, connection);


                SqlDataReader reader = command.ExecuteReader();


                while (reader.Read())
                {
                    MessageBox.Show(String.Format("{0}, {1}, {2}, {3}, {4}",
                        reader[0], reader[1], reader[2], reader[3], reader[4]));
                }

                reader.Close();

                
            }

Open in new window

SELECT REPLACE(REVERSE(LEFT(REVERSE(DBName),CHARINDEX('\\', REVERSE(DBName),1)-1)), '.accdb', '') AS [Database Name], Access_USERS.[Full Name] as [User], Access_USERS.EXt as Ext, Access_USERS.CPU, substring(convert(varchar(20), Access_LOGGER.InDate, 9), 13, 5) as [Time In]
FROM (Access_LOGGER INNER JOIN Access_DBLIST ON Access_LOGGER.fDBID = Access_DBLIST.ID) INNER JOIN Access_USERS ON Access_LOGGER.fUserID = Access_USERS.ID
WHERE Access_USERS.WinSign<>'slecompte' AND Access_LOGGER.OutDate Is Null
ORDER BY [Time In]

Open in new window

logger.png
0
Comment
Question by:stephenlecomptejr
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
Mez4343 earned 2000 total points
ID: 35476237
DataReader is deisgned to read one row at a time. Inorder to bind your result set to the datagridview you need to read each row from the reader and populate some kind of array. This tutorial should help you get started.

http://www.codeproject.com/KB/grid/dataGridview-DataReader.aspx
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 35479855
This is really perfect.  Please allow me to do tomorrow morning to try.

Thanks, mez4343.
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 35486458
Thanks again!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 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