title: Is it safe to access SQL Server CE from multiple threads?
body: <p>I have a large CSV database of about 5MB with ZIP codes, cities, and states that I'm trying to import into a SQL Server CE database. </p>
  
  <p>Using a single thread, the process is estimated to take about 3 hours to complete. While this is fine for getting the job done, I'd like to try and split up the task across multiple threads to cut down on the 3 hours total time. If I create a <code>SqlCeConnection</code> object on each thread, is it safe to run commands on each thread simultaneously? </p>
  
  <p>I have a feeling that there would be issues with concurrency and deadlocks. Here is where I found the CSV database: <a href="http://www.unitedstateszipcodes.org/zip-code-database/" rel="nofollow">http://www.unitedstateszipcodes.org/zip-code-database/</a></p>
  
  <p>Here is my relevant code:</p>
  
  <pre><code>List&lt;AddressSet&gt; addressList;
  
  public void OpenCSV(string file)
  {
      var addresses = from line in File.ReadAllLines(file).Skip(1)
                      let columns = line.Split(',')
                      select new AddressSet
                      {
                          ZipCode = columns[0].Replace("\"", "").Trim(),
                          City = columns[2].Replace("\"", "").Trim(),
                          State = columns[5].Replace("\"", "").Trim()
                      };
      addressList = addresses.ToList();
  
      Thread worker = new Thread(new ThreadStart(ProcessData));
      worker.Start();
  
  }
  
  private void ProcessData()
  {
      try
      {
          int i = 1;
          DateTime operationStart = DateTime.Now;
          foreach (AddressSet address in addressList)
          {
              int stateId = InsertState(address.State);
              int zipCodeId = InsertZipCode(address.ZipCode, stateId);
              int cityId = InsertCity(address.City, stateId);
  
              UpdateRelationships(zipCodeId, cityId);
              float pct = i / (float)addressList.Count() * 100;
              TimeSpan timeSinceStart = DateTime.Now.Subtract(operationStart);
              TimeSpan totalTime = TimeSpan.FromMilliseconds(timeSinceStart.TotalMilliseconds / (pct/100));
              TimeSpan timeLeft = totalTime - timeSinceStart;
              //richTextBox1.BeginInvoke((MethodInvoker)(() =&gt; richTextBox1.Text = pct.ToString("N2") + "% (" + i + " of " + addressList.Count().ToString() + ") " + address.City + ", " + address.State + " " + address.ZipCode 
              //    + "\nEstimated Total Time: " + totalTime.Days.ToString() + " days, " + totalTime.Hours.ToString() + " hours, " + totalTime.Minutes.ToString() + " minutes" +
              //    " - Time Left: " + timeLeft.Days.ToString() + " days, " + timeLeft.Hours.ToString() + " hours, " + timeLeft.Minutes.ToString() + " minutes"));
              richTextBox1.BeginInvoke((MethodInvoker)(() =&gt; richTextBox1.Text = pct.ToString("N2") + "% (" + i + " of " + addressList.Count().ToString() + ") " + address.City + ", " + address.State + " " + address.ZipCode
                  + "\nEstimated Total Time: " + totalTime.ToString("h'h 'm'm 's's'") +
                  "\nTime Left: " + timeLeft.ToString("h'h 'm'm 's's'") +
                  "\nRunning Time: " + timeSinceStart.ToString("h'h 'm'm 's's'")));
              richTextBox1.BeginInvoke((MethodInvoker)(() =&gt; richTextBox1.SelectionStart = richTextBox1.Text.Length));
              richTextBox1.BeginInvoke((MethodInvoker)(() =&gt; richTextBox1.ScrollToCaret()));
              i++;
          }
          this.Invoke(new Action(() =&gt;
          {
              MessageBox.Show("Done!");
              btnChooseCSV.Enabled = true;
          }));
      }
      catch (Exception ex)
      {
          this.Invoke(new Action(() =&gt;
          {
              MessageBox.Show(ex.Message);
          }));
      }
  }
  
  private int InsertZipCode(string zipCode, int stateId)
  {
      string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["AddressInformation"].ConnectionString;
      SqlCeConnection connection = new SqlCeConnection(connstr);
      connection.Open();
  
      SqlCeCommand command = new SqlCeCommand("SELECT COUNT(*) FROM ZipCode WHERE ZipCode = @ZipCode", connection);
  
      command.Parameters.AddWithValue("ZipCode", zipCode);
  
      int result = (int)command.ExecuteScalar();
  
      // if nothing found, insert
      if (result == 0)
      {
          command = new SqlCeCommand("INSERT INTO ZipCode(ZipCode, StateId) VALUES(@ZipCode, @StateId)", connection);
          command.Parameters.AddWithValue("ZipCode", zipCode);
          command.Parameters.AddWithValue("StateId", stateId);
          command.ExecuteNonQuery();
  
          command = new SqlCeCommand("SELECT @@IDENTITY", connection);
      }
  
      if (result == 1)
      {
          command = new SqlCeCommand("SELECT ZipCodeId FROM ZipCode WHERE ZipCode = @ZipCode", connection);
          command.Parameters.AddWithValue("ZipCode", zipCode);
      }
  
      string test = command.ExecuteScalar().ToString();
      result = int.Parse(test);
  
      connection.Close();
      return result;
  }
  
  private int InsertCity(string city, int stateId)
  {
      string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["AddressInformation"].ConnectionString;
      SqlCeConnection connection = new SqlCeConnection(connstr);
      connection.Open();
  
      SqlCeCommand command = new SqlCeCommand("SELECT COUNT(*) FROM City WHERE CityName = @City", connection);
      command.Parameters.AddWithValue("City", city);
      int result = (int)command.ExecuteScalar();
  
      // if nothing found, insert
      if (result == 0)
      {
          command = new SqlCeCommand("INSERT INTO City(CityName, StateId) VALUES(@City, @StateId)", connection);
          command.Parameters.AddWithValue("City", city);
          command.Parameters.AddWithValue("StateId", stateId);
          command.ExecuteNonQuery();
  
          command = new SqlCeCommand("SELECT @@IDENTITY", connection);
      }
  
      if (result == 1)
      {
          command = new SqlCeCommand("SELECT CityId FROM City WHERE CityName = @City", connection);
          command.Parameters.AddWithValue("City", city);
      }
      string test = command.ExecuteScalar().ToString();
      result = int.Parse(test);
  
      connection.Close();
      return result;
  }
  
  private int InsertState(string state)
  {
      string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["AddressInformation"].ConnectionString;
      SqlCeConnection connection = new SqlCeConnection(connstr);
      connection.Open();
  
      SqlCeCommand command = new SqlCeCommand("SELECT COUNT(*) FROM State WHERE State = @State", connection);
      command.Parameters.AddWithValue("State", state);
      int result = (int)command.ExecuteScalar();
  
      // if nothing found, insert
      if (result == 0)
      {
          command = new SqlCeCommand("INSERT INTO State(State) VALUES(@State)", connection);
          command.Parameters.AddWithValue("State", state);
          command.ExecuteNonQuery();
  
          command = new SqlCeCommand("SELECT @@IDENTITY", connection);
      }
  
      if (result == 1)
      {
          command = new SqlCeCommand("SELECT StateId FROM State WHERE State = @State", connection);
          command.Parameters.AddWithValue("State", state);
      }
      string test = command.ExecuteScalar().ToString();
      result = int.Parse(test);
  
      connection.Close();
      return result;
  }
  
  private void UpdateRelationships(int zipCodeId, int cityId)
  {
      string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["AddressInformation"].ConnectionString;
      SqlCeConnection connection = new SqlCeConnection(connstr);
      connection.Open();
  
      SqlCeCommand command = new SqlCeCommand("INSERT INTO CityZipCode(CityId, ZipCodeId) VALUES(@CityId, @ZipCodeId)", connection);
  
      command.Parameters.AddWithValue("CityId", cityId);
      command.Parameters.AddWithValue("ZipCodeId", zipCodeId);
      command.ExecuteNonQuery();
  
      connection.Close();
  }
  </code></pre>
  
  <p><strong>Edit:</strong></p>
  
  <p>Just to clarify, I'm not just simply inserting each row of information from the CSV file. I'm changing how the data is laid out by inserting each respective item into separate tables and adding relationships between each entity. </p>
  
  <p>For example, a city can have multiple zip codes and a zip code can sometimes cover multiple cities so that would be represented by a many to many relationship. Cities and zip codes have only one state so that relationship is many to one.</p>
  
  <p>I have a table for cities, zip codes, and states. I also have a table for relating cities to zip codes. I will need to modify my relationship table schema to take into effect that cities with the same name may exist in multiple states. The relationship table should really be a set including the city, state and zip code and not just the city and zip code.</p>
  
  <p>My end goal is to distribute the SQL Server CE database with password protection with another application for city, state and zip code validation. I don't want to distribute the CSV database as anyone could change that to pass validation.</p>