StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Using insert stored procedures

Using insert stored procedures

Randy Smith — Thu, 23 Feb 2006 10:25:45 GMT

Can anyone give me a sample of saving data with a stored procedure. I am trying to return the uniqueidentifier and I'm not sure exactly how to go about this.

Thanks in advance.

RE: Using insert stored procedures

Randy Smith — Thu, 23 Feb 2006 10:25:45 GMT

Great!! Thanks for all your help.

RE: Using insert stored procedures

StrataFrame Team — Thu, 23 Feb 2006 08:52:25 GMT

Yes, it is possible to use a GUID as a primary key... I would recommend that you set the pk value within your SetDefaultValues event handler within the business object. Then, you can turn off the use of the stored procedures, and set the PrimaryKeyIsAutoIncremented property to False. However, if you want the server to assign the value, you'll need to set the "Default Value" of your field within the database to "(NEWID())", and leave the PrimaryKeyIsAutoIncremented = True so that the value will be retrieved.

If you want to assign the PK on the client side, the code would look like this:

VB:
Private Sub MyBO_SetDefaultValues()
'-- Set the pk value
Me.pkfield = Guid.NewGuid() '-- Creates a new, random GUID just like the NEWID() SQL method
End Sub

C#:
private void MyBO_SetDefaultValues()
{
//-- Set the pk value
this.pkfield = Guid.NewGuid(); //-- Creates a new, random GUID just like the NEWID() SQL method
}

RE: Using insert stored procedures

Randy Smith — Tue, 21 Feb 2006 15:38:59 GMT

Thanks for the information Ben. That fixed my problem. Is it possible to use the uniqueidentifier field without using the stored procedure ?

RE: Using insert stored procedures

StrataFrame Team — Tue, 21 Feb 2006 14:34:25 GMT

I see in your database table that the field receiving the @Admin value is actually [Administrator]. You have to name the stored procedure parameters with the same name as the field within the business object (plus the StoredProcedureParameterPrefix property value). So, if you're StoredProcedureParameterPrefix property equals "@p_", then, you'll need to name you're parameters within your stored procedure each "@p_FieldName" ("@p_Administrator" in this case, not "@p_Admin"), so, since the default value for the prefix is just "@", you'll need to change that "@Admin" parameter to "@Administrator" because the data layer is expecting you to use the same name as the field within the business object. If you run into a name that's a reserved name on SQL Server, then just change the default StoredProcedureParameterPrefix value.

RE: Using insert stored procedures

Randy Smith — Fri, 17 Feb 2006 14:07:08 GMT

Ben,

I still must be doing something wrong. I am now getting the following error when trying to run the stored procedure from the BO.

BusinessLayerException
An error occurred while saving an the data to the server.
DataLayerSavingException
Procedure or Function 'sp_User_Insert' expects parameter '@Admin', which was not supplied.
SqlException
Procedure or Function 'sp_User_Insert' expects parameter '@Admin', which was not supplied.

Source : MicroFour StrataFrame Business

Stack Trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at MicroFour.StrataFrame.Data.SqlDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler)
   at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
   at MicroFour.StrataFrame.Data.DataLayer.SaveByForm(DataTable TableToSave, Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.Business.BusinessLayer.SaveByForm(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save()
   at MicroFour.StrataFrame.UI.Windows.Forms.MaintenanceFormToolStrip.cmdSave_Click(Object sender, EventArgs e)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Here is the stored procedure:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[sp_User_Insert] (@UserID UniqueIdentifier OUTPUT,@UserName [NVarchar] (50),@Password [NVarchar] (50),@Initials [NVarchar] (3),@Email [NVarchar] (50),@Admin [Bit],@Created [DateTime],@LastUpd [DateTime])

BEGIN

Select @UserID = NEWID();

Insert into [dbo].[Users] ([UserID],[UserName],[Password],[Initials],[Email],[Administrator],[Created],[LastUpd])

Values (@UserID,@UserName,@Password,@Initials,@Email,@Admin,@Created,@LastUpd);

end

My business object is populating the Created and Lastupd field from the set default values. I have a form that has the username,password,initials,email and a checkbox for the admin field. I can't figure out what is wrong. I ran the stored proc inside SQL management console and it works just fine.

Thanks for your help.

RE: Using insert stored procedures

StrataFrame Team — Thu, 16 Feb 2006 10:10:34 GMT

Also, make sure that you're PrimaryKeyIsAutoIncremented property is set to True so that the business object's data layer knows that it should retrieve the assigned GUID from the server after the INSERT.

RE: Using insert stored procedures

StrataFrame Team — Thu, 16 Feb 2006 10:07:47 GMT

So, your server assigns the new GUID for the new record? And the primary key is a GUID?

Then you'll want your stored procedure to look like this:

CREATE Procedure sp_MyTable_Insert ( @field_pk GUID OUTPUT, @field_1 INT, @field_2 INT)
AS
BEGIN
SELECT @field_pk = NEWID();
INSERT INTO MyTable (field_pk, field_1, field_2)
VALUES (@field_pk, @field_1, @field_2);
END
GO

Don't forget to define the GUID as a OUTPUT. Then get the guid and insert it into the row as the pk, and it will be returned and retrieved into the business object by the output variable.