Converting scalar function to c#

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi I was trying to convert a scalar function in db to c#.

In db function was like this:This converts frequency to date for e.g
if frequency is monthly : 'M' and input is "2005 M1" (which is [Date] column), then it converts to 01-01-2005

SQL

CREATE FUNCTION [dbo].[funcFreqToDate]
(
    @Freq varchar(50),
    @InputVal varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
    .
    IF @Freq = 'D' 
    	RETURN @InputVal;
    IF @Freq = 'A'
    	RETURN @InputVal +'-01-01';
    
    
    DECLARE @FreqToDate varchar(255)
    DECLARE @FreqInfo varchar(255);
    DECLARE @YearInfo varchar(255);
    DECLARE @FreqPos int;
    
    SET @FreqPos = CHARINDEX(@Freq,@InputVal);
    IF @FreqPos > 0
    	SET @FreqInfo = SUBSTRING(@InputVal,(@FreqPos+1),LEN(@InputVal));
    	SET @YearInfo = SUBSTRING(@InputVal,1,(@FreqPos-1))
    
    SET @FreqToDate = 
    	CASE 
    
    		WHEN @Freq='M' THEN @YearInfo +'-'+ cast((cast(@FreqInfo AS INT)) AS varchar(50)) + '-01'
    		ELSE @InputVal
    	END
    
    
    -- Return the result of the function
    RETURN @FreqToDate

It was called in c# as

C#

query = @"UPDATE " + srcDataTbl + @"
                SET [DateTransfrm] = CAST([dbo].funcFreqToDate([Frequency],[Date]) AS DATE)";

Please remember [Date] is varchar
[DateTransfrm] is of type Date

No i want to remove scalar function completely from db and move it into query in c#.

Can anyone help me write query for same in c#.
Thanks

Posted 4-May-15 21:16pm

glued-to-code

Updated 4-May-15 23:22pm

Tomas Takac

v2

Add a Solution

Comments

OriginalGriff 5-May-15 3:30am

What have you tried?
Where are you stuck?
What help do you need?

glued-to-code 5-May-15 22:45pm

I have tried moving scalar function logic completely to query. But i was not successful.

Sinisa Hajnal 5-May-15 3:37am

And why would you move the function that works and lose time and potentially introduce the bugs into something that works? Instead move the call in C# to stored procedure, send source table, frequency and date as parameters.

Note that, depending on your srcDataTbl variable source, you're introducing potential SQL injection by directly concatenating into the query.

glued-to-code 5-May-15 22:47pm

Ok, But recently we moved lot of such functions to c#

Tomas Takac 5-May-15 5:24am

Do you realize that this scalar function is invoked for every row in "srcDataTbl"? You would have to read the whole table into memory set "DateTransfrm" row by row and then write back to the database.

glued-to-code 5-May-15 22:50pm

yes that's the problem, in-order to move to c#, i will have to read complete frequency and date and then transform and update it

Tomas Takac 6-May-15 3:16am

And you really want to go down that path? In other comment you said you moved "a lot of such functions to c# recently" so you already must have a solution. Am I missing something?

Sinisa Hajnal 6-May-15 6:20am

This moving is bad idea...databases are created with the idea of efficiently working with big datasets. Object Oriented Programming languages...not so much. They model the business domain, but they are not made with the idea of looping through gazillion rows just to update single field.

You could do it by adding computed column into the datatable before calling dataAdapter.Fill method, but I still think it should be done in the database.

You didn't answer WHY you move them, just that you moved "lot" of them.

glued-to-code 9-May-15 23:47pm

We usually do a repository upgrade everytime there is a change in database.
For small functionality such as this,we have make db changes, as we have a procedure to upgrade version whenever we make changes in backend.
On moving to c# this will prevent our repository upgrades everytime we do such small modifications.

Sinisa Hajnal 11-May-15 4:21am

I understand. And still think it is a bad idea...you'll spend some time transferring the logic suited to db engine into the code. You'll spend time testing. You'll introduce bugs even with testing...all that time spent could be better spent doing new stuff even accounting for occasional upgrade procedure time.

Esp. if you actually make things worse by calling the function in the C# loop (for each row that needs updates) or making string concatenation code (that could be done within the function or SP).

As for changing versions, you could accumulate several small changes before changing version of the program, couldn't you?

This is just my opinion, good luck coding. For concrete advice see my comment with computed column.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)