复制一个数据库

Question

列

的另一个消除重复我有一个数据库名为“ovhidhan_e_word”，它有一个名为Table1包含rows.But重复的表，我想复制Table1到一个名为下名为另一个数据库dic“另一个表ovidhan“但这次我不想rows.I的重复使用此代码：

string english_w = ""; 

     SqlConnection mssql_con = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Imon-Bayazid\Documents\ovhidhan_e_word.mdf;MultipleActiveResultSets=true;Integrated Security=True;Connect Timeout=30;User Instance=True"); 
     SqlConnection con2 = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Imon-Bayazid\Documents\ovidhan.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"); 

     mssql_con.Open(); 

     SqlCommand mssql_cmnd = new System.Data.SqlClient.SqlCommand("SELECT * from Table1", mssql_con); 

     SqlDataReader rd = mssql_cmnd.ExecuteReader(); 
     while (rd.Read()) 
     { 
      english_w = rd.GetString(0); 

      con2.Open(); 

      SqlCommand cmnd2 = new System.Data.SqlClient.SqlCommand("select * from dic where [email protected]",con2); 
      cmnd2.Parameters.AddWithValue("@h",english_w); 

      SqlDataReader r = cmnd2.ExecuteReader(); 
      if (r.Read()) 
      { } 
       else { 
        SqlCommand c = new System.Data.SqlClient.SqlCommand("insert into dic values(@k)", con2); ; 
        c.Parameters.AddWithValue("@k", english_w); 
        c.ExecuteNonQuery(); 
       } 

      con2.Close();  
     } 

     mssql_con.Close(); 

但它显示了异常”已经有与此命令必须先关闭相关联的打开的DataReader “。就行“c.ExecuteNonQuery（）;”

我该如何解决？ 另一个问题如何从mssql中的表中删除重复的行？

Answer 1

这是因为mssql_cmnd与您用于第一个SQLReader的SQLCommand相同。您可能想调用SQLCommand c：

c.Parameters.AddWithValue("@k", english_w); 
c.ExecuteNonQuery(); 

至于重复，您可以随时在读取数据时执行SELECT DISTINCT。请参阅下面的链接标题C：

http://msdn.microsoft.com/en-us/library/ms187731.aspx

Answer 2

我觉得应该是“C”，而不是“mssql_cmnd”，也建议有“使用”在你的连接，命令和DataReader对象的语句。

编辑

我没有测试的代码，但试图重构它与执行标量替换的数据读写。

 string english_w = string.Empty; 
     using (SqlConnection mssql_con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Imon-Bayazid\Documents\ovhidhan_e_word.mdf;MultipleActiveResultSets=true;Integrated Security=True;Connect Timeout=30;User Instance=True")) 
     { 
      using (SqlCommand mssql_cmnd = new SqlCommand("SELECT * from Table1", mssql_con)) 
      { 
       mssql_con.Open(); 
       using (SqlDataReader rd = mssql_cmnd.ExecuteReader()) 
       { 
        while (rd.Read()) 
        { 
         english_w = rd.GetString(0); 
         using (SqlConnection con2 = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Imon-Bayazid\Documents\ovidhan.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")) 
         { 
          con2.Open(); 
          using (SqlCommand cmnd2 = new SqlCommand("select top 1 * from dic where [email protected]", con2)) 
          { 
           cmnd2.Parameters.AddWithValue("@h", english_w); 
           object obj = cmnd2.ExecuteScalar(); 
           if (obj == null) 
           { 
            using (SqlCommand c = new SqlCommand("insert into dic values(@k)", con2)) 
            { 
             c.Parameters.AddWithValue("@k", english_w); 
             c.ExecuteNonQuery(); 
            } 
           } 
          } 
          con2.Close(); 
         } 
        } 
        rd.Close(); 
       } 
      } 
      mssql_con.Close(); 
     } 

Answer 3

你从哪里得到错误？ mssql_cmnd没有ExecuteNonQuery()如果是c，请在c.ExecuteNonQuery()之后尝试c.Dispose()。试试这个删除重复行

select * from table1 h1 where H1.ID in (select max(id) from table1 group by duplicate_row_id);