Calling an SQL function in my C# -

Calling an SQL function in my C# -

i have function created in ssms.

this create function used generate unique ids members of department. thought concatenate whatever returned function value generated database.

create function [dbo].[ufnleadingnumberofzeroes]( @value int, @numberofzeroes int ) returns varchar(max) schemabinding begin declare @returnvalue varchar(max); set @returnvalue = convert(varchar(max), @value); set @returnvalue = replicate('0', @numberofzeroes - datalength(@returnvalue)) + @returnvalue; homecoming (@returnvalue); end

i wish phone call in c# code. have already:

private void memberid(object sender, eventargs e) { sqlconnection memberid = new sqlconnection(@"data source=epraise-pc;initial catalog=master;integrated security=true"); using (sqlcommand comm = new sqlcommand("dbo.ufnleadingnumberofzeroes", memberid)) { comm.commandtype = commandtype.storedprocedure; sqlparameter p1 = new sqlparameter("@value", sqldbtype.int); // can phone call homecoming value parameter anything, .e.g. "@result". sqlparameter p2 = new sqlparameter("@numberofzeroes", sqldbtype.int); p1.direction = parameterdirection.input; p2.direction = parameterdirection.returnvalue; p1.value = deptcmbbox; comm.parameters.add(p1); comm.parameters.add(p2); memberid.open(); comm.executenonquery(); } }

what wrong code please?

try selecting function , using executescalar instead.

private void memberid(object sender, eventargs e) { // create connection using (sqlconnection conn = new sqlconnection(@"data source=epraise-pc;initial catalog=master;integrated security=true")) { // create command sqlcommand cmd = new sqlcommand("select dbo.ufnleadingnumberofzeroes(@value, @numberofzeroes)", conn) // create parameters command sqlparameter value = new sqlparameter("@value", sqldbtype.int); sqlparameter numberofzeroes = new sqlparameter("@numberofzeroes", sqldbtype.int); // initialise parameters' values value.value = deptcmbbox; numberofzeroes.value = 0; // add together parameters command cmd.parameters.add(value); cmd.parameters.add(numberofzeroes); // open connection conn.open(); // execute command , retrieve result string str = cmd.executescalar(); } }

c# sql