StrataFrame Forum

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

http://forum.strataframe.net/Topic2037.aspx

By StarkMike - 8/1/2006

How do i use the connection to the database that strataframe has made to execute miscellaneous sql? select statements, stored procedures, etc.
By Trent L. Taylor - 8/1/2006

It depends on what you are trying to do.  All business objects can execute any SQL statement, Stored Procedure, etc.  A business object has the following methods:

FillDataTable()
FillByStoredProcedure()
ExecuteNonQuery()
ExecuteScalar()

and so on... you can look at these through the BO and get a general idea.  If you want to talk straight to the server through the DAL (Data Access Layer) then you will need to do the following:

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteNonQuery("")

MicroFour.StrataFrame.Data.DataBasics.DataSources("").GetDataTable("", Nothing)

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteStoredProcedure("", StrataFrame.Data.DbCommandExecutionType.ExecuteNonQuery, Nothing)

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteScalar("")

Let me know if this doesn't answer your question.  There is a new help install in the "My Account" area that includes a class library reference that may be helpful.  It will show all properties, events, and methods and a description for each item.

By StarkMike - 8/3/2006

I couldnt see the ExecuteNonQuery method on one of my business objects and how do i know what to specify for the datasources?



Here 
.DataSources("")
By StrataFrame Team - 8/3/2006

If you only have one data source, leave it as an empty string, since that is the default.
By Trent L. Taylor - 8/3/2006

The default datasource it generally always an empty string ("").  The data source depends on what you have defined in your AppMain as a required data source.

MicroFour.StrataFrame.Data.ConnectionManager.AddRequiredDataSourceItem("", "SQL Connection", Data.DataSourceTypeOptions.SqlServer, "StrataFrame", "This connection is used by all design-time components and houses the business object mappings, meta-data, messaging, and localization data.")

The string in red above is the name of the key that you will need to specify.  It determines which connection you want to use when you reference the DataSources collection.
By StarkMike - 8/8/2006

Ok, this was the code I used:

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteStoredProcedure("STI30DeleteTagsFromInventoryTempByUserID", MicroFour.StrataFrame.Data.DbCommandExecutionType.ExecuteNonQuery, New SqlParameter("@UserID", gobjUser.UserID))




and it worked... however I forgot to specify a parameter that the stored procedure needed, and when the code ran... nothing happened... i didnt get an error message or anything. Why would it not come back and tell me that I had forgot a parameter?
By StrataFrame Team - 8/8/2006

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. 
By StarkMike - 8/24/2006

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)
By StrataFrame Team - 8/24/2006

Imports MicroFour.StrataFrame.Data

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

Will work fine.  That method will return an object.
By StarkMike - 8/24/2006

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
By StrataFrame Team - 8/24/2006

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

By StarkMike - 10/6/2006

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.





'-- 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

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



'-- 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")
By Trent L. Taylor - 10/6/2006

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.
By StarkMike - 10/6/2006

Ok, thanks for the heads up.
By StrataFrame Team - 10/9/2006

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")

By StarkMike - 10/9/2006

Thanks Ben!
By StrataFrame Team - 10/9/2006

I'm glad that worked for you Smile
By StarkMike - 10/9/2006

Could you also do it like this?



DataBasics.DataSources("").ExecuteScalar(cmd)
By Trent L. Taylor - 10/9/2006

Yes, you can do this.  Really the only difference is that is sets the connection for you.  But yes, you can this this also.
By StarkMike - 12/22/2006

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?

By StrataFrame Team - 1/2/2007

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.
By Robin J Giltner - 1/3/2007

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

By StrataFrame Team - 1/3/2007

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).
By Robin J Giltner - 1/3/2007

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

By StrataFrame Team - 1/3/2007

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.
By Robin J Giltner - 1/3/2007

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

Thanks.

Robin Giltner

By StrataFrame Team - 1/3/2007

No problem Smile
By Bradley Marston - 3/23/2007

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


 
By Bradley Marston - 3/23/2007

please disreguard above

thanks

By StrataFrame Team - 3/26/2007

Hehe, will do.