C# String Format examples for Double

From http://www.csharp-examples.net/string-format-double/

The following examples show how to format float numbers to string in C#. You can use static method String.Format or instance methods double.ToString and float.ToString.

Digits after decimal point

This example formats double to string with fixed number of decimal places. For two decimal places use pattern „0.00“. If a float number has less decimal places, the rest digits on the right will be zeroes. If it has more decimal places, the number will be rounded.

[C#]

// just two decimal places
String.Format("{0:0.00}", 123.4567);      // "123.46"
String.Format("{0:0.00}", 123.4);         // "123.40"
String.Format("{0:0.00}", 123.0);         // "123.00"

Next example formats double to string with floating number of decimal places. E.g. for maximal two decimal places use pattern „0.##“.

[C#]

// max. two decimal places
String.Format("{0:0.##}", 123.4567);      // "123.46"
String.Format("{0:0.##}", 123.4);         // "123.4"
String.Format("{0:0.##}", 123.0);         // "123"

Digits before decimal point

If you want a float number to have any minimal number of digits before decimal point use N-times zero before decimal point. E.g. pattern „00.0“ formats a float number to string with at least two digits before decimal point and one digit after that.

[C#]

// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567);      // "123.5"
String.Format("{0:00.0}", 23.4567);       // "23.5"
String.Format("{0:00.0}", 3.4567);        // "03.5"
String.Format("{0:00.0}", -3.4567);       // "-03.5"

Thousands separator

To format double to string with use of thousands separator use zero and comma separator before an usual float formatting pattern, e.g. pattern „0,0.0“ formats the number to use thousands separators and to have one decimal place.

[C#]

String.Format("{0:0,0.0}", 12345.67);     // "12,345.7"
String.Format("{0:0,0}", 12345.67);       // "12,346"

Zero

Float numbers between zero and one can be formatted in two ways, with or without leading zero before decimal point. To format number without a leading zero use # before point. For example „#.0“ formats number to have one decimal place and zero to N digits before decimal point (e.g. „.5“ or „123.5“).

Following code shows how can be formatted a zero (of double type).

[C#]

String.Format("{0:0.0}", 0.0);            // "0.0"
String.Format("{0:0.#}", 0.0);            // "0"
String.Format("{0:#.0}", 0.0);            // ".0"
String.Format("{0:#.#}", 0.0);            // ""

Align numbers with spaces

To align float number to the right use comma „,“ option before the colon. Type comma followed by a number of spaces, e.g. „0,10:0.0“ (this can be used only in String.Format method, not in double.ToString method). To align numbers to the left use negative number of spaces.

[C#]

String.Format("{0,10:0.0}", 123.4567);    // "     123.5"
String.Format("{0,-10:0.0}", 123.4567);   // "123.5     "
String.Format("{0,10:0.0}", -123.4567);   // "    -123.5"
String.Format("{0,-10:0.0}", -123.4567);  // "-123.5    "

Custom formatting for negative numbers and zero

If you need to use custom format for negative float numbers or zero, use semicolon separator;“ to split pattern to three sections. The first section formats positive numbers, the second section formats negative numbers and the third section formats zero. If you omit the last section, zero will be formatted using the first section.

[C#]

String.Format("{0:0.00;minus 0.00;zero}", 123.4567);   // "123.46"
String.Format("{0:0.00;minus 0.00;zero}", -123.4567);  // "minus 123.46"
String.Format("{0:0.00;minus 0.00;zero}", 0.0);        // "zero"

Some funny examples

As you could notice in the previous example, you can put any text into formatting pattern, e.g. before an usual pattern „my text 0.0“. You can even put any text between the zeroes, e.g. „0aaa.bbb0“.

[C#]

String.Format("{0:my number is 0.0}", 12.3);   // "my number is 12.3"
String.Format("{0:0aaa.bbb0}", 12.3);          // "12aaa.bbb3"
Posted in .NET | Tagged , | Leave a comment

Reseed an Identity column in SQL 2008

In SQL 2000/2005, I used to be able to change the seed of an identity field in table designer. It will just prompt a message saying this table and a few other table (if having FK on) will be modified. However somehow in SQL 2008, I can not do that any more. The table seems can not be dropped.

I found the workaround:

dbcc checkident (my_table, reseed, 1052)

This will raise the next identity value in my_table to be 1052. It does NOT change the column definition but change the run-time seed instead.

You have to make sure the new seed value is greater than all existing values. Otherwise you will get UNIQUE violation errors.

Also to temporarily allow inserting values into the identity column, you can do this:

set identity_insert mytable on

and this will turn it off

set identity_insert mytable off

But you can only run it on one table at a time.

P.S. I figured out the reason why SQL 2008 will report “Saving changes is not permitted. ..” error when trying to change the identity properties. By default, in SQL 2008 Management Studio, Tools->Options->Designers->Table and Database Designer,  “Prevent saving changes that require table re-creation” is checked. Changing identify field will internally drop and re-create the table. Just uncheck it and you can change it in UI.

Posted in SQL | Tagged | Leave a comment

Scan stored procedures source script

Want to remove an obsolete table column but don’t want to break any views that may still reference it?

SELECT v.NAME, c.[text] FROM syscomments c, sys.VIEWS v WHERE v.OBJECT_ID=c.id
AND c.TEXT LIKE ‘%MY FIELD%’

The above is just for the view, this query will give you all the objects (views, stored procedures, functions etc)

SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ‘%table or field name%’

(xtype column: P for stored procedures; V for views; FN for functions)

Posted in SQL | Tagged | Leave a comment

Email notification in MS SQL 2005/2008

A few undocumented steps, on top of the obvious ones, before being able to send email notifications

  • Database Mail -> Configuration -> Manage profile security, make sure there is at least one public and default profile.
  • SQL Server Agent -> Properties -> Alert System, make sure to check “Enable mail profile” and choose correct mail system and profile. Test button will be disabled if “Database Mail” is selected.
  • Create operator
  • Restart SQL Agent
Posted in SQL | Leave a comment

Retire an user error message

 

delete from sysdba.usersecfunctions where userid =’U6UJ9A00001M’
update sysdba.account set accountmanagerid =’ADMIN’ where accountmanagerid =’U6UJ9A00001M’
update sysdba.contact set accountmanagerid =’ADMIN’ where accountmanagerid =’U6UJ9A00001M’
update sysdba.opportunity set accountmanagerid =’ADMIN’ where accountmanagerid =’U6UJ9A00001M’
UPDATE sysdba.ACCOUNT SET SECCODEID =’ADMIN’ WHERE SECCODEID =’U6UJ9A00001M’
UPDATE sysdba.CONTACT SET SECCODEID =’ADMIN’ WHERE SECCODEID =’U6UJ9A00001M’

SELECT * FROM sysdba.CONTACT C WHERE C.SECCODEID = ‘N6UJ9A00001N’
— then change the account manager in account level to someone else

Or start SLXProfiler to check the last query.

Posted in SalesLogix | Leave a comment

Why EntitySet not auto-generated by LINQ-to-SQL?

It turns out the the child table must have a primary key. If not set in database level, that can be set in the *dbml file. Multiple-field primary key works too.

Posted in ASP.NET | Tagged , | Leave a comment

Client-side validator not firing in FF, only in IE

Symptom:

For a legacy web app converted from older version of ASP.NET, the client javascript form validation only fires in IE, not in FF.

Fix:

Change this line in web.config

<xhtmlConformance mode="Legacy"/>

to:

<xhtmlConformance mode="Transitional"/>
Posted in ASP.NET | Leave a comment

Tips on Lookups

  • To have the lookups show all record automatically on initial launch, put a always true condition in LookupRestrictField, LookupRestrictOp, and LookupRestrictValue. ie. OWNERID <> 1234
  • A lookup can be included in the bundle
Posted in SalesLogix | Leave a comment

Full text search time out problem

This is a MS SQL 2005 server. The full text search seems time out after some idle time. Interestingly, this time out only happens to ASP.NET connections. Query Analyzer never timed out no matter what login name used.  This MS KB link explains it:

http://support.microsoft.com/?scid=kb%3Ben-us%3B915850&x=15&y=8

However, my server DOES have internet conneciton. Nevertheless, this command still fixed the problem:

sp_fulltext_service 'verify_signature', 0;

Even Microsoft says there are securities concerns but it doesn’t seem there is an option. To check the current configuration value without changing it, run

sp_fulltext_service 'verify_signature'

I had to restart SQL service for it to take effect.

Posted in SQL | Tagged | Leave a comment

Crosstab Query (Rows to Columns)

First,  columns to rows are very simple with UNION:

SELECT col1 as [new column]  FROM table1
UNION
SELECT col2 FROM table1
UNION
SELECT col3 FROM table1

This is very helpful when I need to run aggregate function on multiple columns:

SELECT MAX([new column]) as [Max Date] FROM
(SELECT col1 as [new column]  FROM table1
UNION
SELECT col2 FROM table1
UNION
SELECT col3 FROM table1) as temp

Rows to columns are a little tricky, depends on the requirement:

A few examples:

  • A report to run aggregate function on selected rows in a table

SELECT MAX(product_id) as ID, /* for joining purpose*/
SUM( CASE WHEN category = ‘drink’  THEN Sales ELSE null END ) as [Drink Total],
SUM( CASE WHEN category = ‘food’  THEN Sales ELSE null END ) as [Food Total]
FROM sales_table

  • A report to list repeating info from a table in horizontal format

Step 1: get a table with unique identifier for each repeating row:

SELECT product_id,  sales_date,  ROW_NUMBER() OVER (ORDER BY sales_date) as sort
FROM sales_table
WHERE product_id = 222 /* this is very important. otherwise ROW_NUMBER() will not have correct value */

Step 2: getting each one by its unique # into horizontal rows, by using aggregate functions:

SELECT MAX(product_id) as ID, /* has to use a aggregate function to get one value back */
MAX( CASE sort WHEN 1 THEN sales_date ELSE null END ) as [Sales Date 1],
MAX( CASE sort WHEN 2 THEN sales_date ELSE null END ) as [Sales Date 2],
MAX( CASE sort WHEN 3 THEN sales_date ELSE null END ) as [Sales Date 3]
FROM
(SELECT product_id,  sales_date,  ROW_NUMBER() OVER (ORDER BY sales_date) as sort
FROM sales_table WHERE product_id = 222) as temp /* use above table in subquery */

Step 3: since we have to take the product ID in the subquery, we need to convert above query into a function:

CREATE FUNCTION dbo.GetSalesDate( @product_id int, @index int )
RETURNS DATETIME
AS
BEGIN
DECLARE @ret DATETIME
SELECT @ret = t.sales_date
FROM
(SELECT sales_date, ROW_NUMBER() OVER (ORDER BY sales_date) as sort FROM sales_table WHERE product_id=@product_id) as t
WHERE sort = @index
RETURN @ret
END
GO

Then you can run:

SELECT p.*, dbo.GetSalesDate( product_id, 1), dbo.GetSalesDate( product_id, 2), dbo.GetSalesDate( product_id, 3)
FROM product_table

Unfortunately this is a very expensive function call.

The above query still hard coded the number of columns. Here is a post on dynamic crosstab.

Posted in SQL | Tagged | Leave a comment