如何在Sql Databse上加入2个数据表与表 [英] How I Can Join 2 Datat Table With Table On Sql Databse
问题描述
我有2个数据表，并在表格中通过查询从数据库表中加入
我使用3层编程
我的报告DAL类如下
Hi,I have 2 Data Table and on Table Joined from Database Tables By Query
I use 3 Layer Programming
My Report Class DAL Like Below
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace DAL
{
public class _Report:SQLActivity
{
public int identity = 0;
public string Table = "";
public int DatasetLink = 0;
DataSet ds = new DataSet("Report");
DataTable dtObject = new DataTable("dtObject");
DataTable dtWarehouse = new DataTable("dtWarehouse");
public void Crate_DataTable()
{
//point 1
switch (DatasetLink)
{
case 0:
{//Coding
DataColumn[] columns = new DataColumn[0];
dtObject.Columns.Add("identity", typeof(int));
dtObject.Columns.Add("iD", typeof(int));
dtObject.Columns.Add("Title", typeof(string));
dtObject.PrimaryKey = columns;
ds.Tables.Add(dtObject);
}
break;
case 9:
{//Warehouse
DataColumn[] columns = new DataColumn[0];
dtWarehouse.Columns.Add("identity", typeof(int));
dtWarehouse.Columns.Add("iD", typeof(int));
dtWarehouse.Columns.Add("Title", typeof(string));
dtWarehouse.PrimaryKey = columns;
ds.Tables.Add(dtWarehouse);
}
break;
}
}
public void AddNew_SelectDatatable_Row(int identity, int iD, string Title)
{
switch (DatasetLink)
{
case 0:
{//Coding
dtObject.Rows.Add(new object[] { identity,iD, Title });
}
break;
case 9:
{//Warehouse
dtWarehouse.Rows.Add(new object[] { identity, iD, Title });
}
break;
}
}
public int Delete_SelectDatatable_Row(int iD)
{
//point 2
switch (DatasetLink)
{
case 0:
{//Coding
DataRow[] drr = dtObject.Select("iD=' " + iD + " ' ");
foreach (var row in drr)
row.Delete();
}
break;
case 9:
{//Warehouse
DataRow[] drr = dtWarehouse.Select("iD=' " + iD + " ' ");
foreach (var row in drr)
row.Delete();
}
return 1;
}
public DataTable Get_Update_SelectDatatable()
{
switch (DatasetLink)
{
case 0:
{//Coding
Table = "dtObject";
}
break;
case 9:
{//Warehouse
Table = "dtWarehouse";
}
break;
return ds.Tables[Table];
}
public int GetFind_SelectDatatable_iD(int iD)
{
int retval = 0;
switch (DatasetLink)
{
case 0:
{//Coding
foreach (DataRow row in dtObject.Rows)
{
if (row["iD"].ToString() == iD + "")
{
retval = 1;
}
}
}
break;
case 9:
{//Warehouse
foreach (DataRow row in dtWarehouse.Rows)
{
if (row["iD"].ToString() == iD+"")
{
retval = 1 ;
}
}
}
break;
return retval;
}
public DataTable Report_WarehouseStore()
{
SqlDataAdapter da =new SqlDataAdapter (@"SELECT WarehouseStore.ProductID, WarehouseStore.ProductCode, WarehouseStore.ProductName, UnitPart.UnitPart, Warehouse.WarehouseName,WarehouseStore.ProductValue FROM WarehouseStore INNER JOIN UnitPart ON WarehouseStore.UnitPartID = UnitPart.UnitPartid INNER JOIN Warehouse ON Warehouse.WarehouseiD = WarehouseStore.WarehouseiD INNER JOIN dtObject ON dtObject.iD =WarehouseStore.ProductCode INNER JOIN dtWarehouse ON dtWarehouse.iD =WarehouseStore.WarehouseiD",new _Connections().Cnn);
DataTable dt=new DataTable();
da.Fill(dt);
ds.Tables.Add(dt);
ds.Tables.Add(dtObject);
ds.Tables.Add(dtWarehouse);
return dt;
}
}
}
我很困惑我如何在数据集和返回数据表上运行此查询
I'm Confused How i can run this Query On Data set and Return Data table
SELECT WarehouseStore.ProductID, WarehouseStore.ProductCode, WarehouseStore.ProductName, UnitPart.UnitPart, Warehouse.WarehouseName,WarehouseStore.ProductValue FROM WarehouseStore INNER JOIN UnitPart ON WarehouseStore.UnitPartID = UnitPart.UnitPartid INNER JOIN Warehouse ON Warehouse.WarehouseiD = WarehouseStore.WarehouseiD INNER JOIN dtObject ON dtObject.iD =WarehouseStore.ProductCode INNER JOIN dtWarehouse ON dtWarehouse.iD =WarehouseStore.WarehouseiD
数据dtObject和dtWarehouse由用户插入。我想从WarehoseStore中选择数据当WarehoseStore.ProductCode = dtObject.iD和WarehouseStore.WarehouseiD = dtWarehouse.iD
如果dtObject数据库表这可以工作但现在我不能在这个位置上运行查询。
运行此查询时
Data "dtObject" and "dtWarehouse" insert by User. i want select data from WarehoseStore when WarehoseStore.ProductCode=dtObject.iD and WarehouseStore.WarehouseiD=dtWarehouse.iD
if dtObject database table is this can work but now i cant run query on this position.
when is run this Query
SELECT WarehouseStore.ProductID, WarehouseStore.ProductCode, WarehouseStore.ProductName, UnitPart.UnitPart, Warehouse.WarehouseName,WarehouseStore.ProductValue FROM WarehouseStore INNER JOIN UnitPart ON WarehouseStore.UnitPartID = UnitPart.UnitPartid INNER JOIN Warehouse ON Warehouse.WarehouseiD = WarehouseStore.WarehouseiD
我没有问题但是将此查询添加到上面的代码
INNER JOIN dtObject ON dtObject.iD = WarehouseStore.ProductCode INNER JOIN dtWarehouse ON dtWarehouse.iD = WarehouseStore.WarehouseiD
我的问题将开始
I have no problem but when add this Query to above code
INNER JOIN dtObject ON dtObject.iD =WarehouseStore.ProductCode INNER JOIN dtWarehouse ON dtWarehouse.iD =WarehouseStore.WarehouseiD
My problem will start
推荐答案
如果你这样做可能会更清楚。
It might be clearer if you do like this.
public DataTable Report_WarehouseStore(int index)
{
int objectID = (int)dtObject.Rows[index]["iD"];
int warehouseID = (int)dtWarehouse.Rows[index]["iD"];
SqlDataAdapter da = new SqlDataAdapter(
String.Format(@"SELECT WarehouseStore.ProductID, WarehouseStore.ProductCode, WarehouseStore.ProductName, UnitPart.UnitPart, Warehouse.WarehouseName,WarehouseStore.ProductValue
FROM WarehouseStore
INNER JOIN UnitPart ON WarehouseStore.UnitPartID = UnitPart.UnitPartid
INNER JOIN Warehouse ON Warehouse.WarehouseiD = WarehouseStore.WarehouseiD
WHERE WarehouseStore.ProductCode = {0} AND WarehouseStore.WarehouseiD = {1}
", objectID, warehouseID),
new _Connections().Cnn);
DataTable dt=new DataTable();
da.Fill(dt);
dt.AcceptChanges();
return dt;
}
然后为数据表中的每一行调用此方法一次。
但是，它们需要具有相同的行数。
不保证SELECT语句是正确的。我没有你的数据库来测试。
Then you call this method once for each row in the data tables.
They need to have the same number of rows, though.
No guarantee that the SELECT statement is correct. I don't have your database to test with.
这篇关于如何在Sql Databse上加入2个数据表与表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
