C＃如何将数据库中的值插入另一个数据库 [英] C# how do I insert values from an database to another database

问题描述

您好，



我正在创建一个默认适用于.SDF数据库的应用程序。有可能将它更改为SQL我已经完成了一切正常我只会将数据库A中的信息插入数据库A.我创建了一个DataReader，他将在数据库B中插入1条记录，但我会记录所有记录在数据库中。有人知道我该如何解决这个问题吗？



我尝试了什么：



这是我的代码;

  void  InitUsers（）
 {
 
  string  query =   SELECT u。 firstname，u.middlename，u.lastname，u.ID，w.value FROM User_ u + 
   INNER JOIN WiegandUserValue w + 
   ON u.ID = w .OWNERID; 
 
 SqlConnection sqlUser =  new  SqlConnection（ 数据源= XXXX;初始目录= XXXXX;用户ID = XXXX;密码= XXXX）; 
 sqlUser.Open（）; 
 
 SqlCommand command =  new  SqlCommand（query，sqlUser）; 
 使用（SqlDataReader read = command.ExecuteReader（））
 {
  if （read.HasRows）
 {
  while （read.Read（））
 {
 ID = （读取[  ID]。ToString（））; 
 Firstname =（读取[  FirstName]。ToString（））; 
 Middlename =（读取[  MiddleName]。ToString（））; 
 Lastname =（读取[  LastName]。ToString（））; 
 Badge =（读取[  Value]。ToString（））; 
} 
} 
 
} 
 
 sqlUser.Close（）; 
 
} 
 
  public   void  SQLQuery（） 
 {
 SqlConnection sc =  new  SqlConnection（GetConnectionString（））; 
  //  尝试 
  / /   { 
 sc.Open（）; 
 
  string  FC =   0\" ; 
  string  AccGrp =   1; 
  //   string FC = TB_FacilityCode.Text;  
  //   string AccGrp = TB_AccGrp.Text;  
  //   int AccGrp =（（AccessGroup）CB_AccessGroup.SelectedItem）。GroupNr;  
  //   string query1 = string.Format（DELETE FROM Badge WHERE Userfield1 ='CABB'）;  
  string  query =  string  .Format（  DELETE FROM徽章WHERE Userfield1 ='CABB' + 
   INSERT INTO徽章（FrstName，Middlename，姓氏，徽章，已启用，常驻，初始化，AGroup1，设施，用户区域1） + 
   VALUES（@ firstname，@ middlename，@ lastname，@ badge，1,1,1 ，{0}，{1}，'CABB'） + 
   INSERT INTO SERVER（记录，Val1，Val2，Val3，Val4，Val5）VALUES（16,0，@ badge，0,0,0） 
 
，AccGrp  //   0  
，FC  //   1  
 
）; 
 SqlCommand command =  new  SqlCommand（query，sc）; 
 
 command.Parameters.AddWithValue（  @ firstname，名字？ （ object ）DBNull.Value）; 
 command.Parameters.AddWithValue（  @ middlename，Middlename ??（对象）DBNull.Value）; 
 command.Parameters.AddWithValue（  @ lastname，姓氏??（对象）DBNull.Value）; 
 command.Parameters.AddWithValue（  @ badge，徽章??（对象）DBNull.Value）; 
 command.ExecuteNonQuery（）; 
  //  }  
  / /   catch（exception ex） 
  //   { 
  //   MessageBox.Show（ex.Message）;  
  //  }  
 sc.Close（）; 
 
  private   void  B_Insert_Click（ object  sender，EventArgs e）
 {
 SQLQuery（）; 
} 
 

解决方案

只需使用循环。 ExecuteReader ，这是一个例子： [ sqldatareader ]

在此循环中使用 ExecuteNonQuery 执行另一个命令以插入读取进入目标数据库，请参阅： ExecuteNonQuery不要插入行数据库 - C＃| Dream.In.Code [ ^ ]

更优雅的方式可能是使用 SELECT INTO ，请参阅此处的示例：

插入新SQL SELECT INTO 的表格[ ^ ]

如果我记得很清楚，这也可用于跨数据库，请参见此示例： SQL SELECT INTO语句 [ ^ ]

Hi there,

I am creating an application that default will work with an .SDF database. There are posibilities to change it to SQL I have done that an all works fine only I will insert the information from database A to database A. I have created an DataReader and he will insert 1 record in database B but I'll all the records in the database. Does somebody knows how I can fix that?

What I have tried:

This is my code;

void InitUsers()
           {

               string query = "SELECT u.firstname, u.middlename, u.lastname, u.ID, w.value FROM User_ u " +
               "INNER JOIN WiegandUserValue w " +
               "ON u.ID = w.OWNERID ";

               SqlConnection sqlUser = new SqlConnection("Data Source=XXXX;Initial Catalog=XXXXX;User ID=XXXX;Password=XXXX");
               sqlUser.Open();

               SqlCommand command = new SqlCommand(query, sqlUser);
               using (SqlDataReader read = command.ExecuteReader())
               {
                   if (read.HasRows)
                   {
                       while (read.Read())
                       {
                           ID = (read["ID"].ToString());
                           Firstname = (read["FirstName"].ToString());
                           Middlename = (read["MiddleName"].ToString());
                           Lastname = (read["LastName"].ToString());
                           Badge = (read["Value"].ToString());
                       }
                   }

               }

               sqlUser.Close();

           }

public void SQLQuery()
       {
           SqlConnection sc = new SqlConnection(GetConnectionString());
           //try
           //{
               sc.Open();

               string FC = "0";
               string AccGrp = "1";
               //string FC = TB_FacilityCode.Text;
               //string AccGrp = TB_AccGrp.Text;
               //int AccGrp = ((AccessGroup)CB_AccessGroup.SelectedItem).GroupNr;
               //string query1 = string.Format("DELETE FROM Badge WHERE Userfield1 = 'CABB'");
               string query = string.Format("DELETE FROM Badge WHERE Userfield1 = 'CABB'" +
                   "INSERT INTO Badge (FrstName,Middlename,Lastname,Badge,Enabled,Resident,Initload,AGroup1,Facility,Userfield1) " +
                   "VALUES(@firstname,@middlename,@lastname,@badge,1,1,1,{0},{1},'CABB')" +
                   "INSERT INTO SERVER (Record,Val1,Val2,Val3,Val4,Val5) VALUES(16,0,@badge,0,0,0)"

                               , AccGrp        //0
                               , FC            //1

                   );
               SqlCommand command = new SqlCommand(query, sc);

               command.Parameters.AddWithValue("@firstname", Firstname ?? (object)DBNull.Value);
               command.Parameters.AddWithValue("@middlename", Middlename ?? (object)DBNull.Value);
               command.Parameters.AddWithValue("@lastname", Lastname ?? (object)DBNull.Value);
               command.Parameters.AddWithValue("@badge", Badge ?? (object)DBNull.Value);
               command.ExecuteNonQuery();
           //}
           //catch (Exception ex)
           //{
           //    MessageBox.Show(ex.Message);
           //}
           sc.Close();

     private void B_Insert_Click(object sender, EventArgs e)
       {
           SQLQuery();
       }

解决方案

Just use a loop for .ExecuteReader, here is an example: [sqldatareader]
In this loop execute another command with ExecuteNonQuery to insert the read row into the target database, see: ExecuteNonQuery Don't Insert Rows Into Database - C# | Dream.In.Code[^]

A more elegant way maybe is to use SELECT INTO, see example here:
INSERT INTO new SQL table with SELECT INTO[^]
If I remember well, this can also be used across databases, see example here: SQL SELECT INTO Statement[^]

这篇关于C＃如何将数据库中的值插入另一个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！