|
I have written an application just as proof of concept and it works fine. But, my boss think its not safe to do like this and he refering to Online Book Which say :-
----------------------
Specifying Transaction Boundaries
You can identify when SQL Server transactions start and end with Transact-SQL statements or API functions and methods.
Transact-SQL statements
Use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements to delineate transactions. These are primarily used in DB-Library applications and in Transact-SQL scripts, such as the scripts that are run using the osql command prompt utility.
API functions and methods
Database APIs such as ODBC, OLE DB, and ADO contain functions or methods used to delineate transactions. These are the primary mechanisms used to control transactions in a SQL Server application.
Each transaction must be managed by only one of these methods. Using both methods on the same transaction can lead to undefined results. For example, you should not start a transaction using the ODBC API functions, and then use the Transact-SQL COMMIT statement to complete the transaction. This would not notify the SQL Server ODBC driver that the transaction was committed. In this case, use the ODBC SQLEndTran function to end the transaction.--------------
As per this document I am not suppose to mix transaction between ADO.NET and T-SQL. And I am not mixing transactions instead, I am nesting it.
Is there is any published article or document online which can help me explaning to my BOSS?
Thanks
Sanjeev
|
|
|
|
|
What the book is saying, and you've already realised, is that if you start a transaction in ADO.NET you must complete it in ADO.NET. If you start a transaction in T-SQL you must complete it in T-SQL. You can of course nest transactions inside each other.
For example (in this pseudo code)
Start transaction 1
Start transaction 2
-- Do stuff
End transaction 2
End transaction 1
The above is perfectly fine. Transaction 1 and 2 can both be in T-SQL, or both be in ADO.NET or Transaction 1 can be in ADO.NET with Transaction 2 being in T-SQL.
Think of it like loops, you can start a for loop and call into another method that perhaps has another for loop inside it. The for loop in the inner method must complete before the for loop in the outer method can complete. Does this make sense?
As for a published article... I am not sure about that. The text you've quoted seems perfectly reasonable to me. How competant is your boss? (Hmmm... maybe that is a bad question) How technical is your boss? Could he understand it in a way similar to the for loop analogy? If he is less technical, how about a Russian doll analogy? (The Russian dolls that are hollow and one fits inside the other, they split at the waist so you can enclose one doll around another. Pretend that the base is a start transaction, the head is the end transaction, and each size of doll is a different technology. You must obviously match the correct base with the correct head otherwise they won't fit together properly.)
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Thanks for the reply
I Hope my boss will understand this.
|
|
|
|
|
Good Luck! 
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Hi All,
Is there any way that I can parse and validate a SQL query (like the Parse Query function in Query Analyzer of SQL Server) before actually executing it in .NET code? Thanks a lot!
Tony Cheng
|
|
|
|
|
Execute SET PARSEONLY ON
Test your syntax and then
Execute SET PARSEONLY OFF
Sanjay Sansanwal
www.sansanwal.com
|
|
|
|
|
Thanks a lot, Sanjay.
Tony
|
|
|
|
|
I have a DataTable with a PKey defined as a GUID. The problem is trying to "lookup" values in the DataTable.Select Method. Since this is a GUID column, I cannot do the normal things like I could in SQL Server (i.e. DataTable.Select("ID = '" & sID & "'")). In SQL, the GUID DataType is treated like a String.
I generated an XSD Schema of my table, and I got this info for the PKey:
xs:element name="ID" msdata:DataType="System.Guid, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xs:string"
How can I get my DataTable to treat my PKey like a String at run time without loading aa Schema first? Or, how can I lookup GUID values in a DataTable???  
|
|
|
|
|
Ok, for some unknown reason, this now works when I know before it didn't
|
|
|
|
|
Alright, I'm about ready to yank what's left of my hair out. Hopefully someone out there has the answer to my problem. Here it is…
I want to allow users to type any damn fool thing they want into a text field and then store that string in a database. I don’t like the idea of disallowing certain characters. I’d like to figure a way to store any string in the database regardless of what silly characters the user inputs.
I’ve done quite a bit of looking around on the internet for the answer to this problem. Surprisingly no one seems to have a good answer. I thought this would be easy and that I would find a viable answer right away. I have to admit that I am a little disappointed with the development community as a whole right now. For shame!
Anyway, I figured out how to deal with apostrophes and “single-qoutes”. ‘ this character whatever you call it anyway. If you simply double-up an apostrophe in a SQL query, for example: the string “Mr. O’Connell” would be changed to “Mr. O’’Connell”. This seems to work fine. Here’s the problem that’s killing me, what if the string was “Mr. O”Connell”, now what do you do. Obviously, it would be stupid to use a quotation mark it this way, but I want to allow users to indulge their stupidity. How in the heck do you put that in a SQL query without invoking a database error? Also, what about other special characters like: \ @ # $ % and so on. What if the string was something like this “Mr. “\@#$’%”. There has got to be an answer.
Not that it matters a whole lot, but the application is written in VB.Net and I am querying against a MS Access database. Access is just for now, when the application is finished users will have the option of several different providers such as: Access, SQL Server or My SQL.
Please Help
|
|
|
|
|
If you use parameters in your query you should get around this problem. No?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Could you give me an example?
|
|
|
|
|
|
Well, stop writing SQL Insert / Update Queries and start using Objects. ADO (and now ADO.NET) lets you enter any damn thing you want into the DB! It has for many years! 
|
|
|
|
|
Yea, But I hate datasets. They just seem so redundant to me. I already have a database so what's the point of making a copy of the tables in code. I already have tables, there in the database. Plus, I've already got everything working, so to change everything over to use those silly datasets would take a lot of time that I don't have. Sending Non-Queries and using data readers is faster anyway.
|
|
|
|
|
And why are you stiking your tounge out at me. That's not nice. 
|
|
|
|
|
I used to think exactly like you. The point is not redundancy though, it's ease of programability (I think I just made that word up) and also being able to handle unforseen problems before they even occur (i.e. handling any character).
Did you know that you can bind DataAdapters to Stored Procs and also bind the Parameters of the Stored Procs to Columns in DataSets?
But, whatever floats your boat. You might want to Build seperate routines then for Save and Delete operations that use DataCommand Objects.
|
|
|
|
|
mikasa wrote:
Did you know that you can bind DataAdapters to Stored Procs and also bind the Parameters of the Stored Procs to Columns in DataSets?
You know, I just discovered that earlier today. I'm almost sold of the idea that datasets are acceptable. Once the typed datasets are sorted out, and apparently there are vast improvements in this area in .NET 2.0, I might actually "like" them.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
I'm with you there. In VB6, I never used databinding! I hated it and it was flaky. Now, however, .NET has made vast improvements with DataBinding! I've been able to Code Business Layers and UIs with very little code at all! Not only that, it's great having the ability to Map DataColumns to DataCommand Parameters and be able to use Stored Procs to update data. And it doesn't stop there, you can even get Values from the DB with "Output" Parameters! 
|
|
|
|
|
Use of special characters in datatable.select(sql):
Encapsulate each of these special chars with "[" and "]".
e.g
[ will become [[]
] will become []]
% will become [%]
so "text [abc] test" should look like "text [[]abc[]] test"
The simple use of string.replace will not help:
.Replace("[","[[]").Replace("]","[]]")
will produce: "text [[[]]abc[]] test" (not really what we expected).
Esp. for brackets you will have to go through some special "escapes":
.Replace("[","<ESCAPE FOR OPEN>").Replace("]","<ESCAPE FOR CLOSE>").Replace("<ESCAPE FOR OPEN>","[[]").Replace("<ESCAPE FOR CLOSE>","[]]")
worked fine for me.
|
|
|
|
|
Hi,
I am working on the guestbook in VB.NET with SQL db. I have a database for registered users, there are names, surnames, and mainly paths to user-defined images, which they want them to be seen together with their comments.
I have two possibilities how to do it:
1)I will authentify the user with the user control, load the guestbook.aspx - I will get all data from table GUESTBOOK only, when user submits the form, I will get the ImagePath from db and save the name, comment, AND IMAGEPATH to db table GUESTBOOK. I think this way is faster during the guestbook.aspx load, but I have to save the same ImagePaths each time the user sends the comment.
2)I will authentify the user with the user control, load the guestbook.aspx - I will get the names and comments from table GUESTBOOK and ImagePaths based on usernames from USERS table. When user submits the form, I will only save the GUESTBOOK table, not more.
What's better - faster?
|
|
|
|
|
I've got a database, it's not set up with cascading delete, and I'd prefer it not be. Instead, I'd like to turn of foreign key checking when I delete all data from all the tables. Is there a way to delete all data without figuring out the correct delete order, by getting SQL Server to ignore the constraints ?
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
I am currently using the following statement to read data from a table, column
my_String = _strdup((char*) ((_bstr_t)(pConnectRead->GetFields()->GetItem("nom_medico")->GetValue())) );
How do i write data to this column?
Please help,
THanks
Ed Rey
|
|
|
|
|
pConnectRead->GetFields()->GetItem("nom_medico")->Value = (LPCTSTR)my_String;
pConnectRead->Update();
Sanjay Sansanwal
www.sansanwal.com
|
|
|
|
|
I am referencing the libraries from msado15.dll and retreiving data from an access database using visual C++. I have already connected succesfully to the DB, and can read data. I use the following command to retreive information from a particular field in the table:
my_String = _strdup((char*) ((_bstr_t)(pConnectRead->GetFields()->GetItem("nom_medico")->GetValue())) );
How do i write a boolean value to a field, once i have established connection to the DB and currently in the row of interest.
Eduardo M. Rey
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
