#眉標=Enterprise Library、Smart Client、Software Factory
#副標=設計模型套件系列（22）
#大標=建置Windows應用程式的軟體工程
#作者=文／圖 王寧疆
===<反灰>=============
public const string ImportExcelToDB = "ImportExcelToDB";
==<end>==============
===<反灰>=============
AddToolStripButton(Constants.CommandNames.ImportExcelToDB, Properties.Resources.ImportExcel,
"匯入Excel文件到資料庫");
==<end>==============
===<反灰>=============
using System.Drawing;
using SCSFDemo.ImportExcelModule.Interface.Constants;
==<end>==============
===<反灰>=============
private void AddToolStripButton(string CommandName, Image ButtonImage, string ToolTipText)
{
ToolStripButton button = new ToolStripButton(); //建立ToolStripButton類別的物件
button.Image=ButtonImage; //設定ToolStripButton類別的物件顯示的圖示
button.ToolTipText = ToolTipText; //建立ToolStripButton類別的物件顯示的工具提示
WorkItem.UIExtensionSites[UIExtensionSiteNames.MainToolbar].Add(button); //將Button加入到工具列
WorkItem.Commands[CommandName].AddInvoker(button, "Click"); //指定處理Click事件的命令名稱
}
==<end>==============
===<反灰>=============
using Microsoft.Practices.CompositeUI;
using System.IO;
==<end>==============
===<反灰>=============
[Service]
public class FileService
{
public string[] GetExcelDocumentLists()
{
List<string> li = new List<string>(); //建立List集合物件
DirectoryInfo di = new DirectoryInfo("Documents"); //建立DirectoryInfo物件
foreach (FileInfo fi in di.GetFiles("*.xls")) //取出Documents中xls檔案
{
li.Add(fi.FullName); //將xls檔案的名稱加入到List集合
}
return li.ToArray(); //將名稱轉型成字串陣列後傳回
}
}
==<end>==============
===<反灰>=============
using System.Data.SqlClient;
using System.Data;
==<end>==============
===<反灰>=============
[Service]
public class DBService
{
string strConn = "Data Source=.;Initial Catalog=Northwind;Integrated Security=true";
public DataTable GetOrderDetails()
{
string strSQL = "Select * from [Order Details]"; //查詢訂單明細的SQL敘述
SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn); //建立SqlDataAdapter物件
DataSet ds = new DataSet(); //建立DataSet類別的物件
da.Fill(ds); //將查詢得到訂單明細記錄存入DataSet物件
return ds.Tables[0]; //傳回存放訂單明細記錄的資料表
}
public void InsertOrderDetails(int OrderID, int ProductID, float UnitPrice, Int16 Quantity, float Discount)
{
string strSQL = "Insert Into [Order Details](OrderID, ProductID, UnitPrice, Quantity, Discount) " + "Values(@OrderID,@ProductID,@UnitPrice,@Quantity,@Discount)"; //新增訂單明細記錄
SqlConnection conn = new SqlConnection(strConn); //建立SqlConnection物件
SqlCommand cmd = new SqlCommand(strSQL, conn); //建立SqlCommand物件
cmd.CommandType = CommandType.Text; //設定SqlCommand類別的物件的CommandType屬性
cmd.Parameters.AddWithValue("@OrderID", OrderID); //填入OrderID參數
cmd.Parameters.AddWithValue("@ProductID", ProductID); //填入ProductID參數
cmd.Parameters.AddWithValue("@UnitPrice", UnitPrice); //填入UnitPrice參數
cmd.Parameters.AddWithValue("@Quantity", Quantity); //填入Quantity參數
cmd.Parameters.AddWithValue("@Discount", Discount); //填入Discount參數
conn.Open(); //開啟資料庫連線
cmd.ExecuteNonQuery(); //執行新增訂單明細記錄
cmd.Dispose(); //丟棄SqlCommand類別的物件
conn.Close();
cmd.Dispose(); //丟棄SqlConnection類別的物件
}
}
==<end>==============
===<反灰>=============
void BindDocumentList(string[] DocList);
==<end>==============
===<反灰>=============
public void BindDocumentList(string[] DocList)
{
treeView1.Nodes.Clear(); //清除TreeView顯示的文件
TreeNode root=treeView1.Nodes.Add("訂單"); //為TreeView加入根節點
foreach(string s in DocList) //取出Excel檔名
{
root.Nodes.Add(s); //將Excel檔名加入到根節點下
}
}
==<end>==============
===<反灰>=============
[ServiceDependency]
public FileService ListFileService
{
set { m_FileService = value; }
}
==<end>==============
===<反灰>=============
public override void OnViewReady()
{
View.BindDocumentList(
m_FileService.GetExcelDocumentLists()); //呼叫DocView類別的BindDocumentList方法
base.OnViewReady();
}
==<end>==============
===<反灰>=============
OrderID ProductID UnitPrice Quantity Discount
10248 1 18 10 0
==<end>==============
===<反灰>=============
DocView docview = ShowViewInWorkspace<DocView>(WorkspaceNames.LeftWorkspace);
==<end>==============
===<反灰>=============
public void BindOrderDetails(System.Data.DataTable dt)
{
dataGridView1.DataSource = dt; //設定DataGridView顯示的資料來源
}
==<end>==============
===<反灰>=============
[ServiceDependency]
public DBService QueryDBService
{
set { m_DBService = value; }
}
==<end>==============
===<反灰>=============
public override void OnViewReady()
{
View.BindOrderDetails(m_DBService.GetOrderDetails()); //呼叫View的BindOrderDetails
base.OnViewReady();
}
==<end>==============
===<反灰>=============
DBView dbview = ShowViewInWorkspace<DBView>(WorkspaceNames.RightWorkspace);
==<end>==============
===<反灰>=============
using Microsoft.Practices.CompositeUI.Commands;
using System.Windows.Forms;
==<end>==============
===<反灰>=============
[CommandHandler(Constants.CommandNames.ImportExcelToDB)]
public void OnImportExcelToDB(object sender, EventArgs e)
{
if (treeView1.SelectedNode != null) //判斷是否已選取匯入的Excel
{
if (Path.GetExtension(
treeView1.SelectedNode.Text).ToUpper().Equals(".XLS")) //判斷匯入文件的副檔
{
OnDataImported(new
Microsoft.Practices.CompositeUI.Utility.DataEventArgs<string>(
treeView1.SelectedNode.Text)); //引發DataImported事件
MessageBox.Show(treeView1.SelectedNode.Text +
"匯入Excel文件成功!"); //匯入文件成功
}
else
{
MessageBox.Show("請選擇欲匯入資料庫的文件!"); //錯誤訊息
}
}
}
==<end>==============
===<反灰>=============
public void ImportExcelData(string FileName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName +
";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; //連線Excel文件的資訊
string strSQL = "Select * from [Sheet1$]"; //查詢第一個工作表
DataSet ds = new DataSet(); //建立DataSet物件
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); //建立OleDbDataAdapter物件
da.Fill(ds); //執行查詢，並將查詢結果存入DataSet物件
foreach (DataRow dr in ds.Tables[0].Rows) //取得查詢得到的記錄
{
int OrderID = Convert.ToInt32(dr["OrderID"]); //取得OrderID欄位值
int ProductID = Convert.ToInt32(dr["ProductID"]); //取得ProductID欄位值
float UnitPrice = Convert.ToSingle(dr["UnitPrice"]); //取得UnitPrice欄位值
Int16 Quantity = Convert.ToInt16(dr["Quantity"]); //取得Quantity欄位值
float Discount = Convert.ToSingle(dr["Discount"]); //取得Discount欄位值
m_DBService.InsertOrderDetails(
OrderID,ProductID,UnitPrice,Quantity,Discount); //資料存入資料庫
}
View.BindOrderDetails(m_DBService.GetOrderDetails()); //命令DBView顯示最新明細
}
==<end>==============
===<反灰>=============
<ModuleInfo AssemblyFile="SCSFDemo.Infrastructure.Layout.dll" />
<ModuleInfo AssemblyFile="SCSFDemo.Infrastructure.Module.dll" />
<ModuleInfo AssemblyFile=" SCSFDemo.HelloWorldModule.dll" />
==<end>==============
==<box>===========
環境建置
● Smart Client Software Factory – April 2008：http://www.microsoft.com/downloads/details.aspx?FamilyID=3BE112CC-B2C1-4215-9330-9C8CF9BCC6FA&displaylang=en。
● Guidance Automation Extensions - February 2008 Release：http://www.microsoft.com/downloads/details.aspx?FamilyID=df79c099-4753-4a59-91e3-5020d9714e4e&DisplayLang=en。
==<end>==============