Can this be done using a Data adapter??

  • Thread starter Thread starter insirawali
  • Start date Start date
I

insirawali

Hi all,

I have this problem, i need to know is there a way i cn use the data
adapter's update method in this scenario.

i have 3 tables as below

create table table1{
id1 int identity(1,1)
Constraint pk_table1 Primary Key,
title varchar(20) not null,
}

create table table2{
id2 int identity(1,1)
Constraint pk_table1 Primary Key,
title varchar(20) not null,
}

create table table3{
id1 int not null
Constraint fk_table3_1 Foreign Key References table1(id1),
id2 int not null
Constraint fk_table3_2 Foreign Key References table2(id2),
value int default 0,
Constraint pk_table3 Primary Key (id1,id2),
}

In my application i have a Datagrid view, which has all the records of
table1 as the columns of the datagrid view and records of table2 as
the rows of the data grid. and i have table 3 to store the values i
enter in Datagrid view.

I was wondering can i use the data adapter to fetch data of table3
using Fill method and use da update method of data adapter to add
values to table3.

I have no leads on how to start, thats why my 1st have the concern
whether i can do it using the Data adapter.

The only method i cn think of is loop through each cell im my datagrid
view and manually run the insert or update SQL commands. But that will
make my application very slow. specially when there is abt 40 records
in table1 and 100 records in table2... :-(

Can anyone giv me any leads.....

Thank you,
Insira.
 
Hi all,

I have this problem, i need to know is there a way i cn use the data
adapter's update method in this scenario.

i have 3 tables as below

create table table1{
            id1 int identity(1,1)
            Constraint pk_table1 Primary Key,
            title varchar(20) not null,

}

create table table2{
            id2 int identity(1,1)
            Constraint pk_table1 Primary Key,
            title varchar(20) not null,

}

create table table3{
            id1 int not null
            Constraint fk_table3_1 Foreign Key References table1(id1),
            id2 int not null
            Constraint fk_table3_2 Foreign Key References table2(id2),
            value int default 0,
            Constraint pk_table3 Primary Key (id1,id2),

}

In my application i have a Datagrid view, which has all the records of
table1 as the columns of the datagrid view and records of table2 as
the rows of the data grid. and i have table 3 to store the values i
enter in Datagrid view.

I was wondering can i use the data adapter to fetch data of table3
using Fill method and use da update method of data adapter to add
values to table3.

I have no leads on how to start, thats why my 1st have the concern
whether i can do it using the Data adapter.

The only method i cn think of is loop through each cell im my datagrid
view and manually run the insert or update SQL commands. But that will
make my application very slow. specially when there is abt 40 records
in table1 and 100 records in table2... :-(

Can anyone giv me any leads.....

Thank you,
Insira.
Click to expand...

I suggest you need to let SQL process the data. Use the SQL Select
statement to pivot the data then select it as you need into a
datatable. The datatable passes only the modified/new/deleted rows to
the respective commands. Those respective SQL statements takes care of
only processing the changed data.

I hope that helps! If not please post more exact details about the
data you are working with.
 
I suggest you need to let SQL process the data. Use the SQL Select
statement to pivot the data then select it as you need into a
datatable. The datatable passes only the modified/new/deleted rows to
the respective commands. Those respective SQL statements takes care of
only processing the changed data.

I hope that helps! If not please post more exact details about the
data you are working with.
Click to expand...

Thanks for ur reply,
but i'm confused on how to use sql to pivot the data. can you give me
an example on how to do it. (my SQL knowledge is not that good)
 
Thanks for ur reply,
but i'm confused on how to use sql to pivot the data. can you give me
an example on how to do it. (my SQL knowledge is not that good)- Hide quoted text -

- Show quoted text -
Click to expand...

Do you have more info about the solution and data you are working
with?

The SQL statements, will just process the changed data, where ever or
however that might be stored in the database.
 
Do you have more info about the solution and data you are working
with?

The SQL statements, will just process the changed data, where ever or
however that might be stored in the database.- Hide quoted text -

- Show quoted text -
Click to expand...

the data im workin with also belongs to the same schema.
what i need to give is an excel like interface to the user with ID
values of table2 as columns and ID values of table1 as rows (i can
have the 1st column read only and put the table1 values there). what i
have done up to now is i bring the data of table1 and table2 from the
server and loop through the each table and add rows and columns to the
data grid view
as below

foreach (DataRow currow in table1.Rows)
{

datagridview1.Columns.Add(currow["ID"].ToString(),currow["ID"].ToString());
}


foreach (DataRow currow in table2.Rows)
{
datagridview1.Rows.Add(currow["ID"].ToString());
}

the trouble I’m having is how can i put the data in table3 into my
datagridview. i can't just give the data source of the grid view as
table3. then i lose all the row and column data.

is there a method i can assign the values of table3 into the
datagridview. the method i can think of is loop through the rows in
table3 and put the values into the datagridview but that makes my
application slow. (imagin when there are about 100 records in table1
and table2.....)

as you said above is there a way where i can create a view in
SQLserver itself for the above scenario and then feach the data of
that view. then it will be very easy...
 
Do you have more info about the solution and data you are working
with?
Click to expand...
The SQL statements, will just process the changed data, where ever or
however that might be stored in the database.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

the data im workin with also belongs to the same schema.
what i need to give is an excel like interface to the user with ID
values of table2 as columns and ID values of table1 as rows (i can
have the 1st column read only and put the table1 values there). what i
have done up to now is i bring the data of table1 and table2 from the
server and loop through the each table and add rows and columns to the
data grid view
as below

foreach (DataRow currow in table1.Rows)
{

datagridview1.Columns.Add(currow["ID"].ToString(),currow["ID"].ToString());

}

foreach (DataRow currow in table2.Rows)
{
      datagridview1.Rows.Add(currow["ID"].ToString());

}

the trouble I’m having is how can i put the data in table3 into my
datagridview. i can't just give the data source of the grid view as
table3. then i lose all the row and column data.

is there a method i can assign the values of table3 into the
datagridview. the method i can think of is loop through the rows in
table3 and put the values into the datagridview but that makes my
application slow. (imagin when there are about 100 records in table1
and table2.....)

as you said above is there a way where i can create a view in
SQLserver itself for the above scenario and then feach the data of
that view. then it will be very easy...- Hide quoted text -

- Show quoted text -
Click to expand...

OK It may not be quite as easy as is sounds. Depending on how good
your SQL is there is a bit of understanding involved. It would be
easier to under them before trying to implement this. Of the top of my
head, you'll need to look at how to create Dynamic SQL, SQL triggers,
SQL temporary tables and SQL Cursor. I assume you are ok with Stored
Procedures and using Insert, update and delete statements in SQL
Stored Procedures.

In a nutshell alter the insert, update and delete stored procedures,
if rows are added to table1 or table2. You can use a SQL Trigger to do
this.

Create a stored procedure which uses a SQL Cursor to build up a SQL
Temporary table based on the values in table1 and table2. Insert from
table3 into the temporary table. This stored procedure will be the
SelectCommand of your adapter.

The only performance hit you should really see is when using the SQL
Cursor to fetch the data, after that it will be out of the way. The
insert, update and delete shouldn't be too heavy then.

The point is you are processing the data server side instead of client
side which should improve your performance.
 
the data im workin with also belongs to the same schema.
what i need to give is an excel like interface to the user with ID
values of table2 as columns and ID values of table1 as rows (i can
have the 1st column read only and put the table1 values there). what i
have done up to now is i bring the data of table1 and table2 from the
server and loop through the each table and add rows and columns to the
data grid view
as below
Click to expand...
foreach (DataRow currow in table1.Rows)
{
datagridview1.Columns.Add(currow["ID"].ToString(),currow["ID"].ToString());
Click to expand...
}
Click to expand...

foreach (DataRow currow in table2.Rows)
{
      datagridview1.Rows.Add(currow["ID"].ToString());
}
Click to expand...

the trouble I’m having is how can i put the data in table3 into my
datagridview. i can't just give the data source of the grid view as
table3. then i lose all the row and column data.
Click to expand...
is there a method i can assign the values of table3 into the
datagridview. the method i can think of is loop through the rows in
table3 and put the values into the datagridview but that makes my
application slow. (imagin when there are about 100 records in table1
and table2.....)
Click to expand...
as you said above is there a way where i can create a view in
SQLserver itself for the above scenario and then feach the data of
that view. then it will be very easy...- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

OK It may not be quite as easy as is sounds. Depending on how good
your SQL is there is a bit of understanding involved. It would be
easier to under them before trying to implement this. Of the top of my
head, you'll need to look at how to create Dynamic SQL, SQL triggers,
SQL temporary tables and SQL Cursor. I assume you are ok with Stored
Procedures and using Insert, update and delete statements in SQL
Stored Procedures.

In a nutshell alter the insert, update and delete stored procedures,
if rows are added to table1 or table2. You can use a SQL Trigger to do
this.

Create a stored procedure which uses a SQL Cursor to build up a SQL
Temporary table based on the values in table1 and table2. Insert from
table3 into the temporary table. This stored procedure will be the
SelectCommand of your adapter.

The only performance hit you should really see is when using the SQL
Cursor to fetch the data, after that it will be out of the way. The
insert, update and delete shouldn't be too heavy then.

The point is you are processing the data server side instead of client
side which should improve your performance.- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry I posted early.... here's another solution..

I'm not sure what kind of performance improvement you'll see on this
one, but if your SQL is not so good, then it's definately easier to
implement.

Create the grid dynamically from table1 (columns) set the column title
as necessary from the title value, and set the id1 as the column name.
You'll also need a hidden column to store the row id - used to pass
id2.

Select table 3 directly into a datatable, but do not use it as the
datasource.

Use the Grid's RowUpdated event to get the updated row of the grid,
loop through each column of that row get the cell value (value column
in table3), and use the column name for the cell (id1 column in
table3) and the value in the hidden column (id2 column in table3).
Call the update method of the datatable. This will be better than
doing every cell in the grid. Call the DataAdapter Update to update
the source.


INSERT INTO table1 (title) VALUES ('Column1')
INSERT INTO table1 (title) VALUES ('Column2')
INSERT INTO table1 (title) VALUES ('Column3')
INSERT INTO table1 (title) VALUES ('Column4')
INSERT INTO table1 (title) VALUES ('Column5')
INSERT INTO table2 (title) VALUES ('Row1')
INSERT INTO table2 (title) VALUES ('Row2')
INSERT INTO table2 (title) VALUES ('Row3')

CREATE PROC GetColumns
AS
BEGIN
SELECT id1, title
FROM table1
END
GO
CREATE PROC GetRows
AS
BEGIN
SELECT id2, title
FROM table2
END
GO
CREATE PROC GetData
AS
BEGIN
SELECT id1, id2, value FROM table3
END
GO
CREATE PROC InsertValue
@Id1 int, @Id2 int, @Value int
AS
BEGIN
INSERT INTO table3 (id1, id2, value)
VALUES (@Id1, @Id2, @Value)
END
GO
CREATE PROC UpdateValue
@Id1 int, @Id2 int, @Value int
AS
BEGIN
UPDATE table3
SET value = @Value
WHERE Id1 = @Id1 and Id2 = @Id2
END
GO
 
Back
Top