C＃从select [FIXED]插入 [英] C# insert into from select [FIXED]

问题描述

您好，



我正在为3个多个数据库中的查询寻找解决方案。



（1）首先我将截断数据库中的表 A A

（2）然后我将删除表 B 来自数据库 B

（3）下一步是插入数据库 B ，选择数据库 C

（4）然后更新数据库 A

（5）最后一步是插入数据库中的所有数据 A 进入数据库 C



SQL CODE

Hi there,

I'm looking for an solution for an query in 3 multiple databases.

(1) First I will truncate table A in database A
(2) Then I will delete table B from database B
(3) Next step is insert into database B with an select from database C
(4) Then update database A
(5) And the last step is to insert all the data from database A into database C

SQL CODE

TRUNCATE TABLE BB.dbo.Credentials -- (1)

DELETE FROM CA.dbo.Badge WHERE UserField1 = 'CABB' -- (2)

INSERT INTO BB.dbo.Credentials (3)
 (id,Badge,firstname,middlename,lastname,createddatetime)
SELECT u.ID, w.VALUE, u.FIRSTNAME, u.MIDDLENAME, u.LASTNAME, w.CREATEDDATETIME FROM MM.dbo.User_ u 
INNER JOIN MM.dbo.WiegandUserValue w
ON u.ID = w.OWNERID

UPDATE BB.dbo.Credentials (4)
SET enabled = 1, resident = 1, initload = 1, accgrp = 1, facility = 0, userfield1 = 'CABB', record = 1, val1 = 0, val3 = 0, val4 = 0, val5 = 0


INSERT INTO CA.dbo.Badge (5)(FrstName,Middlename,Lastname,Badge,Enabled,Resident,InitLoad,AGroup1,Facility,UserField1)
SELECT firstname,middlename,lastname,value,enabled,resident,initload,accgrp,facility,userfield1 FROM cabb.dbo.Credentials

c＃CODE

c# CODE

public void SQLQuery1()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringBiobridge());
            sc.Open();
 
            string query = string.Format("TRUNCATE TABLE BioBridge.dbo.Credentials");
 
            sc.Close();
        }
 
        public void SQLQuery2()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringCardAccess());
            sc.Open();
 
            string query = string.Format("DELETE FROM ca211LiveDB05242017_1213013.dbo.Badge WHERE UserField1 = 'CABB'");
 
            sc.Close();
        }
 
        public void SQLQuery3()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringBiobridge());
            sc.Open();
 
            string query = string.Format("INSERT INTO BioBridge.dbo.Credentials (id,value,firstname,middlename,lastname,createddatetime) " +
                "SELECT u.ID, w.VALUE, u.FIRSTNAME, u.MIDDLENAME, u.LASTNAME, w.CREATEDDATETIME FROM MorphoManager.dbo.User_ u " +
                "INNER JOIN MorphoManager.dbo.WiegandUserValue w ON u.ID = w.OWNERID");
 
            sc.Close();
        }
 
        public void SQLQuery4()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringBiobridge());
            sc.Open();
 
            string query = string.Format("UPDATE Biobridge.dbo.Credentials SET enabled = 1, resident = 1, initload = 1, accgrp = 1, facility = 0, userfield1 = 'CABB', record = 1, val1 = 0, val3 = 0, val4 = 0, val5 = 0");
 
            sc.Close();
        }
 
        public void SQLQuery5()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringCardAccess());
            sc.Open();
 
            string query = string.Format("INSERT INTO ca211LiveDB05242017_1213013.dbo.Badge (FrstName,Middlename,Lastname,Badge,Enabled,Resident,InitLoad,AGroup1,Facility,UserField1) " +
                "SELECT firstname, middlename, lastname, value, enabled, resident, initload, accgrp, facility, userfield1 FROM BioBridge.dbo.Credentials");
 
            sc.Close();
        }
 
        private void btnInsert_Click(object sender, EventArgs e)
        {
            SQLQuery1();
            SQLQuery2();
            SQLQuery3();
            SQLQuery4();
            SQLQuery5();
        }

我的尝试：



当我在SQL Management Studio中执行上述操作时，它可以工作，但是当我在C＃中通过button_click执行此操作时，它不起作用。当我创建一个messagebox.show并按下按钮时，消息框就会出现。



有人知道我做错了吗？



提前致谢！

What I have tried:

When I do above by the hand in SQL Management Studio it works, but when I do this in C# by an button_click it doesn't work. When I create an messagebox.show and I press on the button the messagebox will come up.

Does someone knows what I do wrong?

Thanks in advance!

推荐答案

您好，



您的连接运行sql命令是不完整的。



这里！如何连接和执行sql命令。

Hi,

Your connection to run an sql command is incomplete.

Here! How do you connect and execute an sql command.

public void SQLQuery1()
{
    SqlConnection sc = new SqlConnection(GetConnectionStringBiobridge());
    sc.Open();

    string query = string.Format("TRUNCATE TABLE BioBridge.dbo.Credentials");
            
    SqlCommand SQLcm = new SqlCommand();
    SQLcm.Connection = sc;
    SQLcm.CommandText = query;
    SQLcm.CommandType = CommandType.Text;
    SQLcm.ExecuteNonQuery();
    sc.Close();
}

如果你想了解更多。

Classe SqlConnection（System.Data.SqlClient）



System.Data.SqlClient命名空间

这篇关于C＃从select [FIXED]插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！