C#

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[nameColumnIndex];
                            row["Hours"] = Decimal.Parse(columns[hoursColumnIndex], NumberFormatInfo.InvariantInfo);
                            dataTable.Rows.Add(row);
                            
                        }
                        
                    }
                }
                dataTable.EndLoadData();
                
                // insert the records from the DataTable into the temporary table
                dataAdapter.Update(dataTable);

                // load the result of the "main purpose" query into the DataTable
                dataTable.Clear();
                cmd.CommandText = "SELECT Tmp.Name, SUM(Tmp.Hours) FROM TempTable AS Tmp WHERE NOT EXISTS (SELECT * FROM Stafftracking AS ST WHERE Tmp.Name = ST.ResourceName) GROUP BY Tmp.Name;";
                dataAdapter.Fill(dataTable);

                
                conn.Close();
                return dataTable;
            }
            
        }
    
    }
}

Norris Chappell wrote:

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

Quote:

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

Quote:

One more thing My perm table is called TempTable maybe I should create the temp table with a different name?

Quote:

Okay didn't get the error. However, I don't see the table in SQL Server?

Quote:

I tried to query it and got an error?

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
               conn.Open();
               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 = new StreamReader(NewResourceFileUpload.FileContent))
                      
          
            //    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 from the DataTable into the temporary table
                dataAdapter.Update(dataTable);
         
                // load the result of the "main purpose" query into the DataTable
                dataTable.Clear();
                cmd.CommandText = "SELECT Tmp.Name, SUM(Tmp.Hours) FROM #TempTable AS Tmp WHERE NOT EXISTS (SELECT * FROM StaffTracking AS ST WHERE Tmp.Name = ST.ResourceName) GROUP BY Tmp.Name;";
                dataAdapter.Fill(dataTable);
               
               
                return dataTable;
            }
            
        }
    
    }
}