Webservice and sql

0

I'm making a webservice that does some sql commands and outputs the results in JSON and it kinda works. But now I've been making some changes and I've ran into an issue I can't seem to fix.

The SQL command I want to run looks like this:

declare @param int
--set @param = 5

select u.SagsNr, uf.TekstWeb 
from UdlejningsSagFacilitet uf 
inner join UdlejningsSag u on uf.UdlejningsSagId = u.UdlejningsSagId 
where u.VisesPaaNettet = 1 and 
exists(select * from UdlejningsSagFacilitet uf inner join UdlejningsSag u on uf.UdlejningsSagId = u.UdlejningsSagId where TekstWeb like '%Oven%' and @param IS NULL OR u.SagsNr = @param) 
and @param IS NULL OR u.SagsNr = @param

Basically, when there is not given an ID I want to return everything. This works perfectly in SSMS. However! When I try to get this into my webservice it won't work. I think it should look like this:

SqlConnection con = new SqlConnection();
con = Database();

SqlDataAdapter daf = new SqlDataAdapter("select u.SagsNr, uf.TekstWeb " +
                                                    "from UdlejningsSagFacilitet uf " +
                                                    "inner join UdlejningsSag u on uf.UdlejningsSagId = u.UdlejningsSagId " +
                                                    "where u.VisesPaaNettet = 1 and exists(select * from UdlejningsSagFacilitet uf " +
                                                    "inner join UdlejningsSag u on uf.UdlejningsSagId = u.UdlejningsSagId " + 
                                                    "where TekstWeb like '%Oven%' and " + vSagsNr + " IS NULL OR u.SagsNr = " + vSagsNr + 
                                                    ") and " + vSagsNr + " IS NULL OR u.SagsNr = " + vSagsNr, con);

DataTable dtf = new DataTable();
daf.Fill(dtf);
string jsonf = GetJson(dtf);
return jsonf;

Here vSagsNr takes the place of @param.

If I provide a value for vSagsNr this works but if I don't I get an error:

  Incorrect syntax near the keyword 'IS'.System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'IS'.
  ved System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
  ved System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
  ved System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
  ved System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
  ved System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
  ved System.Data.SqlClient.SqlDataReader.get_MetaData()
  ved System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
  ved System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
  ved System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
  ved System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
  ved System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
  ved System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
  ved System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
  ved System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  ved System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
  ved System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
  ved WCFTest3.Service1.POSTBoligSøgning(Nullable`1 vSagsNr, String vFacilitet) i C:\Users\chm\source\repos\WCFTest3\WCFTest3\Service1.svc.cs:linje 54
ClientConnectionId:5bc85eb2-3e2a-46ef-b941-4419eab09b43
Error Number:156,State:1,Class:15

Line 54 is the "daf.Fill(dtf);" part. I'm thinking the SQL turns out wrong somehow but I can't seem to figure it out. What am I doing wrong?

sql
sql-server
wcf
asked on Stack Overflow Jun 1, 2018 by Der Kejser

1 Answer

1

This part of the query "where TekstWeb like '%Oven%' and " + vSagsNr + " IS NULL is going to fail if you don't give a value to vSagsNr. It would look like this: "where TekstWeb like '%Oven%' and IS NULL

As you can see, that query is incorrect. So your better option is to separate the query in multiple strings and each time you are going to put a condition regarding vSagsNr you check for its value and if its null or empty you remove that condition entirely from the query string.

if (vSagsNr != null && !vSagsNr.equals("")) {
    /* Add: "and " + vSagsNr + " IS NULL OR u.SagsNr = " + vSagsNr" to your query */
} else {
    /* Nothing, the query does not need that condition as the value is not specified */
}
answered on Stack Overflow Jun 1, 2018 by Alex GS • edited Jun 1, 2018 by Alex GS

User contributions licensed under CC BY-SA 3.0