J
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
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