How to compare a sql server table field with a csv field and display what is not in the sql table.

[Norris Chappell]

Here is my code and it still don't work?

using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Globalization;

namespace StaffingWebParts.VisualWebPart1
{
public partial class VisualWebPart1UserControl : UserControl
{
private static char[] Colon = new char[] { ',' };
private DataTable QueryStaff()
{
const int nameColumnIndex = 1;
const int hoursColumnIndex = 2;

        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings\["SQLStaffingConn"\].ConnectionString))
        using (var cmd = new SqlCommand("", conn))
        using (var dataAdapter = new SqlDataAdapter(cmd))
        using (var cmdBuilder = new SqlCommandBuilder(dataAdapter))
        {
            // create temporary table in database
            //cmd.CommandText = "CREATE TABLE #TempTable(Name nvarchar(100) NOT NULL, Hours decimal(6, 2) NOT NULL);";
            // cmd.ExecuteNonQuery();

            // create a DataTable and let the DataAdapter create appropriate columns for it
            DataTable dataTable = new DataTable();
            cmd.CommandText = "SELECT \* FROM TempTable;";
            dataAdapter.Fill(dataTable);

            // read the CSV-records into the DataTable
            dataTable.BeginLoadData();
            using (StreamReader reader = File.OpenText(@"c:\\Users\\pzd74f\\Downloads\\FinalLabor2015.csv"))
            {
                string line;
                if (reader.ReadLine() != null) // skip first line (headers)
                {
                    while ((line = reader.ReadLine()) != null)
                    {
                        string\[\] columns = line.Split(Colon, StringSplitOptions.None);

                        DataRow row = dataTable.NewRow();
                        row\["Name"\] = columns\[nameColumnIndex\];
                        row\["Hours"\] = Decimal.Parse(columns\[hoursColumnIndex\], NumberFormatInfo.InvariantInfo);
                        dataTable.Rows.Add(row);
                        
                    }
                    
                }
            }
            dataTable.EndLoadData();
            
            // insert the records fr

[Sascha Lefevre]

Take a look at the very first code you posted. I don't know to which method it belonged as you didn't include the "method head". At the end you had these lines:

gvDataCenterLabor.DataSource = dt;
gvDataCenterLabor.DataBind();

Supposedly in the same method you would now remove all the code you originally posted and just write instead:

gvDataCenterLabor.DataSource = QueryStaff();
gvDataCenterLabor.DataBind();

dt was the variable for the DataTable you originally created. Now QueryStaff() returns "that" DataTable. Please verify that const int hoursColumnIndex = 2; is correct for you (I think it's not). In the CSV you originally posted, the hours were in the last column. For my testing purposes I created a CSV with just 3 columns and the hours were in the last of it, so index 2 for me.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

You are correct it should be gvNewResource.DataSourse = QueryStaff(); gvNewResource,Databind(); Yes the Name is in column 2 which is 1 and hours is on column 10 which is 9.

[Sascha Lefevre]

Just spotted: Seems like you haven't yet adjusted the column names in the last query. Please tell if it works then.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

Here is my code that I changed. It doesn't even so me anything when I set breakpoints.

using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Globalization;

namespace StaffingWebParts.VisualWebPart1
{
public partial class VisualWebPart1UserControl : UserControl
{
private static char[] Colon = new char[] { ',' };
private DataTable QueryStaff()
{
const int nameColumnIndex = 1;
const int hoursColumnIndex = 9;

        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings\["SQLStaffingConn"\].ConnectionString))
        using (var cmd = new SqlCommand("", conn))
        using (var dataAdapter = new SqlDataAdapter(cmd))
        using (var cmdBuilder = new SqlCommandBuilder(dataAdapter))
        {
            // create temporary table in database
            //cmd.CommandText = "CREATE TABLE #TempTable(Name nvarchar(100) NOT NULL, Hours decimal(6, 2) NOT NULL);";
            // cmd.ExecuteNonQuery();

            // create a DataTable and let the DataAdapter create appropriate columns for it
            DataTable dataTable = new DataTable();
            cmd.CommandText = "SELECT \* FROM TempTable;";
            dataAdapter.Fill(dataTable);

            // read the CSV-records into the DataTable
            dataTable.BeginLoadData();
            using (StreamReader reader = File.OpenText(@"c:\\Users\\pzd74f\\Downloads\\TestLabor2015.csv"))
            {
                string line;
                if (reader.ReadLine() != null) // skip first line (headers)
                {
                    while ((line = reader.ReadLine()) != null)
                    {
                        string\[\] columns = line.Split(Colon, StringSplitOptions.None);

                        DataRow row = dataTable.NewRow();
                        row\["Name"\] = columns\[nameColumnIndex\];
                        row\["Hours"\] = Decimal.Parse(columns\[hoursColumnIndex\], NumberFormatInfo.InvariantInfo);
                        dataTable.Rows.Add(row);
                        
                    }
                    
                }
            }
            dataTable.EndLoadData();

[Norris Chappell]

I changed the hours to 9 the name is 1.

[Norris Chappell]

Sascha, It is still not working for me. Norris

[Sascha Lefevre]

1. I just forgot to remove the line using System.Windows.Forms; - you don't need it. 2) Seems like you haven't yet adjusted the column names in the last query. The reason why you don't get an exception because of that and why nothing happens at all is: 3) I'm pretty sure the method isn't called anywhere. These lines:

gvNewResource.DataSource = QueryStaff();
gvNewResource.DataBind();

aren't supposed to be in QueryStaff(). At the moment it's a snake that bites its own tail - you "try" to call QueryStaff() from within itself but it's not called from anywhere "outside". The method that contained your original code is probably the place where you should move these two lines into (as I've already written in my second-last reply or so ;P ) or just into any method that is actually triggered by something (maybe a button-click).

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

Okay I am confused. I'm suppose to put this method in my other code. I am in a loop now when I add this to my code. protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { } }

[Norris Chappell]

Okay it is now working. Finally. However I need to work on the hours. Which I can do. Let me show you my final code.

using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Globalization;

namespace StaffingWebParts.VisualWebPart1
{
public partial class VisualWebPart1UserControl : UserControl
{
protected void Page_Load(object sender, EventArgs e)
{

        if (!Page.IsPostBack)
        {
            this.QueryStaff();
            gvNewResource.DataSource = QueryStaff();
            gvNewResource.DataBind();
                       
        }
    }
 
    private static char\[\] Colon = new char\[\] { ',' };
    private DataTable QueryStaff()
    {
        const int nameColumnIndex = 1;
        const int hoursColumnIndex = 9;

        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings\["SQLStaffingConn"\].ConnectionString))
        using (var cmd = new SqlCommand("", conn))
        using (var dataAdapter = new SqlDataAdapter(cmd))
        using (var cmdBuilder = new SqlCommandBuilder(dataAdapter))
        {
            // create temporary table in database
            //cmd.CommandText = "CREATE TABLE #TempTable(Name nvarchar(100) NOT NULL, Hours decimal(6, 2) NOT NULL);";
            // cmd.ExecuteNonQuery();

            // create a DataTable and let the DataAdapter create appropriate columns for it
            DataTable dataTable = new DataTable();
            cmd.CommandText = "SELECT \* FROM TempTable;";
            dataAdapter.Fill(dataTable);

            // read the CSV-records into the DataTable
            dataTable.BeginLoadData();
        
            using (StreamReader reader = File.OpenText(@"c:\\Users\\pzd74f\\Downloads\\TestLabor2015.csv"))
            {
                string line;
                if (reader.ReadLine() != null) // skip first line (headers)
                {
                    while ((line = reader.ReadLine()) != null)
                    {
                        string\[\] columns = line.Split(Colon, StringSplitOptions.None);

                        DataRow row = dataTable.NewRow();
                        row\["Name"\] = columns\[nam

[Sascha Lefevre]

I'm sorry I can't help you with ASP.NET, I have next to no experience with that. But. If your original code, wherever it was, was executed (even though it didn't do what it should), then this should work when you remove these two lines from QueryStaff() and insert them where your original code was.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Sascha Lefevre]

Alright, one step further :) You can remove this line: this.QueryStaff(); In which way does the Hours-Stuff not work as expected yet?

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

It's not showing the hours. Just the name. The table has the hours but the sum in not working.

[Norris Chappell]

No problem with asp.net. I took this project over from a person who left to go to another project and division. Java/C++/Oracle programming is what I did in the past. So I am very new to C#.

[Sascha Lefevre]

Please re-enable the code for the temporary table so that we can rule out that there's something wrong with your replacement by a permanent table: - two lines have to be un-commented - in the two query-strings put a # in front of "TempTable" -> #TempTable - delete or rename the permanent table "TempTable" in the database Unrelated: You can remove the line conn.Close(); , it happens automatically because of the using-statement.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

I'm getting this error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

[Sascha Lefevre]

Yep, that's what I mentioned, what I forgot in the posted version of my code, because the code I tested was slightly different and didn't require explicit handling of this. You can fix this, right? ;)

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

[Norris Chappell]

So I will need to code an opening of the database and available connection since it closed?

[Norris Chappell]

If that is the case why didn't I need to have it when I did the select on the "where not exists" query?

[Sascha Lefevre]

Between the opening brace and the following comment "// create temporary table in database" insert a conn.Open();

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson