CODEWITHSUNDEEP
c#ado.netsqldatareader
Funky
Funky

Reputation: 13612

C#: How can I shorten my code to run a stored procedure?

I'm a big fan of keeping my code simple and trim so it can be re-usable, on thing i'm struggling with is using the data reader for different types of objects, I had it in a method and found there were problems with connections closed or being left open. SO I am being forced, for the mean time to copy and paste the code, which is something I hate!!! Is there any way I can scale this down so I can put it in a method and make it re-usable and nice?

ENT_AuctionBid ret = new ENT_AuctionBid();      

try
        {
            SqlParameter[] Params = new SqlParameter[]{ 
                    new SqlParameter("@ID", ID ) 
            };

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("GetItem", conn))
                {
                    SqlDataReader reader;
                    command.CommandType = CommandType.StoredProcedure;

                    conn.Open();

                    command.Parameters.AddRange(Params);
                    reader = command.ExecuteReader(CommandBehavior.SingleRow);

                    while (reader.HasRows)
                    {
                        while (reader.Read())
                        {
            // 
                            ret = this.Convert(reader);
                        }

                        reader.NextResult();
                    }

                    reader.Close();
                }
            }
        }
        catch (Exception ex)
        {

        }  
return ret;

Upvotes: 0

Views: 290

Answers (7)

user557419
user557419

Reputation:

You could start using LINQ-to-SQL, which has it's own DataClass system in which you just drag-&-drop your database tables and stored procedures. Then you just have to create an instance at the top of your classes -- private MyCustomDataClass _db = new MyCustomDataClass(); and then you can just type in _db.<Here all datatables and SPROCs will appaer for you to choose>.

Example (from when all SPROCs are added to the DataClass)

private MyCustomDataClass _db = new MyCustomDataClass();

public void MethodToRunSPROC(string email, Guid userId)
{
    _db.MySPORC_AddEmailToUser(email, userId);
}

Upvotes: 0

GvS
GvS

Reputation: 52518

You can make it using a lot less lines:

// Skipped creating temp variable
try {
   using (SqlConnection conn = new SqlConnection(this.ConnectionString))
   using (SqlCommand command = new SqlCommand("GetItem", conn) { CommandType = CommandType.StoredProcedure} ) {

      command.Parameters.AddWithValue(@ID, ID);
      conn.Open();

      // reader is IDisposable, you can use using
      using (var reader = command.ExecuteReader(CommandBehavior.SingleRow)) {
          // Skipped parsing multiple result sets, you return after the first
          // otherwise there's no point using SingleRow 
          // If nothing is read, return default value
          return reader.Read() ? this.Convert(reader) : new ENT_AuctionBid();
      }
   }
}
catch (Exception ex) {
    // Handle your exception here
}  
// Return default value for error
return new ENT_AuctionBid();

All connections are closed using this code (because using is used). No unneeded loops are created, becuase you only expect a single row. And the temporary variable is not needed, so the abondend object is not created, only when it is used it is created.

Upvotes: 1

Daniel
Daniel

Reputation: 5732

You can use a utility file, such as SqlHelper.cs from Microsoft Data Access Application Block. Then all the code you need is this:

using (SqlDataReader sdr = SqlHelper.ExecuteReader(this.ConnectionString, "GetItem", ID))
  {
    while (sdr.Read())
    {
      ret = this .Convert(sdr);
    }
  }

Upvotes: 0

il_guru
il_guru

Reputation: 8508

In my projects i usually solve this problem creating an utility class that contains all the methods to access to the DB and manage inside all the stuff related to the db connection and the adapter. For example a class called DBSql which contains a connection (SqlConnection connection;) as private member and the following methods:

//execute the query passed to the function
public System.Data.DataSet ExecuteQuery(string query)
//returns if a query returns rows or not
public bool HasRows(string query)
//execute commands like update/insert/etc...
public int ExcuteNonQuery(string sql)

In my class, you just pass a string and the class initialize the various DataAdapter and Command to execute it and return a dataset. Obiously you can complicate it to manage parameters/transaction and everything else. In this way you are sure that the connection and the object are always handled the same way, and, hopefully, in a correct way.

Upvotes: 0

Huske
Huske

Reputation: 9296

Create helper methods for creating and returning an object of type SqlCommand. Pass a connection object to this helper method as well as stored procedure name and parameters list (if any). If you have different objects that are created from the data reader, pass the data reader to a constructor and let it generate an object based on that data. As for closing the connection you should always have try...catch...finally. In the finally section close the connection.

Upvotes: 0

Svarog
Svarog

Reputation: 2208

You should use SQLDataAdapter.
Here's a nice example on how to use it: http://www.dotnetperls.com/sqldataadapter

Also, you might want to consider switching to Entity Framework, it will make your data access much, much easier, but might be complicated in an existing project.

Upvotes: 1

Ross Dargan
Ross Dargan

Reputation: 6021

This is a bit smaller:-

    try
        {

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand command = new SqlCommand("GetItem", conn))
                {
                    command.Paramaters.AddWithValue("@ID",ID);
                    command.CommandType = CommandType.StoredProcedure;

                    conn.Open();

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {

            // 
                            ret = this.Convert(reader);

                    }

                }
            }
        }
        catch (Exception ex)
        {

        }

Upvotes: 0

Related Questions