How I Can Join 2 Datat Table With Table On Sql Databse

Question

0.00/5 (No votes)

See more:

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

C#

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

SQL

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

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

SQL

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

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

Posted 29-Jun-14 19:38pm

Mohammad Soleimani

Updated 29-Jun-14 21:13pm

v5

Add a Solution

Comments

George Jonsson 30-Jun-14 2:02am

Hmm, for me it is not clear what your problem really is.
DataAdapter.Fill will fill a data table with the columns you specify in your select statement. Can you explain more where it goes wrong?
Also, why do specify code for two tables when you seem to use only one table at the time.
You have a lot of redundant code.

Mohammad Soleimani 30-Jun-14 2:55am

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 bou now i cant run query on this position.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Answer 1 · 2014-06-29T21:42:00

It might be clearer if you do like this.

C#

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;
}

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.