Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 ASP.NET
 proposal

Author  Topic 

programer
Posting Yak Master

221 Posts
Posted - 2010-08-04 : 17:18:46
string CreditCardNumberEncrypt = Encrypt(CreditCardNumber.Text);
string NameOnCardEncrypt = Encrypt(NameOnCard.Text);

MembershipUser myObject = Membership.GetUser();
string UserID = myObject.ProviderUserKey.ToString();

SqlDataSource2.InsertParameters["UserID"].DefaultValue = UserID;
// SqlDataSource2.InsertParameters["PaymentId"].DefaultValue = "2";
SqlDataSource2.InsertParameters["PaymentType"].DefaultValue = "dasdasdsa";
SqlDataSource2.Insert();


//Find Max Private key
SqlConnection conn = new SqlConnection("data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\\aspnetdb.mdf;User Instance=true");
SqlDataAdapter a = new SqlDataAdapter
("select MAX(PaymentId) from tbl_payments;", conn);
DataSet s = new DataSet();
a.Fill(s);
foreach (DataRow dr in s.Tables[0].Rows)
{

Label3.Text = (dr[0].ToString());



string PaymentId = (dr[0].ToString());

string year = YYYY.Text;
string yearEnd = year.Substring(year.Length - 2);

var TestRecords = new[] {
new { UserID = UserID,
CARDID = PaymentId,
Attribute = "Credit card number",
Value = CreditCardNumberEncrypt + UserID },
new { UserID = UserID,
CARDID = PaymentId,
Attribute = "Name on card",
Value = NameOnCardEncrypt },
new { UserID = UserID,
CARDID = PaymentId,
Attribute = "Expiry Date",
Value = MM.Text +"/" + yearEnd }};
foreach (var item in TestRecords)
{
SqlDataSource1.InsertParameters["UserID"].DefaultValue = item.UserID;
SqlDataSource1.InsertParameters["CARDID"].DefaultValue = item.CARDID;
SqlDataSource1.InsertParameters["AttributeName"].DefaultValue = item.Attribute;
SqlDataSource1.InsertParameters["Value"].DefaultValue = item.Value;
SqlDataSource1.Insert();


}

}

First, save the table tbl_Payments. When all the data in a table stored in the column PaymentId (primary key - autoinc).
This information is stored in another table tbl_PaymentDetails.

Does this make sense?
Thanks.

Kristen
Test

22859 Posts
Posted - 2010-08-05 : 03:57:29
"select MAX(PaymentId) from tbl_payments;", conn);
...
SqlDataSource1.InsertParameters["CARDID"].DefaultValue = item.CARDID;
...

what happens if another process does the MAX(PaymentId) at the same time as you? Both will try to add the same child records. This method is not ATOMic.

I don't know why you are saving Credit Card details in a Key/Value attributes table, but storing them in a table one-row-per-transaction would probably solve this problem, and Key/Value attribute tables are only ever last-resort here.
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 11:12:10
quote:
Originally posted by Kristen

"select MAX(PaymentId) from tbl_payments;", conn);
...
SqlDataSource1.InsertParameters["CARDID"].DefaultValue = item.CARDID;
...

what happens if another process does the MAX(PaymentId) at the same time as you? Both will try to add the same child records. This method is not ATOMic.

I don't know why you are saving Credit Card details in a Key/Value attributes table, but storing them in a table one-row-per-transaction would probably solve this problem, and Key/Value attribute tables are only ever last-resort here.




How do me recommend?
Thanks.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2010-08-05 : 12:34:50
one way to handle this is to use a SqlTransaction class for your code with the serilizable isolation level.
This way your parent and child inserts will happen atomicaly.

the thing to watch it here is when you have many (100s) users all purchasing at the same time.
But when you reach that level of traffic you usually have to solve other problems first



___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 12:56:36
quote:
Originally posted by spirit1

one way to handle this is to use a SqlTransaction class for your code with the serilizable isolation level.
This way your parent and child inserts will happen atomicaly.

the thing to watch it here is when you have many (100s) users all purchasing at the same time.
But when you reach that level of traffic you usually have to solve other problems first



___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP


thanks!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-08-05 : 13:20:18
I think risky to do that from the application - if the application pauses between Start Transaction and Commit (or rollback) other users will be locked out.

better to do all the SQL in one statement - which either succeeds or fails.

One way is to use a stored procedure - one call from your application, the stored procedure can do several steps such as:

Begin transaction
INSERT into Table1
Get IDENTITY number assigned
INSERT into Table2 with ID
INSERT into Table3 with ID
Commit

another way would be to append all the SQL statements into one string variable (in your application) and then send them to SQL Server for execution as a single operation.
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 16:22:06
quote:
Originally posted by Kristen

