how to loop sql server table to create a datagridview - sql table field matches csv field

Question

0.00/5 (No votes)

See more:

C#

if (this.DataCenterLaborFileUpload.HasFile)
            {
                var extension = Path.GetExtension(DataCenterLaborFileUpload.FileName);
                string currentName = "";
                decimal currentHours = 0.00M;
                decimal currentFTE = 0.00M;
                string ResourceName = "";
                
                if (extension == ".csv")
                {
                    StreamReader csvreader = new StreamReader(DataCenterLaborFileUpload.FileContent);
                    DataTable dt = new DataTable();
                    dt.Columns.Add("txtName");
                    dt.Columns.Add("txtHours");
                    dt.Columns.Add("txtFTE");

                    while (!csvreader.EndOfStream)
                    {
                        DataRow dr = dt.NewRow();
                       
                        var line = csvreader.ReadLine();
                        var values = line.Split(',');

                        if (values[0].Trim() != "Pers.No.")
                        {
                            SqlCommand cmd = new SqlCommand("SELECT ResourceName FROM StaffTracking where PersonnelResourceType  = 'Supplier' order by PersonnelResourceType, ResourceName");
                            cmd.Connection = conn;
                            conn.Open();                         
                            if (ResourceName == (values[1].Trim()))
                            {
                                
                            
                                if (values[1].Trim() == currentName)
                                {

                                    currentHours = currentHours + Convert.ToDecimal(values[9].Trim());

                                }
                                else
                                {
                                    if (currentName != "")
                                    {
                                        dr["txtName"] = currentName;
                                        dr["txtHours"] = currentHours;
                                        dr["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);

                                        dt.Rows.Add(dr);
                                        dt.AcceptChanges();
                                       
                                    }

                                    currentHours = Convert.ToDecimal(values[9].Trim());
                                    currentName = values[1].Trim();
                                }

                            }
                              
                        }
                        
                    }
                    
                    DataRow drfinal2 = dt.NewRow();
                    drfinal2["txtName"] = currentName;
                    drfinal2["txtHours"] = currentHours;
                    drfinal2["txtFTE"] = currentFTE + currentHours / (weekdaysInMonth() * 8);
                    dt.Rows.Add(drfinal2);
                    dt.AcceptChanges();


                    gvDataCenterLabor.DataSource = dt;

                }
                conn.Close();

                gvDataCenterLabor.DataBind();
               
                //     Page.DataBind();
            }
            
            
        }

Posted 28-Mar-15 10:09am

Norris Chappell

Updated 28-Mar-15 11:39am

v4

Add a Solution

Comments

Sascha Lefèvre 28-Mar-15 16:56pm

If you want a helpful answer you should provide an explanation of your problem and not just throw your code at us.

Norris Chappell 28-Mar-15 17:15pm

Sorry it says I only have 20-200 characters. My issue is that I can't seen to get the datagridview to show only the first record in my csv file. It must loop through the csv adding the hours then proceed to the record person. It they are not a supplier then skip that person and go to the next person. If I take out my select statement it writes out both suppliers and others.

Sascha Lefèvre 28-Mar-15 17:31pm

"Sorry it says I only have 20-200 characters." - I think that's just for the title. I don't know if there's a restriction for the body of the post but you're nowhere near it :)

I would suggest: Use the "Improve question"-Link at the bottom of your question to edit it and 1) include a sample csv file (or a meaningful excerpt) and 2) your above comment in a slightly more detailed form. I have some rough idea what you want but not really. What I can tell you right away though: Your database connection doesn't do anything. Are you aware of that?

Norris Chappell 28-Mar-15 17:33pm

Sascha, I am not sure where to put the loop of the sql server table in my code. That is my issue. Thank you for any suggestions.

Sascha Lefèvre 28-Mar-15 17:54pm

I can't tell you without seeing your csv..

Sascha Lefèvre 28-Mar-15 18:06pm

You create an SqlCommand and you open the SqlConnection but you never actually execute the query. Your variable "ResourceName" will always be an empty string.

Norris Chappell 28-Mar-15 18:11pm

That is true when I was stepping through the code. So what did I leave out?

