Solved

using the best practices?

Posted on 2010-08-16
10
257 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 125 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 125 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

739 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