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

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

--
Dave Sexton
dave@xxxxxxxxxxxxxxxxxxx
-----------------------------------------------------------------------
"Gidi" <shnapsi@xxxxxxxxxxxxxxxxxxxx> wrote in message news:8389071A-3064-4FDB-9AAD-1674F0D58EF1@xxxxxxxxxxxxxxxx
> 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.
>>
>>
>>


.