Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

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,
0
shah36
Asked:
shah36
  • 3
  • 2
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
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 !!
0
 
shah36Author Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
Why dont handle this in a stored procedure.. ? If you can explain a small part then we can handle that in SQL itself via proc.
0
 
shah36Author Commented:
Yes i can do this within stored procedure was just thinking to learn bit of OOPs on the way
0
 
Pawan KumarDatabase ExpertCommented:
I think that the SET based would be good for this rather than the other one.

Great !
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now