Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

varchar(n) in C# (and C/C++) Expand / Collapse
Author
Message
Posted Monday, January 21, 2008 6:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2008 7:01 PM
Points: 12, Visits: 24
I have an extended stored procedure in C++ that I am converting to CLR so that it will also work on 64 bit (do not have the original source to just recompile it)

The procedure takes a varchar(n) as one of its inputs. The size of n makes a difference to the output even if the actual data is the same. For example
DECLARE @i VARCHAR(10)
SET @i 'test'
exec xp_test @i, @o OUTPUT

produces a different result to
DECLARE @i VARCHAR(4)
SET @i 'test'
exec xp_test @i, @o OUTPUT

I want to test my functions that are normally called by sql server, in both native C++ and C# with some sample values directly in the code but I can't work out how to pass them varchars of a specific length without the actual data always being that length. For example a varchar(10) with value "test".

How can I mimic a varchar(n) in native C++ and also in C# without always using a string that is actually n characters long?

Thanks
Post #445443
Posted Tuesday, January 22, 2008 7:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 519, Visits: 2,793
In C#, would something like this work? Key is setting the length of the input parameter when you create it. You didn't indicate what type @o is so I assumed it was int.

private int CallProc(int inputParmLength, string inputParmValue)
{
int outputValue = -1;

using (SqlConnection conn = new SqlConnection("[your connection string here]"))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "xp_test";

SqlParameter parm1 = new SqlParameter("@i", SqlDbType.VarChar, inputParmLength);
SqlParameter parm2 = new SqlParameter("@o", SqlDbType.Int);
parm1.Value = inputParmValue;
parm2.Direction = ParameterDirection.Output;

cmd.Parameters.Add(parm1);
cmd.Parameters.Add(parm2);

conn.Open();

int affectedRows = cmd.ExecuteNonQuery();
outputValue = (int)cmd.Parameters["@o"].Value;

conn.Close();
}

return outputValue;
}




Post #445865
Posted Tuesday, January 22, 2008 9:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2008 7:01 PM
Points: 12, Visits: 24
Isn't this only used to get the DBMS to execute the procedure and return the result back to the application?

I was hoping to do something more like just call the function as a normal C# method but pass in parameters that would be the same as SQL Server 2005 passes in when it calls the procedure.

What I was looking for was more along the lines of (from memory)
[SqlProcedure]
public void foo(...)
{
...
}

public void testfoo()
{
foo(...); // send a varchar(n) to foo
}

I was also hoping to do this in C++ as well, where I have even less of a clue how to do it.
Post #445923
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse