Solved

SQL Conncetion Question

Posted on 2011-03-15
10
317 Views
Last Modified: 2012-05-11
Hi,

I have question regarding sql connection open and close each record (1000 times).

Some one give me an idea which is most efficient way to do

Option 1: Open and close connection for every record

Option 2: keep open the connection for all record insert

<pre lang="cs">public void InsertEmployment(DLAEmployeeExport item, SqlConnection sqlConnection1)
     {
         try
         {
             System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(&quot;InsertEmployment&quot;, sqlConnection1);
             cmd.CommandType = System.Data.CommandType.StoredProcedure;
             cmd.Parameters.Add(&quot;@BusinessTitle&quot;, SqlDbType.Char);
             cmd.Parameters[&quot;@BusinessTitle&quot;].Direction = ParameterDirection.Input;
             cmd.Parameters[&quot;@BusinessTitle&quot;].Value = item.PositionCategory;
             sqlConnection1.Open();
             cmd.ExecuteNonQuery();
         }
         catch (System.Exception se)
         {
             Console.WriteLine(se.Message);
         }
         finally
         {
             /// if the connection is not null
             if (sqlConnection1 != null)
             {
                 ///check if the connection is open, if so then close it
                 if (sqlConnection1.State == ConnectionState.Open)
                     sqlConnection1.Close();
             }
         }
     }

Open in new window

0
Comment
Question by:Sha1395
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 200 total points
ID: 35143332
Two things:

For web applications, I believe it's generally practiced that you open and close each time you execute against the database. I am referring to each "load" of a page. If you are doing more than query per "load" of the page, then leaving the connection open, IMO, would be OK.
If you wrap your connection object with a "using" statement, you shouldn't have to worry about explicitly closing it--the using should do it for you.

"using" Example (slight modification to your code for demonstration purposes):
<pre lang="cs">public void InsertEmployment(DLAEmployeeExport item)
     {
         try
         {
             using (SqlConnection sqlConnection1 = new SqlConnection(conn_string))
             {
                 System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(&quot;InsertEmployment&quot;, sqlConnection1);
                 cmd.CommandType = System.Data.CommandType.StoredProcedure;
                 cmd.Parameters.Add(&quot;@BusinessTitle&quot;, SqlDbType.Char);
                 cmd.Parameters[&quot;@BusinessTitle&quot;].Direction = ParameterDirection.Input;
                 cmd.Parameters[&quot;@BusinessTitle&quot;].Value = item.PositionCategory;
                 sqlConnection1.Open();
                 cmd.ExecuteNonQuery();
             }
         }
         catch (System.Exception se)
         {
             Console.WriteLine(se.Message);
             // Connection is implicitly close by "using" even when there is an exception
         }
     }

Open in new window

0
 
LVL 11

Assisted Solution

by:lenordiste
lenordiste earned 50 total points
ID: 35143366
technically option 2 is the way to go performance wise. However please read this article on connection pooling (a mechanism that allows to save time when you have to open/close frequently):
http://msdn.microsoft.com/en-us/library/bb399543.aspx
0
 
LVL 11

Expert Comment

by:lenordiste
ID: 35143394
here is a friendlier ressource on ADO .NET connection pools: http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx#
0
 

Author Comment

by:Sha1395
ID: 35143403
Thanks :kaufmed and lenordiste.

i understand ,i will leave it open but my question to kaufmed is if i don't explicitly closed it,is it not going to make another connection if the next record insert or it calls the next method ?