I think risky to do that from the application - if the application pauses between Start Transaction and Commit (or rollback) other users will be locked out.

better to do all the SQL in one statement - which either succeeds or fails.

One way is to use a stored procedure - one call from your application, the stored procedure can do several steps such as:

Begin transaction
INSERT into Table1
Get IDENTITY number assigned
INSERT into Table2 with ID
INSERT into Table3 with ID
Commit

another way would be to append all the SQL statements into one string variable (in your application) and then send them to SQL Server for execution as a single operation.


Kristen:

What about this way?

string connectionString = "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\\aspnetdb.mdf;User Instance=true";
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();

SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Transaction = sqlTransaction;

string CreditCardNumberEncrypt = Encrypt(CreditCardNumber.Text);
string NameOnCardEncrypt = Encrypt(NameOnCard.Text);

MembershipUser myObject = Membership.GetUser();
string UserID = myObject.ProviderUserKey.ToString();

SqlConnection dataConnection = new SqlConnection();
dataConnection.ConnectionString = "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\\aspnetdb.mdf;User Instance=true";
dataConnection.Open();

SqlCommand dataCommand = new SqlCommand("INSERT INTO tbl_Payments (UserId, PaymentType) " +
"VALUES (@UserId, @PaymentType);SELECT SCOPE_IDENTITY()", dataConnection);


SqlParameter myUserId = new SqlParameter("@UserId", UserID);
SqlParameter myPaymentType = new SqlParameter("@PaymentType", DropDownList4.Text);

dataCommand.Parameters.Add(myUserId);
dataCommand.Parameters.Add(myPaymentType);

// dataCommand.ExecuteNonQuery();


int UserId = Convert.ToInt32(dataCommand.ExecuteScalar().ToString());
Label3.Text = (UserId.ToString());

string year = YYYY.Text;
string yearEnd = year.Substring(year.Length - 2);

