如何在c中使用sql数据适配器将行内查询作为存储过程
本文关键字:查询 存储过程 适配器 数据 sql | 更新日期: 2023-09-27 18:14:56
im有内联查询,我想让它成为存储过程。在我的例子中,如何在C#中编写sp
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);
}
在您的数据库中:
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
在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);
}
}
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)
{
}
Dap_Proj = new SqlDataAdapter("sp_getLinks", connection);
Dap_Proj.SelectCommand.CommandType = CommandType.StoredProcedure;
ds = new DataSet();
Dap_Proj.Fill(ds);