Category: ASP

Table Of Contents

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

 

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.