Re: C# and SQL - how to insert few lines together

Tech-Archive recommends: Fix windows errors by optimizing your registry

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,


"Dave" wrote:

> 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;
> }
> }
> }
>
>
> --
> Dave Sexton
> dave@xxxxxxxxxxxxxxxxxxx
> -----------------------------------------------------------------------
> "Gidi" <shnapsi@xxxxxxxxxxxxxxxxxxxx> wrote in message news:DD0BE545-2A7F-45F7-9870-34B6E81CC064@xxxxxxxxxxxxxxxx
> > 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.
>
>
>
.

Quantcast