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