[C#][WebForm] 利用ADO.NET呼叫stored procedure填入Word做列印

2017-12-21

4974
0

Novacode

ADO.NET：SqldataAdapter、SqlDataReader

SqlDataSource

SqldataAdapter、SqlDataReade、SqlDataSource只有在向Database讀取資料的時候不一樣

列印的部分沒有差別

準備作業：

Web.Config中增加HTML連線字串

</appSettings>

</connectionString>

</configuration>

<--!設定compilation debug會將偵錯符號插入編譯過得頁面，基於效能，開發期間才設定為true-->

SqldataAdapter：

using Novacode;
using System.Web.Configuration;

/*
SqldataAdapter抓stored procedure填入Word做列印
在用using System.Web.Configuration時,需要手動加入"參考"dll
@期別 varchar='201708'
*/

string 期別 = "201708";

string filePath = @"D:\server上的位置";
#if DEBUG
	filePath = @"local上的位置";
#endif

//====SqlDataAdapter呼叫Stored Procedure讀取資料====
SqlConnection conn = new SqlConnection(Web.ConfigurationManager.ConnectionString["conn_str"].Connection); 
SqlDataAdapter adapter = new SqlDataAdapter("預存程式", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
adapter.SelectCommand.Parameters.Add("@期別", SqlDbType.Varchar).Value = 期別;

DataTable dt = new DataTable();
adapter.Fill(dt);

using(DocX PrintDocx = DocX.Load(filePath))
{
	//抓Word檔中的Table
	Novacode.Table t = PrintDocx.Tables[0];
	t.Alignment =Alignment.center; //設定Table置中

	//寫入Title
	Paragraph p標題 = t.Rows[0].Cells[0].Paragraphs.First();
	p標題.Remove(false); //把格式清乾淨
	p標題.Append((Convert.ToInt16("期別")-191100).ToString()).FontSize(14).Bold();
	p標題.Alignment = Alignment.center;

	//寫入Rows
	foreach (DataRow row in dt.Rows)
	{
		Row NewRow = t.InsertRow();//Add a new row in the table
		Paragraph p欄位1 = NewRow.Cells[0].Paragraphs.First().Append(row[0].ToString());
		Paragraph p欄位2 = NewRow.Cells[1].Paragraphs.First().Append(row[1].ToString());
		Paragraph p欄位3 = NewRow.Cells[2].Paragraphs.First().Append(row[2].ToString());
		
		//美觀調整
		NewRow.Cells[0].VerticalAlignment = VerticalAlignment.Center;	//垂直置中
		NewRow.Cells[1].VerticalAlignment = VerticalAlignment.Center;
		NewRow.Cells[2].VerticalAlignment = VerticalAlignment.Center;

		p欄位1.Alignment = Alignment.center;
		p欄位2.Alignment = Alignment.center;
		p欄位3.Alignment = Alignment.center;

		t.Rows.Add(NewRow);
	}

	//將文件設定為標楷體
	PrintDocx.Paragraphs.First().Font(new FontFamily("DFKai-SB"));	//DFKai-SB為標楷體的英文代號
	
	//另存新檔
	string 暫存檔名稱 = Server.MapPath("~/Temp/" + "檔案名稱.docx");
	PrintDocx.SaveAs(暫存檔名稱);
	Response.Redirect("FileDownloader.ashx?File=/Temp/" + 暫存檔名稱.Split('\\').Last());
}

SqlDataReader：

using System.Data;
using System.Data.SqlClient;

string 期別 = "201708";
string 接資料 = null;

//====SqlDataReader====
SqlConnection conn = WebConfigurationManager.ConnectionStrings["conn_str"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "預存程式";
cmd.CommandType = CommandType.StoredProcedure;
try
{
	conn.open();
	cmd.Parameters.Add("@預存參數", SqlDbType.Varchar).Value = 期別;
	SqlDataReader dr = cmd.ExecuteReader();
	while (dr.Reader())
	{
		接資料 = dr[0].ToString();
	}
}
catch (Exception ex)
{
	StringHandle.ShowMessage(this, ex.Message);
}
finally
{
	if(conn.State != ConnectionState.Closed) conn.Close();
}

SqlDataSource：

using System.Data;
using System.Data.SqlClient;

string 期別 = "201708";
string 接資料 = null;

//====SqlDataSource====
SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = WebConfigurationManager.ConnectionStrings["conn_str"].ConnectionString;
sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
sds.SelectCommand = "預存程式";
sds.SelectParameters.Add("預存參數",期別);

//把SqlDataSource抓到的資料放到DataTable
DataSourceSelectArguments args = new DataSourceSelectArguments();
DataView view = (DataView)sds.Select(args);
DataTable table = view.ToTable();

//順便連接GridView
GV.DataSource = table; //其實GV.DataSource = sds 就可以了~
GV.DataBind();
sds.SelectParameters.Clear();

回首頁