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