Convert int to string from SQL function c# Sql2012

Question

0.00/5 (No votes)

See more:

I have a function in sql db that I need to use in my c# windows app. I got partial code working to get an investigation number format of '2015SP0001'. Fiscal yr begins 7 of each yr. The CaseType gets the 1st 2 char"SP" to complete number.

Function (I did not create):

SQL

ALTER FUNCTION [dbo].[fGetNewInvestigationNum] 
(
	-- Add the parameters for the function here
	@Opened as datetime,
	@Type as int
)
RETURNS varchar(12)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @CType char(2)
	DECLARE @NewID varchar(12)

	-- Add the T-SQL statements to compute the return value here
	SELECT @CType = left(CaseType,2)
		FROM CaseTypeList
		WHERE CaseTypeID = @Type
	SELECT @NewID = Year(@Opened) + @CType + dbo.fGetNextNum(@CType,@Opened) 

	-- Return the result of the function
	RETURN @NewID

My DataAccess method:

C#

public static string GetNewInvestigationNumber(DateTime openedDate, int caseType)
			{
			  Database db = DatabaseFactory.CreateDatabase();
			  DbCommand dbCommand = db.GetStoredProcCommand("DL_NewInvestigationNumber");
			  db.AddInParameter(dbCommand, "OpenedDate", DbType.DateTime, openedDate);
              db.AddInParameter(dbCommand, "CaseType", DbType.Int32, caseType);

			  return (string)db.ExecuteScalar(dbCommand);
			}

* Need help here-convert int CaseType to string to retrieve the 'SP' in If, Else. My c# code in BLL layer:

C#

private void GetNewInvestigationNumber()
        {
            int openedDate;
            int caseType;
            
            if (OpenedDate.Month >= 7)
                openedDate = OpenedDate.Year + 1;
            else
                openedDate = OpenedDate.Year;

            InvestigationNum = CaseInvestigationDB.GetNewInvestigationNumber(openedDate, caseType);

        }

Posted 31-Dec-14 10:24am

Sam 9100

Add a Solution

Comments

George Jonsson 1-Jan-15 1:43am

Is this the value you want to parse? '2015SP0001'
What does the different parts of the string mean and how do you want to split them?
Like
2015
SP
0001

Sam 9100 2-Jan-15 11:45am

Thanks George. 2015 is the fiscal year start. SP is the case type name "SP-Complex Crimes". But I need to get the SP portion. Database table was set up like that so I have to work with whats there. The '0001' are syst-generated consecutive #s for when a case is entered, it should increment for every new case.

2 solutions

Solution 2

If CaseType always contains a character value then what is the need to declare it as int ?
Try your code using string data type

public static string GetNewInvestigationNumber(DateTime openedDate, string caseType)
{
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbCommand = db.GetStoredProcCommand("DL_NewInvestigationNumber");
    db.AddInParameter(dbCommand, "OpenedDate", DbType.DateTime, openedDate);
    db.AddInParameter(dbCommand, "CaseType", DbType.varchar, caseType );
    
    return (string)db.ExecuteScalar(dbCommand);
}
 

private void GetNewInvestigationNumber()
{
    int openedDate;
    string caseType;  // use string data type
    
    if (OpenedDate.Month >= 7)
        openedDate = OpenedDate.Year + 1;
    else
        openedDate = OpenedDate.Year;
 
    InvestigationNum = CaseInvestigationDB.GetNewInvestigationNumber(openedDate, caseType);
 
}

Posted 1-Jan-15 22:54pm

Sumit Jawale

Updated 1-Jan-15 22:56pm

v2

Comments

Sam 9100 2-Jan-15 11:52am

Thank you for all your help everyone. I will test it today

Sam 9100 2-Jan-15 13:26pm

The String works for the caseType. Thankyou. Now I cannot implicitly convert type int to System.dateTime for openedDate. I am trying to calculate the start of fiscal yr(OpenedDate) each year on July 1. Is there a better way to do this? The original way this is done is the NewInvestigationNumber is an insert stored procedure using the function above. Maybe there is a better way to call the stored proc and just use that so no double entry is done in the database by 2 different users simultaneously. Any help is appreciated.

Add a Solution

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)

mudgilsks · Accepted Answer · 2014-12-31T23:06:00

hi,

your problem to convert int to string,or you want to pass the CaseType as string

try this

C#

public static string GetNewInvestigationNumber(DateTime openedDate, int caseType)
{
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbCommand = db.GetStoredProcCommand("DL_NewInvestigationNumber");
    db.AddInParameter(dbCommand, "OpenedDate", DbType.DateTime, openedDate);
    db.AddInParameter(dbCommand, "CaseType", DbType.varchar, '" + caseType + "');
    
    return (string)db.ExecuteScalar(dbCommand);
}


private void GetNewInvestigationNumber()
{
    int openedDate;
    int caseType;
    
    if (OpenedDate.Month >= 7)
        openedDate = OpenedDate.Year + 1;
    else
        openedDate = OpenedDate.Year;

    InvestigationNum = CaseInvestigationDB.GetNewInvestigationNumber(openedDate, caseType);

}