Click here to Skip to main content
15,847,264 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
SQL
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#
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();
        }


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!
Posted
Updated 2-Aug-17 11:11am
v3
Comments
RickZeeland 2-Aug-17 11:58am    
We can't tell if you don't show the code !
Rob¡nUters 2-Aug-17 12:00pm    
I have add the code on the question
OriginalGriff 2-Aug-17 12:02pm    
Without seeing the code you tried - rather than just the SQL you (presumably) inserted into the code - we are as in the dark as you are.

Use the "Improve question" widget to edit your question and provide better information.
Rob¡nUters 2-Aug-17 12:39pm    
I have add the code on the question. Thx! :-)
PIEBALDconsult 2-Aug-17 12:49pm    
Where do you execute the SQL statements?

1 solution

Hi,

Your connection to run an sql command is incomplete.

Here! How do you connect and execute an sql command.
C#
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();
}


if you want to know more.
Classe SqlConnection (System.Data.SqlClient)

System.Data.SqlClient Namespace
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900