Having a little bit of a strange error here that I have never encountered before. I have an application where users can type in a list of accounts in a datagrid and a date range and press a button and it will return the data for these accounts in a datagrid and give them the option to export it to an excel file. This works perfectly for me, logged in under my username and even when I log in under other people's username. The problem is when they try it, they get no data back. No errors, just it doesn't pull any data.
The interesting thing is this is all in the same database as the other information which they access without any problem. The only difference, which I think might be the explanation is I am calling this SQL code directly from the Application whereas everything else is called using stored procedures that sit on the server. The reason for this is I have to concatenate the SQL Query string for each item in the accounts field. Since they are able to enter as many accounts as they want, I cannot use a stored procedure since I don't know how many parameters it will have ultimately(if someone could let me know a method of doing this, I would actually prefer this way for keeping things consistent). Obviously the query string is working properly, as it's pulling data back for me, but the question I have is why is it failing to return data for others? The connection string is an SQL Authentication, so it shouldn't have anything to do with them not having Windows Authentication on the server, plus they are already able to log in to the application and it displays data on their dashboard, which couldn't happen...
Anyone that can point me in the right direction with this I would appreciate it...the only thing I can think of is it is an issue with using an in-code SQL string versus a stored procedure, but this doesn't make any sense since other people do this all the time in applications without issue.
public ICommand GetData
{
get => new RelayCommand(() =>
{
//call the SQL Code to lookup the account numbers
var SQLStr = "SELECT * FROM [Clients].[Data] WHERE (Account_Number = '";
for (var i = 0; i< AccountNums.Count; i++)
{
if (!String.IsNullOrEmpty(AccountNums[i].accNum)) SQLStr += i == 0 ? $"{AccountNums[i].accNum}'" : $" OR Account_Number = '{AccountNums[i].accNum}'";
}
SQLStr += $") AND SUB_QUERY_CREATED_ON BETWEEN '{StartDate.ToString()}' AND '{EndDate.ToString()}'";
_Data = DBMethods.GetSQLData(_Data, new Models.Clients.Data(), SQLStr, new List<string> { "ID" }, true);
ShowResPnl = true; //there are results, toggle the panel visibility bound variable
});
}
public static ObservableCollection<T> GetSQLData<T>(ObservableCollection<T> myCollection, T myClass, String SQLString, List<string> remParams, bool UseSQLQuery) where T : class
{
var conn = new SqlConnection();
try
{
var paramList = GenerateSQLParameters(myClass, remParams);
using (getConnection(conn))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(SQLString, conn))
{
cmd.CommandType = CommandType.Text;
SqlDataReader reader;
reader = cmd.ExecuteReader();
//only execute if the reader has data
if (reader.HasRows)
{
while (reader.Read())
{
var tempModel = Global.GenerateNewInstance(myClass) as T;
Type model = tempModel.GetType();
var prop = model.GetProperties();
PropertyInfo pi;
//set the values for each property in the model
foreach (var p in prop)
{
if (!remParams.Contains(p.Name))
{
pi = tempModel.GetType().GetProperty(p.Name);
if (reader[p.Name] == DBNull.Value)
{
pi.SetValue(tempModel, null);
}
else
{
pi.SetValue(tempModel, reader[p.Name]);
}
}
}
myCollection.Add(tempModel);
}
reader.Close();
cmd.Dispose();
}
}
}
}
catch (Exception ex)
{
ErrorWindow errWin = new ErrorWindow("There was a problem trying to Get the Data with the Query '" + SQLString + "'! Error: " + ex.Message);
errWin.Show();
}
return myCollection;
}
UPDATE: OK I got it working perfectly with help from THIS thread:
How do I split a string so I can access item x? and more specifically this post: What about using string and values() statement?
DECLARE @str varchar(max)
SET @str = 'Hello John Smith'
DECLARE @separator varchar(max)
SET @separator = ' '
DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, '''),(''')
SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)'
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
I created a stored procedure using this, then did a left join on Account numbers from the Data Table and used a WHERE clause to set the Start and End Dates and exclude items that were NULL(checked one of the columns). Works perfectly and only took about 2 or 3 seconds to return the data. I had another working method as detailed here https://sqlperformance.com/2012/07/t-sql-queries/split-strings#comments using a function which was taking well over a minute to return data for only 4 accounts...obviously was not going to work well enough so I found the method mentioned prior and it works excellently!
