1
\$\begingroup\$

I have the following code:

const String sqlSelect = "SELECT * FROM UserPasswords WHERE username='System Administrator';";
const String sqlInsert = "INSERT INTO UserPasswords VALUES (@username,@Password,@startDate,@Expired,@UserPasswordsID)";
using (var con1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["dataConnectionString"].ConnectionString))
using (var con2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["utilConnectionString"].ConnectionString))
{
  con1.Open();
  con2.Open();
  using (var selectCommand = new SqlCommand(sqlSelect, con1))
  {
    using (var reader = selectCommand.ExecuteReader())
    {
      if (reader.Read())
      {
        using (var insertCommand = new SqlCommand(sqlInsert, con2))
        {
          insertCommand.Parameters.Add(new SqlParameter("@username", reader.GetString(0)));
          insertCommand.Parameters.Add(new SqlParameter("@Password", reader.GetString(1)));
          insertCommand.Parameters.Add(new SqlParameter("@startDate", reader.GetDateTime(2)));
          insertCommand.Parameters.Add(new SqlParameter("@Expired", reader.GetBoolean(3)));
          insertCommand.Parameters.Add(new SqlParameter("@UserPasswordsID", reader.GetInt32(4)));
          insertCommand.ExecuteNonQuery();
        }
      }
    }
  }

All I need to do is copy the row with the 'System Administrator' info into another table as a backup. Though this code works is there someway to do this more elegantly?

\$\endgroup\$

2 Answers 2

4
\$\begingroup\$

Transferring Data Using SqlBulkCopy

private static void PerformBulkCopy()
{
    string connectionString =
            @"Server=localhost;Database=Northwind;Trusted_Connection=true";
    // get the source data
    using (SqlConnection sourceConnection = 
            new SqlConnection(connectionString))
    {
        SqlCommand myCommand =
            new SqlCommand("SELECT * FROM Products_Archive", sourceConnection);
        sourceConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader();

        // open the destination data
        using (SqlConnection destinationConnection =
                    new SqlConnection(connectionString))
        {
            // open the connection
            destinationConnection.Open();

            using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy(destinationConnection.ConnectionString))
            {
                bulkCopy.BatchSize = 500;
                bulkCopy.NotifyAfter = 1000;
                bulkCopy.SqlRowsCopied +=
                    new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                bulkCopy.DestinationTableName = "Products_Latest";
                bulkCopy.WriteToServer(reader);
            }
        }
        reader.Close();
    }
}

Solution #2: stored procedure

\$\endgroup\$
2
  • \$\begingroup\$ Excellent, thanks! This is much easier. I didn't look into SqlBulkCopy because I'm only dealing with one row, but it seems this is the way to do it. \$\endgroup\$
    – Family
    Apr 8, 2013 at 17:22
  • \$\begingroup\$ You could also try a stored procedure. \$\endgroup\$
    – Peter Kiss
    Apr 8, 2013 at 17:31
1
\$\begingroup\$

Why not just INSERT INTO newtable (SELECT * FROM UserPasswords WHERE username='System Administrator');?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.