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
DELETE FROM CA.dbo.Badge WHERE UserField1 = 'CABB'
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
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!