C# and SQL - how to insert few lines together

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I'm writing a program that deals with product selling. one of my forms
contains a dataGrid which the user enters the product the customer wants to
buy.
this table can contain one line (means one product) or more. each product is
unique and can not be sold twice (i.e, car with license plate). my DB is SQL
and i'm using stored procedures. when the user click the last OK button, i
want to check that none of these products have been sold in different
station (untill one pressed the ok button, the car can still be sold), i'm
using a transaction and i want to check all the products at the same
transaction so if one has been sold, i will notify the user and rollback the
insert command. my problem is to send all the data to the sql at the same
time. how can it be done?
if i'll call for each line to the SP there is a situation which i'll insert
2 lines and i won't insert the third (because it can't be sold) and i don't
want to do so, i want to insert all lines together or not to insert at all.

Thanks, i hope i was cleared.
 
SP's have implicit transactions, but only for the lifetime of the call. This is why on your "3rd" attempt a roll-back would only
effect the latest insert.

To wrap multiple insert operations in a transaction, the approach I would use is an explicit transaction in code. Here's a quick
example for Sql Server:


using (System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection("..."))
{
conn.Open();

using (System.Data.SqlClient.SqlTransaction tran =
conn.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
{
try
{
using (System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand("PerformInsert", conn, tran))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;

System.Data.SqlClient.SqlParameter paramName =
cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);

System.Data.SqlClient.SqlParameter paramDesc =
cmd.Parameters.Add("@Description", System.Data.SqlDbType.VarChar);

// not required:
cmd.Prepare();

// "DataObject" is a hypothetical custom business object. A DataSet and adapter may
// be used instead to perform the following operation.
// "data" is assumed to be a variable that is an enumerable collection of DataObjects.
foreach (DataObject obj in data)
{
// The following lines must ensure that no exception will be thrown,
// or the exception type should be handled in a catch statement below
paramName.Value = obj.Name;
paramDesc.Value = obj.Description;

// The stored procedure should call "Raiserror" if the data insert should fail. This will generate an SqlException when
// the following line is executed. The catch statment below will catch the exception and rollback the entire transaction.
if (cmd.ExecuteNonQuery() == 0) // Set noncount off; in procedure
// If no rows are affected, the transaction should be rolled-back
throw new ApplicationException("Data was not inserted.");
}
}

tran.Commit();
}
catch (ApplicationException ex)
{
tran.Rollback();

// Todo: Handle error, if possible
throw ex;
}
catch (System.Data.SqlClient.SqlException ex)
{
tran.Rollback();

// Todo: Handle error, if possible
throw ex;
}
}
}
 
i'm not checking each time because i want to let the user to know that one of
the products is already sold, and to give the customer to decided weather he
wants to continue or cancel the purchase
 
Hi Gidi

I think solution suggested by Dave is perfect one for that and you will have
more control over the transactions.
Although there is another solution if you want to adapt this. Send data as
text to the sp which contains columns
and rows seperated by delemeters and then extract each row from that text in
you sp and validate and insert, in
this case you can use transaction is stored procedure but I will recommend
the solution of Dave.

Regards
Fiaz Ali Saleemi
 
Hi Gidi

I think solution suggested by Dave is perfect one for that and you will have
more control over the transactions.
Although there is another solution if you want to adapt this. Send data as
text to the sp which contains columns
and rows seperated by delemeters and then extract each row from that text in
you sp and validate and insert, in
this case you can use transaction is stored procedure but I will recommend
the solution of Dave.

Regards
Fiaz Ali Saleemi
 
Hi Dave,

i tried that and i'm having problems inserting paramerts. if i call a SP
without any parameters the syntax is ok and it's working, if i send even one
parameter i get an error (i don't know which one).

here is my code:

public void Insert_Shtar_Buy_Tranc(DataGrid dataGrid1)
{

System.Data.SqlClient.SqlTransaction tran
=m_mf.GetDbCon().GetConn().BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
try
{
System.Data.SqlClient.SqlCommand comm =new
System.Data.SqlClient.SqlCommand("Try", m_mf.GetDbCon().GetConn(), tran);
/* System.Data.SqlClient.SqlParameter date_p=
comm.Parameters.Add("@buy_date",SqlDbType.VarChar,50);
System.Data.SqlClient.SqlParameter time_p=
comm.Parameters.Add("@buy_time",SqlDbType.VarChar,50);
System.Data.SqlClient.SqlParameter cust_num_p=
comm.Parameters.Add("@cust_num",SqlDbType.VarChar,50);*/
System.Data.SqlClient.SqlParameter kod_kli_p=
comm.Parameters.Add("@kod_kli",SqlDbType.VarChar,50);
/* System.Data.SqlClient.SqlParameter kli_num_p=
comm.Parameters.Add("@kli_num",SqlDbType.VarChar,50);
System.Data.SqlClient.SqlParameter
earot_p=comm.Parameters.Add("@earot",SqlDbType.VarChar,50);
System.Data.SqlClient.SqlParameter full_name_p=
comm.Parameters.Add("@full_name",SqlDbType.VarChar,100);*/
// System.Data.SqlClient.SqlParameter num_p= new
SqlParameter("@num",SqlDbType.Int);
for(int i=0;i<2;i++)
{
// The following lines must ensure that no exception will be thrown,
// or the exception type should be handled in a catch statement below


// The stored procedure should call "Raiserror" if the data insert
should fail. This will generate an SqlException when
// the following line is executed. The catch statment below will catch
the exception and rollback the entire transaction.
/* date_p.Value="1";
time_p.Value="2";
cust_num_p.Value=1;
kod_kli_p.Value="32";
kli_num_p.Value="3";
earot_p.Value="3";
full_name_p.Value="3";*/
// comm.Parameters.Add(num_p);
kod_kli_p.Value="32";

// num_p.Direction=ParameterDirection.Output;
if (comm.ExecuteNonQuery() == 0) // Set noncount off; in procedure
// If no rows are affected, the transaction should be rolled-back
throw new ApplicationException("Data was not inserted.");
}
tran.Commit();
}
catch (ApplicationException ex)
{
tran.Rollback();
// Todo: Handle error, if possible
throw ex;
}
catch (System.Data.SqlClient.SqlException ex)
{
tran.Rollback();
// Todo: Handle error, if possible
throw ex;
}
}


what's the problem here?
thanks,
 
It appears that in your example you are not setting the CommandType:
System.Data.SqlClient.SqlCommand comm =new
System.Data.SqlClient.SqlCommand("Try", m_mf.GetDbCon().GetConn(), tran);
Click to expand...
comm.CommandType = System.Data.CommandType.StoredProcedure;

A couple of things to note:

1. You should dispose of your connection and transaction objects when you are no longer using them. In my example the "using"
statements disposed of both objects automattically.

2. It may be easier for you to use an SqlDataAdapter and a strong-typed DataSet. For maintaince I would recommend the DataSet
approach, but if you already have custom business objects than iterating would be fine.

3. In your example you are iterating 2 times and executing the command each time without adjusting the parameter's value. This is
probably not intentional so I wanted to point it out.

GL
 
Back
Top