Pass c# parameter to SqlQuery

string SQLstr = @"
DISTINCT * FROM(
SELECT A.DOCNUMBR, A.CURNCYID, C.CRNCYSYM, C.CRNCYDSC, A.DOCDATE, B.CUSTNAME, A.TRXDSCRN, A.CHEKNMBR, CASE WHEN a.CURNCYID = (SELECT TOP 1 FUNLCURR FROM dbo.MC40000) THEN A.ORTRXAMT ELSE(SELECT ISNULL(SUM(ORDBTAMT), 0) FROM dbo.RM10101 WHERE DOCNUMBR = A.DOCNUMBR AND RMDTYPAL = 9) END AS CURTRXAM,
ISNULL((SELECT CMPNYNAM FROM DYNAMICS..SY01500 WHERE INTERID = 'TWO'), '') AS COMPANYNAME
FROM dbo.RM10201 AS A
INNER JOIN RM00101 AS B ON A.CUSTNMBR = B.CUSTNMBR
INNER JOIN DYNAMICS..MC40200 AS C ON C.CURNCYID = A.CURNCYID
WHERE DOCNUMBR LIKE 'ParameterHere'
UNION ALL
SELECT A.DOCNUMBR,A.CURNCYID,C.CRNCYSYM,C.CRNCYDSC,A.DOCDATE,B.CUSTNAME,A.TRXDSCRN,A.CHEKNMBR,CASE WHEN a.CURNCYID = (SELECT TOP 1 FUNLCURR FROM dbo.MC40000 ) THEN A.ORTRXAMT ELSE (SELECT ISNULL(SUM(ORDBTAMT), 0) FROM dbo.RM10101 WHERE DOCNUMBR = A.DOCNUMBR AND RMDTYPAL = 9 ) END AS CURTRXAM ,
ISNULL((SELECT CMPNYNAM FROM DYNAMICS..SY01500 WHERE INTERID = 'TWO') ,'') AS COMPANYNAME
FROM dbo.RM20101 AS A
INNER JOIN RM00101 AS B ON A.CUSTNMBR = B.CUSTNMBR
INNER JOIN DYNAMICS..MC40200 AS C ON C.CURNCYID = A.CURNCYID
WHERE DOCNUMBR = 'ParameterHere'
) AS MAIN ";
SqlDataAdapter da = new SqlDataAdapter(SQLstr, obj.SQLConn);
DataSet ds = new DataSet();
da.SelectCommand.CommandTimeout = 99999999;
da.Fill(ds);

CshRcptDocNum

Hi Fares,

Please refer the below.

