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
LVL 10
Christian de BellefeuilleProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
As to the identity, I think you would need a stored proc because you have to issue one statement and access this special function to a variable immediately afterwards.  That's how I've always done it.

If the DataGridView is bound and not unbound then I don't think you should have to manually assign. Here's what Microsoft shows for doing bound DataGridViews:
https://msdn.microsoft.com/en-us/library/fbk67b6z%28v=vs.110%29.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christian de BellefeuilleProgrammerAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.