StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Using Strata Frame's connection to the datasource...

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Mon, 26 Mar 2007 08:31:34 GMT

Hehe, will do.

Using Strata Frame's connection to the datasource...

StarkMike — Mon, 26 Mar 2007 08:31:34 GMT

How do i use the connection to the database that strataframe has made to execute miscellaneous sql? select statements, stored procedures, etc.

RE: Using Strata Frame's connection to the datasource...

Bradley Marston — Fri, 23 Mar 2007 14:06:19 GMT

please disreguard above

thanks

RE: Using Strata Frame's connection to the datasource...

Bradley Marston — Fri, 23 Mar 2007 14:04:46 GMT

I was just wondering if

DataBasics.DataSources("").ExecuteStoredProcedure("proc Here")

can be part of a transaction along the lines of

Try

BusinessLayer.TransactionBegin("", Data.IsolationLevel.ReadCommitted)

Me.Customers.Save(True)
Me.Orders.Save(True)

DataBasics.DataSources("").ExecuteStoredProcedure(Do some processing here) BusinessLayer.TransactionEnd()

BusinessLayer.TransactionEnd()

Catch

BusinessLayer.TransactionRollback()

End Try

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Wed, 03 Jan 2007 14:51:45 GMT

No problem :)

RE: Using Strata Frame's connection to the datasource...

Robin J Giltner — Wed, 03 Jan 2007 14:49:09 GMT

Awesome, thanks Ben. That should save some heartache at a later date.

Thanks.

Robin Giltner

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Wed, 03 Jan 2007 14:43:58 GMT

You can still use the same key with multiple users talking to the same database, because the key is only used on the client side. However, when you add the ES to the mix, the key is used as an identifier by the ES as well, so it would probably be best for you to use a unique key since you're planning on using the ES.

RE: Using Strata Frame's connection to the datasource...

Robin J Giltner — Wed, 03 Jan 2007 14:08:58 GMT

What about with multiple users using the same application on the same database? And what about the Middle Tier when it comes out?

Thanks,

Robin Giltner

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Wed, 03 Jan 2007 14:01:18 GMT

It's perfectly fine for all transactions to have the same name. But, you can only have one transaction with a specific name at a time. A transaction's life begins when you call TransactionBegin() and ends when you call TransactionCommit() or TransactionRollback(). So, if you need to have more than one transaction open at a time, you'll have to use a unique name for each one (which will almost always happen in a web environment).

RE: Using Strata Frame's connection to the datasource...

Robin J Giltner — Wed, 03 Jan 2007 13:56:25 GMT

This post has helped me a ton. Quick question, is it important to make sure that each transaction has a different name? Ir is it ok to have all Transactions named "MyTrans" or some such.

Thanks,

Robin Giltner

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Tue, 02 Jan 2007 13:51:13 GMT

Yes, it throws an exception if an error occurs, so you can use a basic try/catch within your code to handle the error. The error will come back as a SqlException which has all sorts of properties like the ErrorCode and the Errors collection that will give you extended information on the error.

RE: Using Strata Frame's connection to the datasource...

StarkMike — Fri, 22 Dec 2006 09:56:38 GMT

When using the DataBasics.DataSources("")... method of executing stored procedures... how does it handle errors that might occur? Does it throw and exception?

Also... Do you think I need any error handling in my stored procedures or user-defined functions to pass back info to my app or will the info get passed back without them?

RE: Using Strata Frame's connection to the datasource...

Trent L. Taylor — Mon, 09 Oct 2006 21:23:45 GMT

Yes, you can do this. Really the only difference is that is sets the connection for you. But yes, you can this this also.

RE: Using Strata Frame's connection to the datasource...

StarkMike — Mon, 09 Oct 2006 15:04:49 GMT

Could you also do it like this?

DataBasics.DataSources("").ExecuteScalar(cmd)

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Mon, 09 Oct 2006 11:53:34 GMT

I'm glad that worked for you :)

RE: Using Strata Frame's connection to the datasource...

StarkMike — Mon, 09 Oct 2006 08:51:49 GMT

Thanks Ben!

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Mon, 09 Oct 2006 08:13:12 GMT

Whenever you use the .DataSource("") to execute a command, it creates a new SqlConnection object to execute your command on. However, when you attach a transaction to a command, you have to use the same connection as the transaction, so, you won't be able to use the ExecuteScalar() or ExecuteStoredProcedure() methods of the DbDataSourceItem when you are using a transaction, you will have to execute the SqlCommand objects directly.

'-- Start the transaction
DataBasics.DataSources("").TransactionBegin("MyTrans", Data.IsolationLevel.ReadCommitted)

'-- Create the command object
Dim cmd As New SqlCommand("STI30PostTempDetailsToPOOrderDetails")
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)
cmd.Connection = CType(DataBasics.DataSources("").Transactions("MyTrans").Connection, SqlConnection)
cmd.Parameters.AddRange(lodefaultParams.ToArray)

'-- Execute the command
Dim returnVal As Object = cmd.ExecuteScalar()

'-- Create the command object
cmd = New SqlCommand("STI30DeleteRecordsFromSTItmpPODetails")
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)
cmd.Connection = CType(DataBasics.DataSources("").Transactions("MyTrans").Connection, SqlConnection)
cmd.Parameters.AddRange(lodefaultParams.ToArray)

'-- Execute the command
returnVal = cmd.ExecuteScalar()

'-- Commit the transaction
DataBasics.DataSources("").TransactionCommit("MyTrans")

RE: Using Strata Frame's connection to the datasource...

StarkMike — Fri, 06 Oct 2006 13:55:13 GMT

Ok, thanks for the heads up.

RE: Using Strata Frame's connection to the datasource...

Trent L. Taylor — Fri, 06 Oct 2006 13:52:30 GMT

I will have to set this up and look at it closer. It will more than likely not be until Monday until I can look at this in more detail with a sample. Thanks.

RE: Using Strata Frame's connection to the datasource...

StarkMike — Fri, 06 Oct 2006 12:47:54 GMT

I'm trying to use a transaction with two stored procedures and its not working correctly. Here is the error message I get and the code below I am trying to execute. I've bolded the line of code that it stops on.

The transaction is either not associated with the current connection of has been completed.

[code]
'-- Start the transaction
DataBasics.DataSources("").TransactionBegin("MyTrans", Data.IsolationLevel.ReadCommitted)

'-- Create the command object
Dim cmd As New SqlCommand("STI30PostTempDetailsToPOOrderDetails")
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)
cmd.Parameters.AddRange(lodefaultParams.ToArray)

'-- Execute the command
[b]DataBasics.DataSources("").ExecuteScalar(cmd)[/b]

'-- Create the command object
cmd = New SqlCommand("STI30DeleteRecordsFromSTItmpPODetails")
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)
cmd.Parameters.AddRange(lodefaultParams.ToArray)

'-- Execute the command
DataBasics.DataSources("").ExecuteScalar(cmd)

'-- Commit the transaction
DataBasics.DataSources("").TransactionCommit("MyTrans")
[/code]

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Thu, 24 Aug 2006 15:01:30 GMT

If you need manual access to a transaction, you will need to create the transaction and create the SqlCommand objects manually and execute them like this:

Try
'-- Start the transaction
DataBasics.DataSources("").TransactionBegin("MyTrans", Data.IsolationLevel.ReadCommitted)

    '-- Create the command object
    Dim command As New SqlCommand("STI30PostTempTagsToInventory")
    command.CommandType = Data.CommandType.StoredProcedure
    command.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)

'-- Execute the command
DataBasics.DataSources("").ExecuteScalar(command)

'-- Commit the transaction
DataBasics.DataSources("").TransactionCommit("MyTrans")

Catch ex As Exception

DataBasics.DataSources("").TransactionRollback("MyTrans")

End Try

RE: Using Strata Frame's connection to the datasource...

StarkMike — Thu, 24 Aug 2006 14:53:32 GMT

Is it possible for me to bind these to a transaction and roll them back if any of them fail? I wasnt sure if StrataFrame supports transactional updates.

DataBasics.DataSources("").ExecuteStoredProcedure("STI30PostTempTagsToInventory", DbCommandExecutionType.ExecuteNonQuery, loParams.ToArray)
DataBasics.DataSources("").ExecuteStoredProcedure("STI30CreateProductionCosts", DbCommandExecutionType.ExecuteNonQuery, loParams.ToArray)

Thanks

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Thu, 24 Aug 2006 13:59:26 GMT

Imports MicroFour.StrataFrame.Data

DataBasics.DataSources("").ExecuteStoredProcedure("STI80CreateTagsInInvTempTable", _
DbCommandExecutionType.ExecuteScalar, loParams.ToArray)

Will work fine. That method will return an object.

RE: Using Strata Frame's connection to the datasource...

StarkMike — Thu, 24 Aug 2006 13:16:38 GMT

How do I convert this statement to work like the one below it? So that an object is returned?

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteScalar().ExecuteStoredProcedure("STI80CreateTagsInInvTempTable", MicroFour.StrataFrame.Data.DbCommandExecutionType.ExecuteScalar, loParams.ToArray)

Dim oResult As Object = SqlHelper.ExecuteScalar(CONNECTION_STRING, CommandType.StoredProcedure, "STI40GetProductID", ProductIDParams.ToArray)

RE: Using Strata Frame's connection to the datasource...

StrataFrame Team — Tue, 08 Aug 2006 13:10:18 GMT

Yes, it should have throw an SqlException indicating that a required parameter was not supplied... The stored procedure itself wouldn't have caused an error because it never would have even been executed.