SQL Server dataset read from database A and write to database B

  • Thread starter Thread starter RWC
  • Start date Start date
R

RWC

Hello,

What I want I fairly simple (I guess), but I can't work it out.

In one application I export data from a SQL Server database to an XML
file. I have used the DataSet.WriteXML method to do this. My XML file
looks like this (simplified):

<?xml version="1.0" standalone="yes"?>
<brokers>
<broker>
<broker_number>1</broker_number>
<broker_name>Peter</broker_name>
</broker>
<broker>
<broker_number>2</broker_number>
<broker_name>Eric</broker_name>
</broker>
</brokers>

As you can guess this is just an export from a table with two fields:
broker_nummer and broker_name.

Now I want to fill the table "broker" in another SQL Server database.
I can not read from the XML file directly, but I have to read from a
string (which is just the content of the XML file). In the new
database the field names are slightly different: broker_number is
called BrokerId and broker_name is called Name

I have produced the following:

private bool ImportXML(string docIn, ref string ErrorMsg)
{

const string selectquery = "SELECT * FROM broker WHERE BrokerID
= @BrokerID";
const string insertquery = "INSERT INTO broker (Name) VALUES
(@Name)";
const string updatequery = "UPDATE broker SET BrokerID =
@BrokerID, Name= @Name WHERE BrokerID = @BrokerID";
const string deletequery = "DELETE FROM broker WHERE BrokerID =
@BrokerID";


try
{
//create a custom adapter
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

//fill dataset with XML-data
DataSet ds = new DataSet();
//we need a string reader to read from string
StringReader sr = new StringReader(docIn);

// Create the SelectCommand
SqlCommand query = new SqlCommand(selectquery, m_Connection);
query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
sqlDataAdapter.SelectCommand = query;

// Create the DeleteCommand
query = new SqlCommand(deletequery, m_Connection);
query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
sqlDataAdapter.DeleteCommand = query;

// Create the InsertCommand.
query = new SqlCommand(insertquery, m_Connection);
query.Parameters.Add("@Name", SqlDbType.NVarChar, 50,
"broker_name");
sqlDataAdapter.InsertCommand = query;

// Create the UpdateCommand.
query = new SqlCommand(updatequery, m_Connection);
query.Parameters.Add("@pBrokerID", SqlDbType.Int);
query.Parameters["@pBrokerID"].SourceVersion =
DataRowVersion.Current;
query.Parameters["@pBrokerID"].SourceColumn = "broker_number";

query.Parameters.Add("@pNaam", SqlDbType.VarChar);
query.Parameters["@pNaam"].SourceVersion =
DataRowVersion.Current;
query.Parameters["@pNaam"].SourceColumn = "broker_naam";

sqlDataAdapter.UpdateCommand = query;

ds.ReadXml(sr);

sqlDataAdapter.Update(ds, "Broker");

return true;
}
catch (Exception e)
{
ErrorMsg = e.Message;
return false;
}
}

I have got a few questions:

1. Is this the right approach?
2. Are the queries correct?
3. I am inserting data in an empty table. Why I keep getting this
message after inseting 1 record?

"Violation of UNIQUE KEY constraint 'IX_Broker_1'. Cannot insert
duplicate key in object 'dbo.broker'.\r\nThe statement has been
terminated."

Can you help me out. Thanks.
 
Hello,

What I want I fairly simple (I guess), but I can't work it out.

In one application I export data from a SQL Server database to an XML
file. I have used the DataSet.WriteXML method to do this. My XML file
looks like this (simplified):

<?xml version="1.0" standalone="yes"?>
<brokers>
<broker>
<broker_number>1</broker_number>
<broker_name>Peter</broker_name>
</broker>
<broker>
<broker_number>2</broker_number>
<broker_name>Eric</broker_name>
</broker>
</brokers>

As you can guess this is just an export from a table with two fields:
broker_nummer and broker_name.

Now I want to fill the table "broker" in another SQL Server database.
I can not read from the XML file directly, but I have to read from a
string (which is just the content of the XML file). In the new
database the field names are slightly different: broker_number is
called BrokerId and broker_name is called Name

I have produced the following:

private bool ImportXML(string docIn, ref string ErrorMsg)
{

const string selectquery = "SELECT * FROM broker WHERE BrokerID
= @BrokerID";
const string insertquery = "INSERT INTO broker (Name) VALUES
(@Name)";
const string updatequery = "UPDATE broker SET BrokerID =
@BrokerID, Name= @Name WHERE BrokerID = @BrokerID";
const string deletequery = "DELETE FROM broker WHERE BrokerID =
@BrokerID";


try
{
//create a custom adapter
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

//fill dataset with XML-data
DataSet ds = new DataSet();
//we need a string reader to read from string
StringReader sr = new StringReader(docIn);

// Create the SelectCommand
SqlCommand query = new SqlCommand(selectquery, m_Connection);
query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
sqlDataAdapter.SelectCommand = query;

// Create the DeleteCommand
query = new SqlCommand(deletequery, m_Connection);
query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
sqlDataAdapter.DeleteCommand = query;

// Create the InsertCommand.
query = new SqlCommand(insertquery, m_Connection);
query.Parameters.Add("@Name", SqlDbType.NVarChar, 50,
"broker_name");
sqlDataAdapter.InsertCommand = query;

// Create the UpdateCommand.
query = new SqlCommand(updatequery, m_Connection);
query.Parameters.Add("@pBrokerID", SqlDbType.Int);
query.Parameters["@pBrokerID"].SourceVersion =
DataRowVersion.Current;
query.Parameters["@pBrokerID"].SourceColumn = "broker_number";

query.Parameters.Add("@pNaam", SqlDbType.VarChar);
query.Parameters["@pNaam"].SourceVersion =
DataRowVersion.Current;
query.Parameters["@pNaam"].SourceColumn = "broker_naam";

sqlDataAdapter.UpdateCommand = query;

ds.ReadXml(sr);

sqlDataAdapter.Update(ds, "Broker");

return true;
}
catch (Exception e)
{
ErrorMsg = e.Message;
return false;
}
}

