Christian de Bellefeuille
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:
This piece of code demonstrate what i've been testing:
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;
}
}
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
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
test.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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.