|
Instead of using the Office Interop libraries, you may want to take a look at DocX[^], which is a pure C# library for handling Microsoft Word files.
I am not sure if it works in Silverlight (I can't find anything about that configuration), but if it does it might help you.
What do you get when you cross a joke with a rhetorical question?
The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.
Do questions with multiple question marks annoy you???
|
|
|
|
|
The browser does not have access to your D: unless the app is installed as out of browser on your local system with elevated permissions.
You need to download the docx and get the user to open the file, you cannot do it from the browser (Silverlight).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I all ready installed on server but still I have problem.
|
|
|
|
|
Installed what on the server!
I repeat, you need to download the DocX file as a binary (probably), get the user to save it on their local (D drive and then the user must open the file in word. Without elevated permissions Silverlight cannot access a local drive.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
My questions are about the certification exam 70-483 - MCSD programming in C#
The web provides inconsistent info. Even the testing company (Pearson Vue) fails to provide clear answers. My questions are:
- the number of questions on the actual exam, does the number vary between exams?
- duration of the exam, i.e. max time allowed
- how is the passing score computed? Is the numeric score reported on the certification? Is the numeric score private?
- how the test is conducted? Probably online at a testing center. Is it possible to use any materials like books or web during the test?
- the format of the answers: all are multiple choice (A,B,C,D)? Some sources claim that selected answers are fill-in, or drag-drop, is it true?
modified 24-Apr-15 17:21pm.
|
|
|
|
|
CRobert456 wrote: - the number of questions on the actual exam, does the number vary between exams?
The number of questions varies depending on your answers to previous questions.
CRobert456 wrote: - duration of the exam, i.e. max time allowed
You'll get that when you sign up for the exam.
CRobert456 wrote: - the format of the answers: all are multiple choice (A,B,C,D)? Some sources claim that selected answers are fill-in, or drag-drop, is it true?
There are all kinds of question types on the exam.
Seriously? Who gives a crap about how many questions, how long and what type of questions are on the test? Those things are entirely irrelevant. The entire point of the test is DO YOU KNOW THE MATERIAL? Do any of your questions relate to that? Nope.
|
|
|
|
|
I wonder if the OP would be interested in a doctorate, I believe they are available for a modest sum.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString))
{
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT ResourceName FROM StaffTracking order by PersonnelResourceType, ResourceName";
using (DbDataReader row = cmd.ExecuteReader())
{
while (row.Read())
{
ResourceName = row.GetString(0);
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 + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);
dt.Rows.Add(drfinal2);
dt.AcceptChanges();
gvDataCenterLabor.DataSource = dt;
}
gvDataCenterLabor.DataBind();
}
}
Here is my csv file:
Pers.No. Name HomeFID Copy Range/Value from Column V Worked Date Job Code Account Pay Type Activity Type Hours
11111111 Doe Jane USA7064810 US1-08333.01.08.02.03 20150223 00S15H 9.61E+09 410 8
11111111 Doe John USA7064810 US1-08333.01.08.02.03 20150210 00S15H 9.61E+09 410 9
11111111 Smith Jane USA7064810 US1-08333.01.08.02.03 20150226 00S15H 9.61E+09 410 8
So if Doe Jane is not in the SQL table I would like to only display her.
It's only displaying the ones from the CSV file that are in the SQL Table.
|
|
|
|
|
I would like to convince you to start over 
Use this for reading CSV: A Fast CSV Reader[^] - you'll be happy ever after
Currently you open an SqlConnection, execute a query and close the connection for every single record in the CSV-file. You'll get away with it if your CSV-file only contains a few records but it's nasty and will become a bottleneck when your CSV-file becomes larger.
Instead, I would suggest you to either load your database-table into a System.Data.DataTable if it's not extremely large and match it to the CSV-records or to insert your CSV-records into a temporary table in the database and perform a Select-query with a not-exists-subquery[^].
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Hi Sascha,
I did what you said and create a temporary table in sql server and was able to get the results I wanted.
SELECT ResourceName FROM [SP2010_EDCStaffing_AppDB].[dbo].[Tempname] where not exists
(select Resourcename from Stafftracking where StaffTracking.id = Tempname.id);
Now, How would I code this in a C#? Just read both tables and display the differences?
Thanks,
Norris
|
|
|
|
|
Hi Norris,
I'm not sure I understand correctly. Sounds like you did this query with SQL Server Management Studio (or similar) and have no new C#-code yet, is that correct?
/Sascha
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Yes I was able to load my csv file into a table and do a query against both table and got the results I was looking for. I haven't done any new C# code.
|
|
|
|
|
Use Linq-To-SQL
If it's not broken, fix it until it is
|
|
|
|
|
Alright. I would suggest:
1) Your original code looks like you not only want the names of the persons but also their total hours and "FTE". At least the total hours are easy to calculate in the same query, using a GROUP BY-clause and the Sum()-function. Tune that query in SQL Server Management Studio (or whatever you're using) so that you get exactly the query result you want to display, possibly except those "FTE". (If you're not familiar with GROUP BY, please take a look at the MSDN online documentation, there should be a sample that also shows the Sum()-function.)
2) Either have that temporary table as a permanent table in your DB (which you would clear after use) or write some C#-code that creates an actual temporary table. SQL Server Management Studio can produce a CREATE TABLE-script for you from your current test-table which you would practically only have to execute from C# with an SqlCommand and ExecuteNonQuery(). (Just add a # to the start of the table name to make it a temp table.)
3) Get that CSV-Reader-library that I linked in my first reply running and read the CSV-file (that's a piece of cake).
4) Write the code that inserts the CSV-records into the (temp) table.
5) Write the code that executes the query from point 1 and read the result into a DataTable.
6) If you didn't calculate the "FTE" with the SQL-Query, add a new DataColumn to the DataTable and "fill" it.
7) Set the DataTable as DataSource for your DataGrid and you're done.
One more Hint: Temp tables are valid throughout the course of a connection. So you will have to use the same open connection from creating the temp table to reading the query result.
(I'm packing up for today - if you have follow-up questions, maybe someone else will be able to answer them, else I'll get back to you tomorrow.) Good luck!
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
You have been quite helpful. I have redone my code and now using "where not exists" code and it is working. However, the temporary table is not updated with the csv file yet. I read the article you recommended. I not sure how to put it in my code? I don't need the FTE only the name. I don't think I will need the hours but since the file have several rows with the same name it wouldn't be bad to sum up the hours too. Once I can update the table with the csv file, I think I will be done.
Thanks,
Norris
I forgot to include my code:
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
namespace StaffingWebParts.VisualWebPart1
{
public partial class VisualWebPart1UserControl : UserControl
{
public SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
SqlDataReader myReader = null;
SqlCommand cmd = new SqlCommand("select Name FROM [SP2010_EDCStaffing_AppDB].[dbo].[Tempname] where not exists (select * FROM [SP2010_EDCStaffing_AppDB].[dbo].StaffTracking WHERE tempname.Name = Stafftracking.ResourceName)");
cmd.Connection = conn;
conn.Open();
myReader = cmd.ExecuteReader();
while (myReader.Read())
{
DataRow dr = dt.NewRow();
dr[0] = myReader["Name"].ToString();
dt.Rows.Add(dr);
}
GridView1.DataSource = dt;
GridView1.DataBind();
conn.Close();
}
}
}
}
|
|
|
|
|
Hi Norris,
what difficulty do you have with integrating the CSV-Reader? Basically you download it (either just the binaries or the source-zip), reference the DLL in your project and then use the first code-example from the article as a starting point. Your options for inserting the records into the database:
1) Loop through the CSV-records and build an INSERT-batch-statement (batch-statement: multiple INSERT-statements, separated by ; in one String.) To do this properly with SqlParameters is a tiny bit more effort than option 2.
2) Load the CSV-records into a DataTable: Either with DataTable.Load(csvReader) or with csvReader.ReadIntoDataTable() or (since you only need one of the columns) similar to what you do in your current code by looping through the CSV-records and adding DataRows to a DataTable "manually". Then use an SqlDataAdapter to insert the records from the DataTable into the temp table in the database. You would either have to provide the Insert-statement[^] to the SqlDataAdapter yourself (no big deal) or use this "trick": http://stackoverflow.com/a/1631133/4320056[^] (there a DataSet is used instead of a DataTable - just use the DataTable instead). (Prerequisite: adding the Create-Temp-Table-part to your code as described earlier.)
Your current code looks alright, except: You should use the SqlCommand and SqlDataReader and probably also the SqlConnection in a using-block.
/Sascha
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
modified 24-Apr-15 9:39am.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
