Solved

Object Oriented Best Practice

Posted on 2016-11-01
5
41 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
  • 3
  • 2
5 Comments
 
LVL 24

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 24

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 24

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SlingPlayer alternatives 1 35
Run time Error 4 34
Javascript and Jquery not firing 9 42
Best resource to learn entity framework 2 8
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now