Checking Connection Working or not by using a Class [Resolved]

Posted by Akiii under C# on 3/16/2011 | Points: 10 | Views : 1774 | Status : [Member] | Replies : 21
I have a class on a class file known as connectionclass.cs, the code is below :-

public class connectionclass
{
public void connectionmethod()
{
string connstr = "Data Source=Akiii-PC;Initial Catalog=rgdb;";
SqlConnection connobj = new SqlConnection(connstr);

try
{
connobj.Open();
Console.WriteLine("connection opened");
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connobj.Close();
Console.WriteLine("connection closed");
}
}

}

in the pageload method on the default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)
{
connectionclass obj = new connectionclass();
obj.connectionmethod();
}

I want to see if my connection is working or not by printing the console.writeline messages. When i run my program its giving me no error but no message is showing, why?(note:- its a web application)
Can anybody help ?

Thanks and Regards
Akiii




Responses

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down

Resolved
Sir i have modified the code like this....
@Gsolvers...Sir your code is working fine, thank you sir for your help. Can i do this in if-else block ??
@T.saravanan .....Yes sir you are right, its always executing the finally block so i have not used it. Thank you for your help.

public class connectionclass

{
public string connectionmethod()
{
string connstr = "Data Source=ARKA-PC;Initial Catalog=rgdb;User Id = sa;;password = 12345";
SqlConnection connobj = new SqlConnection(connstr);
string errormsg = "";

try
{
connobj.Open();
errormsg = "connection opened";
}
catch (Exception ex)
{
return ("The Connection could not be opened and results in the following error :" + ex.ToString());
}
connobj.Close();

return errormsg;
}
}


Thanks and Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down

Resolved
@Gsolvers.....Sir, i have written this and is working fine.
Thank you very much for your help all the way.

 connobj.Open();


if (connobj.State == ConnectionState.Open)
{
return "connection opened";
}
else
{
return "connection closed";
}


Thanks and Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/16/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

In web application we can not use the 'Console' keyword for write a text in page, use 'Response' object for write a text in page.
So try this....
public class connectionclass

{
public string connectionmethod()
{
string connstr = "Data Source=Akiii-PC;Initial Catalog=rgdb;";
SqlConnection connobj = new SqlConnection(connstr);

try
{
connobj.Open();
return "connection opened";
}
catch (Exception ex)
{
return ex.ToString();
}
finally
{
connobj.Close();
return "connection closed";
}
}
}

protected void Page_Load(object sender, EventArgs e)
{
connectionclass obj = new connectionclass();
Response.Write(obj.connectionmethod());
}





Thanks,
T.Saravanan

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: SheoNarayan on: 3/16/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Could you please copy-paste the error you are getting?

using connobj.State, you can know whether the database connection is open or closed in some other state.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down
@T.Saravanan................Thank you sir for your prompt reply. I just did what you told me but one error is creeping up...
I want to give you the screen shot but please tell me how to post a screen shot here ?

Thanks and Regards
Akiii



Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down
@SheoNarayan...Thank you Sir for your time, i am getting an error in the finally block as "control cannot leave the body of a finally clause".....
I have used the above code as told by T.Saravanan Sir.
I want to post the screen shot of the error but i don't know how to post a screen shot here, can you please tell me how to do that ?

Thanks and Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/17/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

In our reply section, 'Reply with attachment' button is there.But 'Zip' file only allowed to attach in our website.

Try this...

Thanks,
T.Saravanan

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsolvers on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
CORRECT the code as below. I have altered the placement of return. Please try

public class connectionclass
{
public string connectionmethod()
{
string connstr = "Data Source=Akiii-PC;Initial Catalog=rgdb;";
SqlConnection connobj = new SqlConnection(connstr);
string errormsg="";
try
{
connobj.Open();
errormsg="connection opened";
}
catch (Exception ex)
{
return ex.ToString();
}
finally
{
connobj.Close();
errormsg="connection closed";
}
return errormsg;
}

Best Regards,

VG
www.TeacherJi.com

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/17/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Akiii,

Just follow @Gsolvers answer....
But when ever 'Try' block process completed means its automatically comes to 'finally' block.So it will return only finally block message.
If error occurred in try block means its come to catch block, as per @Gsolvers code error message will be return.

Try @Gsolvers code....


Thanks,
T.Saravanan

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down
@T.saravanan .....

There is the snapshot Sir.....Please check it

Thanks and Regards
Akiii
 Download source file

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsolvers on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
Are you saying that code which i posted is failing or the error message returned is not satisfactory?

Best Regards,

VG
www.TeacherJi.com

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/17/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Akiii,

I checked it out your attachment.Sorry for my mistake, in finally block we can not use the keyword 'return'.
Kindly follow @Gsolvers answer.... he change the correction....


Thanks,
T.Saravanan

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsolvers on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
Akiii,
Let me know if this is sorted out.

Best Regards,

VG
www.TeacherJi.com

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down
can connectionobject.close() should be written in finally block ??

Thanks and Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsolvers on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
yes it shld be in finally block. Let me know if you need to in detaill the reason for this?

Best Regards,

VG
www.TeacherJi.com

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down
@Gsolvers........
Yes sir please explain why it shd be written in finally block !
and can the code be written in if-else stmt ?
like

if connection is open
then do this....
else
do this....


How shd i modify the code above ?

Thanks and Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsolvers on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
Finally block executes irrespective of exception. So this means that the statement in this finally block will execute whether there is a error or no error and i ams ure we want to close the DB connection in any of the case. If the code runs smoothly we want to close the connection adn also if there is an exception in that case also we want to close the connection.

Yes you can always use the if statement as below. This will prevent in any other exception if connection object is already closed and this is recommended practice.

if (connection.state=1) ' i assume 1 = connection open state. You can see this when you type in VS IDE.
{
connection.close()
}


Best Regards,

VG
www.TeacherJi.com

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down
@Gsolvers......Sir, i have written like this, but its not working, can you please write the whole code :-

if (connobj.State = 1) 

{
connobj.Close();
}


Thanks and Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsolvers on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
Try this!

if (connobj.State==ConnectionState.Open)
{
connobj.Close();
}

Best Regards,

VG
www.TeacherJi.com

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsolvers on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
Sounds good! Please mark the answer as complete:)

Best Regards,

VG
www.TeacherJi.com

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 3/17/2011 [Member] Bronze | Points: 25

Up
0
Down
@Gsolvers......Done Sir

Thanks and Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response