Here is my full code



        public void SetEmployeesToDatabase(EmployeeExport item)
        {
            ///  connection string here. 
           ///  
           
            SqlConnection sqlConnection1 = new SqlConnection(@"Data Source=SYDTESTSVR05;Initial Catalog=DLAPGateway_DEV;Integrated Security=SSPI");
                                   
            InsertPerson(item, sqlConnection1);
            InsertHomeAddress(item, sqlConnection1);
            InsertJob(item, sqlConnection1);
            InsertEmployment(item, sqlConnection1);
        }


 public void InsertPerson(DLAEmployeeExport item, SqlConnection sqlConnection1)
        {
            try
            {

                //I will add the If condition Only EmployeeNo in SP to check the file is Exist.
                //If it Exist then i will update the row with current value.
                // If not then i will insert the row in the Table
               

                //Sql Connection

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("InsertPerson", sqlConnection1);
                
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                
                cmd.Parameters.Add("@EmployeeNo", SqlDbType.Int);
                cmd.Parameters["@EmployeeNo"].Direction = ParameterDirection.Input;
                cmd.Parameters["@EmployeeNo"].Value = item.EmployeeNumber;
catch (System.Exception se) //  exception on the console. 
            {
                Console.WriteLine(se.Message);
            }
            finally //Always execute
            {
                /// here  will check if the connection is not null
                if (sqlConnection1 != null)
                {
                    ///check if the connection is open, if so then close it 
                    if (sqlConnection1.State == ConnectionState.Open)
                        sqlConnection1.Close();
                }
   
            }


 public void InsertHomeAddress(DLAEmployeeExport item, SqlConnection sqlConnection1)
        {
            try
            {


                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("InsertHomeAddress", sqlConnection1);
                
                cmd.CommandType = System.Data.CommandType.StoredProcedure;


		cmd.Parameters.Add("@AddressLine1", SqlDbType.Char);
                cmd.Parameters["@AddressLine1"].Direction = ParameterDirection.Input;
                cmd.Parameters["@AddressLine1"].Value = item.ResidentialAddress1;

  catch (System.Exception se) //  exception on the console. 
            {
                Console.WriteLine(se.Message);
            }
            finally //Always execute
            {
                /// here  will check if the connection is not null
                if (sqlConnection1 != null)
                {
                    ///check if the connection is open, if so then close it 
                    if (sqlConnection1.State == ConnectionState.Open)
                        sqlConnection1.Close();
                }
   
            }

I have two more methods to call too

Open in new window

0
 
LVL 11

Expert Comment

by:lenordiste
ID: 35143460
if you are in a web scenario, make sure that you have connection pooling enabled (it is by default); this way you can open/close on each request without having to worry much about performance. It's best from a design standpoint to systematically open/close in order to make sure you return the available connections to the pool as soon as possible.
0
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.

 

Author Comment

by:Sha1395
ID: 35143556
this is a C# console application lenordiste.

I use to practice always open/close but it doesn't make sense to open 1000 times and close.

Am just going to thru 'USING' its good for connection for pooling but some how it throws exception here

sqlConnection1 doesn't exist in the current context

public void InsertHomeAddress(DLAEmployeeExport item)
        {

            try
            {
                using (SqlConnection sqlConnection1 = new SqlConnection(@"Data Source=SYDTESTSVR05;Initial Catalog=DLAPGateway_DEV;Integrated Security=SSPI")) ;
                {
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("InsertHomeAddress", sqlConnection1);

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add("@PostCode", SqlDbType.Char);
                    cmd.Parameters["@PostCode"].Direction = ParameterDirection.Input;
                    cmd.Parameters["@PostCode"].Value = item.ResidentialPostCode;


                    sqlConnection1.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (System.Exception se) //  exception on the console. 
            {
                Console.WriteLine(se.Message);
            }

Open in new window

0
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35143945
It seems like you have a list of some data that you're wanting to insert, though it seems you have more than 1 list - and I'm not really clear on how, or if, those different sets of data inter-relate.  I can't imagine opening and closing a connection a 1000 times rapidly would be better'n opening it once, running a 1,000 ops, and then closing it.

Though, mostly for organizational sake, I'm not a big fan of passing around SqlConnection objects. If possible I would use a single method that takes a list of data and inserts them, e.g.:
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

class Program
{
	static void Main(string[] args)
	{
		List<Person> ppl = new List<Person>();
		ppl.Add(new Person() { FirstName = "Santa", LastName = "Claus" });
		ppl.Add(new Person() { FirstName = "Easter", LastName = "Bunny" });
		ppl.Add(new Person() { FirstName = "Hanuka", LastName = "Harry" });

		InsertPeople(ppl);
	}

	static void InsertPeople(IEnumerable<Person> people)
	{
		using (SqlConnection cnx = new SqlConnection("Data Source=.\\SQLExpress;Initial Catalog=Test;User ID=sa;Password=;"))
		{
			cnx.Open();
			using (SqlCommand cmd = cnx.CreateCommand())
			{
				cmd.CommandText = "INSERT INTO TestTable (FirstName,LastName) VALUES(@fname, @lname)";
				cmd.Parameters.Add("@fname", SqlDbType.VarChar);
				cmd.Parameters.Add("@lname", SqlDbType.VarChar);
				foreach (Person p in people)
				{
					cmd.Parameters["@fname"].Value = p.FirstName;
					cmd.Parameters["@lname"].Value = p.LastName;
					cmd.ExecuteNonQuery();
				}
			}
			cnx.Close();
		}
	}
}

class Person
{
	public string FirstName { get; set; }
	public string LastName { get; set; }
}

Open in new window


0
 

Author Comment

by:Sha1395
ID: 35144185
Hi :tgerbert

I have only one list (that comes from webservice) but am inserting those values to 4 different tables,that's why i use to open 4 sql connection in each method.

Yep it doesn't make sense though too..

please correct me if am wrong, migrate all the store procedure in to One ,Insert and update will handle by that only one store procedure.

In that way i can use only one connection,am i right ?
0
 
LVL 33

Accepted Solution

by:
Todd Gerbert earned 250 total points
ID: 35144367
SQL isn't really my area of expertise, I'm particularly bad with stored procedures, but I'd wager it wouldn't be too difficult for you to write a stored procedure that would insert the values into the different tables with one SqlCommand.ExecuteNonQuery().
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

class Program
{
	static void Main(string[] args)
	{
		List<EmployeeExport> employeeList = EmployeeExport.GetDummyData();

		InsertEmployeeData(employeeList);
	}

	static void InsertEmployeeData(IEnumerable<EmployeeExport> employees)
	{
		using (SqlConnection cnx = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=TestDB;User ID=sa;Password=;"))
		{
			cnx.Open();
			using (SqlCommand cmd = cnx.CreateCommand())
			{
				cmd.CommandText = "InsertValues";
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@employeenum", SqlDbType.VarChar).Direction = ParameterDirection.Input;
				cmd.Parameters.Add("@address", SqlDbType.VarChar).Direction = ParameterDirection.Input;
				cmd.Parameters.Add("@title", SqlDbType.VarChar).Direction = ParameterDirection.Input;
				cmd.Parameters.Add("@employment", SqlDbType.VarChar).Direction = ParameterDirection.Input;

				foreach (EmployeeExport employee in employees)
				{
					cmd.Parameters["@employeenum"].Value = employee.EmployeeNumber;
					cmd.Parameters["@address"].Value = employee.HomeAddress;
					cmd.Parameters["@title"].Value = employee.JobTitle;
					cmd.Parameters["@employment"].Value = employee.EmploymentStatus;
					cmd.ExecuteNonQuery();
				}
			}
		}
	}
}

class EmployeeExport
{
	public string EmployeeNumber { get; set; }
	public string HomeAddress { get; set; }
	public string JobTitle { get; set; }
	public string EmploymentStatus { get; set; }

	public static List<EmployeeExport> GetDummyData()
	{
		List<EmployeeExport> employees = new List<EmployeeExport>();

		employees.Add(new EmployeeExport()
		{
			EmployeeNumber = "abc",
			HomeAddress = "123",
			JobTitle = "Manager",
			EmploymentStatus = "Full Time"
		});

		employees.Add(new EmployeeExport()
		{
			EmployeeNumber = "efg",
			HomeAddress = "456",
			JobTitle = "Executive",
			EmploymentStatus = "Full Time"
		});

		employees.Add(new EmployeeExport()
		{
			EmployeeNumber = "hij",
			HomeAddress = "789",
			JobTitle = "Worker",
			EmploymentStatus = "Part Time"
		});

		return employees;
	}
}

Open in new window


At the very least, you should be able to mush together the four insert statements:
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

class Program
{
	static void Main(string[] args)
	{
		List<EmployeeExport> employeeList = EmployeeExport.GetDummyData();

		InsertEmployeeData(employeeList);
	}

	static void InsertEmployeeData(IEnumerable<EmployeeExport> employees)
	{
		using (SqlConnection cnx = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=TestDB;User ID=sa;Password=;"))
		{
			cnx.Open();
			using (SqlCommand cmd = cnx.CreateCommand())
			{
				StringBuilder commandText = new StringBuilder();
				commandText.Append("INSERT INTO AddressTable (AddressLine1) VALUES(@address);");
				commandText.Append("INSERT INTO EmploymentTable (EmploymentStatus) VALUES(@employment);");
				commandText.Append("INSERT INTO JobTable (JobTitle) VALUES(@title);");
				commandText.Append("INSERT INTO PersonTable (EmployeeNo) VALUES(@employeenum);");

				cmd.CommandText = commandText.ToString();
				cmd.Parameters.Add("@employeenum", SqlDbType.VarChar);
				cmd.Parameters.Add("@address", SqlDbType.VarChar);
				cmd.Parameters.Add("@title", SqlDbType.VarChar);
				cmd.Parameters.Add("@employment", SqlDbType.VarChar);

				foreach (EmployeeExport employee in employees)
				{
					cmd.Parameters["@employeenum"].Value = employee.EmployeeNumber;
					cmd.Parameters["@address"].Value = employee.HomeAddress;
					cmd.Parameters["@title"].Value = employee.JobTitle;
					cmd.Parameters["@employment"].Value = employee.EmploymentStatus;
					cmd.ExecuteNonQuery();
				}
			}
			cnx.Close();
		}
	}
}

class EmployeeExport
{
	public string EmployeeNumber { get; set; }
	public string HomeAddress { get; set; }
	public string JobTitle { get; set; }
	public string EmploymentStatus { get; set; }

	public static List<EmployeeExport> GetDummyData()
	{
		List<EmployeeExport> employees = new List<EmployeeExport>();

		employees.Add(new EmployeeExport()
		{
			EmployeeNumber = "abc",
			HomeAddress = "123",
			JobTitle = "Manager",
			EmploymentStatus = "Full Time"
		});

		employees.Add(new EmployeeExport()
		{
			EmployeeNumber = "efg",
			HomeAddress = "456",
			JobTitle = "Executive",
			EmploymentStatus = "Full Time"
		});

		employees.Add(new EmployeeExport()
		{
			EmployeeNumber = "hij",
			HomeAddress = "789",
			JobTitle = "Worker",
			EmploymentStatus = "Part Time"
		});

		return employees;
	}
}

Open in new window

0
 

Author Comment

by:Sha1395
ID: 35144385
thanks tgerbert for all your help
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

705 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

19 Experts available now in Live!

Get 1:1 Help Now