VB 2005 Insert records into multiple tables

[AnikSol]

Hi All,

Not sure if this is the right forum to post this!
VB 2005 Prof, SQL server 2005, Windows application

Scenario: In a winform application, using SQL server authentication method
(not windows authentication), we have a text/binary file containing multiple
SQL statements (insert,update,delete commands) for multiple tables.
From the Winform , we need to create a process wherein we process this file
and update the database.
In case of a failure to process any sql statement in the middle of this file
(for whatsoever may be the reason , possibility is remote but should not be
excluded), then all the transaction need to be rolled back - meaning the
database state should be same as before we started the processing of this
file.

Query: What is the best method to carryout this functionality without using
DTS. Ofcourse, this process should be executed from a winform and from a
client machine.

Thanks.

 

[Otis Mukinfus]

Hi All,

Not sure if this is the right forum to post this!
VB 2005 Prof, SQL server 2005, Windows application

Scenario: In a winform application, using SQL server authentication method
(not windows authentication), we have a text/binary file containing multiple
SQL statements (insert,update,delete commands) for multiple tables.
From the Winform , we need to create a process wherein we process this file
and update the database.
In case of a failure to process any sql statement in the middle of this file
(for whatsoever may be the reason , possibility is remote but should not be
excluded), then all the transaction need to be rolled back - meaning the
database state should be same as before we started the processing of this
file.

Query: What is the best method to carryout this functionality without using
DTS. Ofcourse, this process should be executed from a winform and from a
client machine.

Thanks.

Write a file parsing routine and insert the data from the file in a transaction.

For file parsing help, see System.IO help and string.Split help. For processing
binary files help see the help files describing Binary Serialization.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[AnikSol]

Thanks.

Maybe I wan't clear in my query.

What I wanted to know was which method would be more appropriate -
- using BCP (since the file only contains valid SQL commands)
- use transaction scope (but somehow I am unable to do multiple commands in
the scope)
- use Adon.net transaction ( transaction.begin , commit, etc)

thanks.

 

[Wei Lu [MSFT]]

Hello Anik,

My suggestion is the Stored procedure in the SQL Server side or begin a
ADO.NET transcation in the client side are more appropriate.

The stored procedure could be used by other SQL Server object and all the
clients.

The ADO.NET transcation will need to implement in the client and you could
only use it in your client program.

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Wei Lu [MSFT]]

Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.