|
You can probably use some clever SQL to do it; try the database forum.
|
|
|
|
|
Well, assuming you are connecting to a database and want to use straightforward SQL, you could just execute the following scalar command:
SELECT COUNT(1) FROM MyTable WHERE FieldA IS NOT NULL So, you would have something like
private int GetCommand(IDbConnection dbConnection)
{
IDbCommand command = dbConnection.CreateCommand();
command.CommandText = "SELECT COUNT(1) FROM MyTable WHERE FieldA IS NOT NULL";
command.CommandType = CommandType.Text;
dbConnection.Open();
object output = command.ExecuteScalar();
dbConnection.Close();
int retValue;
int.TryParse(output, ref retValue);
return retValue;
}
|
|
|
|
|
Pete O'Hanlon wrote: int.TryParse(output, ref retValue);
That's not going to compile - TryParse expects the first parameter to be a string , not an object .
It should be safe to unbox the output variable directly to an int . Otherwise, Convert.ToInt32 should work.
Also, it would probably be a good idea to wrap the IDbCommand in a using block, and test whether or not you need to open the connection, since it might already be open.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Also, the CommandType "text" does not need to be set, it is (very conveniently) the default; since it is not a nullable enum it will always have a value, meaning a default must exist.
..but still bonuspoints, as the code is not tied to a specific IDbCommand implementation, and he's using the factory-method to create a command of the correct type
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
To be fair, I just coded this in the textblock as a minimal version. It's been a long time since I went away from the Db libraries I've built up that hide all this implementation.
|
|
|
|
|
As Richard says, this is easiest in SQL:
SELECT SUM(CASE WHEN fieldA IS NOT NULL THEN 1 ELSE 0 END) FROM MyTable
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Wouldn't just the COUNT function do this?
SELECT COUNT(fieldA) FROM MyTable
In this form it counts the NON NULL values.
See: SQL COUNT() Function[^] and COUNT (Transact-SQL)[^]
"Fairy tales do not tell children the dragons exist. Children already know that dragons exist. Fairy tales tell children the dragons can be killed."
- G.K. Chesterton
|
|
|
|
|
Yes...it would...
Sorry about that, it's been one of those weeks...
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
I should have said I am trying to do this using LINQ:
I have a 20 records in a database table. Each record has and ID. I want to read a field in each record fieldA. I only want to read the records that have an ID '1'. For each record that has an ID '1' and fieldA Is Not Null I want to return a count of those records.
So if 10 records have data in fieldA and have ID '1' then my count for fieldA will be 10.
Anyone know how to do this in Linq?
|
|
|
|
|
How about something like:
int count = yourDbContext.yourEntityCollection.Where(entity => entity.ID == 1 && entity.FieldA != null).Count();
You have just been Sharapova'd.
|
|
|
|
|
Or, more simply:
int count = yourDbContext.yourEntityCollection.Count(entity => entity.ID == 1 && entity.FieldA != null);
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Not sure about Linq to SQL, but (for Linq to objects and XML) I have always found Where(predicate).Count() to be performing better than .Count(predicate) .
--EDIT
Here's a StackOverflow question[^] that might interest you.
You have just been Sharapova'd.
|
|
|
|
|
The optimizations only apply to in-memory LINQ queries (LINQ to Objects and LINQ to XML). With LINQ to SQL and Entity Framework, both versions should produce an identical SQL query.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Indeed, they should. But I have always used the Where(predicate).Count() version, so that came implicitly when I posted the answer.
You have just been Sharapova'd.
|
|
|
|
|
This is the code that I am looking to get a Count of complexQuery.
string refName = entity.GetAttributeValue<string>("contractorname");
var complexQuery = (from c in orgContext.CreateQuery<contractorframe>()
join e in orgContext.CreateQuery<dcc_eval>()
on c.ContractorName equals e.RegardingObjectId.ToString()
where e.RegardingObjectId.ToString() == refName
and e.QW Is Not Null)
modified 15-Oct-15 10:27am.
|
|
|
|
|
macca24 wrote: I am looking to get a Count of complexQuery Not sure what you mean by that, but if your complexQuery is already working, then you can simply use the Count() method:
int complexQueryCount = (from c in orgContext.CreateQuery<contractorframe>()
join e in orgContext.CreateQuery<dcc_eval>()
on c.ContractorName equals e.RegardingObjectId.ToString()
where e.RegardingObjectId.ToString() == refName
and e.QW Is Not Null).Count();
You have just been Sharapova'd.
|
|
|
|
|
int complexQuery = (from e in mycontext.CreateQuery<new_eval>()
where e.new_QW1 != null
select e.new_QW1).Count();
if (complexQuery < 1)
{
var newQuery = "HasData";
entity.Attributes.Add("new_testcomplexquery", newQuery);
}
else
{
var newQueryToo = "Blank";
entity.Attributes.Add("new_testcomplexquery", newQueryToo);
}
I want to use the results of complexQuery in an If statement but the value of complexQuery doesnt appear to be getting picked up. Anyone know why?
|
|
|
|
|
I am not sure what you are trying to do, but shouldn't your if condition be reversed? i.e. it "HasData" if complexQuery > 0 and it's "Blank" if complextQuery == 0 . Try this:
if (complexQuery != 0)
{
var newQuery = "HasData";
entity.Attributes.Add("new_testcomplexquery", newQuery);
}
else
{
var newQueryToo = "Blank";
entity.Attributes.Add("new_testcomplexquery", newQueryToo);
}
You have just been Sharapova'd.
|
|
|
|
|
I have a serial port controller (for sensing any object that passes through its transport module) connected to USB. It responses with either 0 or 1 after input command "t" when it senses any dropping or no dropping of object through the transport. The page CshDeposit5 contains the code. Start.xaml is the page the activity starts and ends up in CshDeposit5.xaml. TmpErrorMsg and TmpMsg are the pages to show the user what happened. Everything runs fine for the first time. But when it tries to come to this CshDeposit5.xaml 2nd time it throws exception on the follwoing highlighted codes:
CshDeposit5.xaml.cs
--------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.IO.Ports;
using System.Windows.Threading;
namespace NWCDM
{
public partial class CshDeposit5 : Page
{
private System.Windows.Threading.DispatcherTimer dispatcherTimer = new System.Windows.Threading.DispatcherTimer();
private static int i;
public CshDeposit5()
{
InitializeComponent();
}
private void Page_Loaded(object sender, RoutedEventArgs e)
{
i = Global.xDropTime;
TimeLeft.Text = i.ToString();
dispatcherTimer.Tick += dispatcherTimer_Tick;
dispatcherTimer.Interval = new TimeSpan(0, 0, 1);
Global.xController.WriteLine("t");
dispatcherTimer.Start();
SerialPort sp2 = Global.xController;
sp2.DataReceived += new SerialDataReceivedEventHandler(DataReceivedHandler );
}
public void DataReceivedHandler(object sender, SerialDataReceivedEventArgs e)
{
SerialPort sp = (SerialPort)sender;
string indata = sp.ReadExisting();
Dispatcher.Invoke(DispatcherPriority.Send, new Action(() => DoTrans(indata)));
}
private void DoTrans(string x){
if (i >= 0)
{
if (x.IndexOf("1") >= 0)
{
dispatcherTimer.Stop();
Dispatcher.BeginInvoke(new System.Threading.ThreadStart(delegate
{
NavigationService.Navigate(new Uri("TmpMsg.xaml", UriKind.RelativeOrAbsolute));
}));
}
else
{
if (x.IndexOf("0") >= 0)
{
Dispatcher.BeginInvoke(new System.Threading.ThreadStart(delegate
{
NavigationService.Navigate(new Uri("TmpErrorMsg.xaml", UriKind.RelativeOrAbsolute));
}));
}
}
}
}
private void dispatcherTimer_Tick(object sender, EventArgs e)
{
if (i >= 0)
{
if (Global.VarLang == 0)
{
TimeLeft.Text = i.ToString();
}
i--;
}
else
{
dispatcherTimer.Stop();
Global.xController.WriteLine("r");
Dispatcher.BeginInvoke(new System.Threading.ThreadStart(delegate
{
NavigationService.Navigate(new Uri("TmpErrorMsg.xaml", UriKind.RelativeOrAbsolute));
}));
}
}
}
}
modified 14-Oct-15 4:31am.
|
|
|
|
|
Please edit your question, remove the blank lines, add proper indentation, and add <pre lang="c#"></pre> tags around your code to make it more readable, like this:
public CshDeposit5()
{
InitializeComponent();
}
Next, you should use your debugger to identify exactly which variable is null when the exception is thrown.
|
|
|
|
|
NavigationService.Navigate(new Uri("TmpMsg.xaml", UriKind.RelativeOrAbsolute)); throws the null exception. I tried the following,
NavigationService nav = NavigationService.GetNavigationService(this);
nav.Navigate(new Uri("TmpMsg.xaml", UriKind.RelativeOrAbsolute));
where nav variable is shown null.
|
|
|
|
|
So you need to find out why NavigationService.GetNavigationService(this); is returning null rather than a valid object.
|
|
|
|
|
Please note as I mentioned earlier it doesn't thro any null exception 1st time. I get this when CshDeposit5.xaml is called the 2nd time.
How do I find its returning null?
|
|
|
|
|
Unfortunately that is the nature of code bugs, they only happen when you least expect them. The only way to track the problem is by debugging and testing. You should examine all the information in the exception including the stack trace, in order to try and find out what has happened leading up to the error.
|
|
|
|
|
Good day.
Please fix the formatting. It makes it really hard to read, so I have not read through your code.
Anyway, the first thing that comes to mind is check that when you try to navigate to a XAML page a second time, the
NavigationService.Navigate method already has the page you are trying to navigate to in memory.
Something similar happened to me quote some time ago.
I fixed it by first checking if the page I wanted to navigate to was not already loaded into the NavigationService. If it was, I didn't do anything, else let the Navigation proceed.
Hope that helps.
Neill
|
|
|
|