Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>sql
Showing posts with label updating. Show all posts
Showing posts with label updating. Show all posts
Thursday, March 22, 2012
DataSet Performance
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
--
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
--
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
--
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
--
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at, www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant. :)
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at, www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
DataSet Performance
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
Use a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegr oups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>
|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
|||You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor
|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
Use a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegr oups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>
|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
|||You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor
|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
DataSet Performance
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerUse a stored procedure to do the update. This sounds very much like you
are using a table as an array though, which isn't generally a good way
to model data in SQL.
David Portas
SQL Server MVP
--|||I've already tried it with stored procedures and the performance isn't
better.
The problem on the data model is that the 100 rows and 100 columns are
representing a fincance plan. So each column must save additional
information (like formats, formula, ...).
So I have a table for each row (called "Position") and this table references
another table which stores the columns (called "PosVal") of the row. With
this data model I've the possibility that the table "PosVal" can reference
other tables which contains the format, formulas...
Or this there any other way to model this?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109333983.806317.283120@.g14g2000cwa.googlegroups.com...
> Use a stored procedure to do the update. This sounds very much like you
> are using a table as an array though, which isn't generally a good way
> to model data in SQL.
> --
> David Portas
> SQL Server MVP
> --
>|||It seems like you are trying to model an abstraction ("rows", "columns"
and "formulae" from a hypothetical spreadsheet) instead of modelling
the actual data. Isn't your metadata static enough to create a proper
relational representation of it? If not then I suggest you need a
middle tier to present this data. The back end may be largely
irrelevent - I'm not sure just what benefit you are hoping to get from
using SQL Server as the data store for this.
If you do have some real data to model, then A) Normalize your tables,
B) post a CREATE TABLE statement and your stored proc. Since your proc
can update an entire row at a time I would have expected 100 updates to
outperform 10,000 but that largely depends on how you are doing the
updates and what your data looks like.
David Portas
SQL Server MVP
--|||Klaus Aschenbrenner wrote:
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter,
> this generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which
> reduces the UPDATE statements? Or how can I handle such big updates
> with SQL Server?
>
Are you using the CommandBuilder to generate the code? It probably makes
more sense to write the code yourself.
You stated in a subsequent reply that you created a stored procedure to do
the update but that it did not improve performance. Could you elaborate on
what the procedure did? I'm assuming you created a procedure that accepted
parameters for each of the 100 columns and did the update for an entire row
at a time, requiring 100 calls to the procedure instead of 10000 calls to a
procedure that did 1 column at a time...
Is that correct?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Klaus,
You can use SQLXML that comes with MDAC to reduce the number of hits going
to your database. You can then send an updategram. With 2.0, you have a
graceful upgrade to the Managed SQLXML driver, so it's not a deadend.
This is obviously SQL Server specific, but Oracle has other such equivalent
solutions.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:#hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
by using the tool correctly and using the native enhancements to boost
performance. That is not to say that an RDBMS can not be fast, on the
contrary; however, that is not its chief purpose.
If performance is your ONLY concern, use a flat file or an XML file.
You use an RDBMS to MODEL THE DATA, so that others can query it in a myriad
of ways and garauntee that there results are accurate. Therefore, you have
to use the RELATIONAL rules to model your data before you build the physical
database and constrain it in order to provide DATA INTEGRITY. It is this
integrity that you build on an RDBMS system. The system is optimized for
performance, but only after providing the foundation, a relational database
properly constrained.
Sincerely,
Anthony Thomas
"Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
Hi!
I've a DataTable with 100 rows and 100 columns. Then I'm updating each
column in each row. When I call the Update method on the DataAdapter, this
generates 10000 UPDATE statements.
Are there any solutions how I can create a better solution, which reduces
the UPDATE statements? Or how can I handle such big updates with SQL Server?
Thanks
Klaus Aschenbrenner
MVP Visual C#
www.csharp.at,www.anecon.com
http://weblogs.asp.net/klaus.aschenbrenner|||You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>|||Klaus,
The rows are updated depending on the rowstate.
It can be that when you start that the rowstate are set to "added" while you
don't want to update them all. (The dataadapter.fill set them automaticly
to unchanged when you have not set the property for that to false, however
when you load them by hand, by instance using a datareader they are all set
to "added".).
You can by instance in the case of that filling with the datareader set the
rowstate of all rows to unchanged by ds.acceptchanges
Maybe this helps?
Cor|||If only consultants would be so highly critical.
Sincerely,
Anthony Thomas
"Sahil Malik" <contactmethrumyblog@.nospam.com> wrote in message
news:%23Phk7IcHFHA.2984@.TK2MSFTNGP15.phx.gbl...
You must be a consultant.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIk#AyDHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> First and foremost, an RDBMS is NOT FOR PERFORMANCE. You gain performance
> by using the tool correctly and using the native enhancements to boost
> performance. That is not to say that an RDBMS can not be fast, on the
> contrary; however, that is not its chief purpose.
> If performance is your ONLY concern, use a flat file or an XML file.
> You use an RDBMS to MODEL THE DATA, so that others can query it in a
myriad
> of ways and garauntee that there results are accurate. Therefore, you
have
> to use the RELATIONAL rules to model your data before you build the
physical
> database and constrain it in order to provide DATA INTEGRITY. It is this
> integrity that you build on an RDBMS system. The system is optimized for
> performance, but only after providing the foundation, a relational
database
> properly constrained.
> Sincerely,
>
> Anthony Thomas
>
>
> --
> "Klaus Aschenbrenner" <Klaus.Aschenbrenner@.anecon.com> wrote in message
> news:%23hBwlNzGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi!
> I've a DataTable with 100 rows and 100 columns. Then I'm updating each
> column in each row. When I call the Update method on the DataAdapter, this
> generates 10000 UPDATE statements.
> Are there any solutions how I can create a better solution, which reduces
> the UPDATE statements? Or how can I handle such big updates with SQL
Server?
> Thanks
> Klaus Aschenbrenner
> MVP Visual C#
> www.csharp.at,www.anecon.com
> http://weblogs.asp.net/klaus.aschenbrenner
>
Sunday, March 11, 2012
DataGridView/SQL Express - Updating record using seperate form
Hi,
I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:
Level 0 Level 1 Level 2
1:N 1:N
Furniture --> Component --> Component
You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.
I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.
My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.
What would be the proper way to do it? I would certainly appreciate to hear you view on this.
Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?
Thanks in advance,
Stphane
Hi Stephane,
It sounds like you're looking for a Master/Detail implementation. There are a number of topics covering this in MSDN, start with the first three hits on this search.
Mike
|||Hi Mike,
I followed links supplied. For what I could see, they are only showing how to put 2 DataGridView controls, master and detail, on same windows form and have data displayed accordingly. I already had a look at this technique. Unfortunately, this is not exactly what I'm looking for.
I'm definitely looking to be able to get/highlight a record (a row...) from a DataGridView control and then be able to perform any insert/update/delete operation, update its content using a new windows form which will be my "updating" form where user will be able to supply any required infos for that specific record/row instead of doing it directly from within DataGridView control.
Any hint or useful links?
Thanks again for your help,
Stphane
|||Hi Stephane,
First off, remember this is the SQL Express forum and you're asking about a VS component so you might get a better answer in one of the VS forums. I'd suggest the .NET Data Access forum as a place to start, but language specific forums or Windows Forms forums may also be usefull.
That said, if you start with this topic about using two DataGridViews but split the two DGs between two forms, it should just be a matter of writing some code to react to an action on the Master form. Check out the documentation for this control, there are a number of Properties that allow you to determin the selection as well as some events (i.e. OnClick) that should let you respond to user action in the control.
I'm guessing that if you ask around in the VS forums someone will have already written a framework to do this.
Mike
|||
Hi Mike,
Thanks for clarification. I must admit that I get quite lost when trying to find appropriate forum... Any document or article listing all forums and their purposes? Would certainly help!
I followed your link but anything I can find on MSDN is an example using 2 datagridviews on same form. For my case, I just try to find a way to, first, select a SQL table record from datagridview in form1, open a new form, form2, where I will enable user to update record's content. Scenario I illustrated could also include a second datagridview in form2.
Now browsing through the many forums available, it seems that there is one discussing specific issues regarding datagridview:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1
Hope I will be able to get any hint from there!
Thanks for your help,
Stphane
|||Hi Stephanie,
You might be confused because many of the VS examples and Walkthroughs, including this one, do not walk you through using the VS UI to create the example, they use a 100% code based solution. The sample that I linked to provides code that programatically creates a form with two DataGridViews on it, attaches those DGVs to BindingSources and then hooks them up to the Northwind Database.
VS doesn't have many help topics that walk through specific tasks using the UI tools. I looked around on http://windowsforms.com and didn't really find anything that discusses how to use the UI to do this either, but you're welcome to take a look yourself as you may find some interesting material there.
There isn't a document listing all the fourms, but this page has them all. Each has a short description which hopefully describes it's use.
Mike
|||Hi again Mike,
Thanks for links. I will keep looking around. I got my hand on a book which seems to give some hints about DGVs. Hope I will find something...
Stphane
DataGridView/SQL Express - Updating record using seperate form
Hi,
I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:
Level 0 Level 1 Level 2
1:N 1:N
Furniture --> Component --> Component
You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.
I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.
My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.
What would be the proper way to do it? I would certainly appreciate to hear you view on this.
Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?
Thanks in advance,
Stphane
Hi Stephane,
It sounds like you're looking for a Master/Detail implementation. There are a number of topics covering this in MSDN, start with the first three hits on this search.
Mike
|||Hi Mike,
I followed links supplied. For what I could see, they are only showing how to put 2 DataGridView controls, master and detail, on same windows form and have data displayed accordingly. I already had a look at this technique. Unfortunately, this is not exactly what I'm looking for.
I'm definitely looking to be able to get/highlight a record (a row...) from a DataGridView control and then be able to perform any insert/update/delete operation, update its content using a new windows form which will be my "updating" form where user will be able to supply any required infos for that specific record/row instead of doing it directly from within DataGridView control.
Any hint or useful links?
Thanks again for your help,
Stphane
|||Hi Stephane,
First off, remember this is the SQL Express forum and you're asking about a VS component so you might get a better answer in one of the VS forums. I'd suggest the .NET Data Access forum as a place to start, but language specific forums or Windows Forms forums may also be usefull.
That said, if you start with this topic about using two DataGridViews but split the two DGs between two forms, it should just be a matter of writing some code to react to an action on the Master form. Check out the documentation for this control, there are a number of Properties that allow you to determin the selection as well as some events (i.e. OnClick) that should let you respond to user action in the control.
I'm guessing that if you ask around in the VS forums someone will have already written a framework to do this.
Mike
|||
Hi Mike,
Thanks for clarification. I must admit that I get quite lost when trying to find appropriate forum... Any document or article listing all forums and their purposes? Would certainly help!
I followed your link but anything I can find on MSDN is an example using 2 datagridviews on same form. For my case, I just try to find a way to, first, select a SQL table record from datagridview in form1, open a new form, form2, where I will enable user to update record's content. Scenario I illustrated could also include a second datagridview in form2.
Now browsing through the many forums available, it seems that there is one discussing specific issues regarding datagridview:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1
Hope I will be able to get any hint from there!
Thanks for your help,
Stphane
|||Hi Stephanie,
You might be confused because many of the VS examples and Walkthroughs, including this one, do not walk you through using the VS UI to create the example, they use a 100% code based solution. The sample that I linked to provides code that programatically creates a form with two DataGridViews on it, attaches those DGVs to BindingSources and then hooks them up to the Northwind Database.
VS doesn't have many help topics that walk through specific tasks using the UI tools. I looked around on http://windowsforms.com and didn't really find anything that discusses how to use the UI to do this either, but you're welcome to take a look yourself as you may find some interesting material there.
There isn't a document listing all the fourms, but this page has them all. Each has a short description which hopefully describes it's use.
Mike
|||Hi again Mike,
Thanks for links. I will keep looking around. I got my hand on a book which seems to give some hints about DGVs. Hope I will find something...
Stphane
DataGrid DataSet DataAdaptor DataBase problem.
My dataset is not updating my database after the user modifies the datagrid.
I populate my data with the load sub below.
In the Save Sub (below), I have generated my DataSet 'dataSet11' from my
DataAdaptor 'SqlDataAdapter1' and the DataConnection 'SqlConnection1' and
they all seem to be connected correctly. But my data does not update.
the dataAdaptor is configured for Insert/Update/delete and the datagrid
datasource is DataSet11.TableName. Any ideas?
Private Sub Thresholds_Load(ByVal sender As....
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
& Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
cmdSelect.Connection = cn
Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select
* from MISRE_Threshold", cn)
Dim dsThreshold As DataSet = New DataSet
' fill dataset
da.Fill(dsThreshold, "MISRE_Threshold")
'Attach DataSet to DataGrid
dgThreshold.DataSource = dsThreshold.DefaultViewManager
Catch ex As Exception
MessageBox.Show("Error: Could not establish database connection")
End Try
Private Sub btnSave_Click(ByVal sender....
SqlDataAdapter1.Update(DataSet11)
EndSubThe DataAdapter InsertCommand/UpdateCommand/DeleteCommand properties need to
be set in order for the DataAdapter to execute the appropriate commands to
update your table. The SqlCommandBuilder can be used to generate the needed
commands (if you have a primary key) or you can create those the commands
yourself.
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:392099C1-6587-46B7-BD60-A20AD3C884AB@.microsoft.com...
> My dataset is not updating my database after the user modifies the
> datagrid.
> I populate my data with the load sub below.
> In the Save Sub (below), I have generated my DataSet 'dataSet11' from my
> DataAdaptor 'SqlDataAdapter1' and the DataConnection 'SqlConnection1' and
> they all seem to be connected correctly. But my data does not update.
> the dataAdaptor is configured for Insert/Update/delete and the datagrid
> datasource is DataSet11.TableName. Any ideas?
> Private Sub Thresholds_Load(ByVal sender As....
> Try
> cn = New SqlClient.SqlConnection("user id=" & UserName.Text &
> ";password="
> & Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
> cn.Open()
> cmdSelect.Connection = cn
> Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select
> * from MISRE_Threshold", cn)
> Dim dsThreshold As DataSet = New DataSet
> ' fill dataset
> da.Fill(dsThreshold, "MISRE_Threshold")
> 'Attach DataSet to DataGrid
> dgThreshold.DataSource = dsThreshold.DefaultViewManager
> Catch ex As Exception
> MessageBox.Show("Error: Could not establish database
> connection")
> End Try
>
> Private Sub btnSave_Click(ByVal sender....
> SqlDataAdapter1.Update(DataSet11)
> EndSub|||They are configured, that's why i'm stumped!
Its the update one i'm interested in as follows:
UPDATE MISRE_Threshold
SET ThresholdType = @.ThresholdType, Threshold = @.Threshold,
Threshold_Flag = @.Threshold_Flag, Actual = @.Actual, Fail = @.Fail,
Category = @.Category, ID = @.ID
WHERE (ThresholdType = @.Original_ThresholdType) AND (Actual =
@.Original_Actual) AND (Category = @.Original_Category OR
@.Original_Category IS NULL AND Category IS NULL) AND
(Fail = @.Original_Fail) AND (ID = @.Original_ID OR
@.Original_ID IS NULL AND ID IS NULL) AND (Threshold =
@.Original_Threshold OR
@.Original_Threshold IS NULL AND Threshold IS NULL) AND
(Threshold_Flag = @.Original_Threshold_Flag OR
@.Original_Threshold_Flag IS NULL AND Threshold_Flag IS
NULL);
SELECT ThresholdType, Threshold,
Threshold_Flag, Actual, Fail, Category, ID
FROM MISRE_Threshold
WHERE (ThresholdType = @.ThresholdType)|||> They are configured, that's why i'm stumped!
The original code you posted instantiates and uses a new untyped dataset:
Dim dsThreshold As DataSet = New DataSet
However, your update routine uses the DataAdapter and DataSet generated by
the windows form designer:
SqlDataAdapter1.Update(DataSet11)
DataSet11 is never filled in the code snippets you posted so it will always
be empty. I believe your intention is to fill DataSet11 in the load
routine:
SqlDataAdapter1.Fill(DataSet11)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E94A87F0-9D2B-4DB1-85E3-5AE7635656DA@.microsoft.com...
> They are configured, that's why i'm stumped!
> Its the update one i'm interested in as follows:
>
> UPDATE MISRE_Threshold
> SET ThresholdType = @.ThresholdType, Threshold = @.Threshold,
> Threshold_Flag = @.Threshold_Flag, Actual = @.Actual, Fail = @.Fail,
> Category = @.Category, ID = @.ID
> WHERE (ThresholdType = @.Original_ThresholdType) AND (Actual =
> @.Original_Actual) AND (Category = @.Original_Category OR
> @.Original_Category IS NULL AND Category IS NULL) AND
> (Fail = @.Original_Fail) AND (ID = @.Original_ID OR
> @.Original_ID IS NULL AND ID IS NULL) AND (Threshold =
> @.Original_Threshold OR
> @.Original_Threshold IS NULL AND Threshold IS NULL)
> AND
> (Threshold_Flag = @.Original_Threshold_Flag OR
> @.Original_Threshold_Flag IS NULL AND Threshold_Flag
> IS
> NULL);
> SELECT ThresholdType, Threshold,
> Threshold_Flag, Actual, Fail, Category, ID
> FROM MISRE_Threshold
> WHERE (ThresholdType = @.ThresholdType)
I populate my data with the load sub below.
In the Save Sub (below), I have generated my DataSet 'dataSet11' from my
DataAdaptor 'SqlDataAdapter1' and the DataConnection 'SqlConnection1' and
they all seem to be connected correctly. But my data does not update.
the dataAdaptor is configured for Insert/Update/delete and the datagrid
datasource is DataSet11.TableName. Any ideas?
Private Sub Thresholds_Load(ByVal sender As....
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
& Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
cmdSelect.Connection = cn
Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select
* from MISRE_Threshold", cn)
Dim dsThreshold As DataSet = New DataSet
' fill dataset
da.Fill(dsThreshold, "MISRE_Threshold")
'Attach DataSet to DataGrid
dgThreshold.DataSource = dsThreshold.DefaultViewManager
Catch ex As Exception
MessageBox.Show("Error: Could not establish database connection")
End Try
Private Sub btnSave_Click(ByVal sender....
SqlDataAdapter1.Update(DataSet11)
EndSubThe DataAdapter InsertCommand/UpdateCommand/DeleteCommand properties need to
be set in order for the DataAdapter to execute the appropriate commands to
update your table. The SqlCommandBuilder can be used to generate the needed
commands (if you have a primary key) or you can create those the commands
yourself.
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:392099C1-6587-46B7-BD60-A20AD3C884AB@.microsoft.com...
> My dataset is not updating my database after the user modifies the
> datagrid.
> I populate my data with the load sub below.
> In the Save Sub (below), I have generated my DataSet 'dataSet11' from my
> DataAdaptor 'SqlDataAdapter1' and the DataConnection 'SqlConnection1' and
> they all seem to be connected correctly. But my data does not update.
> the dataAdaptor is configured for Insert/Update/delete and the datagrid
> datasource is DataSet11.TableName. Any ideas?
> Private Sub Thresholds_Load(ByVal sender As....
> Try
> cn = New SqlClient.SqlConnection("user id=" & UserName.Text &
> ";password="
> & Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
> cn.Open()
> cmdSelect.Connection = cn
> Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select
> * from MISRE_Threshold", cn)
> Dim dsThreshold As DataSet = New DataSet
> ' fill dataset
> da.Fill(dsThreshold, "MISRE_Threshold")
> 'Attach DataSet to DataGrid
> dgThreshold.DataSource = dsThreshold.DefaultViewManager
> Catch ex As Exception
> MessageBox.Show("Error: Could not establish database
> connection")
> End Try
>
> Private Sub btnSave_Click(ByVal sender....
> SqlDataAdapter1.Update(DataSet11)
> EndSub|||They are configured, that's why i'm stumped!
Its the update one i'm interested in as follows:
UPDATE MISRE_Threshold
SET ThresholdType = @.ThresholdType, Threshold = @.Threshold,
Threshold_Flag = @.Threshold_Flag, Actual = @.Actual, Fail = @.Fail,
Category = @.Category, ID = @.ID
WHERE (ThresholdType = @.Original_ThresholdType) AND (Actual =
@.Original_Actual) AND (Category = @.Original_Category OR
@.Original_Category IS NULL AND Category IS NULL) AND
(Fail = @.Original_Fail) AND (ID = @.Original_ID OR
@.Original_ID IS NULL AND ID IS NULL) AND (Threshold =
@.Original_Threshold OR
@.Original_Threshold IS NULL AND Threshold IS NULL) AND
(Threshold_Flag = @.Original_Threshold_Flag OR
@.Original_Threshold_Flag IS NULL AND Threshold_Flag IS
NULL);
SELECT ThresholdType, Threshold,
Threshold_Flag, Actual, Fail, Category, ID
FROM MISRE_Threshold
WHERE (ThresholdType = @.ThresholdType)|||> They are configured, that's why i'm stumped!
The original code you posted instantiates and uses a new untyped dataset:
Dim dsThreshold As DataSet = New DataSet
However, your update routine uses the DataAdapter and DataSet generated by
the windows form designer:
SqlDataAdapter1.Update(DataSet11)
DataSet11 is never filled in the code snippets you posted so it will always
be empty. I believe your intention is to fill DataSet11 in the load
routine:
SqlDataAdapter1.Fill(DataSet11)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E94A87F0-9D2B-4DB1-85E3-5AE7635656DA@.microsoft.com...
> They are configured, that's why i'm stumped!
> Its the update one i'm interested in as follows:
>
> UPDATE MISRE_Threshold
> SET ThresholdType = @.ThresholdType, Threshold = @.Threshold,
> Threshold_Flag = @.Threshold_Flag, Actual = @.Actual, Fail = @.Fail,
> Category = @.Category, ID = @.ID
> WHERE (ThresholdType = @.Original_ThresholdType) AND (Actual =
> @.Original_Actual) AND (Category = @.Original_Category OR
> @.Original_Category IS NULL AND Category IS NULL) AND
> (Fail = @.Original_Fail) AND (ID = @.Original_ID OR
> @.Original_ID IS NULL AND ID IS NULL) AND (Threshold =
> @.Original_Threshold OR
> @.Original_Threshold IS NULL AND Threshold IS NULL)
> AND
> (Threshold_Flag = @.Original_Threshold_Flag OR
> @.Original_Threshold_Flag IS NULL AND Threshold_Flag
> IS
> NULL);
> SELECT ThresholdType, Threshold,
> Threshold_Flag, Actual, Fail, Category, ID
> FROM MISRE_Threshold
> WHERE (ThresholdType = @.ThresholdType)
Sunday, February 26, 2012
Databases updating simultaneously
Hi,
It sounds funny to me that you can see the files grow. Autogrow events
on live databases should be rare events and not happen frequently. You
create them with a reasonable initial size, and then you preferrably
increase then while you have a maintenance window. Autogrow during
production should be avoided, as it could cause the database to be
inaccessible while autogrow is in progress.
I have managed to create a second copy of my "live" database, for
software testing purposes.
Inspecting the properties of the new database, everything seems in
order. The logical file name is the same, which I believe is fine, and
the physical database (and log file name) is different.
However, despite the fact that there is no application currently
accessing the "testing" copy, both databases are seemingly being
updated simultanously. I can tell this from the physical file sizes on
the server, which are identical, and growing at the same rate.
Does anyone have any suggestions why this might be happening - and how
I can stop it?
Thanks in anticipation!
PhilRS200Phil (philsowden@.dataservicesltd.co.uk) writes:
Quote:
Originally Posted by
I have managed to create a second copy of my "live" database, for
software testing purposes.
>
Inspecting the properties of the new database, everything seems in
order. The logical file name is the same, which I believe is fine, and
the physical database (and log file name) is different.
>
However, despite the fact that there is no application currently
accessing the "testing" copy, both databases are seemingly being
updated simultanously. I can tell this from the physical file sizes on
the server, which are identical, and growing at the same rate.
>
Does anyone have any suggestions why this might be happening - and how
I can stop it?
It sounds funny to me that you can see the files grow. Autogrow events
on live databases should be rare events and not happen frequently. You
create them with a reasonable initial size, and then you preferrably
increase then while you have a maintenance window. Autogrow during
production should be avoided, as it could cause the database to be
inaccessible while autogrow is in progress.
To tell why your databases grow in parallel would require more knowledge
about your server. Here we are left to wild guesses. Maybe you set up
replication between the databases?
sp_who can tell you if there are any processes in the database at all.
You can use SQL Server Profiler to see if there is any action in the
server.
Which version of SQL Server are you using?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)