透過 DataSet 將 SQL 與 SQLite 兩個不同資料庫的資料表做 JOIN

 //透過 DataSet 將 SQL 與 SQLite 兩個不同資料庫的資料表做 JOIN

string connectionString = @"Data Source=192.168.100.166;Initial Catalog=trdata;Persist Security Info=True;User ID=cowell;Password=tr2000";

string connectionString2 = @"data source=D:\Work\FTS_MSN_Import_Ticket\db\Msn_Ticket.db";

string queryString = "";

queryString += "SELECT TOP (50) CAST(a.TSUBD_SQ AS varchar(12)) AS INFO_PK, a.TKT_ROUT_TP, a.UPD_DTM ";

queryString += "FROM TRTSUBD AS a LEFT OUTER JOIN TRTSUBDS AS b ON b.TSUBD_SQ = a.TSUBD_SQ ";

queryString += "WHERE (a.WEB_PD = 'Y') AND (a.SALE_FG = 1) AND (a.ADT_CURR_AM > 0) AND ";

queryString += " (a.TKT_ROUT_TP = '來回') AND (a.SALE_EDT >= CONVERT(datetime, GETDATE(), ";

queryString += " 111)) AND (b.DST_CTY_CD1 IS NOT NULL) ";

queryString += "ORDER BY a.TSUBD_SQ";

string queryString2 = "SELECT * FROM Msn_Ticket";

using (DataSet ds = new DataSet())

{

try

{

SqlConnection cnn1 = new SqlConnection(connectionString);

SqlCommand cmd1 = new SqlCommand(queryString, cnn1);

cnn1.Open();

SqlDataAdapter adapter1 = new SqlDataAdapter();

adapter1.TableMappings.Add("Table", "SQL_Ticket");

cmd1.CommandType = CommandType.Text;

adapter1.SelectCommand = cmd1;

adapter1.Fill(ds);

cnn1.Close();

//-----------------------

SQLiteConnection cnn2 = new SQLiteConnection(connectionString2);

SQLiteCommand cmd2 = new SQLiteCommand(queryString2, cnn2);

cnn2.Open();

SQLiteDataAdapter adapter2 = new SQLiteDataAdapter();

adapter2.TableMappings.Add("Table", "SQLite_Ticket");

cmd2.CommandType = CommandType.Text;

adapter2.SelectCommand = cmd2;

adapter2.Fill(ds);

cnn2.Close();

DataColumn parentColumn = ds.Tables["SQL_Ticket"].Columns["INFO_PK"];

DataColumn childColumn = ds.Tables["SQLite_Ticket"].Columns["INFO_PK"];

DataRelation relation = new DataRelation("Ticket", parentColumn, childColumn);

ds.Relations.Add(relation);

ds.AcceptChanges();

DataTable p = ds.Tables["SQL_Ticket"];

DataTable c = ds.Tables["SQLite_Ticket"];

var query =

from t1 in p.AsEnumerable()

join t2 in c.AsEnumerable()

on t1.Field<string>("INFO_PK") equals t2.Field<string>("INFO_PK")

select new

{

INFO_PK = t1.Field<string>("INFO_PK"),

ROUTE = t1.Field<string>("TKT_ROUT_TP"),

Modify_Date = t1.Field<DateTime>("UPD_DTM")

}

into newtable

orderby newtable.INFO_PK

select newtable;

dataGridView1.DataSource = query.ToList();

}

catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

}