从数据库检索多行 [英] Retereving multiple rows from database
本文介绍了从数据库检索多行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
地狱世界，
我创建了一个动态表，并且要在该表上绑定数据，当我必须将多个数据添加到单个列并且如果不存在数据时，就会出现问题.我正在将整个代码与数据库输出一起发布.
如果有人找到答案，请给我，谢谢.
TimeTable.aspx.cs
Hell World,
i have created a dynamic table and on that table i want to bind data , the problem arises when i have to add multiple data to a single column and if data is not present. i am posting the whole code along with the database outputs.
if anyone found the answer please send me, thankyou.
TimeTable.aspx.cs
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Drawing;
using System.Data.SqlClient;
public partial class Admin_RptTimeTable : System.Web.UI.Page
{
int RowCount = 0, ColumnCount = 0, match = 0;
string time1 = "", textValue = "", tme = "";
Table tbl = new Table();
Table tbl2 = new Table();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString.ToString());
DataSet dsSubject = new DataSet();
DataSet dsBatchMWF = new DataSet();
DataSet dsBatchTTS = new DataSet();
DataSet dsTimming = new DataSet();
DataSet connectionSubject()
{
SqlDataAdapter sda = new SqlDataAdapter("select distinct[Subject] from subject", con);
sda.Fill(dsSubject);
return dsSubject;
}
DataSet connectionTimming()
{
SqlDataAdapter sda = new SqlDataAdapter("select distinct timefrom + TimeFromAmPm +' - '+ timeto + TimeToAmPm as timet from Toppers_BatchTimming", con);
sda.Fill(dsTimming);
return dsTimming;
}
DataSet connectionBatchMWF(string sub)
{
SqlDataAdapter sda = new SqlDataAdapter("SELECT distinct timefrom + TimeFromAmPm +' - '+ timeto + TimeToAmPm as timet, Toppers_Batch.BatchName FROM Toppers_Batch INNER JOIN Toppers_BatchTimming ON Toppers_Batch.BatchName = Toppers_BatchTimming.BatchName INNER JOIN Subject ON Toppers_Batch.SubjectId = Subject.Id INNER JOIN Toppers_days ON Toppers_BatchTimming.DayId = Toppers_days.dayid WHERE (Toppers_days.Dayname IN('Monday','Wednesday','Friday')) and Subject.Subject = '" + sub + "'", con);
sda.Fill(dsBatchMWF);
return dsBatchMWF;
}
DataSet connectionBatchTTS(string sub)
{
SqlDataAdapter sda = new SqlDataAdapter("SELECT distinct timefrom + TimeFromAmPm +' - '+ timeto + TimeToAmPm as timet, Toppers_Batch.BatchName FROM Toppers_Batch INNER JOIN Toppers_BatchTimming ON Toppers_Batch.BatchName = Toppers_BatchTimming.BatchName INNER JOIN Subject ON Toppers_Batch.SubjectId = Subject.Id INNER JOIN Toppers_days ON Toppers_BatchTimming.DayId = Toppers_days.dayid WHERE (Toppers_days.Dayname IN('Tuesday','Thursday','Saturday')) and Subject.Subject = '" + sub + "'", con);
sda.Fill(dsBatchTTS);
return dsBatchTTS;
}
protected void Page_Load(object sender, EventArgs e)
{
connectionSubject();
RowCount = dsSubject.Tables[0].Rows.Count;
connectionTimming();
ColumnCount = dsTimming.Tables[0].Rows.Count;
CreateDynamicTable();
CreateDynamicTable2();
}
void CreateDynamicTable()
{
int tblRows = RowCount;
int tblCols = ColumnCount;
tbl.BorderWidth = 1;
tbl.BorderColor = Color.Black;
tbl.GridLines = GridLines.Both;
tbl.ForeColor = Color.Black;
createTableHeader(tbl);
PlaceHolder1.Controls.Add(tbl);
for (int i = 0; i < tblRows; i++)
{
connectionSubject();
TableRow tr = new TableRow();
for (int j = 0; j <= tblCols; j++)
{
TableCell tc = new TableCell();
Label txtBox = new Label();
if (j == 0)
txtBox.Text = dsSubject.Tables[0].Rows[i][0].ToString();
else
txtBox.Text = " --";
tc.Controls.Add(txtBox);
tr.Cells.Add(tc);
}
tbl.Rows.Add(tr);
dsSubject.Clear();
}
ViewState["dynamictable"] = true;
insertDataMWF();
}
void CreateDynamicTable2()
{
int tblRows = RowCount;
int tblCols = ColumnCount;
tbl2.BorderWidth = 1;
tbl2.BorderColor = Color.Black;
tbl2.GridLines = GridLines.Both;
tbl2.ForeColor = Color.Black;
createTableHeader(tbl2);
PlaceHolder2.Controls.Add(tbl2);
for (int i = 0; i < tblRows; i++)
{
connectionSubject();
TableRow tr = new TableRow();
for (int j = 0; j <= tblCols; j++)
{
TableCell tc = new TableCell();
Label txtBox = new Label();
if (j == 0)
txtBox.Text = dsSubject.Tables[0].Rows[i][0].ToString();
else
txtBox.Text = " --";
tc.Controls.Add(txtBox);
tr.Cells.Add(tc);
}
tbl2.Rows.Add(tr);
dsSubject.Clear();
}
ViewState["dynamictable"] = true;
insertDataTTS();
}
protected override void LoadViewState(object earlierState)
{
base.LoadViewState(earlierState);
if (ViewState["dynamictable"] == null)
CreateDynamicTable();
}
DataSet insertDataMWF()
{
dsSubject.Clear();
dsBatchMWF.Clear();
connectionSubject();
string subject = "";
for (int i = 1; i <= RowCount; i++)
{
subject = dsSubject.Tables[0].Rows[i - 1][0].ToString();
connectionBatchMWF(subject);
if (dsBatchMWF.Tables[0].Rows.Count != 0)
{
for (int k = 0; k < dsBatchMWF.Tables[0].Rows.Count; k++)
{
time1 = dsBatchMWF.Tables[0].Rows[k][0].ToString();
textValue = dsBatchMWF.Tables[0].Rows[k][1].ToString();
getTime(time1);
match = Convert.ToInt32(tme);
for (int m = 1; m <= ColumnCount; m++)
{
if (m == match)
{
tbl.Rows[i].Cells[m].Text = textValue;
}
else
tbl.Rows[i].Cells[m].Text = " --";
}
}
}
}
return dsSubject;
}
DataSet insertDataTTS()
{
dsSubject.Clear();
dsBatchTTS.Clear();
connectionSubject();
string subject = "";
for (int i = 1; i <= RowCount; i++)
{
subject = dsSubject.Tables[0].Rows[i - 1][0].ToString();
connectionBatchTTS(subject);
if (dsBatchMWF.Tables[0].Rows.Count != 0)
{
for (int k = 0; k < dsBatchTTS.Tables[0].Rows.Count; k++)
{
time1 = dsBatchTTS.Tables[0].Rows[k][0].ToString();
textValue = dsBatchTTS.Tables[0].Rows[k][1].ToString();
getTime(time1);
match = Convert.ToInt32(tme);
for (int m = 1; m <= ColumnCount; m++)
{
if (m == match)
tbl2.Rows[i].Cells[m].Text = textValue;
else
tbl2.Rows[i].Cells[m].Text = " --";
}
}
}
}
return dsSubject;
}
Table createTableHeader(Table tble)
{
TableHeaderRow myHeader = new TableHeaderRow();
myHeader.Width = Unit.Percentage(100);
TableHeaderCell cell1 = new TableHeaderCell();
cell1.Width = Unit.Percentage(30);
cell1.Text = "Subjects";
myHeader.Cells.Add(cell1);
connectionTimming();
for (int i = 0; i < ColumnCount; i++)
{
TableHeaderCell cell2 = new TableHeaderCell();
cell2.Width = Unit.Percentage(25);
cell2.Text = dsTimming.Tables[0].Rows[i][0].ToString();
myHeader.Cells.Add(cell2);
}
tble.Rows.AddAt(0, myHeader);
return tble;
}
string getTime(string t)
{
connectionTimming();
for (int i = 0; i < ColumnCount; i++)
{
if (t == dsTimming.Tables[0].Rows[i][0].ToString())
{
tme = (i + 1).ToString();
}
}
return tme;
}
}
TimeTable.aspx
TimeTable.aspx
<%@ Page Language="C#" MasterPageFile="~/Admin/MasterPage.master" AutoEventWireup="true"
CodeFile="RptTimeTable.aspx.cs" Inherits="Admin_RptTimeTable" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
<table align="center">
<tr>
<td align="center" style="font-family: Arial Black; font-size: medium;
color: Gray">
MWF(Monday, Wednesday, Friday) Batches
</td>
</tr>
<tr>
<td>
<asp:PlaceHolder ID="PlaceHolder1" runat="server"></asp:PlaceHolder>
</td>
</tr>
<tr>
<td align="center" style="font-family: Arial Black; font-size: medium;
color: Gray">
TTS(Tuesday, Thursday, Saturday) Batches
</td>
</tr>
<tr>
<td>
<asp:PlaceHolder ID="PlaceHolder2" runat="server"></asp:PlaceHolder>
</td>
</tr>
</table>
</asp:Content>
-------------------------------------------------- ---------
数据库输出
查询1
-----------------------------------------------------------
database outputs
query1
select distinct[Subject] from subject
主题
----------
化学
计算机科学
经济学
英文
数学
物理
query2
Subject
----------
Chemistry
Computer Science
Economics
English
Maths
Physics
query2
select distinct timefrom + TimeFromAmPm +'' - ''+ timeto + TimeToAmPm as timet from Toppers_BatchTimming timet
-----------
12AM-1AM
1AM-2AM
2.3AM-3AM
3AM-4AM
下午3点-下午4点
下午3点-下午5点
5AM-6AM
query3
timet
-----------
12AM - 1AM
1AM - 2AM
2.3AM - 3AM
3AM - 4AM
3PM - 4PM
3PM - 5PM
5AM - 6AM
query3
SELECT distinct timefrom + TimeFromAmPm +'' - ''+ timeto + TimeToAmPm as timet, Toppers_Batch.BatchName
FROM Toppers_Batch
INNER JOIN Toppers_BatchTimming ON Toppers_Batch.BatchName = Toppers_BatchTimming.BatchName
INNER JOIN Subject ON Toppers_Batch.SubjectId = Subject.Id
INNER JOIN Toppers_days ON Toppers_BatchTimming.DayId = Toppers_days.dayid
WHERE (Toppers_days.Dayname IN(''Monday'',''Wednesday'',''Friday''))
and Subject.Subject = ''maths''
如果主题是化学
计时器BatchName
----------------------
XIChe1上午8点至上午9点
如果主题是数学
计时器BatchName
-------------
12AM-1AM XMat2
1AM-2AM XMat3
2.3AM-3AM XMat4
3AM-4AM XMat5
5PM-7PM XMat1
对于其他主题，数据为nill
if subject is chemistry
timet BatchName
----------------------
8AM - 9AM XIChe1
if subject is maths
timet BatchName
-------------
12AM - 1AM XMat2
1AM - 2AM XMat3
2.3AM - 3AM XMat4
3AM - 4AM XMat5
5PM - 7PM XMat1
and for other subjects data is nill
推荐答案
如果您的意思是问题是在连接两个字符串而另一个为null时，则结果为null.例如:
If you mean that the problem is when concatenating two strings and the other one is null, the result is null. For example:
SELECT ''A'' + NULL
返回NULL.如果要在语句中处理它，则可以使用例如COALESCE:
returns NULL. If you want to handle it in the statement you can use for example COALESCE:
SELECT ''A'' + COALESCE(NULL, '''')
这将返回"A".当然，您将拥有真实的列名，而不是"A"和NULL.
This would return ''A''. Of course you would have real column names instead of the ''A'' and NULL.
这篇关于从数据库检索多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