string CshRcptDocNum = RmCashRept.RmCashReceipts.DocumentNumber.ToString().Trim();
string SQLstr = @"
DISTINCT * FROM(
SELECT A.DOCNUMBR, A.CURNCYID, C.CRNCYSYM, C.CRNCYDSC, A.DOCDATE, B.CUSTNAME, A.TRXDSCRN, A.CHEKNMBR, CASE WHEN a.CURNCYID = (SELECT TOP 1 FUNLCURR FROM dbo.MC40000) THEN A.ORTRXAMT ELSE(SELECT ISNULL(SUM(ORDBTAMT), 0) FROM dbo.RM10101 WHERE DOCNUMBR = A.DOCNUMBR AND RMDTYPAL = 9) END AS CURTRXAM,
ISNULL((SELECT CMPNYNAM FROM DYNAMICS..SY01500 WHERE INTERID = 'TWO'), '') AS COMPANYNAME
FROM dbo.RM10201 AS A
INNER JOIN RM00101 AS B ON A.CUSTNMBR = B.CUSTNMBR
INNER JOIN DYNAMICS..MC40200 AS C ON C.CURNCYID = A.CURNCYID
WHERE DOCNUMBR LIKE '" + CshRcptDocNum + "'"
+" UNION ALL "
+ "SELECT A.DOCNUMBR,A.CURNCYID,C.CRNCYSYM,C.CRNCYDSC,A.DOCDATE,B.CUSTNAME,A.TRXDSCRN,A.CHEKNMBR,CASE WHEN a.CURNCYID = (SELECT TOP 1 FUNLCURR FROM dbo.MC40000 ) THEN A.ORTRXAMT ELSE (SELECT ISNULL(SUM(ORDBTAMT), 0) FROM dbo.RM10101 WHERE DOCNUMBR = A.DOCNUMBR AND RMDTYPAL = 9 ) END AS CURTRXAM , "
+ "ISNULL((SELECT CMPNYNAM FROM DYNAMICS..SY01500 WHERE INTERID = 'TWO') ,'') AS COMPANYNAME "
+ "FROM dbo.RM20101 AS A "
+ "INNER JOIN RM00101 AS B ON A.CUSTNMBR = B.CUSTNMBR "
+ " INNER JOIN DYNAMICS..MC40200 AS C ON C.CURNCYID = A.CURNCYID "
+ " WHERE DOCNUMBR = '" + CshRcptDocNum + "'"
+ " ) AS MAIN ";
SqlDataAdapter da = new SqlDataAdapter(SQLstr, obj.SQLConn);
DataSet ds = new DataSet();
da.SelectCommand.CommandTimeout = 99999999;
da.Fill(ds);

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hariharan Krishnamoorthi9y ago
Accepted answer
Hi Fares,
Please refer the below.
string CshRcptDocNum = RmCashRept.RmCashReceipts.DocumentNumber.ToString().Trim();

string SQLstr = @"
DISTINCT * FROM(
SELECT A.DOCNUMBR, A.CURNCYID, C.CRNCYSYM, C.CRNCYDSC, A.DOCDATE, B.CUSTNAME, A.TRXDSCRN, A.CHEKNMBR, CASE WHEN a.CURNCYID = (SELECT TOP 1 FUNLCURR   FROM   dbo.MC40000) THEN A.ORTRXAMT  ELSE(SELECT  ISNULL(SUM(ORDBTAMT), 0) FROM    dbo.RM10101 WHERE   DOCNUMBR = A.DOCNUMBR AND RMDTYPAL = 9)  END AS CURTRXAM,
ISNULL((SELECT CMPNYNAM FROM DYNAMICS..SY01500 WHERE INTERID = 'TWO'), '') AS COMPANYNAME
FROM dbo.RM10201 AS A
INNER JOIN RM00101 AS B ON A.CUSTNMBR = B.CUSTNMBR
INNER JOIN DYNAMICS..MC40200 AS C ON C.CURNCYID = A.CURNCYID
WHERE DOCNUMBR LIKE '" + CshRcptDocNum + "'"
+" UNION ALL "
+ "SELECT A.DOCNUMBR,A.CURNCYID,C.CRNCYSYM,C.CRNCYDSC,A.DOCDATE,B.CUSTNAME,A.TRXDSCRN,A.CHEKNMBR,CASE WHEN a.CURNCYID = (SELECT TOP 1 FUNLCURR FROM   dbo.MC40000 ) THEN A.ORTRXAMT ELSE (SELECT  ISNULL(SUM(ORDBTAMT), 0) FROM    dbo.RM10101 WHERE   DOCNUMBR = A.DOCNUMBR AND RMDTYPAL = 9 )  END AS CURTRXAM ,   "
+ "ISNULL((SELECT CMPNYNAM FROM DYNAMICS..SY01500 WHERE INTERID = 'TWO') ,'') AS COMPANYNAME "
+ "FROM dbo.RM20101 AS A  "
+ "INNER JOIN RM00101 AS B ON A.CUSTNMBR = B.CUSTNMBR  "
+ " INNER JOIN DYNAMICS..MC40200 AS C ON C.CURNCYID = A.CURNCYID  "
+ " WHERE DOCNUMBR = '" + CshRcptDocNum + "'"
+ " ) AS MAIN ";

SqlDataAdapter da = new SqlDataAdapter(SQLstr, obj.SQLConn);
DataSet ds = new DataSet();
da.SelectCommand.CommandTimeout = 99999999;
da.Fill(ds);
+1
Amit Gupta9y ago
A parameterized query will be passed by using @ symbol.

Replace your ParameterHere text with @param (a meaningful name).

Have a look at the below link for passing parameterized query

https://www.aspsnippets.com/Articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx
0