Friday, August 13, 2010

Handle Fatal Error in SQL using DOT NET Try Catch block

Stored Procedure with fatal error

tempPK has one column with datatype = int
==============================================
CREATE PROCEDURE [dbo].[spError]
AS
BEGIN

Begin Tran
--Select 1/0

--execute('insert into tempPK select ''s''')
execute('insert into tempPK select ''fg''')
execute('insert into tempPK select 4')
Commit Tran
print 'Success'
END

======================================================
Dot Net Calling Method
try
{
SqlConnection sCon = new SqlConnection();
sCon.ConnectionString = "Database=xyz;Server=10.62;User Id=sa;Password=#12#";
sCon.Open();
SqlCommand sCom = new SqlCommand();
sCom.Connection = sCon;
sCom.CommandText = "Exec spError";
sCom.ExecuteNonQuery();
}
catch (Exception ex)
{
if(ex.Message.Substring(ex.Message.Length-7) == "Success")
MessageBox.Show("No Fatal Error");
else
MessageBox.Show("Fatal Error");
}
======================================================

Non Fatal Errors in SQL in DOT NET CATCH block

Stored Procedure having errors within
=======================
CREATE PROCEDURE spError
AS
BEGIN

Begin Tran
Select 1/0
execute('insert into te mpPK select 1')
execute('insert into tempPK select 3')
Commit Tran
END
GO
=======================
Application Code
try
{
SqlConnection sCon = new SqlConnection();
sCon.ConnectionString = "Database=xyz;Server=11.62;User Id=sa;Password=klk";
sCon.Open();
SqlCommand sCom = new SqlCommand();
sCom.Connection = sCon;
sCom.CommandText = "Exec spError";
sCom.ExecuteNonQuery();
}
catch (Exception ex)
{
textBox1.Text = ex.Message;
}
=======================
Errors captured
Divide by zero error encountered.
Incorrect syntax near 'mpPK'.

Monday, August 9, 2010

Method Overloading

1) Class can not be declared as Private.

Elements defined in a namespace cannot be explicitly declared as private, protected, or protected internal

namespace OOPS
{
private class A
{
}
}

By default it is Public

2) Method must have a return type
class A
{
public M()
{
}
}

3) Type 'OOPS.A' already defines a member called 'M' with the same parameter types

class A
{
public void M()
{
}
public void M()
{
}
}

4) True Method Overload
class A
{
public void M(int i)
{
}
public void M()
{
}
}

5) Type 'OOPS.A' already defines a member called 'M' with the same parameter types

class A
{
public void M()
{
}
public string M()
{
return "HI";
}
}

6) Method Overload

class A
{
public void M()
{
}
public string M(int i)
{
return "HI";
}
}





7) Method Overload
public void M(int i)
{
}
private string M()
{
return "HI";
}

8) Method Overload
class A
{
public void M(Int16 i)
{
}
public void M(Int32 i)
{
}
public void M(Int64 i)
{
}
}

9) Type 'OOPS.A' already defines a member called 'M' with the same parameter types

public void M(int i)
{
}
public void M(Int32 i)
{
}
10)Method Overload
class A
{
public void M(int i)
{
}
public void M(double i)
{
}
}

Conclusion: Access modifier and return type of function does not matter in Method Overloading. What matter is
1) Number of function parameters.
2) Datatype of parameters

If same parameters then overloading is not possible.