Solved

Object Oriented Best Practice

Posted on 2016-11-01
5
34 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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Accepted Solution

by:
Pawan Kumar Khowal 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 17

Assisted Solution

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

20 Experts available now in Live!

Get 1:1 Help Now