var TestRecords = new[] {
new { UserID = UserID,
CARDID = (UserId.ToString()),
Attribute = "Credit card number",
Value = CreditCardNumberEncrypt },
new { UserID = UserID,
CARDID = (UserId.ToString()),
Attribute = "Name on card",
Value = NameOnCardEncrypt },
new { UserID = UserID,
CARDID = (UserId.ToString()),
Attribute = "Expiry Date",
Value = MM.Text +"/" + yearEnd }};
foreach (var item in TestRecords)
{

SqlCommand dataCommand2 = new SqlCommand("INSERT INTO tbl_PaymentDetails (UserId, CardId, AttributeName, Value) " +
"VALUES (@UserId, @CardId, @AttributeName, @Value)", dataConnection);


SqlParameter myUserId2 = new SqlParameter("@UserId", item.UserID);
SqlParameter myCardId = new SqlParameter("@CardId", item.CARDID);
SqlParameter myAttributeName = new SqlParameter("@AttributeName", item.Attribute);
SqlParameter myValue = new SqlParameter("@Value", item.Value);


dataCommand2.Parameters.Add(myUserId2);
dataCommand2.Parameters.Add(myCardId);
dataCommand2.Parameters.Add(myAttributeName);
dataCommand2.Parameters.Add(myValue);
dataCommand2.ExecuteNonQuery();
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2010-08-05 : 17:15:39
doing it this way is no improvement than doing it the first way you did it.
what Kristen meant was to put all your SQL into one string variable and send that to the SQL Server.
but this would be then better implemented by a stored proc.

as for your original method you should probably introduce some optimistic concurency via rowversion/timestamp type column
for the max value.

any reason why you're not using an ORM which solves these problems out of the box?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 17:20:52
quote:
Originally posted by spirit1

doing it this way is no improvement than doing it the first way you did it.
what Kristen meant was to put all your SQL into one string variable and send that to the SQL Server.
but this would be then better implemented by a stored proc.

as for your original method you should probably introduce some optimistic concurency via rowversion/timestamp type column
for the max value.

any reason why you're not using an ORM which solves these problems out of the box?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP


spirit1:
Did not you think in this way? sqltransaction?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2010-08-05 : 17:35:49
using a serializable isolation level is a pesimistic concurency option.
it will not allow other users to insert new private keys.
this way you will always get the max key.
the negative effect of this is that while this is happening all other users
will have to wait for the transaction to end.
Because of that your transaction should span as little lines of code as possible.
the problem that Kristen talks about is that the code covered by the transaction can for whatever reason slow down.
it's not likely but it can happen due to whatever reasons.

The second way is to have a stored procedure that you pass needed data to and you do everything in it.
The good thing about it is that you remove the dependency on the application slowness.
The serializable transaction isolation level still applies and you have to use begin tran ... commit inside the stored proc.

You can also have an optimistic concurency scenario but this gets complex quick.
it works basicaly like this:
when you get the max private key you alse retreive its rowversion.
Rowversion is a special type of column that is unique for the db and is always increasing.
when you insert data you have to compare your rowversion with the current max private key rowversion.
and complexity just increases from there.

In the end it depends how you want your app to behave, what's the expected workload, etc...

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 17:50:01
quote:
Originally posted by spirit1

using a serializable isolation level is a pesimistic concurency option.
it will not allow other users to insert new private keys.
this way you will always get the max key.
the negative effect of this is that while this is happening all other users
will have to wait for the transaction to end.
Because of that your transaction should span as little lines of code as possible.
the problem that Kristen talks about is that the code covered by the transaction can for whatever reason slow down.
it's not likely but it can happen due to whatever reasons.

The second way is to have a stored procedure that you pass needed data to and you do everything in it.
The good thing about it is that you remove the dependency on the application slowness.
The serializable transaction isolation level still applies and you have to use begin tran ... commit inside the stored proc.

You can also have an optimistic concurency scenario but this gets complex quick.
it works basicaly like this:
when you get the max private key you alse retreive its rowversion.
Rowversion is a special type of column that is unique for the db and is always increasing.
when you insert data you have to compare your rowversion with the current max private key rowversion.
and complexity just increases from there.

In the end it depends how you want your app to behave, what's the expected workload, etc...

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP


Do you have an example how would I do that?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2010-08-05 : 18:06:35
i guess you meant this so here's a nice example of optimistic concurrency using rowversion showing the principle
http://developer.bracora.com/Articles/Optimistic.aspx



___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 18:09:13
quote:
Originally posted by programer

quote:
Originally posted by spirit1

doing it this way is no improvement than doing it the first way you did it.
what Kristen meant was to put all your SQL into one string variable and send that to the SQL Server.
but this would be then better implemented by a stored proc.

as for your original method you should probably introduce some optimistic concurency via rowversion/timestamp type column
for the max value.

any reason why you're not using an ORM which solves these problems out of the box?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP


spirit1:
Did not you think in this way? sqltransaction?


Is it also available for C #? thanks.
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 18:26:13
quote:
Originally posted by programer

quote:
Originally posted by programer

quote:
Originally posted by spirit1

doing it this way is no improvement than doing it the first way you did it.
what Kristen meant was to put all your SQL into one string variable and send that to the SQL Server.
but this would be then better implemented by a stored proc.

as for your original method you should probably introduce some optimistic concurency via rowversion/timestamp type column
for the max value.

any reason why you're not using an ORM which solves these problems out of the box?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP


spirit1:
Did not you think in this way? sqltransaction?


Is it also available for C #? thanks.



Please check out this tutorial: http://www.asp.net/data-access/tutorials/implementing-optimistic-concurrency-cs

and tell if it is correct.

thanks.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2010-08-05 : 18:28:05
the example on the page? i don't know.
but translating it from VB should be straight forward.

or google for rowversion concurreny example C# and you should get a few results.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2010-08-05 : 18:34:36
quote:
Originally posted by programer
Please check out this tutorial: http://www.asp.net/data-access/tutorials/implementing-optimistic-concurrency-cs

and tell if it is correct.

thanks.


i haven't read the whole article but the principle show seems correct.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 18:39:40
quote:
Originally posted by spirit1

the example on the page? i don't know.
but translating it from VB should be straight forward.

or google for rowversion concurreny example C# and you should get a few results.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP


Ok.

Just one question:

I have one table tbl_Payment. In this table, the UserId, PaymentId, PaymentType ...

I have another table tbl_PaymentDetails in this table is UserId, PaymentId, AttributeName, Value.

If I TextBox1.text = Credit Card Number
If I TextBox1.text = Number On Name

It is also possible to store data:
AttributeName
- Credit Card Number
- Number On Name

One-click 'Save'

thanks.
Go to Top of Page

programer
Posting Yak Master

221 Posts
Posted - 2010-08-05 : 20:47:11
quote:
Originally posted by programer

quote:
Originally posted by spirit1

the example on the page? i don't know.
but translating it from VB should be straight forward.

or google for rowversion concurreny example C# and you should get a few results.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.8 out!

SQL Server MVP


Ok.

Just one question:

I have one table tbl_Payment. In this table, the UserId, PaymentId, PaymentType ...

I have another table tbl_PaymentDetails in this table is UserId, PaymentId, AttributeName, Value.

If I TextBox1.text = Credit Card Number
If I TextBox1.text = Number On Name

It is also possible to store data:
AttributeName
- Credit Card Number
- Number On Name

One-click 'Save'

thanks.



I understand the code written in App_code.

Example:
Customer.cs
example1.cs
example2.cs

ObjectDatasSource use.

Am I right?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site