从多个表中检索数据。 [英] Retrieve data from multiple tables.
本文介绍了从多个表中检索数据。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试从多个表中返回结果。
我的桌子是这些
Masterleave：
代码varchar（20）未选中
名称varchar（150）已检查
描述varchar（250）已检查
-------------------- ----------------------
TrLeaveRequest：
RequestID int未选中
RequestBy varchar（20）已检查
LeaveCode varchar（20）已检查
RequestDate datetime已检查
RequiredDate datetime已检查
JoinDate datetime Checked
TotalDays int Checked
还有更多...
---- -----------------------------------
TrLeaveApproval：
RequestID int未选中
RefTrID int已检查
RequestTo varchar（20）已检查
TrDate datetime已检查
状态int已检查
------------------------------- -------
我的asp代码是
i am trying to return result from multiple tables.
My tables are these
Masterleave:
Code varchar(20) Unchecked
Name varchar(150) Checked
Description varchar(250) Checked
------------------------------------------
TrLeaveRequest:
RequestID int Unchecked
RequestBy varchar(20) Checked
LeaveCode varchar(20) Checked
RequestDate datetime Checked
RequiredDate datetime Checked
JoinDate datetime Checked
TotalDays int Checked
there is some more...
---------------------------------------
TrLeaveApproval:
RequestID int Unchecked
RefTrID int Checked
RequestTo varchar(20) Checked
TrDate datetime Checked
Status int Checked
--------------------------------------
My asp code is
private void FillGrid()
{
grvList.PageSize = Convert.ToInt16(drpRecords.SelectedValue.ToString());
int PageNumber = Convert.ToInt32(txtPageNo.Text);
LeaveRequestList objLst = new LeaveRequestList();
string strFilter = "";
string strFilter1 = "";
strFilter1 += " AND R.RequestBy ='" + Session["empid"].ToString() + "'";
if (txtLetterType.Text != "")
{
strFilter += " AND R.LeaveCode LIKE '%" + ExpertGeneric.CleanString(txtLetterType.Text) + "%'";
}
if (txtStatus.Text != "")
{
strFilter += " AND R.Status LIKE '%" + ExpertGeneric.CleanString(txtStatus.Text) + "%'";
}
objDB.strFilter = strFilter1 + strFilter;
int cnt = objDB.Count();
lblRecordNo.Text = "Total Rows : " + cnt.ToString("#0") + " ";
int TotalPage = (cnt / grvList.PageSize) + ((cnt % grvList.PageSize) > 0 ? 1 : 0);
lblShowing.Text = " of " + TotalPage.ToString();
if (PageNumber > TotalPage)
{
txtPageNo.Text = TotalPage.ToString();
PageNumber = TotalPage;
}
objLst = objDB.GetListForGrid(grvList.PageSize, PageNumber, "RequestDate");
// objLst = objDB.GetListForGrid(Session["empid"].ToString());
grvList.DataSource = objLst;
grvList.DataBind();
foreach (GridViewRow gr in grvList.Rows)
{
if (gr.Cells[3].Text != "Requested")
{
((ImageButton)gr.FindControl("imgDelete")).Visible = false;
((ImageButton)gr.FindControl("imgEdit")).Visible = false;
}
}
}
-------------------- -----------------------------------
-------------------------------------------------------
public LeaveRequestList GetListForGrid(int RecCount, int PageNo, string OrderBy)
{
strSql = "WITH TempTable AS (SELECT R.RequestID,R.RequestBy,R.LeaveCode,R.RequestDate,R.RequiredDate,R.JoinDate,R.TotalDays,R.Remark,R.Status,R.CreatedBy,R.CreatedDate,R.ModifiedBy,R.ModifiedDate,T.Name AS LeaveTypeName , E.Name AS EmployeeName, (CASE R.Status WHEN 1 THEN 'Requested' WHEN 2 THEN 'Approved' WHEN 3 THEN 'Rejected' ELSE '--' END) AS StatusString, ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") AS 'RowNumber' FROM TrLeaveRequest R INNER JOIN MasterLeave T ON R.LeaveCode = T.Code INNER JOIN EmployeeMaster E ON R.RequestBy = E.EmpID WHERE 1 = 1 " + strFilter + ") SELECT * FROM TempTable WHERE RowNumber BETWEEN " + ((PageNo == 1) ? 1 : ((PageNo - 1) * RecCount) + 1).ToString() + " AND " + ((PageNo == 1) ? RecCount : (PageNo * RecCount)).ToString().ToString();
LeaveRequestList objList = new LeaveRequestList();
DataTable dt = new DataTable();
dt = objDB.GetDataTableFromSQL(strSql);
if (dt != null)
{
foreach (DataRow Dr in dt.Rows)
{
LeaveRequest obj = new LeaveRequest();
obj.RequestID = Convert.ToInt32(Dr["RequestID"].ToString());
if (Dr["RequestBy"] != DBNull.Value)
obj.RequestBy = Dr["RequestBy"].ToString();
else
obj.RequestBy = "";
if (Dr["LeaveCode"] != DBNull.Value)
obj.LeaveCode = Dr["LeaveCode"].ToString();
else
obj.LeaveCode = "";
if (Dr["LeaveTypeName"] != DBNull.Value)
obj.LeaveTypeName = Dr["LeaveTypeName"].ToString();
else
obj.LeaveTypeName = "";
if (Dr["EmployeeName"] != DBNull.Value)
obj.EmployeeName = Dr["EmployeeName"].ToString();
else
obj.EmployeeName = "";
if (Dr["StatusString"] != DBNull.Value)
obj.StatusString = Dr["StatusString"].ToString();
else
obj.StatusString = "";
if (Dr["RequestDate"] != DBNull.Value)
obj.RequestDate = Convert.ToDateTime(Dr["RequestDate"].ToString());
else
obj.RequestDate = new DateTime();
if (Dr["RequiredDate"] != DBNull.Value)
obj.RequiredDate = Convert.ToDateTime(Dr["RequiredDate"].ToString());
else
obj.RequiredDate = new DateTime();
if (Dr["JoinDate"] != DBNull.Value)
obj.JoinDate = Convert.ToDateTime(Dr["JoinDate"].ToString());
else
obj.JoinDate = new DateTime();
if (Dr["TotalDays"] != DBNull.Value)
obj.TotalDays = Convert.ToInt32(Dr["TotalDays"].ToString());
else
obj.Status = 0;
if (Dr["Remark"] != DBNull.Value)
obj.Remark = Dr["Remark"].ToString();
else
obj.Remark = "";
if (Dr["Status"] != DBNull.Value)
obj.Status = Convert.ToInt32(Dr["Status"].ToString());
else
obj.Status = 0;
objList.Add(obj);
}
}
return objList;
}
--------------------------------------
it is not give me any error, same time no return. please help me to find solution.
推荐答案
你应该检查SQL编辑器是否得到了结果，为了更好的处理我建议创建一个存储过程，而不是在前端使用普通查询，并传递过滤器所需的SP中的所有参数。
You should check in SQL editor whether you are getting the results and for better process i suggest to create a Stored procedure instead of using plain query in the front end and pass all the parameters in the SP required for filter.
您的ASP.NET可能会打开 SQL注入 [ ^ ]。
如何避免它？请阅读：
如何：保护ASP.NET中的SQL注入 [ ^ ]
在停止之前阻止SQL注入攻击 [ ^ ]
SQL注入以及如何避免它 [ ^ ]
动态SQL& SQL注入 [ ^ ]
正如shweta nikhil mishra所提到的，在这种情况下的一般解决方案是使用存储过程 [ ^ ]。
演练：使用GridView Web服务器控件中的存储过程显示数据 [ ^ ]
如何：执行返回行的存储过程 [ ^ ]
Your ASP.NET is potentially open for SQL Injection[^].
How to avoid it? Please, read this:
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
SQL Injection and how to avoid it[^]
Dynamic SQL & SQL injection[^]
As shweta nikhil mishra mentioned the general solution in this case is to use stored procedure[^].
Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server Control[^]
How to: Execute a Stored Procedure that Returns Rows[^]
CREATE PROCEDURE
@param1 type,
@param2 type
@paramN type
AS
BEGIN
--sample SELECT statement
SELECT <Field_list>
FROM TableName
WHERE Field1 = @param1
END
You can use something like below, but just check the highlihted code below as this condition does seems the cause of issue for getting no data as you are trying to match some filter string i.e. 1=1+some filter which will never match. i am not sure if this is neccessary condition , so if it is still required you can write that line as 1 = 1 +@param3
Create Proc Rpt_LeaveInformation(@param1 Nvarchar(100),@param2 Nvarchar(100),@param3 Nvarchar(50))
As
BEGIN
SELECT
R.RequestID,R.RequestBy,R.LeaveCode,R.RequestDate,R.RequiredDate,R.JoinDate,R.TotalDays,R.Remark,R.Status,R.CreatedBy,R.CreatedDate,
R.ModifiedBy,R.ModifiedDate,T.Name AS LeaveTypeName , E.Name AS EmployeeName,
(CASE R.Status WHEN 1 THEN 'Requested' WHEN 2 THEN 'Approved' WHEN 3 THEN 'Rejected' ELSE '--' END)
AS StatusString, ROW_NUMBER() OVER (ORDER BY RequestDate) AS 'RowNumber'
FROM
TrLeaveRequest R
INNER JOIN MasterLeave T ON R.LeaveCode = T.Code
INNER JOIN EmployeeMaster E ON R.RequestBy = E.EmpID
WHERE
<pre>1 = 1 " + strFilter + ") </pre>SELECT *
FROM TempTable
WHERE RowNumber BETWEEN @param1 AND @param2
END
这篇关于从多个表中检索数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
