|
I have list of information with following fields:
Project State Title
ABC Resolved Title1
ABC Pending Title2
DEF Archived Title3
DEF Resolved Title4
DEF Committed Title5
DEF Active Title6
I want output in following format using c#
Project Pending Resolved Committed Active
ABC 2 1 0 3
DEF 1 3 1 15
private void button2_Click(object sender, EventArgs e)
{
List<Item> objItems = new List<Item>();
objItems.Add(new Item(1, "ABC", "Title1", "Resolved"));
objItems.Add(new Item(2, "ABC", "Title2", "Pending"));
objItems.Add(new Item(3, "DEF", "Title3", "Archived"));
objItems.Add(new Item(4, "DEF", "Title4", "Resolved"));
objItems.Add(new Item(5, "DEF", "Title5", "Committed"));
objItems.Add(new Item(6, "DEF", "Title6", "Active"));
objItems.Add(new Item(7, "ABC", "Title2", "Pending"));
var data = objItems.Pivot(c => c.Project, c => c.State, lst => lst.Sum(c=> c.State)).ToList();
dataGridView1.DataSource = data;
}
public class Item
{
public int Id { get; set; }
public string Project { get; set; }
public string Title { get; set; }
public string State { get; set; }
public Item(int id, string project, string title, string state)
{
this.Id = id;
this.Project = project;
this.Title = title;
this.State = state;
}
}
public static class Extension
{
public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
this IEnumerable<T> source,
Func<T, TColumn> columnSelector,
Expression<Func<T, TRow>> rowSelector,
Func<IEnumerable<T>, TData> dataSelector)
{
DataTable table = new DataTable();
var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
table.Columns.Add(new DataColumn(rowName));
var columns = source.Select(columnSelector).Distinct();
foreach (var column in columns)
table.Columns.Add(new DataColumn(column.ToString()));
var rows = source.GroupBy(rowSelector.Compile())
.Select(rowGroup => new
{
Key = rowGroup.Key,
Values = columns.GroupJoin(
rowGroup,
c => c,
r => columnSelector(r),
(c, columnGroup) => dataSelector(columnGroup))
});
foreach (var row in rows)
{
var dataRow = table.NewRow();
var items = row.Values.Cast<object>().ToList();
items.Insert(0, row.Key);
dataRow.ItemArray = items.ToArray();
table.Rows.Add(dataRow);
}
return table;
}
public static Dictionary<TKey1, Dictionary<TKey2, TValue>> Pivot<TSource, TKey1, TKey2, TValue>(
this IEnumerable<TSource> source
, Func<TSource, TKey1> key1Selector
, Func<TSource, TKey2> key2Selector
, Func<IEnumerable<TSource>, TValue> aggregate)
{
return source.GroupBy(key1Selector).Select(
x => new
{
X = x.Key,
Y = source.GroupBy(key2Selector).Select(
z => new
{
Z = z.Key,
V = aggregate(from item in source
where key1Selector(item).Equals(x.Key)
&& key2Selector(item).Equals(z.Key)
select item
)
}
).ToDictionary(e => e.Z, o => o.V)
}
).ToDictionary(e => e.X, o => o.Y);
}
}
This Pivot code i got from here https://stackoverflow.com/a/6282079
Tell me how to arrange my data using above Pivot function. please tell me what to rectify in code to get desire output. thanks
|
|
|
|
|
I just pasted this in Google, and lo' and behold, it is in QA, marked as "solved", and twice on SO.
The post in QA does a pivot, so you could expand on that; in the example you give I do not see where the numbers are to be coming from.
Perhaps you would like to rentacoder?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
As Eddy said, it's not clear where those numbers are coming from. But the obvious error is that you can't Sum a string column.
Try replacing lst => lst.Sum(c=> c.State) with lst => lst.Count() .
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
public void Test()
{
/* Flow board variable declaration */
UInt64 frpHandle = 0;
ushort Serial = 0;
ushort Version = 0;
/* Flow-rate acquisition variables */
byte sensor_index = 0; // sensor index coresponds to flow-unit port on the flowboard from 0 to 7
byte TimeCheck = 0;
float flow_rate = 0;
uint loop = 0;
frpHandle = frp_initialization(0);
RTBox.Text="\n FRP session initialized";
RTBox.Refresh();
frp_get_serial(frpHandle, ref Serial, ref Version);
RTBox.Text = ("\n FLOWBOARD SN:" + Convert.ToInt32(Serial));
RTBox.Refresh();
if (Serial != 0)
{
for (loop = 0; loop < 20; loop++)
{
frp_read_flow(frpHandle, sensor_index, ref TimeCheck, ref flow_rate);
RTBox.Text = ("\n Flow-rate:" + (flow_rate) + "\t ul/min");
RTBox.Refresh();
}
Thread.Sleep(100);
};
frp_close(frpHandle);
RTBox.Text = ("\n FRP session closed");
RTBox.Refresh();
}
private void button1_Click(object sender, EventArgs e)
{
Test();
}
|
|
|
|
|
No, they won't. And there are loads of reasons why not.
Firstly, you set the RTB Text each time - which discards any text in it already, so only the last text will ever be displayed.
Secondly, this is code that runs on the UI thread - if it didn't, you would get a "Cross-threading exception" when you set the Text property - so it blocks the thread which is in charge of changing the display which means that the RTB content can't be drawn until your method exits.
Thirdly, you are waiting for serial input data, which blocks the method from doing anything else - and that further blocks the UI thread.
What you need to do is move that code into a separate thread and receive updates from the thread to display data in your UI thread.
I'd suggest a BackgroundWorker Class (System.ComponentModel) | Microsoft Docs[^] - it's easy to use and provides progress reporting back to the UI thread via an Event which you can use to update your RTB as it goes along.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
modified 16-Nov-19 2:13am.
|
|
|
|
|
By using BackgroundWorker , can we display the multiple iterations in the loop?
|
|
|
|
|
Yes.
The BackgroundWorker creates a second thread, which runs your serial access code - freeing up your main UI thread to handle the display. Each time you want to update the display, you signal that "progress has occurred" and pass the text you want to display. In the progress event, you update your RichTextBox from the passed data.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I have a SQL table called Users with a DateTime field called ModifiedDT.
In this code...
results = (from u in dc.Users
select new UserEntity
{
.
.
.
ModifiedDT = u.ModifiedDT.GetValueOrDefault()
}).ToList();
The code above throws "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
if I change it to this, it works:
results = (from u in dc.Users
select new UserEntity
{
.
.
.
ModifiedDT = u.ModifiedDT.HasValue ? u.ModifiedDT : null
}).ToList();
I always thought that GetValueOrDefault() was doing this under the sheets:
public T GetValueOrDefault(T defaultValue)
{
return HasValue ? value : defaultValue;
}
Any ideas on why this doesn't work?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Kevin Marois wrote: I always thought that GetValueOrDefault() was doing this under the sheets:
Tis, according to the Reference Source[^].
Kevin Marois wrote: The code above throws "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." Sounds like it is not a DateTime type, but has been converted to a varchar datatype along the way. Cast it to a datetime in the query, see if it makes a difference.
--edit
Stupid question, but what culture is your app running under?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Tried this:
ModifiedDT = (DateTime)u.ModifiedDT.GetValueOrDefault()
Got the same error
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
So, what is the actual value of the field?
And does the Sql Server date format equal to the systems' format? If you're running a sproc to populate the query, SET the dateformat explicitly.
System.Diagnostics.Debug.WriteLine(default(DateTime).ToString()); Results in "01/01/0001 00:00:00", which would be out of range for Sql Server (see datetime (Transact-SQL) - SQL Server | Microsoft Docs[^] for valid range of DateTime).
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
It's a nullable SQL DateTime without a value. It's null
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Then I'm going to guess it is the date-format; if Sql Server has a different one than your system, switching days and months might lead to an out-of-range for the month-part.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Any luck yet? If no, I'll try to recreate the problem here
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
So a datetime , not a datetime2 ?
SQL's datetime type can't handle dates earlier than 1753/01/01 , whereas the default value for .NET's DateTime type is 0001/01/01 .
It sounds like Linq to SQL is passing in .NET's default value to the SQL query, which is then failing to convert it to a datetime .
If possible, try changing the column type to datetime2 . Otherwise, you'll need to provide a default value which is within the range of SQL's datetime type.
datetime (Transact-SQL) - SQL Server | Microsoft Docs[^]
datetime2 (Transact-SQL) - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It's a DateTime.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
So you'll need to specify a default which is in range for the SQL type. Eg:
ModifiedDT = u.ModifiedDT.GetValueOrDefault(System.Data.SqlTypes.SqlDateTime.MinValue.Value)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That worked. Thanks!
Interesting though, you would think that in a Linq To SQL statement that the default for that would be SqlDateTime.MinValue.Value.
Also, any reason to use this syntax over
ModifiedDT = u.ModifiedDT.Value
Seems like they're both doing the same thing and it's less verbose.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
.Value will throw an exception if it's null , whereas .GetValueOrDefault() will return the default value instead.
I think it would be odd if .GetValueOrDefault() returned different values depending on whether it was executed by Linq to Objects or Linq to SQL.
You could always use the null-coalescing operator instead:
ModifiedDT = u.ModifiedDT ?? System.Data.SqlTypes.SqlDateTime.MinValue.Value
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
One thing I was just thinking.. I'd rather have this be null than min value, so that I can tell if it's been set
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Which is even simpler.
ModifiedDT = u.ModifiedDT
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ya I guess this is all for nothing
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
I'm curious how you guys do exception handling in your DAL.
My Apps are usually structured like this... The Client (this case a WPF app) calls the BL, which then calls the DAL:
WPF App
private async void AddUser()
{
UserEntity user = new UserEntity
{
FirstName = "Jack",
LastName = "Stone",
UserName = "jstone",
Password = ""
};
await Task.Factory.StartNew(() =>
{
IBizObj bo = new BizObj(connString);
bo.AddUser(user);
}).ContinueWith(task =>
{
UserList.Add(user);
});
}
Bis Layer
public class BizObj : IBizObj
{
IRepository _repoisitory;
<pre>
public BizObj(string connectionString)
{
_repoisitory = new FalconRepository(connectionString);
}
public int AddUser(UserEntity entity)
{
return _repoisitory.AddUser(entity);
}
}
DAL
public int AddUser(UserEntity entity)
{
using (var dc = GetDataContext())
{
int results = 0;
try
{
User user = new User
{
FirstName = entity.FirstName,
LastName = entity.LastName,
UserName = entity.UserName,
Password = entity.Password,
CanLogIn = entity.CanLogIn,
PasswordChangeDate = entity.PasswordChangeDate,
CreatedByUserId = 1,
CreatedDT = DateTime.Now,
};
dc.Users.InsertOnSubmit(user);
dc.SubmitChanges();
results = user.Id;
}
catch (Exception e)
{
}
return results;
}
}
So, the question is wwhat is the correct way to handle the exception on the DAL? Would you handle the exception in the UI, the DAL, both?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
The general rule of thumb is that you should only handle exceptions at the point that you can actually do anything with them. In your example, for instance, what good does it do handling the exception in the DAL? What are you going to do with it? Are you going to log that there was an exception and then return a value of 0 to indicate that the request failed for some reason? In that case, you're going to have to have something in your business layer that knows that a result of 0 means that there was an exception internally. In which case, why did you bother handling it in the DAL at all?
|
|
|
|
|
I'd handle it in the DAL, log it in the DAL log with as much detail as possible, and then throw a new exception with the current exception as the inner exception. The BL can handle it or not as it choses, but since there may be multiple DAL layers, it's important (to me at least) to log it correctly for later analysis - and that means as early as possible before you lose potentially useful info, or debug opportunities.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|