I have got a few questions:

1. Is this the right approach?
2. Are the queries correct?
3. I am inserting data in an empty table. Why I keep getting this
message after inseting 1 record?

"Violation of UNIQUE KEY constraint 'IX_Broker_1'. Cannot insert
duplicate key in object 'dbo.broker'.\r\nThe statement has been
terminated."

Can you help me out. Thanks.
Click to expand...

I think you are trying to insert the primary key from the original table into
the primary key of the new table, which may be a an autoincrement column. If
that's the case you have two options.

1. Don't insert the primary key of the first table into the second.

or

2. Turn off the autoincrement of the second table before doing the inserts and
turn it back on when you are done.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
If the overall purpose is to move data from one SQL Server to another you
should use SqlBulkCopy, DTS, SSIS or somesuch. You can also link the two
servers and do a direct INSERT from one to the other.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

RWC said:
Hello,

What I want I fairly simple (I guess), but I can't work it out.

In one application I export data from a SQL Server database to an XML
file. I have used the DataSet.WriteXML method to do this. My XML file
looks like this (simplified):

<?xml version="1.0" standalone="yes"?>
<brokers>
<broker>
<broker_number>1</broker_number>
<broker_name>Peter</broker_name>
</broker>
<broker>
<broker_number>2</broker_number>
<broker_name>Eric</broker_name>
</broker>
</brokers>

As you can guess this is just an export from a table with two fields:
broker_nummer and broker_name.

Now I want to fill the table "broker" in another SQL Server database.
I can not read from the XML file directly, but I have to read from a
string (which is just the content of the XML file). In the new
database the field names are slightly different: broker_number is
called BrokerId and broker_name is called Name

I have produced the following:

private bool ImportXML(string docIn, ref string ErrorMsg)
{

const string selectquery = "SELECT * FROM broker WHERE BrokerID
= @BrokerID";
const string insertquery = "INSERT INTO broker (Name) VALUES
(@Name)";
const string updatequery = "UPDATE broker SET BrokerID =
@BrokerID, Name= @Name WHERE BrokerID = @BrokerID";
const string deletequery = "DELETE FROM broker WHERE BrokerID =
@BrokerID";


try
{
//create a custom adapter
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

//fill dataset with XML-data
DataSet ds = new DataSet();
//we need a string reader to read from string
StringReader sr = new StringReader(docIn);

// Create the SelectCommand
SqlCommand query = new SqlCommand(selectquery, m_Connection);
query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
sqlDataAdapter.SelectCommand = query;

// Create the DeleteCommand
query = new SqlCommand(deletequery, m_Connection);
query.Parameters.Add("@BrokerId", SqlDbType.Int, 4);
sqlDataAdapter.DeleteCommand = query;

// Create the InsertCommand.
query = new SqlCommand(insertquery, m_Connection);
query.Parameters.Add("@Name", SqlDbType.NVarChar, 50,
"broker_name");
sqlDataAdapter.InsertCommand = query;

// Create the UpdateCommand.
query = new SqlCommand(updatequery, m_Connection);
query.Parameters.Add("@pBrokerID", SqlDbType.Int);
query.Parameters["@pBrokerID"].SourceVersion =
DataRowVersion.Current;
query.Parameters["@pBrokerID"].SourceColumn = "broker_number";

query.Parameters.Add("@pNaam", SqlDbType.VarChar);
query.Parameters["@pNaam"].SourceVersion =
DataRowVersion.Current;
query.Parameters["@pNaam"].SourceColumn = "broker_naam";

sqlDataAdapter.UpdateCommand = query;

ds.ReadXml(sr);

sqlDataAdapter.Update(ds, "Broker");

return true;
}
catch (Exception e)
{
ErrorMsg = e.Message;
return false;
}
}

I have got a few questions:

1. Is this the right approach?
2. Are the queries correct?
3. I am inserting data in an empty table. Why I keep getting this
message after inseting 1 record?

"Violation of UNIQUE KEY constraint 'IX_Broker_1'. Cannot insert
duplicate key in object 'dbo.broker'.\r\nThe statement has been
terminated."

Can you help me out. Thanks.
Click to expand...
 
Back
Top