[.NET]設定物件Binding的格式

2012-11-07

4483
0
.NET

前陣子因為資料表存放金額欄位的資料格式從float改成money([SQL]資料庫使用FLOAT欄位來記錄金額對嗎?)，結果程式執行起來後，那些變成欄位居然都出現了小數點4位的0，如下，

因為原本的程式中並沒有特別去控制數值的格式，所以當資料庫中是money格式，Bind到物件的呈現出來就會到小數4位。如下，

string queryString = @"SELECT *, UnitPrice * Quantity * 10 AS TotalPrice, GETDATE() as CurrDate FROM [dbo].[Order Details]";
using (SqlConnection conn = new SqlConnection(@"Server=.;Database=NORTHWND;Trusted_Connection=True;"))
{
	try
	{
		conn.Open();
		SqlDataAdapter da = new SqlDataAdapter(queryString, conn);
		DataTable order_Details = new DataTable();
		da.Fill(order_Details);
		bs.DataSource = order_Details;
		dataGridView1.DataBindings.Clear();
		dataGridView1.DataSource = bs;

		lblOrderID.DataBindings.Clear();
		lblOrderID.DataBindings.Add("Text", bs, "OrderID");

		lblProductId.DataBindings.Clear();
		lblProductId.DataBindings.Add("Text", bs, "ProductID");

		lblUnitPrice.DataBindings.Clear();
		lblUnitPrice.DataBindings.Add("Text", bs, "UnitPrice");

		txtTotalPrice.DataBindings.Clear();
		txtTotalPrice.DataBindings.Add("Text", bs, "TotalPrice");
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.ToString());
	}
	finally
	{

		conn.Close();
	}
}

跟小章討論了一下，GridView要設定DataGridViewColumn的DefaultCellStyle.Format屬性(資料庫中是money到Ado.NET中是decimal)。

//因為money取出來的值會有小數4位，所以要設定format
string myGridMoneyFormat = @"#,##0.####";

//處理GridView的欄位
foreach(DataGridViewColumn dc in dataGridView1.Columns){
	//判斷值為decmial就要套用格式
	if (dc.ValueType == typeof(decimal))
		dc.DefaultCellStyle.Format = myGridMoneyFormat;
}

而Label, TextBox的話，小弟發現，可以設定CurrencyManager.Bindings中的Binding物件的FormatString屬性，並設定FormattingEnabled屬性為true。

//處理TextBox欄位，判斷Binds中是否有為decmial就要套用格式
string myTextBoxMoneyFormat = @"###0.####";
foreach (Binding bd in bs.CurrencyManager.Bindings)
{
	string columnName = bd.BindingMemberInfo.BindingField;
	if (order_Details.Columns[columnName].DataType == typeof(decimal))
	{
		bd.FormatString = myTextBoxMoneyFormat;
		bd.FormattingEnabled = true;
	}
}

而這些格式設定可定義在config中，可依使用者的需求來調整它。

string queryString = @"SELECT *, UnitPrice * Quantity * 10 AS TotalPrice, GETDATE() as CurrDate FROM [dbo].[Order Details]";
using (SqlConnection conn = new SqlConnection(@"Server=.;Database=NORTHWND;Trusted_Connection=True;"))
{
	try
	{
		conn.Open();
		SqlDataAdapter da = new SqlDataAdapter(queryString, conn);
		DataTable order_Details = new DataTable();
		da.Fill(order_Details);
		bs.DataSource = order_Details;
		dataGridView1.DataBindings.Clear();
		dataGridView1.DataSource = bs;

		lblOrderID.DataBindings.Clear();
		lblOrderID.DataBindings.Add("Text", bs, "OrderID");

		lblProductId.DataBindings.Clear();
		lblProductId.DataBindings.Add("Text", bs, "ProductID");

		lblUnitPrice.DataBindings.Clear();
		lblUnitPrice.DataBindings.Add("Text", bs, "UnitPrice");

		txtTotalPrice.DataBindings.Clear();
		txtTotalPrice.DataBindings.Add("Text", bs, "TotalPrice");

		//因為money取出來的值會有小數4位，所以要設定format
		string myGridMoneyFormat = System.Configuration.ConfigurationManager.AppSettings["GridMoneyFormat"];

		//處理GridView的欄位
		foreach(DataGridViewColumn dc in dataGridView1.Columns){
			//判斷值為decmial就要套用格式
			if (dc.ValueType == typeof(decimal))
				dc.DefaultCellStyle.Format = myGridMoneyFormat;
		}

		//處理TextBox欄位，判斷Binds中是否有為decmial就要套用格式
		string myTextBoxMoneyFormat = System.Configuration.ConfigurationManager.AppSettings["TextBoxMoneyFormat"];
		foreach (Binding bd in bs.CurrencyManager.Bindings)
		{
			string columnName = bd.BindingMemberInfo.BindingField;
			if (order_Details.Columns[columnName].DataType == typeof(decimal))
			{
				bd.FormatString = myTextBoxMoneyFormat;
				bd.FormattingEnabled = true;
			}
		}
		 
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.ToString());
	}
	finally
	{
		
		conn.Close();
	}
}