Norris Chappell 28-Mar-15 18:12pm

Pers.No. Name HomeFID Copy Range/Value from Column V Worked Date Job Code Account Pay Type Activity Type Hours
81047641 Jones Beverly J USA7064810 US1-08333.01.08.02.03 20150223 00S15H 9610000163 410 8
81047641 Jones Beverly J USA7064810 US1-08333.01.08.02.03 20150210 00S15H 9610000163 410 9

the Pers.No., Name and hours are the only fields I am using from the csv file.

The stafftacking table has the following fields:
SELECT TOP 1000 [ID]
,[Project]
,[Tower]
,[FunctionalArea]
,[ResourceName]
,[CATWResourceName]
,[Org]
,[IndicateifBillingFP]
,[IndicateifBillingTM]
,[DefaultFTE]
,[Active]
,[PersonnelResourceType]
FROM [SP2010_EDCStaffing_AppDB].[dbo].[StaffTracking]

My select statement is only using ResourceName and PersonnelResourceTpe.
If the Resourcename is a supplier then I want to compare to the csv file and add up the hours. Then show only the supplier hours and etc.

Norris Chappell 28-Mar-15 18:17pm

reader = myCommand.ExecuteReader(); Did I leave out this command?

Sascha Lefèvre 28-Mar-15 18:46pm

Correct, and then reading the result. I posted a solution, please see below.

You should make it a habit stepping through your code in debug mode and comparing each variable to your expectation of what it should be if something doesn't work - that's the basic idea of debugging ;-)

2 solutions

Solution 3

C#

if (this.DataCenterLaborFileUpload.HasFile)
            {
                var extension = Path.GetExtension(DataCenterLaborFileUpload.FileName);
                string currentName = "";
                decimal currentHours = 0.00M;
                decimal currentFTE = 0.00M;
                string ResourceName = "";
                
                if (extension == ".csv")
                {
                    StreamReader csvreader = new StreamReader(DataCenterLaborFileUpload.FileContent);
                    DataTable dt = new DataTable();
                    dt.Columns.Add("txtName");
                    dt.Columns.Add("txtHours");
                    dt.Columns.Add("txtFTE");

                    while (!csvreader.EndOfStream)
                    {
                        DataRow dr = dt.NewRow();
                       
                        var line = csvreader.ReadLine();
                        var values = line.Split(',');

                        if (values[0].Trim() != "Pers.No.")
                        {
                            using (DbConnection conn = new SqlConnection("SQLStaffingConn"))

                            {
                                conn.Open();

                                using (DbCommand cmd = conn.CreateCommand())
                                {
                                    cmd.Connection = conn;
                                    cmd.CommandText = "SELECT ResourceName FROM StaffTracking where PersonnelResourceType  = @restype order by PersonnelResourceType, ResourceName";
                                    DbParameter p1 = cmd.CreateParameter();
                                    p1.ParameterName = "restype";
                                    p1.Value = "Supplier";
                                    cmd.Parameters.Add(p1);

                                    using (DbDataReader row = cmd.ExecuteReader())
                                    {
                                        while (row.Read())
                                        {
                                            string col2  = row.GetString(1);
                                        }
                                    }
                                }
                            }
                               if  (ResourceName == (values[1].Trim()))
                               {
                                
                            
                                if (values[1].Trim() == currentName)
                                {

                                    currentHours = currentHours + Convert.ToDecimal(values[9].Trim());

                                }
                                else
                                {
                                    if (currentName != "")
                                    {
                                        dr["txtName"] = currentName;
                                        dr["txtHours"] = currentHours;
                                        dr["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);

                                        dt.Rows.Add(dr);
                                        dt.AcceptChanges();
                                       
                                    }

                                    currentHours = Convert.ToDecimal(values[9].Trim());
                                    currentName = values[1].Trim();
                                }

                            }
                               
                        }
                         
                    }
                    
                    DataRow drfinal2 = dt.NewRow();
                    drfinal2["txtName"] = currentName;
                    drfinal2["txtHours"] = currentHours;
                    drfinal2["txtFTE"] = currentFTE + currentHours / (weekdaysInMonth() * 8);
                    dt.Rows.Add(drfinal2);
                    dt.AcceptChanges();


                    gvDataCenterLabor.DataSource = dt;

                }
                

                gvDataCenterLabor.DataBind();
               
                //     Page.DataBind();
            }
            
            
        }

Posted 28-Mar-15 15:14pm

Norris Chappell

Comments

Norris Chappell 28-Mar-15 21:30pm

Sorry. I didn't understand what you were saying. I want to thank you for all of your help. I have been working on this for quite awhile.

Sascha Lefèvre 28-Mar-15 22:00pm

You're welcome!

You want to read ResourceName from the database-query-result into your variable "ResourceName", right? Then instead of

string col2 = row.GetString(1);

you have to write

ResourceName = row.GetString(0);

and you need to move all the code that should use the variable "ResourceName" into the loop below that line, otherwise, like your code looks currently, "ResourceName" will have only the value of the very last result record (see my 3rd last comment).

Norris Chappell 28-Mar-15 21:58pm

Removing the quotes gives me errors.

Sascha Lefèvre 28-Mar-15 22:04pm

In your original post it is not visible where your SqlConnection-Object "conn" is being created. Wherever you do that you should have some kind of connection string (!?) - otherwise please post the code-part where you (originally) created the "conn" object.

Norris Chappell 28-Mar-15 22:43pm

public SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);

Sascha Lefèvre 28-Mar-15 22:50pm

Yep, that's it.

using (DbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString))

I'm out for today. Gn8 and good luck! :)

Norris Chappell 28-Mar-15 22:51pm

string col2 = row.GetString(1);

you have to write

ResourceName = row.GetString(0);
I change this and got an error. Error 2 A local variable named 'ResourceName' cannot be declared in this scope because it would give a different meaning to 'ResourceName', which is already used in a 'parent or current' scope to denote something else C:\Users\pzd74f\Downloads\SharePointProjects\SharePointProjects\StaffingWebParts\StaffingWebParts\ImportDataCenterandCATWData\ImportDataCenterandCATWDataUserControl.ascx.cs 196 52 StaffingWebParts

Norris Chappell 29-Mar-15 11:18am

It is now working properly. Thanks again for all of your valuable information, help and time.

Sascha Lefèvre 3-Apr-15 9:55am

Ah, just found your last comment by chance - didn't get a notification because you replied to yourself ;-)

You're welcome, glad I could help!

Please consider marking my solution as accepted answer :-)

Norris Chappell 3-Apr-15 13:07pm

The information and answer provided by Sascha Lefevre was right on point and very helpful in resolving my project.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Accepted Answer · 2015-03-28T12:43:00

Solution 2

I don't know if your code will work 100% after you apply this but at least it will solve the issue reading from the database.

A sample database-access (for SQL-Server), from opening the connection to reading all the records, applying good practices (using-Blocks, Sql-Parameters and abstract Db*****-baseclasses instead of database-specific classes):

C#

using (DbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
 
    using (DbCommand cmd = conn.CreateCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT col1, col2 FROM table1 WHERE col3 = @p1;";
        // let's assume col1 is an int (Int32); col2 and col3 a nvarchar (String)

        DbParameter p1 = cmd.CreateParameter();
        p1.ParameterName = "p1";
        p1.Value = "something";

        cmd.Parameters.Add(p1);
 
        using (DbDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read()) // loops through all result records
            {
                int col1 = dr.GetInt32(0);
                string col2 = dr.GetString(1);

                // do something with col1 and col2 here
            }
        } // the datareader will be closed and disposed here
    } // the command will be disposed here
} // the connection will be closed and disposed here

Edit after comment:

Sql-Parameters are the best way to "communicate" your query-constraints to the database. In your case, instead of this:

SQL

SELECT ResourceName FROM StaffTracking where PersonnelResourceType  = 'Supplier' order by PersonnelResourceType, ResourceName

you could write this:

SQL

SELECT ResourceName FROM StaffTracking where PersonnelResourceType = @restype order by PersonnelResourceType, ResourceName

and create an Sql-Parameter (in this case named "restype") that carries the desired value (in this case "Supplier") and append it to the Parameter-Collection of the Sql-Command:

C#

DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "restype";
p1.Value = "Supplier";
cmd.Parameters.Add(p1);

Think of it like some sort of variable for SQL-commands. SQL-Server takes your query-string and replaces all occurrences of those Parameter-Names (starting with an @) by the value that you assigned to the Sql-Parameter-Object with the same name.

It is "the best way" because:
- If you develop an application that stores input from strangers (e.g. website-visitors) into your database, you avoid the risk of SQL-Injection-Attacks[^]
- It is better maintainable because your query-string is easily readable
- In case of string-values you avoid potential SQL-syntax errors in case the string contains quotes

Edit 2:

The last two points don't directly apply to your previous code but very often you see people constructing their query-strings like this:

C#

string query = "SELECT col1 FROM table1 WHERE col2 = '" + someVariable + "' AND col3 = '" someOtherVariable "'";

Posted 28-Mar-15 12:43pm

Sascha Lefèvre

Updated 28-Mar-15 13:39pm

v3

Comments

Norris Chappell 28-Mar-15 19:12pm

Replace SELECT col1, col2 FROM table1 WHERE col3 = @p1 with
SELECT ResourceName FROM StaffTracking where PersonnelResourceType = 'Supplier' order by PersonnelResourceType, ResourceName

I don't understand this part?
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "p1"; What goes here?
p1.Value = "something"; What goes here?

Norris Chappell 28-Mar-15 19:20pm

p1.ParameterName = "PersonnelResourceType";
p1.Value = "Supplier";

cmd.Parameters.Add(p1); Why add?
Is this code a replacement for my while statement?

Sascha Lefèvre 28-Mar-15 19:33pm

I updated the solution, please take a look.

> cmd.Parameters.Add(p1); Why add?
> Is this code a replacement for my while statement?

The Sql-Parameter needs to be "transported" to the database-server - for that to happen it must be added to the Parameter-Collection of the Sql-Command.

Sascha Lefèvre 28-Mar-15 19:39pm

Edited a second time.

Norris Chappell 28-Mar-15 20:29pm

using (DbConnection conn = new SqlConnection(connectionString))
{
conn.Open();

using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT ResourceName FROM StaffTracking where PersonnelResourceType = @restype order by PersonnelResourceType, ResourceName";
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "restype";
p1.Value = "Supplier";
cmd.Parameters.Add(p1);

using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Reader())
{

}
}
}
}

I getting errors:
Error 2 The type or namespace name 'DbConnection' could not be found (are you missing a using directive or an assembly reference?) C:\Users\pzd74f\Downloads\SharePointProjects\SharePointProjects\StaffingWebParts\StaffingWebParts\ImportDataCenterandCATWData\ImportDataCenterandCATWDataUserControl.ascx.cs 176 36 StaffingWebParts
Error 3 The name 'connectionString' does not exist in the current context C:\Users\pzd74f\Downloads\SharePointProjects\SharePointProjects\StaffingWebParts\StaffingWebParts\ImportDataCenterandCATWData\ImportDataCenterandCATWDataUserControl.ascx.cs 176 74 StaffingWebParts
Error 4 The type or namespace name 'DbCommand' could not be found (are you missing a using directive or an assembly reference?) C:\Users\pzd74f\Downloads\SharePointProjects\SharePointProjects\StaffingWebParts\StaffingWebParts\ImportDataCenterandCATWData\ImportDataCenterandCATWDataUserControl.ascx.cs 180 40 StaffingWebParts
Error 5 The type or namespace name 'DbParameter' could not be found (are you missing a using directive or an assembly reference?) C:\Users\pzd74f\Downloads\SharePointProjects\SharePointProjects\StaffingWebParts\StaffingWebParts\ImportDataCenterandCATWData\ImportDataCenterandCATWDataUserControl.ascx.cs 184 37 StaffingWebParts
Error 6 The type or namespace name 'DbDataReader' could not be found (are you missing a using directive or an assembly reference?) C:\Users\pzd74f\Downloads\SharePointProjects\SharePointProjects\StaffingWebParts\StaffingWebParts\ImportDataCenterandCATWData\ImportDataCenterandCATWDataUserControl.ascx.cs 189 44 StaffingWebParts
Error 7 A local variable named 'dr' cannot be declared in this scope because it would give a different meaning to 'dr', which is already used in a 'parent or current' scope to denote something else C:\Users\pzd74f\Downloads\SharePointProjects\SharePointProjects\StaffingWebParts\StaffingWebParts\ImportDataCenterandCATWData\ImportDataCenterandCATWDataUserControl.ascx.cs 189 57 StaffingWebParts

