Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it OK to pass SQLCommand as a parameter?

Tags:

c#

sql

I have a Business Layer that passes a Conn string and a SQLCommand to a Data Layer like so

    public void PopulateLocalData()
    {
       System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
       cmd.CommandType = System.Data.CommandType.StoredProcedure;
       cmd.CommandText = "usp_PopulateServiceSurveyLocal";
       DataLayer.DataProvider.ExecSQL(ConnString, cmd);
    }

The DataLayer then just executes the sql like so

        public static int ExecSQL(string sqlConnString, System.Data.SqlClient.SqlCommand cmd)
    { 
        int rowsAffected;
        using (SqlConnection conn = new SqlConnection(sqlConnString))
        {
            conn.Open();
            cmd.Connection = conn;
            rowsAffected = cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        return rowsAffected;
    }

Is it OK for me to pass the SQLCommand as a parameter like this or is there a better more accepted way of doing it. One of my concerns is if an error occurs when executing the query the cmd.dispose line will never execute. Does that mean it will continue to use up memory that will never be released?

Update:

Following Eric's advice I more explicitly divided the Business and Data Layers so the method in the Business Layer looks like this

    public void PopulateLocalData()
    {
        DataLayer Data = new DataLayer(this.ConnString);
        Data.UpdateLocalData();
    }

and method that is called in the DataLayer looks like this.

        public void UpdateLocalData()
    {
        using (SqlConnection conn = new SqlConnection(this.ConnString))
        using(SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "usp_PopulateServiceSurveyLocal";
            conn.Open();
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
        }
    }

This way it is very clear that both the SQLCommand and the SQLConnection will be disposed of properly. Thanks.

like image 953
etoisarobot Avatar asked Apr 23 '10 13:04

etoisarobot


People also ask

How many parameters does a SqlCommand function have?

Microsoft SQL Server has a limit on the number of parameters that a parameterized query can have (2100).

How to add parameters in SqlCommand?

preparing a SqlCommand Object for Parameters Proper syntax of a parameter is to use an '@' symbol prefix on the parameter name as shown below: // 1. declare command object with parameter SqlCommand cmd = new SqlCommand( "select * from Customers where city = @City", conn);

Does SqlCommand dispose close connection?

Dispose(); The first command was disposed when the using block was exited. The connection was still open and good for the second command. So, disposing of the command definitely does not dispose of the connection it was using.

Why do we use SqlCommand?

A SqlCommand object allows you to specify what type of interaction you want to perform with a database. For example, you can do select, insert, modify, and delete commands on rows of data in a database table.


1 Answers

Ideally, your business layer should not be aware of the implementation details of your data layer. So, whether you implement the data layer with SqlCommand objects or with something like NHibernate, should be irrelevant to the business layer. This makes it theoretically speaking easy to 'shift out' your data layer and replace it with another one.

Summarizing: passing the SqlCommand from the business layer to the data layer is in my eyes not considered good practice.

Regarding Dispose(): if you are using a using-statement (like using(SqlConnection ...)), the Dispose() method is called automatically at the end of the using statement. You don't have to do this manually.

like image 115
Eric Eijkelenboom Avatar answered Sep 30 '22 14:09

Eric Eijkelenboom