Hi,
I have a relativley large/complex typed dataset that contains 7-8 tables and
some supporting relational tables (lookups) for many-to-many relations. A
good exampel would be a dataset that contained the whole Northwind database
with all tables and relations.
In my business logic I will retrive a populated instance of the dataset and
perform some operations (adding/deleting/updating of rows in various
tables...).
What is the best way to persist these changes back to the database (SQL
Server)?.
I would rely appreciate a link to a relativly complete exampel or if someone
could detail how/and what best practices is for a scenario like this one.
Regards
/Oscar 4 3810
Just use the SqlDataAdapter!
you can look for it on the msdn website msdn.microsoft. com/library en
search for the SqlDataAdapter class, there will be an example of how to
load and save data by using the DataAdapter class.
greetz
Oscar Thornell schreef: Hi,
I have a relativley large/complex typed dataset that contains 7-8 tables and some supporting relational tables (lookups) for many-to-many relations. A good exampel would be a dataset that contained the whole Northwind database with all tables and relations.
In my business logic I will retrive a populated instance of the dataset and perform some operations (adding/deleting/updating of rows in various tables...).
What is the best way to persist these changes back to the database (SQL Server)?.
I would rely appreciate a link to a relativly complete exampel or if someone could detail how/and what best practices is for a scenario like this one.
Regards /Oscar
Ok! Thanks Dries...
The SqlDataAdapter is of course the class that ADO.NET will use to transfer
data between a Dataset and persistent storage.
The core in my question is really what is the best practices to obtain the
SqlCommand instance for the current state of my dataset...
In other words: I don´t want to write the extreme amount of sql manually
that such an update would require...
///
I don´t want 1000 rows of this with coresponding sprocs:
// Establish our Insert, Delete, and Update commands
DBCommandWrappe r insertCommandWr apper =
db.GetStoredPro cCommandWrapper ("AddProduct ");
insertCommandWr apper.AddInPara meter("@Categor yID", DbType.Int32,
"CategoryID ", DataRowVersion. Current);
insertCommandWr apper.AddInPara meter("@UnitPri ce", DbType.Currency ,
"UnitPrice" , DataRowVersion. Current);
DBCommandWrappe r deleteCommandWr apper =
db.GetStoredPro cCommandWrapper ("DeleteProduct ");
deleteCommandWr apper.AddInPara meter("@Product ID", DbType.Int32, "ProductID" ,
DataRowVersion. Current);
DBCommandWrappe r updateCommandWr apper =
db.GetStoredPro cCommandWrapper ("UpdateProduct ");
updateCommandWr apper.AddInPara meter("@Product ID", DbType.Int32, "ProductID" ,
DataRowVersion. Current);
updateCommandWr apper.AddInPara meter("@LastUpd ate", DbType.DateTime ,
"LastUpdate ", DataRowVersion. Current);
///
So basically should I use the SqlCommandBuild er class here? Or what? What
support(or rather recommendations ..) is there for Entlib here?
Maybee I am just stupid here..but I would rely like some advice.. ->
pointers to best practices code here...
/Oscar
"Dries De Rudder" <dr***@bestopia .be> wrote in message
news:11******** *******@seven.k ulnet.kuleuven. ac.be... Just use the SqlDataAdapter!
you can look for it on the msdn website msdn.microsoft. com/library en search for the SqlDataAdapter class, there will be an example of how to load and save data by using the DataAdapter class.
greetz
Oscar Thornell schreef: Hi,
I have a relativley large/complex typed dataset that contains 7-8 tables and some supporting relational tables (lookups) for many-to-many relations. A good exampel would be a dataset that contained the whole Northwind database with all tables and relations.
In my business logic I will retrive a populated instance of the dataset and perform some operations (adding/deleting/updating of rows in various tables...).
What is the best way to persist these changes back to the database (SQL Server)?.
I would rely appreciate a link to a relativly complete exampel or if someone could detail how/and what best practices is for a scenario like this one.
Regards /Oscar
Oscar,
there is a way to simplify your problem the SqlCommandBuild er. This
class creates the InsertCommand, UpdateCommand and DeleteCommand for
you, based on the SelectCommand.
an example the way this works:
SqlConnection conn = new SqlConnection(. ..);
SqlCommand selectcmd = new SqlCommand("SEL ECT * FROM tablename",conn );
SqlDataAdapter da = new SqlDataAdapter( selectcmd);
SqlCommandBuild er cb = new SqlCommandBuild er(da);
and here everything is ready to be used.
the SqlCommandBuild er needs the SqlDataAdapter as a
constructor-parameter, it will use the SelectCommand property of the
SqlDataAdapter en create the three other Commands for you, and add them
to the SqlDataAdapter.
Be careful: Microsoft does advice to use the CommandBuilder only once,
just to create the sql-strings etc. For performance reasons you have to
retype the Insert, Update and Delete commands afterwards yourself.
so Oscar, I hope this is a more complete answer.
succes, Dries
Oscar Thornell schreef: Ok! Thanks Dries... The SqlDataAdapter is of course the class that ADO.NET will use to transfer data between a Dataset and persistent storage. The core in my question is really what is the best practices to obtain the SqlCommand instance for the current state of my dataset... In other words: I don´t want to write the extreme amount of sql manually that such an update would require...
/// I don´t want 1000 rows of this with coresponding sprocs: // Establish our Insert, Delete, and Update commands DBCommandWrappe r insertCommandWr apper = db.GetStoredPro cCommandWrapper ("AddProduct "); insertCommandWr apper.AddInPara meter("@Categor yID", DbType.Int32, "CategoryID ", DataRowVersion. Current); insertCommandWr apper.AddInPara meter("@UnitPri ce", DbType.Currency , "UnitPrice" , DataRowVersion. Current);
DBCommandWrappe r deleteCommandWr apper = db.GetStoredPro cCommandWrapper ("DeleteProduct "); deleteCommandWr apper.AddInPara meter("@Product ID", DbType.Int32, "ProductID" , DataRowVersion. Current);
DBCommandWrappe r updateCommandWr apper = db.GetStoredPro cCommandWrapper ("UpdateProduct "); updateCommandWr apper.AddInPara meter("@Product ID", DbType.Int32, "ProductID" , DataRowVersion. Current); updateCommandWr apper.AddInPara meter("@LastUpd ate", DbType.DateTime , "LastUpdate ", DataRowVersion. Current); ///
So basically should I use the SqlCommandBuild er class here? Or what? What support(or rather recommendations ..) is there for Entlib here?
Maybee I am just stupid here..but I would rely like some advice.. -> pointers to best practices code here...
/Oscar "Dries De Rudder" <dr***@bestopia .be> wrote in message news:11******** *******@seven.k ulnet.kuleuven. ac.be...
Just use the SqlDataAdapter!
you can look for it on the msdn website msdn.microsoft. com/library en search for the SqlDataAdapter class, there will be an example of how to load and save data by using the DataAdapter class.
greetz
Oscar Thornell schreef:
Hi,
I have a relativley large/complex typed dataset that contains 7-8 tables and some supporting relational tables (lookups) for many-to-many relations. A good exampel would be a dataset that contained the whole Northwind database with all tables and relations.
In my business logic I will retrive a populated instance of the dataset and perform some operations (adding/deleting/updating of rows in various tables...) .
What is the best way to persist these changes back to the database (SQL Server)?.
I would rely appreciate a link to a relativly complete exampel or if someone could detail how/and what best practices is for a scenario like this one.
Regards /Oscar
Thanks Dries!
Some of the issues with SqlCommandBuild er like the extra roundtrip for
gathering metadata I can avoid by using a typed dataset. And for the sql
with all the WHERE clauses in this case I can live with it...
The thing that rely bothers me though is the approach taken for the actual
update...
adapter.Update( Table1);
adapter.Update( Table2);
adapter.Update( Table3);
....
I realise that this is the approach choosen by MS but I don´t like it (or
maybee understand the beauty of it..).
I would like to be able to just do one larger:
adapter.Update( myDataSet);
Somehow I hoped that I could come up with schema for this... :-(
/Oscar
"Dries De Rudder" <dr***@bestopia .be> wrote in message
news:11******** *******@seven.k ulnet.kuleuven. ac.be... Oscar,
there is a way to simplify your problem the SqlCommandBuild er. This class creates the InsertCommand, UpdateCommand and DeleteCommand for you, based on the SelectCommand.
an example the way this works:
SqlConnection conn = new SqlConnection(. ..); SqlCommand selectcmd = new SqlCommand("SEL ECT * FROM tablename",conn );
SqlDataAdapter da = new SqlDataAdapter( selectcmd); SqlCommandBuild er cb = new SqlCommandBuild er(da);
and here everything is ready to be used.
the SqlCommandBuild er needs the SqlDataAdapter as a constructor-parameter, it will use the SelectCommand property of the SqlDataAdapter en create the three other Commands for you, and add them to the SqlDataAdapter.
Be careful: Microsoft does advice to use the CommandBuilder only once, just to create the sql-strings etc. For performance reasons you have to retype the Insert, Update and Delete commands afterwards yourself.
so Oscar, I hope this is a more complete answer.
succes, Dries
Oscar Thornell schreef: Ok! Thanks Dries... The SqlDataAdapter is of course the class that ADO.NET will use to transfer data between a Dataset and persistent storage. The core in my question is really what is the best practices to obtain the SqlCommand instance for the current state of my dataset... In other words: I don´t want to write the extreme amount of sql manually that such an update would require...
/// I don´t want 1000 rows of this with coresponding sprocs: // Establish our Insert, Delete, and Update commands DBCommandWrappe r insertCommandWr apper = db.GetStoredPro cCommandWrapper ("AddProduct "); insertCommandWr apper.AddInPara meter("@Categor yID", DbType.Int32, "CategoryID ", DataRowVersion. Current); insertCommandWr apper.AddInPara meter("@UnitPri ce", DbType.Currency , "UnitPrice" , DataRowVersion. Current);
DBCommandWrappe r deleteCommandWr apper = db.GetStoredPro cCommandWrapper ("DeleteProduct "); deleteCommandWr apper.AddInPara meter("@Product ID", DbType.Int32, "ProductID" , DataRowVersion. Current);
DBCommandWrappe r updateCommandWr apper = db.GetStoredPro cCommandWrapper ("UpdateProduct "); updateCommandWr apper.AddInPara meter("@Product ID", DbType.Int32, "ProductID" , DataRowVersion. Current); updateCommandWr apper.AddInPara meter("@LastUpd ate", DbType.DateTime , "LastUpdate ", DataRowVersion. Current); ///
So basically should I use the SqlCommandBuild er class here? Or what? What support(or rather recommendations ..) is there for Entlib here?
Maybee I am just stupid here..but I would rely like some advice.. -> pointers to best practices code here...
/Oscar "Dries De Rudder" <dr***@bestopia .be> wrote in message news:11******** *******@seven.k ulnet.kuleuven. ac.be...
Just use the SqlDataAdapter!
you can look for it on the msdn website msdn.microsoft. com/library en search for the SqlDataAdapter class, there will be an example of how to load and save data by using the DataAdapter class.
greetz
Oscar Thornell schreef:
Hi,
I have a relativley large/complex typed dataset that contains 7-8 tables and some supporting relational tables (lookups) for many-to-many relations . A good exampel would be a dataset that contained the whole Northwind database with all tables and relations.
In my business logic I will retrive a populated instance of the dataset and perform some operations (adding/deleting/updating of rows in various tables... ).
What is the best way to persist these changes back to the database (SQL Server)?.
I would rely appreciate a link to a relativly complete exampel or if someone could detail how/and what best practices is for a scenario like this one.
Regards /Oscar
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mythran |
last post by:
I downloaded the patterns and practices enterprise library and found that it
is much different for the Data library that I am used to (SqlHelper)..It's
not just an update but a total rewrite. Anywho, figured out how to get it
to work which wasn't too hard. What I haven't found and am having trouble
with is updating and inserting into a database. In the SqlHelper library,
there were methods such as ExecuteScalarTypedParams and such which...
|
by: David |
last post by:
I have been developing applications with Java for quite a while but I am new
to .NET development. I am trying to learn the ".NET way" of creating Strongly
Typed Objects from a database. The Enterprise Library seems pretty close to
what I am used to in Java but I am not sure how to create strongly-typed
DataSets. I see that I can use the LoadDataSet method from a
Microsoft.Practices.EnterpriseLibrary.Data.Database but I am not clear on how...
|
by: poifull |
last post by:
Is anyone using the Microsoft Enterprise Library? If yes, do you like it or
not?
Any feedback will be appreciated.
|
by: Mateusz [PEYN] Adamus |
last post by:
Hi
I have a Typed DataSet.
I've databinded it to some text controls on my form, controls show data
when I show record.
But now I would like to create a new record with this form, controls and
dataset. A add new row to dataset:
<C#>
|
by: Joe |
last post by:
Hello All:
I am trying to use the Enterprise Libraries created by Microsoft to update a
DB2 database and am stuck. I need to be able to Insert, Update and Delete
records from the DB2 datbase tables. I can use the UpdateDataSet method to
do this, but I am not sure how to use it. I have Googled the method and
haven't relly found anything helpful. Here's one question:
The method takes several parameters, among them an insert command,...
| |
by: TattedProgrammer |
last post by:
Hello All,
I am completely stuck, I have tried for an entire day to get a simple
procedure call to my Oracle Db via the enterprise Library 06. I am not
an oracle fan to start, but have to deal with it for now. I am only
trying to retrieve data into a dataset from the oracle procedure,
nothing fancy! Here is my 100th version of code I have.
|
by: -pb- |
last post by:
I am using .NET 2.0 along with Data Access Application Block of
Enterprise Library 2.0 in one of my wrapper class and what I observered
is once the connection is left open after execution of ExecuteDataSet
function of Database class.
I used Preformance counter for SQL user connection. When
DataFactroy.CreateDatabase is executed it create a connection but when
Database.ExecuteDataset doesn't closes the connection. Furthermore, if
I close...
|
by: nostradumbass77 |
last post by:
Using Enterprise Library 2.0 (Jan 06) and .NET 2.0 (VB.Net 2005)
Dim dbPF As Data.Common.DbProviderFactory
Try
dbPF =
Data.Common.DbProviderFactories.GetFactory("System.Data.OleDb")
db = New
Microsoft.Practices.EnterpriseLibrary.Data.GenericDatabase( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data
|
by: Dharmen Patel |
last post by:
I am using Enterprise Library 2006 , Data Access Application Blocks.
1. I am connecting to Oracle 9i database using DAAB.
2. I retrieve a dataset using the following code in VB.NET.
Dim sqlCommand As String = "SP_GET_BATCH"
Dim dbCommand As DbCommand
dbCommand = db.GetStoredProcCommand(sqlCommand)
ds = db.ExecuteDataSet(dbCommand)
3. I update the dataset using the following code.
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |