//透過 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());
}
}
