Solved

using the best practices?

Posted on 2010-08-16
10
255 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
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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 29

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

776 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