vb.net调用oracle存储过程,VB.net 调用带参数存储过程-.NET教程,VB.Net语言

–1:存储过程返回dataset 的例子:

c# 代码 :通用类

public static int singlestcd(datetime starttime,datetime endtime,int smtp,string jl,string stdcd,string stcdstr, out dataset ds )

{

int ret=0;

ds=null;

oledbconnection constring=new oledbconnection(appraise.constr);

oledbcommand cmd=new oledbcommand(“p_get_stcd_singleappraise_xunqi”,constring);

try

{

oledbdataadapter ada=new oledbdataadapter();

cmd.commandtype=commandtype.storedprocedure;

cmd.parameters.add(“@starttime”, oledbtype.dbtimestamp).value=starttime ;

cmd.parameters.add(“@endtime”, oledbtype.dbtimestamp).value=endtime ;

cmd.parameters.add(“@smtp”, oledbtype.integer).value=smtp ;

cmd.parameters.add(“@jl”, oledbtype.varchar, 50).value=jl ;

//cmd.parameters.add(“@stdcd”, oledbtype.varchar, 50).value=stdcd ;  //评价标准

cmd.parameters.add(“@stcdstr”, oledbtype.varchar, 8000).value=stcdstr ;

system.data.dataset data = new dataset();

ada.selectcommand = cmd;

ada.fill(data,”table”);

ds = data;

ret=ds.tables[0].rows.count;

if ( ret ==-1 )

{

return -1000; //无数据

}

else

{

return ret;

}

}

catch(exception e)

{

//数据库操作发生错误,返回错误代码

system.diagnostics.debug.writeline(e.message);

return -1001;

}

finally

{

constring.close();

cmd.parameters.clear();

}

vb.net 调用:

private sub bindingdatagrid_query()

dim ds as system.data.dataset

zehua.water.appraise.singlestcd(“1999-5-1”, “1999-9-1”, 1, “ⅲ”, “”, “50181300,50185050,50181350,50185100,50185150,50185200,50185250,50181200,50185150,51282300”, ds)

datagrid_query.datasource = ds

datagrid_query.databind()

end sub

–1:存储过程返回数组字符串

rem 执行存储过程,返回一数组

public shared function executesp_toarrary(byval sconnstring as string, byval text_stcd as string, byval text_time as string, byref tableinfo() as string)

dim conn as oledbconnection = new oledbconnection(sconnstring)

redim tableinfo(2)

try

conn.open()

dim command as oledbcommand = new oledbcommand(“p_get_queryvalue”, conn)

command.commandtype = commandtype.storedprocedure

dim sqlparams() as oledbparameter = {new oledbparameter(“@stcd”, text_stcd), new oledbparameter(“@getm”, text_time), new oledbparameter(“@returnvalue”, oledbtype.varchar, 8000), new oledbparameter(“@returncolumncode”, oledbtype.varchar, 8000), new oledbparameter(“@returncolumnname”, oledbtype.varchar, 8000)}

sqlparams(2).direction = parameterdirection.output

sqlparams(3).direction = parameterdirection.output

sqlparams(4).direction = parameterdirection.output

dim i as integer = 0

while i < sqlparams.length

command.parameters.add(sqlparams(i))

i += 1

end while

command.executenonquery()

if sqlparams(2).value.tostring().length > 0 then

tableinfo(0) = sqlparams(2).value.tostring()

else

end if

if sqlparams(3).value.tostring().length > 0 then

tableinfo(1) = sqlparams(3).value.tostring()

else

end if

if sqlparams(4).value.tostring().length > 0 then

tableinfo(2) = sqlparams(4).value.tostring()

else

end if

catch ex as exception

zehua.log.exnotebugs(ex, “siglestcdedit.aspx”) sb.append(ex.message)

finally

conn.close()

end try

end function