c# - SQL Server and how to handle the type of error without conflict -

c# - SQL Server and how to handle the type of error without conflict -

i utilize sql server 2008 , asp.net c# , have stored procedure returns calculated rows.

i want check parameters values before doing process , want homecoming custom error message if parameters values wrong.

in ui side, must homecoming different text , utilize different window according error type. example:

errors when parameters values wrong. non handled errors.

i utilize kind of sp in database, tried utilize state argument , severity argument identify type of error. have conflicts non handled error returning same state number parameter error, , wrong window comes up. if utilize severity level, guess sp homecoming error same severity number in cases.

i give quick illustration have improve view:

create procedure dbo.getdata @date1 date, @date2 date begin -- check parameters if @date2 < @date1 begin raiserror(n'date 2 cannot less date 1', 16, 2); -- <= here state 2 homecoming end -- process here... declare @table1 table ( name nvarchar(50) not null) -- supposing have insert row null value insert @table1 values (null); -- thus, query returns error state 2 well! --msg 515, level 16, state 2, procedure getdata, line 21 --cannot insert value null column 'name', table '@table1'; column not allow nulls. insert fails. select 'record 1'; select 'record 2'; end

from c#:

list<string> info = new list<string>(); protected void button1_click(object sender, eventargs e) { string errormessage = string.empty; bool iserrorfromchecking = false; if (getvalues(configurationmanager.connectionstrings["testconnectionstring"].connectionstring, new datetime(2011, 01, 01), new datetime(2011, 02, 01), ref iserrorfromchecking, ref errormessage)) { label1.text = data[0].tostring(); return; } if (iserrorfromchecking) { label1.text = errormessage; return; } label1.text = string.format("internal error: {0}.", errormessage); } private bool getvalues(string connectionstring, datetime date1, datetime date2, ref bool iserrorfromchecking, ref string errormessage) { info = new list<string>(); seek { using (sqlconnection sqlcon = new sqlconnection(connectionstring)) { sqlcon.open(); sqlcommand sqlcmd = new sqlcommand(); sqlcmd.connection = sqlcon; sqlcmd.commandtype = commandtype.storedprocedure; sqlcmd.commandtext = "dbo.getdata"; sqlcmd.parameters.addwithvalue("date1", date1); sqlcmd.parameters.addwithvalue("date2", date2); sqldatareader reader = sqlcmd.executereader(); while (reader.read()) { data.add(reader[0].tostring()); } reader.close(); sqlcon.close(); } } grab (sqlexception ex) { if (ex.state == 2) { iserrorfromchecking = true; errormessage = ex.message; homecoming false; } iserrorfromchecking = false; errormessage = ex.message; homecoming false; } grab (exception ex) { iserrorfromchecking = false; errormessage = ex.message; homecoming false; } homecoming true; }

in code above, dates right programme not homecoming message “internal error: …” although sp had error.

i have ideas, want know point of view , best way it.

thanks.

simply split logic in two. mean have 'parameter check' logic in 1 stored procedure , rest in sp. in 'check' sp utilize output parameters codes or error text want returned.

the sec sp can phone call sp first ensure it's not getting sent bad data.

the c# should phone call 'check' sp first , see returned.

alternative utilize output parameter error status , homecoming empty dataset.

create procedure sptest (@param1 int, @errortext varchar(50) output ) begin set @errortext = null if @param1 = 1 begin set @errortext = 'i cant except 1 parameter' select null queryplaceholdercol end else begin select 3.141 queryplaceholdercol end end

to test in t-sql:

declare @returnederror varchar(50) exec sptest 1,@returnederror output select case when @returnederror null 'worked fine' else @returnederror end outputs exec sptest 1423,@returnederror output select case when @returnederror null 'worked fine' else @returnederror end outputs

c# sql-server-2008 raiserror