how to make in line query as stored procedure with sql dataadapter in c#

Question

im having inline query i want to make it stored procedure . how to write sp in C# in my case

SqlConnection connection = new SqlConnection(GetConnectionString());
DataSet ds = new DataSet();
SqlDataAdapter Dap_Proj;

if (Session["LoginUser"] == null)
{
    Response.Redirect("Login.aspx");
}
else if (Session["LoginUser"].ToString() == "admin" || Session["CustomerId"] == "1")
{

    Dap_Proj = new SqlDataAdapter("select LinkId,LinkName,CategoryId, ReportLinks,SubmissionStatus,convert(nvarchar(18),LnkSubmsnDate) as LnkSubmsnDate, convert(nvarchar(18), LnkUpdateDate) as LnkUpdateDate,LnkSubmtdBy,K.KeyWord,RenewalDate  from tbl_Link L left join Tbl_keywords K on L.KeywordID=K.KeywordID  where (SubmissionStatus='Approved'or SubmissionStatus='Waiting for Approval') and  LnkSubmtdBy like '%%' and Convert(Char(4),LnkSubmsnDate,100) in (select Convert(Char(4),LnkSubmsnDate,100) from tbl_Link )order by case when RenewalDate is null then 1 else 0 end,RenewalDate", connection);
    ds = new DataSet();
    Dap_Proj.Fill(ds);
}

possible duplicate of [How to execute a stored procedure within C# program](http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) — Alex K., Sep 29 '15 at 11:17
its not duplicate question.im new in writting stored proc with sql sata adpater. — krishna mohan, Sep 29 '15 at 11:26

Tim Schmelter · Accepted Answer · 2015-09-29T11:38:41.857

In your database:

CREATE PROCEDURE uspGetLinks
AS

SELECT linkid, 
       linkname, 
       categoryid, 
       reportlinks, 
       submissionstatus, 
       CONVERT(NVARCHAR(18), lnksubmsndate) AS LnkSubmsnDate, 
       CONVERT(NVARCHAR(18), lnkupdatedate) AS LnkUpdateDate, 
       lnksubmtdby, 
       K.keyword, 
       renewaldate 
FROM   tbl_link L 
       LEFT JOIN tbl_keywords K 
              ON L.keywordid = K.keywordid 
WHERE  ( submissionstatus = 'Approved' 
          OR submissionstatus = 'Waiting for Approval' ) 
       AND lnksubmtdby LIKE '%%' 
       AND CONVERT(CHAR(4), lnksubmsndate, 100) IN (SELECT 
           CONVERT(CHAR(4), lnksubmsndate, 100) 
                                                    FROM   tbl_link) 
ORDER  BY CASE 
            WHEN renewaldate IS NULL THEN 1 
            ELSE 0 
          END, 
          renewaldate 
GO

In C#:

if (Session["LoginUser"] == null)
{
    Response.Redirect("Login.aspx", true);
}

DataSet ds = new DataSet();
using(var connection  = new SqlConnection(GetConnectionString()))
{
    using(var Dap_Proj = new SqlDataAdapter("uspGetLinks", connection))
    {
        Dap_Proj.SelectCommand.CommandType = CommandType.StoredProcedure;
        Dap_Proj.Fill(ds);
    }
}

@krishnamohan: i've edited my answer, there were two closing parenthesis missing. Always use the `using`-statement when you use objects that use unmanaged resources and implement `IDisposable`. This also closes the connection if it's still open. In this case opening/closing the connection is not needed anyway because `SqlDataAdapter.Fill` does it implicitly. — Tim Schmelter, Sep 29 '15 at 11:39

score 0 · Answer 2 · answered Sep 29 '15 at 11:27

            String connStr = GetConnectionString());
            String cmdStr = "SQLStoredProcedure";
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.ExecuteNonQuery();
                        conn.Close();
                        cmd.Dispose();
                        conn.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                TextBox1.Text = ex.Message;
            }
    DataSet ds = new DataSet();
    using (SqlDataAdapter Dap_Proj = new SqlDataAdapter)
    {

... }

score 0 · Answer 3 · answered Sep 29 '15 at 11:34

0

  Dap_Proj = new SqlDataAdapter("sp_getLinks", connection);
            Dap_Proj.SelectCommand.CommandType = CommandType.StoredProcedure;
            ds = new DataSet();
            Dap_Proj.Fill(ds);

answered Sep 29 '15 at 11:34

krishna mohan

257
2
4
14

Consider adding commentary to explain this answer. Without it, it's hard for newcomers to learn from your answer, even if it is accurate. – neontapir Sep 29 '15 at 19:29

how to make in line query as stored procedure with sql dataadapter in c#

3 Answers3