SQL string returns no values when coded in C#

Question

Using visual studio 2012 express to create a web api that connects to a sql database and searches for a term, and returns values. When I run the code, the sql string works. I can copy the string and load it into a query in SQL server 2014 and expected results are returned. However, when I run the code here my sql string - on the return data - all my values are null.

Does anyone perhaps have any suggestions

Thank you

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace WebAPi
{
public class TableBusinessTerm : TableGenericBase<stBusinessTerm>
{
    private string TableName = "[MetadataRepository].[dbo].[QrySocialGraphMobile]";
    public override void CreateTable()
    {
        throw new NotImplementedException();
    }

    public stBusinessTerm GetBusinessTerm(string termName)
    {
        string sql = "Select * From " + TableName + " Where BusinessTerm = '" + termName + "'";
        var data = new stBusinessTerm();
        bool result = GetRecord(ref data, sql);
        return data;
    }
    protected override void DatabaseRow_Get(DataRow dr, ref stBusinessTerm data)
    {
        DatabaseUtilities.DataRow_Get(dr, "BusinessTerm", ref data.BusinessTerm);
        DatabaseUtilities.DataRow_Get(dr, "BusinessTermLongDesc", ref data.BusinessTermLongDesc);
        DatabaseUtilities.DataRow_Get(dr, "DomainCatID", ref data.DomainCatID);
        DatabaseUtilities.DataRow_Get(dr, "SystemName", ref data.SystemName);
        DatabaseUtilities.DataRow_Get(dr, "DataSteward", ref data.DataSteward);
        DatabaseUtilities.DataRow_Get(dr, "DomainCatName", ref data.DomainCatName);
        DatabaseUtilities.DataRow_Get(dr, "GoldenSource", ref data.GoldenSource);
        DatabaseUtilities.DataRow_Get(dr, "GTS_table", ref data.GTS_table);
        DatabaseUtilities.DataRow_Get(dr, "TableOwnerName", ref data.TableOwnerName);
        DatabaseUtilities.DataRow_Get(dr, "Synonym", ref data.Synonym);
    }

    protected override void DatabaseRow_Get(long autoIncrementedID, ref stBusinessTerm data)
    {
        throw new NotImplementedException();
    }

    protected override void DatabaseRow_Set(ref DataRow dr, stBusinessTerm data)
    {
        throw new NotImplementedException();
    }
}

}

because I am not totally sure what I am doing truthfully and it was the only way I could avoid s sytnax error. Assume that you are referring to the line bool result = GetRecord(ref data, sql); return data — Jext
– Jext, Commented Apr 12, 2018 at 9:59
Firstly: never concatenate inputs (termName) to create SQL - that's really really dangerous - you should look at parameters. However, the more relevant thing here is that you seem to be using some custom code that you aren't showing us. We can't tell what the problem is if we can't see what GetRecord does or what DatabaseRow_Get does. Right now, it would just throw NotImplementedExceptions. What even is stBusinessTerm? What is the relationship between GetRecord and DatabaseRow_Get? because right now: nothing calls that, and GetRecord doesn't exist — Marc Gravell
– Marc Gravell, Commented Apr 12, 2018 at 10:07
Ok, maybe more details needed. The TERMNAME is being passed from a Web page. This then creates the string sql which is why I have concatenated the inputs. I have never used sqlDataAdapter, so thought it would be easier to create the sql string and then pass the values to the data rows - clearly this is not the case. Was hoping it was something silly like I had left of a comma, or something, but now see its more complex than that. Thanks for the advise thus far — Jext
– Jext, Commented Apr 12, 2018 at 10:17

Marc Gravell · Accepted Answer · 2018-04-12 10:12:31Z

Your current data code is ... complex and hard to understand, and with much of it missing it is very hard to comment directly on what is happening. However! I strongly suspect you are making life very hard for yourself unnecessarily. It looks like you have a type stBusinessTerm (I'm guessing a struct, although that is almost certainly a bad choice here, but that is a separate issue - also: you should generally avoid prefixes like st for things), with public fields (another bad choice, almost certainly) that exactly match the column names. In that case, I strongly suggest using a tool such as Dapper. Here's the entire code for that, correctly parameterized:

public stBusinessTerm GetBusinessTerm(string termName)
{
    using(var conn = GetConnectionFromSomewhere())
    {
        return conn.QuerySingleOrDefault<stBusinessTerm>(@"
Select * From [MetadataRepository].[dbo].[QrySocialGraphMobile]
Where BusinessTerm = @termName",
            new { termName }); // parameters
    }
}

and... that's it! Basically, Dapper works on the principle that most of the code that people write to execute commands and consume the results is rote, boring, and usually inefficient and buggy - and it would be better if a library just did all that stuff for you.

Collectives™ on Stack Overflow

SQL string returns no values when coded in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related