Solved

Object Oriented Best Practice

Posted on 2016-11-01
5
65 Views
Last Modified: 2016-11-01
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
Comment
Question by:shah36
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41868148
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
 

Author Comment

by:shah36
ID: 41868159
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
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41868161
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
 

Author Comment

by:shah36
ID: 41868165
Yes i can do this within stored procedure was just thinking to learn bit of OOPs on the way
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 total points
ID: 41868169
I think that the SET based would be good for this rather than the other one.

Great !
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question