вторник, 20 марта 2012 г.

Transfering schema and data (SQL Server ), without SMO. Using only System.Data.SqlClient. Second part (Transfering data)



Hello to everyone. Here is the second part of our transfer methods without using SMO dlls.
I have new speed results. Something around 15 seconds. It's faster than previous method (using SMO which was 31 second). If you will have any questions please add comments or send me email. At next post I will write about migration data and structures from DBF  to the SQL Server. (x64 full support) Thanks.


 private void TestFunction2(string dbNameFrom, string dbNameTo)
    {
      string cnnString = "server=" + Server + ";user id=" + UserId
            + ";password=" + Password + ";Initial Catalog = " + dbNameFrom
            + ";Connect Timeout=30;Integrated Security=False;";

      DataTable tbData = null;
      using (System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(cnnString))
      {
        cnn.Open();
        for (int i = 0; i < dataTables.Length; i++)
        {
          string tbName = dataTables[i];
          string cmdText = " select * from " + tbName;
          tbData = new DataTable(tbName);
          using (System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmdText, cnn))
          {
            adapter.Fill(tbData);
          }       
          Insertrecord(tbData, dbNameTo);
          tbData = null;
        }
      }
    }



private void Insertrecord(DataTable tb, string dbNameTo)
    {
      string cnnStringTo = "server=" + Server + ";user id=" + UserId
            + ";password=" + Password + ";Initial Catalog = " + dbNameTo
            + ";Connect Timeout=30;Integrated Security=False;";

          using (System.Data.SqlClient.SqlConnection cnnTo = new System.Data.SqlClient.SqlConnection(cnnStringTo))
          {
            cnnTo.Open();

            string ccm = "delete from "+ tb.TableName;
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(ccm, cnnTo))
            {
              int result = cmd.ExecuteNonQuery();
              string rr = "";
              GenerateInsertStatement(ref rr, tb);
              cmd.CommandText = rr;
              for (int j = 0; j < tb.Rows.Count; j++)
              {
                for (int i = 0; i < tb.Columns.Count; i++)
                {
                  object val = tb.Rows[j][i];
                  cmd.Parameters.AddWithValue("@" + tb.Columns[i].ColumnName, val);
                }
                int res = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
              }
             
            }
          }    
    }

Комментариев нет:

Отправить комментарий