How to create and maintain a dataset using Microsoft EnterpriseLibrary for .NET Framework 2.0

Download and reference the Microsoft.Practices.EnterpriseLibrary

Add the following using directives

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

Connect to the database

Database db = DatabaseFactory.CreateDatabase();

Load the dataset and set the primary Key

 DbCommand dbCommand = db.GetStoredProcCommand("SP_GUI_SELECT_FEED");
db.LoadDataSet(dbCommand, _ConfigDataset, _ConfigFeedsTable);
using (DataTable dt = _ConfigDataset.Tables[_ConfigFeedsTable])
	{
		dt.PrimaryKey = new DataColumn[] { dt.Columns["ID"] };
	}

Update the dataset and save to the database

 DataTable dt = _ConfigDataset.Tables[_ConfigFeedsTable];
            DataRow row = dt.Rows.Find(feedID);

            row["FILE_NAME"] = txtFileName.Text;
            row["NAME"] = txtName.Text;
            row["DESCRIPTION"] = txtDescription.Text;
            row["DELIMITER"] = txtDelimiter.Text;
            row["HASHEADER"] = chkHasHeader.Checked;
            row["FILE_PATH"] = txtPath.Text;
            row["TABLE_NAME"] = txtTableName.Text;
            row["ENABLED"] = chkEnabled.Checked;

            int rowsAffected = db.UpdateDataSet(_ConfigDataset, _ConfigFeedsTable, GetFeedInsertCommand(), GetFeedUpdateCommand(), GetFeedDeleteCommand(), UpdateBehavior.Standard);
           

Insert into the dataset and save to the database, this will automcatically populate an identity column

 DataTable dt = _ConfigDataset.Tables[_ConfigFeedsTable];
            DataRow row = dt.NewRow();
            int newFeedID = (int)dt.Select("[ID] = MAX(ID)")[0]["ID"];
            newFeedID++;
            row["ID"] = newFeedID;
            row["FILE_NAME"] = txtFileName.Text;
            row["NAME"] = txtName.Text;
            row["DESCRIPTION"] = txtDescription.Text;
            row["DELIMITER"] = txtDelimiter.Text;
            row["HASHEADER"] = chkHasHeader.Checked;
            row["FILE_PATH"] = txtPath.Text;
            row["TABLE_NAME"] = txtTableName.Text;
            row["ENABLED"] = chkEnabled.Checked;
            dt.Rows.Add(row);
            int rowsAffected = db.UpdateDataSet(_ConfigDataset, _ConfigFeedsTable, GetFeedInsertCommand(), GetFeedUpdateCommand(), GetFeedDeleteCommand(), UpdateBehavior.Standard);
            //MessageBox.Show(rowsAffected.ToString());

Delete from the dataset which will delete from the database

 int rowsAffected = db.UpdateDataSet(_ConfigDataset, _ConfigFeedsTable, GetFeedInsertCommand(), GetFeedUpdateCommand(), GetFeedDeleteCommand(), UpdateBehavior.Standard);

Command Functions

private DbCommand GetFeedUpdateCommand()
        {
            DbCommand updateCommand = db.GetStoredProcCommand("SP_GUI_UPDATE_CONFIG");
            db.AddInParameter(updateCommand, "@FEED_ID", DbType.String, "ID", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@FILE_NAME", DbType.String, "FILE_NAME", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@NAME", DbType.String, "NAME", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@DESCRIPTION", DbType.String, "DESCRIPTION", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@DELIMITER", DbType.String, "DELIMITER", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@HASHEADER", DbType.Boolean, "HASHEADER", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@FILE_PATH", DbType.String, "FILE_PATH", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@TABLE_NAME", DbType.String, "TABLE_NAME", DataRowVersion.Current);
            db.AddInParameter(updateCommand, "@ENABLED", DbType.Boolean, "ENABLED", DataRowVersion.Current);
            return updateCommand;
        }

        private DbCommand GetFeedInsertCommand()
        {
            DbCommand insertCommand = db.GetStoredProcCommand("SP_GUI_INSERT_CONFIG");
            db.AddParameter(insertCommand, "@IDENTITY", DbType.Int32, ParameterDirection.Output, "ID", DataRowVersion.Current, 1);
            db.AddInParameter(insertCommand, "@FILE_NAME", DbType.String, "FILE_NAME", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@NAME", DbType.String, "NAME", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@DESCRIPTION", DbType.String, "DESCRIPTION", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@DELIMITER", DbType.String, "DELIMITER", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@HASHEADER", DbType.Boolean, "HASHEADER", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@FILE_PATH", DbType.String, "FILE_PATH", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@TABLE_NAME", DbType.String, "TABLE_NAME", DataRowVersion.Current);
            db.AddInParameter(insertCommand, "@ENABLED", DbType.Boolean, "ENABLED", DataRowVersion.Current);
            return insertCommand;
        }
        private DbCommand GetFeedDeleteCommand()
        {
            DbCommand deleteCommand = db.GetStoredProcCommand("SP_GUI_DELETE_CONFIG");
            db.AddInParameter(deleteCommand, "@FEED_ID", DbType.String, "ID", DataRowVersion.Current);
            return deleteCommand;
        }

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



create PROCEDURE [dbo].[SP_GUI_DELETE_CONFIG] 
	@FEED_ID int
	
as
delete  [CREDITDB].dbo.FEED_IMPORT
where ID_CONFIG_FEED =  @FEED_ID

delete  [CREDITDB].[dbo].[CONFIG_FEED_COLUMN_MAPPINGS]
where ID_CONFIG_FEED =  @FEED_ID

delete  [CREDITDB].[dbo].[CONFIG_FEED]
where ID =  @FEED_ID




create PROCEDURE [dbo].[SP_GUI_INSERT_CONFIG] 
	@IDENTITY int OUTPUT,
	@FILE_NAME varchar(50),
	@NAME varchar(150),
    @DESCRIPTION varchar(500),
    @DELIMITER char(1),
    @HASHEADER bit,
    @FILE_PATH varchar(500),
    @TABLE_NAME varchar(50),
    @ENABLED bit    
as
INSERT INTO [CREDITDB].[dbo].[CONFIG_FEED]
           ([FILE_NAME]
		   ,[NAME]
           ,[DESCRIPTION]
           ,[DELIMITER]
           ,[HASHEADER]
           ,[FILE_PATH]
           ,[TABLE_NAME]
           ,[ENABLED]
           ,[ID_CONFIG_FILTER_TYPE])
     VALUES
           (@FILE_NAME,
	@NAME,
    @DESCRIPTION,
    @DELIMITER,
    @HASHEADER,
    @FILE_PATH,
    @TABLE_NAME,
    @ENABLED,6    )


SET @Identity = SCOPE_IDENTITY()



create PROCEDURE [dbo].[SP_GUI_UPDATE_CONFIG] 
	@FEED_ID int,
	@FILE_NAME varchar(50),
	@NAME varchar(50),
    @DESCRIPTION varchar(500),
    @DELIMITER char(1),
    @HASHEADER bit,
    @FILE_PATH varchar(500),
    @TABLE_NAME varchar(50),
    @ENABLED bit    
as
UPDATE [CREDITDB].[dbo].[CONFIG_FEED]
set [FILE_NAME] = @FILE_NAME,
			[NAME] = @NAME,
           [DESCRIPTION] = @DESCRIPTION,
           [DELIMITER] = @DELIMITER,
           [HASHEADER] = @HASHEADER,
           [FILE_PATH] = @FILE_PATH,
           [TABLE_NAME] = @TABLE_NAME,
           [ENABLED] = @ENABLED
where ID =  @FEED_ID