Click here to Skip to main content
15,944,136 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
Hello all, i have a question that has been bugging me. I'm not sure which way is the right way of declaring the sqlcommnad and other ado.net objects....plz view my code below...

C#
public class DataOperations
    {
        string strConnString =  ConfigurationManager.ConnectionStrings["dbconn"].ToString();
        private static DataOperations singleInstance = null;
        private SqlConnection sqlConn;
        public SqlCommand sqlCmd;
        public SqlDataAdapter sqlAda;

        DataOperations()
        {
            sqlConn=new SqlConnection(strConnString);
            sqlCmd = new SqlCommand();
            sqlCmd.Connection = sqlConn;
            sqlCmd.CommandType = System.Data.CommandType.Text;
            sqlAda = new SqlDataAdapter();

        }


this is how i usuall declare a sqlcommand obj and others....and i use the instance of it in methods as follows...

C#
public bool InsertAlumniDetails(.....some values)

        {
            bool bRetVal = false;
            
            
            string strSqlInsert = @"some sql";

            try
            {
                OpenConnection();
                sqlCmd.Parameters.Clear();
                sqlCmd.Parameters.AddWithValue("name", strName);
                sqlCmd.Parameters.AddWithValue("gender", strGender);
                sqlCmd.CommandText = strSqlInsert;
                sqlCmd.ExecuteNonQuery();


my problem is i sometimes get error "COM object that has been separated from its underlying RCW cannot be used."
The error is solved if i use a local sqlcommand instance...why is it so?...can anyone explain cleary???
Thanks in advance,
mlimbu
Posted
Updated 6-Sep-12 8:25am
v2

It is hard to tell what is happening for sure. But this sounds like you are instantiating a COM object and then trying to use it in another thread.

Part of the problem here is you SHOULD be using those object locally. It is actually best to use those object in a
C#
using
statement.

If you are writing a Data Access Layer the only thing that should be 'global' is the connection to the database. You don't want to open and close the connection constantly. You do however only want to use a SqlCommand/SqlDataReader pretty quickly and for a specific purpose, then you want to get rid of (Dispose) it.
 
Share this answer
 
Thank you expert coming for the swift reply, for now i'll be instantiating the objects locally. I always thought that declaring these objects globally would be quicker and less codes...but i guess that's not the case...if i find a good explanation to this problem i have then, i'll surely post it.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900