Link to home
Start Free TrialLog in
Avatar of Christian de Bellefeuille
Christian de BellefeuilleFlag for Canada

asked on

C# DataGridView without SqlCommandBuilder

I'm trying to understand how to properly create a DataGridView, linked to an MS-SQL Table, able to insert/update/delete, with some parameters on the SELECT (not a simple "SELECT * FROM MyTable").   My question is more "Am i missing something?   Is there more efficient way to code this?"

Considering that i can't use SqlCommandBuilder because parameters names are random:

Do i have to assign every commands parameters with data from my grid?  Because with SQB, it seems to be done automagically
Do i have to use a Store Procedure to be able to retrieve the SCOPE_IDENTITY of the row i've just inserted?  That's the only solution I've found

This piece of code demonstrate what i've been testing:

Grid CellChanged detection

        private void gridTest_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            Validate();

            // IT WON'T WORK IF NOT ASSIGNED MANUALLY
            if (rbtnProg.Checked)
            {
                mAdapterTest.UpdateCommand.Parameters["@p1"].Value = gridTest.Rows[e.RowIndex].Cells["TestName"].Value;
                mAdapterTest.UpdateCommand.Parameters["@p2"].Value = gridTest.Rows[e.RowIndex].Cells["TestScore"].Value;
                mAdapterTest.UpdateCommand.Parameters["@p3"].Value = gridTest.Rows[e.RowIndex].Cells["TestID"].Value;
                mAdapterTest.UpdateCommand.Parameters["@p4"].Value = gridTest.Rows[e.RowIndex].Cells["TestMisc"].Value;
                mAdapterTest.InsertCommand.Parameters["@p1"].Value = gridTest.Rows[e.RowIndex].Cells["TestName"].Value;
                mAdapterTest.InsertCommand.Parameters["@p2"].Value = gridTest.Rows[e.RowIndex].Cells["TestScore"].Value;
            }

            mAdapterTest.Update(mDataSetTest);

            // Detect if there's a key or not in the grid.  If not, then an INSERT just been executed...
            if (gridTest.Rows[e.RowIndex].Cells["TestID"].Value.ToString() == "")
            {
                gridTest.Rows[e.RowIndex].Cells["TestID"].Value = mAdapterTest.InsertCommand.Parameters["@p3"].Value;
                mAdapterTest.InsertCommand.Parameters["@p3"].Value = 0;
            }
        }

Open in new window


Code to create the Test Table

CREATE TABLE [dbo].[Test](
	[TestID] [int] IDENTITY(1,1) NOT NULL,
	[TestName] [nvarchar](50) NULL,
	[TestScore] [int] NULL,
	[TestMisc] [int] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[TestID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_Test_TestScore]  DEFAULT ((0)) FOR [TestScore]
GO
ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_Test_TestMisc]  DEFAULT ((0)) FOR [TestMisc]
GO

Open in new window


SaveTest SP

CREATE PROCEDURE [dbo].[SaveTest] 
	@p1 nvarchar(50) = '', 
	@p2 int = 0,
	@p3 int output
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO Test (TestName, TestScore) VALUES (@p1, @p2);	
	SELECT @p3 = SCOPE_IDENTITY();
END

Open in new window

test.zip
ASKER CERTIFIED SOLUTION
Avatar of SStory
SStory
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Christian de Bellefeuille

ASKER

Sorry if i've left this question behind, but the whole point about my post is exactly what Microsoft doesn't answer.  Doing insert/update/delete with SQLCommandBuilder with a simple query like "select * from Customers" is just plain simple.  

Microsoft sample you have provided (Like many other sample of MS), are too simple and doesn't fit for the job.

I'll explain exactly what i'm trying to do here and you will understand why i've asked this precise question:

My customer want to have a grid with predefined columns, but they would like to modify it just like if they were modifying an Excel Sheet.  That mean, you start to fill information in it, press tab, then its automatically saved in the cell (and db)
Before filling the sheet, they need to select a customer in a listbox in this form.
Each time they fill something in the sheet (DGV), we must save the customer ID (an autonumber) in the database for the row he just edited, and this is the main reason why i'm not using the SQLCommandBuilder because it can't be easily parametrized as far as i know

yet, i've got all this working.

The only problem i still have to figure out is how to Save data on CellLeave event.  That will be a part of another question because it's a bit off topic the initial question.