|
Sascha,
Would something like this code work ? I'm really very new to C#. I think option 2 would be more in line with what I could do.
Thanks,
Noris
DataTable dt = new DataTable();
string line = null;
int i = 0;
using (StreamReader sr = File.OpenText(@"c:\temp\table1.csv"))
{
while ((line = sr.ReadLine()) != null)
{
string[] data = line.Split(',');
if (data.Length > 0)
{
if (i == 0)
{
foreach (var item in data)
{
dt.Columns.Add(new DataColumn());
}
i++;
}
DataRow row = dt.NewRow();
row.ItemArray = data;
dt.Rows.Add(row);
}
}
}
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsoleApplication3.Properties.Settings.daasConnectionString"].ConnectionString))
{
cn.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(cn))
{
copy.ColumnMappings.Add(0, 0);
copy.ColumnMappings.Add(1, 1);
copy.ColumnMappings.Add(2, 2);
copy.ColumnMappings.Add(3, 3);
copy.ColumnMappings.Add(4, 4);
copy.DestinationTableName = "Censis";
copy.WriteToServer(dt);
}
}
For option 2
this is what I came up with for the SqlDataAdapter part:
public static SqlDataAdapter CreateCustomerAdapter(
SqlConnection connection)
{
SqlDataAdapter adapter = new SqlDataAdapter();
// Create the InsertCommand.
command = new SqlCommand(
"INSERT INTO Tempname(ResourceName) " +
"VALUES (@ResourceName)", connection);
// Add the parameters for the InsertCommand.
command.Parameters.Add("@ResoureName", SqlDbType.NVarChar, 50, "ResourceName");
adapter.InsertCommand = command;
return adapter;
}
I not able to download/integrate the CSV-Reader on this server. I will need to pursuit another route.
modified 24-Apr-15 11:56am.
|
|
|
|
|
Right, SqlBulkCopy would be a third option, but actually I've never worked with it myself.
I wrote a method for you which should work for you. You should only have to adjust some minor stuff 
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Windows.Forms;
private static char[] Colon = new char[] { ',' };
private DataTable QueryStaff()
{
const int nameColumnIndex = 1;
const int hoursColumnIndex = 2;
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsoleApplication3.Properties.Settings.daasConnectionString"].ConnectionString))
using (var cmd = new SqlCommand("", conn))
using (var dataAdapter = new SqlDataAdapter(cmd))
using (var cmdBuilder = new SqlCommandBuilder(dataAdapter))
{
cmd.CommandText = "CREATE TABLE #TempTable(Name nvarchar(100) NOT NULL, Hours decimal(6, 2) NOT NULL);";
cmd.ExecuteNonQuery();
DataTable dataTable = new DataTable();
cmd.CommandText = "SELECT * FROM #TempTable;";
dataAdapter.Fill(dataTable);
dataTable.BeginLoadData();
using (StreamReader reader = File.OpenText(@"c:\temp\table1.csv"))
{
string line;
if (reader.ReadLine() != null)
{
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();
dataAdapter.Update(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.Name) GROUP BY Tmp.Name;";
dataAdapter.Fill(dataTable);
return dataTable;
}
}
GridView1.DataSource = QueryStaff();
GridView1.DataBind();
You have to check/adjust:
- nameColumnIndex and hoursColumnIndex
- the table- and column names in the last query string
Other notes:
- I recommended the CSV-Reader-library because reading CSV like it's done here is a brittle approach. It can't deal with colons in values.
- In case the amount of hours in your CSV-file can be a decimal value, this solution will read it correctly if a dot is being used as decimal separator (NumberFormatInfo.InvariantInfo).
If you shouldn't get it to work or if you don't understand something I've done here, please ask.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Sascha,
I really appreciate you taking your valuable time to write this method. I will make the adjustments and let you know
how it comes out. I was going to be working on this all weekend. I am going to ask my Manager to send me to some C# classes.
Thank you again,
Norris
|
|
|
|
|
Norris Chappell wrote: I was going to be working on this all weekend.
Maybe invest a part of the time I (hopefully) saved you by investigating how it works and trying to understand things that are new for you ... F1, F12 and debugging step by step are your friends 
Norris Chappell wrote: and let you know how it comes out
Yes please do.
Norris Chappell wrote: I am going to ask my Manager to send me to some C# classes.
You obviously have to know for yourself but if you'd ask me, I'd say let him give you the money to buy some good books and some time to work them through. More than one book because one may explain something in a way that's better understandable than the other and vice versa. If then still something is unclear, Google is the next step. And if you can't find the answer with Google, you could ask here.
cheers, Sascha
edit: Just realized there's a small flaw in my code because it's not 100% the same as the version I tested. But I'm sure you can fix it
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
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))
{
DataTable dataTable = new DataTable();
cmd.CommandText = "SELECT * FROM TempTable;";
dataAdapter.Fill(dataTable);
dataTable.BeginLoadData();
using (StreamReader reader = File.OpenText(@"c:\Users\pzd74f\Downloads\FinalLabor2015.csv"))
{
string line;
if (reader.ReadLine() != null)
{
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();
dataAdapter.Update(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.Name) GROUP BY Tmp.Name;";
dataAdapter.Fill(dataTable);
return dataTable;
}
}
}
}
I went ahead and create a perm table. It is not doing anything.
These two lines I don't know where they belong. If I put it anywhere it gives me a error.
gvNewResource.DataSource = dt;
gvNewResource.DataBind();
The name 'dt' does not exist in the current context.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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))
{
DataTable dataTable = new DataTable();
cmd.CommandText = "SELECT * FROM TempTable;";
dataAdapter.Fill(dataTable);
dataTable.BeginLoadData();
using (StreamReader reader = File.OpenText(@"c:\Users\pzd74f\Downloads\TestLabor2015.csv"))
{
string line;
if (reader.ReadLine() != null)
{
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();
dataAdapter.Update(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.Name) GROUP BY Tmp.Name;";
dataAdapter.Fill(dataTable);
gvNewResource.DataSource = QueryStaff();
gvNewResource.DataBind();
return dataTable;
}
}
}
}
The only thing that is different in your method is that I can't used the using System.Windows.Forms; I changed your connection string to where my table resides. SQLStaffingConn. Did I leave something out? I don't get any errors when I run but it shows nothing in the sql server table TempTable.
|
|
|
|
|
I changed the hours to 9 the name is 1.
|
|
|
|
|
Sascha,
It is still not working for me.
Norris
|
|
|
|
|
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  ) 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
|
|
|
|
|
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)
{
}
}
|
|
|
|
|
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))
{
DataTable dataTable = new DataTable();
cmd.CommandText = "SELECT * FROM TempTable;";
dataAdapter.Fill(dataTable);
dataTable.BeginLoadData();
using (StreamReader reader = File.OpenText(@"c:\Users\pzd74f\Downloads\TestLabor2015.csv"))
{
string line;
if (reader.ReadLine() != null)
{
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();
dataAdapter.Update(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;
}
}
}
}
|
|
|
|
|
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
|
|
|
|
|
It's not showing the hours. Just the name.
The table has the hours but the sum in not working.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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#.
|
|
|
|
|
I'm getting this error:
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
|
|
|
|
|
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
|
|
|
|
|
So I will need to code an opening of the database and available connection since it closed?
|
|
|
|
|
If that is the case why didn't I need to have it when I did the select on the "where not exists" query?
|
|
|
|
|
One more thing My perm table is called TempTable maybe I should create the temp table with a different name?
|
|
|
|
|
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?
What do you mean by "it" here?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
