FAQ - Frequently Asked Questions

Re: Handle database connections with care

yorock — Sun, 22 Mar 2009 20:47:39 GMT

hi all,

whenever i try 2 execute the connection code.A server error is gettin displayed "cannot open the database "projdb.mdf"requested .login failed..login failed for the user "YOGESH\Owner"

Re: Handle database connections with care

sudipta — Mon, 09 Mar 2009 09:24:24 GMT

Hi Sriya,

Replying to a very old post :)

Use the code as below in your try block.

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

if (rdr.Read())
{
   str = rdr[0].ToString();
}

rdr.Close();

Re: Handle database connections with care

sirdneo — Wed, 31 Dec 2008 10:03:47 GMT

Keeping connection open for bunch of operation or open/close connection for every operation depends on application behavior and usage.

I always use connection pool which have multiple connections and always colse connection for each operation. But sometimes I use same connection from pool for multple operation.

Re: Handle database connections with care

abc@abc.com — Sat, 29 Nov 2008 11:27:52 GMT

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for Cls_DataAccess
/// </summary>
    public class Cls_DataAccess
    {
        private SqlConnection con;
        public bool blnIsError;
        public string strErrorMessage;
        public Cls_DataAccess()
        {
            //
            // TODO: Add constructor logic here
            //
            blnIsError = false;
            strErrorMessage = "";
        }

        /// <summary>
        /// Returns Error Message
        /// </summary>
        /// <param name="ResetError">Boolean value to specify if error needs to be reset</param>
        /// <returns></returns>
        public string GetErrorMessage(bool ResetErrorStatus)
        {
            string strReturn = strErrorMessage;

            if (ResetErrorStatus == true)
            {
                ResetError();
            }

            return strReturn;
        }

        /// <summary>
        /// Initilaizes the error message of the class
        /// </summary>
        /// <param name="ErrorMessage"></param>

        private void InitializeError(string ErrorMessage, string Function)
        {
            blnIsError = true;
            strErrorMessage = ErrorCodes.ReturnError(this.ToString(), Function, ErrorMessage);
        }

        /// <summary>
        /// Resets the error
        /// </summary>
        public void ResetError()
        {
            blnIsError = false;
            strErrorMessage = "";
        }

        /// <summary>
        /// Returns an open database connection
        /// </summary>
        /// <returns>Connection Object</returns>
        public SqlConnection GetConnection()
        {


            //olecon.ConnectionString = ConfigurationManager.ConnectionStrings["tempStoreConnString"].ConnectionString;

            string strConnectionString = ConfigurationManager.ConnectionStrings["tempConnString"].ConnectionString;

            strConnectionString = strConnectionString.Replace("App_Data/", @""+AppDomain.CurrentDomain.BaseDirectory+@"App_Data/");

            con = new SqlConnection();

            con.ConnectionString = strConnectionString;

            try
            {
                if (olecon.State != System.Data.ConnectionState.Open)
                {
                    con.Open();
                }
                return con;
            }
            catch (Exception errOlecon)
            {
                InitializeError(errOlecon.Message, "GetConnection");
                return null;
            }
        }

        /// <summary>
        /// Close the database connection if opened
        /// </summary>
        public void CloseConnection()
        {
            if (con.State == System.Data.ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

Re: Handle database connections with care

vijjendra — Tue, 25 Nov 2008 17:46:14 GMT

when you use datareader first declare as null

dataReader dr=null;

and use using because using is the combination of try and finally.

Re: Handle database connections with care

sriyasarkar — Thu, 20 Nov 2008 15:38:43 GMT

I have one question.

If I am using a datareader, should I close that in the try block or the finally block?

Re: Handle database connections with care

SirPaladin1969 — Sun, 26 Oct 2008 04:19:09 GMT

I never got an anwer on this: Whose UserName and Passwords would be hard coded in the web.config? Wouldn't each user need to login with their own UserName and Password to gain access? Do you add Keys for each? That would seem very inefficient if you had lots of users.

Re: Handle database connections with care

drpcken — Sun, 07 Sep 2008 17:25:26 GMT

Thats really helpful information. I'm definately going to implement this into my current project.

Thanks!!

Re: Handle database connections with care

mbanavige — Sun, 07 Sep 2008 15:47:59 GMT

a using statement causes the object being used to have its Dispose method invoked when that object goes out of scope (when the using block ends). The Dispose method is where unmanaged resources are normally released. So in the case of the SqlConnection object, we can see what the Dispose method looks like by using a tool such as Reflector.

protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

So here we can clearly see what sort of cleanup work will get done when the using block ends on a SqlConnection object. That dispose code will execute even in the event of an exception getting thrown. That insures that the connection will get closed - no matter what happens.

Re: Handle database connections with care

drpcken — Sun, 07 Sep 2008 15:21:39 GMT

haha thanks. I understand what using statements are but I didn't realize you could handle dbconnections with them.

So basically you use the connection object, open and perform your work, but you don't have to close the connection and it takes care of cleanup after it leaves the using block?

Re: Handle database connections with care

mbanavige — Sun, 07 Sep 2008 12:20:39 GMT

drpcken:
Where can I get more info?

Using Statement (Visual Basic) - http://msdn.microsoft.com/en-us/library/htd05whh.aspx

using Statement (C# Reference) - http://msdn.microsoft.com/en-us/library/yh598w02(VS.80).aspx

Note that the Using Statement is available in VB.NET only from the 2.0 framework and forwards.

Re: Handle database connections with care

drpcken — Sun, 07 Sep 2008 02:55:54 GMT

or the using pattern in C#:

using (SqlConnection conn = new SqlConnection(dsn))
{
   //...
  conn.Open();
}
which will close the db connection automatically when leaving the using block.

I'm intrigued by this. I've never heard of doing it this way. Where can I get more info?

Re: Handle database connections with care

sudipta — Thu, 14 Aug 2008 14:28:17 GMT

themasterpiece83:
hi all.. what i hav found out is web.config is just a text file which can be hacked easily..

Web.config file can't be hacked as such. When browser requests a Web.config file, this type of request is not processed. The file can only be hacked i you gain access to the web server. In such a case, connection string in a public library can also be hacked if the Web Server security is compromised.

Re: Handle database connections with care

donkiely — Mon, 04 Aug 2008 15:55:39 GMT

themasterpiece83:
hi all.. what i hav found out is web.config is just a text file which can be hacked easily.. so its better to write a constant string in a public class library which can be access anywhere that class accessible is.. e.g.

public static class Constants

{
#if Localhost
public const string SqlConnectionString = @"server=.; uid=UserName; pwd=Password; database=DatabaseName";

#endif

}

now u can access it with Constants.SqlConnectionString by adding this class into your cs file.. this is the best way i found out..

post your suggestions if m wrong..

No, no, NO! It is NEVER good practice to embed secrets in an executable. Those are far too easy to find, and no less hackable than a web.config file.

The best practice for protecting connection strings is to put them in the connectionStrings section of web.config, and encrypt them using the features built into ASP.NET. This uses strong encryption, and protects the data as well as it can be.

Don

Re: Handle database connections with care

vivek_cs — Fri, 27 Jun 2008 14:49:55 GMT

Better to use Using whenever open a connection or its applicable for all classes which implement Idisposable

The new code would looking something like this:

using (SqlConnection cn = new SqlConnection(connectionString))
{
    using (SqlCommand cm = new SqlCommand(commandString, cn))
    {
        cn.Open();
        cm.ExecuteNonQuery();
    }
}

This is essentially equivalent to the following, although my guess is that C# will internally generate two try / finally blocks (one for the SqlConnection and one for the SqlCommand), but you get the idea:

SqlConnection cn = null;
SqlCommand cm = null;

try
{
    cn = new SqlConnection(connectionString);
    cm = new SqlCommand(commandString, cn);
    cn.Open();
    cm.ExecuteNonQuery();
}
finally
{
    if (null != cm);
        cm.Dispose();
    if (null != cn)
        cn.Dispose();
}