?
Solved

using the best practices?

Posted on 2010-08-16
10
Medium Priority
?
260 Views
Last Modified: 2012-05-10
Hi, i have this code
    protected void btnSave_Click(object sender, EventArgs e)
    {
        con = new SqlConnection("Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True");

        cmd = new SqlCommand("EvaluacionSentenciasJudicialesInsertarT", con);
        cmd.Parameters.Add("@tipoDocumento", SqlDbType.Char).Value = ddlTipoDocumento.SelectedItem.Value;
        cmd.Parameters.Add("@numeroDocumento", SqlDbType.VarChar).Value = txtNumeroDocumento.Text.Trim();
        cmd.Parameters.Add("@condicionMagistrado", SqlDbType.Char).Value = ddlCondicionMagistrado.SelectedItem.Value;
        cmd.Parameters.Add("@identificacionDespacho", SqlDbType.Char).Value = ddlIdentificacionDespacho.SelectedItem.Value;
        cmd.Parameters.Add("@especialidadJuridica", SqlDbType.Char).Value = ddlEspecialidadJuridica.SelectedItem.Value;

        cmd.CommandType = CommandType.StoredProcedure;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception ex)
        {
           
            throw ex;
        }
     }
how I can improve it using the best practices?
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 6

Expert Comment

by:tikusbalap
ID: 33451267
Here's what I will do:


using(SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True"))
{
	con.Open();
	using (SqlTransaction trans = con.BeginTransaction())
	{
		using (SqlCommand cmd = new SqlCommand("EvaluacionSentenciasJudicialesInsertarT", con))
		{
			
			try
			{
				cmd.Parameters.Add("@tipoDocumento", SqlDbType.Char).Value = ddlTipoDocumento.SelectedItem.Value;
				cmd.Parameters.Add("@numeroDocumento", SqlDbType.VarChar).Value = txtNumeroDocumento.Text.Trim();
				cmd.Parameters.Add("@condicionMagistrado", SqlDbType.Char).Value = ddlCondicionMagistrado.SelectedItem.Value;
				cmd.Parameters.Add("@identificacionDespacho", SqlDbType.Char).Value = ddlIdentificacionDespacho.SelectedItem.Value;
				cmd.Parameters.Add("@especialidadJuridica", SqlDbType.Char).Value = ddlEspecialidadJuridica.SelectedItem.Value;

				cmd.CommandType = CommandType.StoredProcedure;
				cmd.ExecuteNonQuery();
				trans.Commit();				
			}
			catch (Exception ex)
			{
				trans.Rollback();
				throw ex;
			}
		}	
	}
}

Open in new window

0
 
LVL 6

Expert Comment

by:tikusbalap
ID: 33451273
The point is use "using" and "transaction".
0
 

Author Comment

by:enrique_aeo
ID: 33451283
is necessary and use transaction? it is the insertion and a record
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:enrique_aeo
ID: 33451291
I test your code and i get this error
ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.
0
 
LVL 30

Accepted Solution

by:
anarki_jimbel earned 500 total points
ID: 33451788
Honestly, don't understand why would we use a transaction to insert ONE record?

"Using" make sense.

Really the code like:

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception ex)
        {
           
            throw ex;
        }

is not very good. Say, something happens in the ".ExecuteNonQuery" - your connection is not closed.
One more way would be to use "finally" block:

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
             throw ex;
        }
       finally
        {
            con.Close();
        }

"using" is considered as the best practice.
0
 
LVL 14

Expert Comment

by:Dhanasekaran Sengodan
ID: 33452240
           SqlConnection connection = new SqlConnection(// Your connection string );
            SqlCommand command = new SqlCommand();
            SqlParameter SQLParameter;
            SqlParameterCollection SQLParameters;
             SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet outputDataSet = new DataSet();

            try
            {
                connection.Open();
                SqlTransaction tranSQL=connection.BeginTransaction();
                command.Connection = connection;
                command.CommandText = request.Command;
                command.CommandType = request.CommandTypes;
                if(request.Parameters.Count > 0)
                {
                    foreach(DataRequest.Parameter param in request.Parameters)
                    {
                        SQLParameter = command.Parameters.Add(param.ParamName, param.Paramvalue);
                    }
                }
                if(request.Transactional)
                {
                   tranSQL = connection.BeginTransaction();
                }
                adapter = new SqlDataAdapter(command);
                adapter.Fill(outputDataSet);
            }
            catch(SqlException exSQL)
            {
                Debug.WriteLine(exSQL.Message);
                request.Exception = exSQL;
                if(request.Transactional)
                {
                    tranSQL.Rollback();
                }
            }
            catch(Exception ex)
            {
                Debug.WriteLine(ex.Message);
                Trace.Write("Error Message");
                Trace.Write(ex.Message);
                request.Exception = ex;
                if(request.Transactional)
                {
                    tranSQL.Rollback();
                }
            }
            finally
            {
                if(request.Transactional)
                {
                   tranSQL.Commit();
                }
                if(connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
                command.Dispose();
                adapter.Dispose();
                connection.Dispose();
            }
           
        }
0
 
LVL 15

Expert Comment

by:angus_young_acdc
ID: 33453996
I would also say that you should take all the code out of your button click event, and create a seperate method.  Then just call that method from the click event, the reason being that (for me anyway) it makes everything more readable as you can region your methods / events, and also it promotes code re-use.

I would also put your connection string into an app.config file, which you can then call at any time.  This also means that should your database name change, you won't have to edit and recompile your code.
0
 

Author Comment

by:enrique_aeo
ID: 33454419
Dear dhansmani
I have 35 prameters like that, in your code do not see where I place

cmd.Parameters.Add("@tipoDocumento", SqlDbType.Char).Value = ddlTipoDocumento.SelectedItem.Value;
        cmd.Parameters.Add("@numeroDocumento", SqlDbType.VarChar).Value = txtNumeroDocumento.Text.Trim();
0
 

Author Comment

by:enrique_aeo
ID: 33489460
that time by the parameters? with variable request.Parameters
0
 
LVL 6

Assisted Solution

by:tikusbalap
tikusbalap earned 500 total points
ID: 33493396
@anarki_jimbel
It is a best practive. It is a habit. You will need it.

@enrique_aeo
Try create command from SqlConnection.CreateCommand() instead.

using(SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True"))
{
	con.Open();
	using (SqlTransaction trans = con.BeginTransaction())
	{
		using (SqlCommand cmd = con.CreateCommand())
		{
			
			try
			{
				cmd.CommandText = "EvaluacionSentenciasJudicialesInsertarT";
				cmd.CommandType = CommandType.StoredProcedure;
				
				cmd.Parameters.Add("@tipoDocumento", SqlDbType.Char).Value = ddlTipoDocumento.SelectedItem.Value;
				cmd.Parameters.Add("@numeroDocumento", SqlDbType.VarChar).Value = txtNumeroDocumento.Text.Trim();
				cmd.Parameters.Add("@condicionMagistrado", SqlDbType.Char).Value = ddlCondicionMagistrado.SelectedItem.Value;
				cmd.Parameters.Add("@identificacionDespacho", SqlDbType.Char).Value = ddlIdentificacionDespacho.SelectedItem.Value;
				cmd.Parameters.Add("@especialidadJuridica", SqlDbType.Char).Value = ddlEspecialidadJuridica.SelectedItem.Value;

				cmd.ExecuteNonQuery();
				trans.Commit();				
			}
			catch (Exception ex)
			{
				trans.Rollback();
				throw ex;
			}
		}	
	}
}

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question