C# SqlDataAdapter question: tracking changes?

[Jamie Risk]

Hello,

Can SqlDataAdapter track changes I've made in my C# DataTable
structures?

In the past few months I've written SQL scripts and windows
forms code to present data to a user. Now, I'd like the user to
be able to commit changes made in the form to the SQL table.

To fetch data from the DataBase I've written a function:

DataTable dt;
dt = GetGeneric("GetDiaryEntries","@id_Patient",patient_ID);

I was wondering if I should rewrite the function so it takes
a DataTable parameter and return a reference (of some kind) so
that I can easily commit DataTable Changes.

SqlDataAdapter a;
a =GetGeneric(dt,"GetDiaryEntries","@id_Patient",patient_ID);

-- or --

GetGeneric(a,dt,"GetDiaryEntries","@id_Patient",patient_ID);
SetGeneric(a,dt,"SetDiaryEntries"); //TODO: stored proc.


- Jamie


// ============================================================
// C# code
// ============================================================
public DataTable GetGeneric(string cmd, string argName, int arg)
{
SqlConnection myConnection = new SqlConnection(SqlConnect);
myConnection.Open();

SqlCommand myCmd = new SqlCommand(cmd, this.con);
myCmd.CommandType = CommandType.StoredProcedure;

if (null != argName)
{
SqlParameter myParm;
myParm = myCmd.Parameters.Add(argName, SqlDbType.Int);
myParm.Value = arg;
}
SqlDataAdapter adapter = new SqlDataAdapter(myCmd);

DataTable dt = new DataTable();
adapter.Fill(dt);
myConnection.Close();
return dt;
}


-- ============================================================
-- SQL scripts.
-- ============================================================
-- Table: Patient
-- ---------------------------
CREATE TABLE [dbo].[Patient]
(
[id_Patient] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[f_Name] [nvarchar] (20),
[l_Name] [nvarchar] (30) NOT NULL,
[birthday] [datetime]
) ON [PRIMARY]
GO

-- Table: Diary
-- ---------------------------
CREATE TABLE [dbo].[Diary]
(
[id_Diary] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[id_Patient] [int] FOREIGN KEY
REFERENCES [dbo].[Patient]([id_Patient])
ON DELETE CASCADE
ON UPDATE NO ACTION ,
[note] [nvarchar] (1000) NOT NULL,
[note_Date] [datetime] NOT NULL
CONSTRAINT [DF_Diary_Date] DEFAULT (getdate())
) ON [PRIMARY]
GO

-- Description: Get log entries for a specific paitent
-- =============================================
CREATE PROCEDURE [dbo].[GetDiaryEntries]
-- Add the parameters for the stored procedure here
@id_Patient int = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT * FROM [ABR_Client].[dbo].[Diary] WHERE
[ABR_Client].[dbo].[Diary].[id_Patient] = @id_Patient;
END
GO

 

[Jamie Risk]

[Originally (and mistakenly) cross posted to m.p.d. f.w.databinding]

Hello,

Can SqlDataAdapter track changes I've made in my C# DataTable
structures?

In the past few months I've written SQL scripts and windows
forms code to present data to a user. Now, I'd like the user to
be able to commit changes made in the form to the SQL table.

To fetch data from the DataBase I've written a function:

DataTable dt;
dt = GetGeneric("GetDiaryEntries","@id_Patient",patient_ID);

I was wondering if I should rewrite the function so it takes
a DataTable parameter and return a reference (of some kind) so
that I can easily commit DataTable Changes.

SqlDataAdapter a;
a =GetGeneric(dt,"GetDiaryEntries","@id_Patient",patient_ID);

-- or --

GetGeneric(a,dt,"GetDiaryEntries","@id_Patient",patient_ID);
SetGeneric(a,dt,"SetDiaryEntries"); //TODO: stored proc.


- Jamie


// ============================================================
// C# code
// ============================================================
public DataTable GetGeneric(string cmd, string argName, int arg)
{
SqlConnection myConnection = new SqlConnection(SqlConnect);
myConnection.Open();

SqlCommand myCmd = new SqlCommand(cmd, this.con);
myCmd.CommandType = CommandType.StoredProcedure;

if (null != argName)
{
SqlParameter myParm;
myParm = myCmd.Parameters.Add(argName, SqlDbType.Int);
myParm.Value = arg;
}
SqlDataAdapter adapter = new SqlDataAdapter(myCmd);

DataTable dt = new DataTable();
adapter.Fill(dt);
myConnection.Close();
return dt;
}


-- ============================================================
-- SQL scripts.
-- ============================================================
-- Table: Patient
-- ---------------------------
CREATE TABLE [dbo].[Patient]
(
[id_Patient] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[f_Name] [nvarchar] (20),
[l_Name] [nvarchar] (30) NOT NULL,
[birthday] [datetime]
) ON [PRIMARY]
GO

-- Table: Diary
-- ---------------------------
CREATE TABLE [dbo].[Diary]
(
[id_Diary] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[id_Patient] [int] FOREIGN KEY
REFERENCES [dbo].[Patient]([id_Patient])
ON DELETE CASCADE
ON UPDATE NO ACTION ,
[note] [nvarchar] (1000) NOT NULL,
[note_Date] [datetime] NOT NULL
CONSTRAINT [DF_Diary_Date] DEFAULT (getdate())
) ON [PRIMARY]
GO

-- Description: Get log entries for a specific paitent
-- =============================================
CREATE PROCEDURE [dbo].[GetDiaryEntries]
-- Add the parameters for the stored procedure here
@id_Patient int = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT * FROM [ABR_Client].[dbo].[Diary] WHERE
[ABR_Client].[dbo].[Diary].[id_Patient] = @id_Patient;
END
GO