Solved

using the best practices?

Posted on 2010-08-16
10
253 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now