Am I missing something else?

Sascha Lefèvre 28-Mar-15 20:35pm

Error 3: In my sample I used "connectionString" to indicate that there should either go your connection-string or a variable named "connectionString" containing your connection-string. You don't show that part in your code-post but there should be something like that somewhere already.

Error 7: I named the DbDataReader "dr" but you already named your DataRow "dr". Rename one of them. I would recommend renaming the DataRow to either "row" or "dataRow".

All other errors: place this among your using-Directives at the top of the source-file:
using System.Data.Common;

Norris Chappell 28-Mar-15 20:50pm

I am also there. I not sure how to handle the ResourceName I get from the table
while (row.Read())
{
string col2 = row.GetString(1);
}
}
}
}
if ( ResourceName == (values[1].Trim()))
It is giving me a error when I use Resourcename?

Sascha Lefèvre 28-Mar-15 21:01pm

In the code you just posted you didn't fully adapt my sample-code to your case. You select only one column, so it's in index 0 of the datareader. And you read it into "col2", not into "ResourceName". I think it should look like this:

while (row.Read())
{
  ResourceName = row.GetString(0);

  if (ResourceName == values[1].Trim())
  {
    // ...
  }
}

If you run into yet another error, please edit your question and add your re-worked code as a second code-block so I can see "the full picture" ;-)

Norris Chappell 28-Mar-15 21:12pm

okay sorry
I only have this one last issue and I think I am done.
Format of the initialization string does not conform to specification starting at index 0.

if (this.DataCenterLaborFileUpload.HasFile)
{
var extension = Path.GetExtension(DataCenterLaborFileUpload.FileName);
string currentName = "";
decimal currentHours = 0.00M;
decimal currentFTE = 0.00M;
string ResourceName = "";

if (extension == ".csv")
{
StreamReader csvreader = new StreamReader(DataCenterLaborFileUpload.FileContent);
DataTable dt = new DataTable();
dt.Columns.Add("txtName");
dt.Columns.Add("txtHours");
dt.Columns.Add("txtFTE");

while (!csvreader.EndOfStream)
{
DataRow dr = dt.NewRow();

var line = csvreader.ReadLine();
var values = line.Split(',');

if (values[0].Trim() != "Pers.No.")
{
using (DbConnection conn = new SqlConnection("SQLStaffingConn"))

{
conn.Open();

using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT ResourceName FROM StaffTracking where PersonnelResourceType = @restype order by PersonnelResourceType, ResourceName";
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "restype";
p1.Value = "Supplier";
cmd.Parameters.Add(p1);

using (DbDataReader row = cmd.ExecuteReader())
{
while (row.Read())
{
string col2 = row.GetString(1);
}
}
}
}
if (ResourceName == (values[1].Trim()))
{

if (values[1].Trim() == currentName)
{

currentHours = currentHours + Convert.ToDecimal(values[9].Trim());

}
else
{
if (currentName != "")
{
dr["txtName"] = currentName;
dr["txtHours"] = currentHours;
dr["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);

dt.Rows.Add(dr);
dt.AcceptChanges();

}

currentHours = Convert.ToDecimal(values[9].Trim());
currentName = values[1].Trim();
}

}

}

}

DataRow drfinal2 = dt.NewRow();
drfinal2["txtName"] = currentName;
drfinal2["txtHours"] = currentHours;
drfinal2["tx

Norris Chappell 28-Mar-15 21:13pm

got the error at this line:
using (DbConnection conn = new SqlConnection("SQLStaffingConn"))

Sascha Lefèvre 28-Mar-15 21:24pm

If "SQLStaffingConn" is a string-variable containing your connection-string, you have to remove the quotes here.

Please note you didn't follow my last comment yet :-)