asked on

Object Oriented Best Practice

Hi guys,

Just trying to learn a bit of .Net while using the SSIS script component.
I have got two stored procedures and want to invoke the second stored procedure if there is error in the first one.
That is want to insert a record via a stored procedure and if there is error for that record and doesn't get inserted then want to insert that same record in a separate table.

What should be the structure of the classes? Both the stored procedure have different number of parameters. At the moment i have got the following classes.

public  class AddressesDto
    {
        public int AddressStringId { get; set; }
        public string Guid { get; set; }
        public string UniqueHashString { get; set; }
        public string LocationName { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string AddressLine3 { get; set; }
        public string BuildingNumber { get; set; }
        public string BuildingName { get; set; }
        public string Street { get; set; }
        public string Locality { get; set; }
        public string AdministrativeArea { get; set; }
        public string SubAdministrativeArea { get; set; }
        public string Municipality { get; set; }
        public string PostCode { get; set; }
        public string Iso3166MinusOneCountryAlpha2Code { get; set; }
        public string Longitude { get; set; }
        public string Latitude { get; set; }
        public string SearchString { get; set; }
        public decimal PointBufferDistanceInMeters { get; set; }

    }

Open in new window

public class InsertMethods
    {
        public string cnStr = "Server =162.13.186.141; initial Catalog =PAF; user id =SSIS; Password = Test1234";

        public int InsertData()
        {
            int totalRecords = 0;

            SqlConnection cnSql = new SqlConnection(cnStr);
            cnSql.Open();

            SqlCommand cmSql = new SqlCommand("SelectAllData", cnSql);
            SqlDataReader myDataReader = cmSql.ExecuteReader();

            if (myDataReader.HasRows)
            {

                while (myDataReader.Read())
                {
                    AddressesDto dto = new AddressesDto();

                    dto.AddressStringId =Convert.ToInt32(myDataReader["AddressStringId"]);
                    dto.Guid = myDataReader["Guid"].ToString();
                    dto.UniqueHashString = myDataReader["UniqueHashString"].ToString();
                    dto.LocationName = myDataReader["LocationName"].ToString();
                    dto.AddressLine1 = myDataReader["AddressLine1"].ToString();
                    dto.AddressLine2 = myDataReader["AddressLine2"].ToString();
                    dto.AddressLine3 = myDataReader["AddressLine3"].ToString();
                    dto.BuildingNumber = myDataReader["buildingNumber"].ToString();
                    dto.BuildingName = myDataReader["buildingName"].ToString();
                    dto.Street = myDataReader["street"].ToString();
                    dto.Locality = myDataReader["locality"].ToString();
                    dto.AdministrativeArea = myDataReader["administrativeArea"].ToString();
                    dto.SubAdministrativeArea = myDataReader["subAdministrativeArea"].ToString();
                    dto.Municipality = myDataReader["municipality"].ToString();
                    dto.PostCode = myDataReader["postCode"].ToString();
                    dto.Iso3166MinusOneCountryAlpha2Code = myDataReader["iso3166MinusOneCountryAlpha2Code"].ToString();
                    dto.Longitude = myDataReader["longitude"].ToString();
                    dto.Latitude = myDataReader["latitude"].ToString();
                    dto.SearchString = myDataReader["searchString"].ToString();
                    dto.PointBufferDistanceInMeters = Convert.ToDecimal(myDataReader["pointBufferDistanceInMeters"]);
                   

                    SpMethod sm = new SpMethod();
                    sm.StoredProcedure(dto);



                    totalRecords += 1;
                }
            }

            return totalRecords;
        }
    }

Open in new window

 public class SpMethod
    {
        string connStr = "Server =(local); initial Catalog =Spaces_PAF;  integrated Security = SSPI";

        public void StoredProcedure(AddressesDto Dto)
        {
            using (SqlConnection cnSql = new SqlConnection(connStr))
            {

                SqlParameter errorNumber = new SqlParameter("@ErrorNumber", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                SqlParameter errorSeverity = new SqlParameter("@ErrorSeverity", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                SqlParameter errorState = new SqlParameter("@ErrorState", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                SqlParameter errorProcedure = new SqlParameter("@ErrorProcedure", SqlDbType.NVarChar, 128)
                {
                    Direction = ParameterDirection.Output
                };
                SqlParameter errorLine = new SqlParameter("@ErrorLine", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                SqlParameter errorMessage = new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 4000)
                {
                    Direction = ParameterDirection.Output
                };
                SqlParameter insertedLocations = new SqlParameter("@NumberOfInsertedLocations", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };


                cnSql.Open();

                SqlCommand cmSql = new SqlCommand("[CrossContext].[USP_Locations_AddPointLocations]", cnSql);
                cmSql.CommandType = CommandType.StoredProcedure;

                cmSql.Parameters.Add(new SqlParameter("@LocationGuid", Dto.Guid));
                cmSql.Parameters.Add(new SqlParameter("@AddressStringId", Dto.AddressStringId));
                cmSql.Parameters.Add(new SqlParameter("@LocationName", Dto.LocationName));
                cmSql.Parameters.Add(new SqlParameter("@AddressLine1", Dto.AddressLine1));
                cmSql.Parameters.Add(new SqlParameter("@AddressLine2", Dto.AddressLine2));
                cmSql.Parameters.Add(new SqlParameter("@AddressLine3", Dto.AddressLine3));
                cmSql.Parameters.Add(new SqlParameter("@BuildingNumbers", Dto.BuildingNumber));
                cmSql.Parameters.Add(new SqlParameter("@BuildingName", Dto.BuildingName));
                cmSql.Parameters.Add(new SqlParameter("@Street", Dto.Street));
                cmSql.Parameters.Add(new SqlParameter("@Locality", Dto.Locality));
                cmSql.Parameters.Add(new SqlParameter("@Municipality", Dto.Municipality));
                cmSql.Parameters.Add(new SqlParameter("@AdministrativeArea", Dto.AdministrativeArea));
                cmSql.Parameters.Add(new SqlParameter("@SubAdministrativeArea", Dto.SubAdministrativeArea));
                cmSql.Parameters.Add(new SqlParameter("@PostalCode", Dto.PostCode));
                cmSql.Parameters.Add(new SqlParameter("@Iso3166MinusOneCountryAlpha2Code",
                    Dto.Iso3166MinusOneCountryAlpha2Code));
                cmSql.Parameters.Add(new SqlParameter("@Wgs84Longitude", Dto.Longitude));
                cmSql.Parameters.Add(new SqlParameter("@Wgs84Latitude", Dto.Latitude));
                cmSql.Parameters.Add(new SqlParameter("@PointBufferDistanceInMeters", Dto.PointBufferDistanceInMeters));
                cmSql.Parameters.Add(new SqlParameter("@UniqueHashString", Dto.UniqueHashString));
                cmSql.Parameters.Add(new SqlParameter("@SearchString", Dto.SearchString));
                cmSql.Parameters.Add(errorNumber);
                cmSql.Parameters.Add(errorSeverity);
                cmSql.Parameters.Add(errorState);
                cmSql.Parameters.Add(errorProcedure);
                cmSql.Parameters.Add(errorLine);
                cmSql.Parameters.Add(errorMessage);
                cmSql.Parameters.Add(insertedLocations);

                try
                {
                    cmSql.ExecuteScalar();
                }
                catch (SqlException exception)
                {
                    
                    throw;
                }

                

               //int erNumber =Convert.ToInt32(errorNumber.Value ?? 0);
               //string errMsg = errorMessage.Value.ToString() ?? "No Error";

                cnSql.Close();
           }

       }
   }

Open in new window

Where should i put the method/procedure where it inserts in case of the error in the above?

regards,

Pawan Kumar

Why dont you use DataSet, DataAdapter and DataTables

Get data into the dataSet ..and verify the rows and then decide if you want to insert in another table or nt.

sample..

using (SqlCommand mySqlCommand = new SqlCommand("Test_2_Sp", myCustomConnection))
            {
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlCommand.Parameters.AddWithValue("@firstnum", "1");
                mySqlCommand.Parameters.AddWithValue("@secondnum", "0");
 
                myCustomConnection.Open();
 
                try
                {
                    SqlDataAdapter adp = new SqlDataAdapter(mySqlCommand);
 
                    DataSet myDataSet = new DataSet();
                    adp.Fill(myDataSet);
 
                    if (myDataSet.Tables.Count > 0)
                    {
                        DataTable dt = myDataSet.Tables[0];
                    }
 
                    MessageBox.Show("Hello World!");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ouch!");
                }
                finally
                {
                    MessageBox.Show("Good Bye!");
                }
            }

Open in new window

Hope it helps !!

Ali Shah

ASKER

Hi Pawn,

Thanks for your reply. However the requirement is bit different that what you have suggested. For each row i need to call a stored procedure which does lots of operations for that row. In case of any error in the stored procedure i want to call the second procedure which just inserts the records

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ali Shah

ASKER

Yes i can do this within stored procedure was just thinking to learn bit of OOPs on the way

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial