1

Change the data type of a table in SQL Server via Visual Studio (C#)

Context

  • I have a web scraper that I use to pull data from various websites each morning (via automated tasks).

  • Each scraper project then creates a separate SQL Server table and populates it with the extracted data.

Problem

  • My problem is that the web scraper will only use nvarchar(4000) as the data type for every column.

  • I need to consume the data from the auto-generated tables in C# code.

  • The column in question (again remembering that this table is auto-generated by the web scraper) is consistent and holds data that looks like "$12.34".

  • When I consume the data I need "$12.34" to be a decimal (or some other numeric format) so that I can do comparisons to other numeric values.

Tried

  • My first thought was to simply do a Convert.ToDecimal(myDecimalStringNum), but that throws a System.FormatException.

  • I also tried Decimal.Parse in case it was a culture issue; same result.

  • I tried programatically (from VB or C#) changing the column data type from nvarchar to decimal, didn't work. Incidentally, I couldn't even change the data type from nvarchar to decimal in SSMS.

  • One thing that DOES work is changing the data type for the column from nvarchar(4000) to money using SSMS. From the money data type I can successfully use Convert.ToDecimal.

Question

The problem, C# doesn't have a "money" data type that I can use to change the column in SQL Server to money. All the web scraping, SQL Server table generation, and the C# program that uses the tables need to run automatically each morning.

I'm 99% of the way there, I just need a solution to this problem that can be added to my automated workflow. As of now that looks like figuring out a way to automate changing the auto-generated table's column data type from nvarchar(4000) to money in SSMS, but I'm open to other suggestions!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bighapa67
  • 11
  • 2
  • 1
    In C# you can use `decimal.Parse("$12.34", NumberStyles.Currency, new CultureInfo("en-us"));`. You should specify NumberStyle and FormatProvider. – Alexander Petrov May 16 '16 at 20:30

2 Answers2

0

If you have control over the SQL to read from the table then you could use convert on the SQL query:

select CONVERT(money, '$123.34')

replacing '$123.34' with your column. Then C# should be able to use that as a Decimal.

OldBoyCoder
  • 876
  • 6
  • 16
0

I know this is an old post and you have figured this out by now, but if you need to do the conversion in C# I would first do a:

myDecimalStringNum = myDecimalStringNum.Replace("$","");

and then do the conversion as you were at first:

var newDecimalStringNum = Convert.ToDecimal(myDecimalStringNum);
Anthony Griggs
  • 1,469
  • 2
  • 17
  • 39