Fetch records from mySQL server.

Question

4.33/5 (2 votes)

See more:

Hi, I have a mySQL server hosting on google cloud and want to fetch records from that server, below is the method I am using. So my question is that is there any better way of doing so. Thanks in advance.

C#

public List<GetCarrierResult> GetCarrier(System.Nullable<int> p_BillPaymentProviderID, System.Nullable<int> p_CarrierID)
{
   try
   {
      List<GetCarrierResult> records = new List<GetCarrierResult>();

      if (Connection.State != ConnectionState.Open)
      {
         Connection.Open();
      }

      Command = new MySqlCommand("GetCarrier", Connection);
      Command.CommandType = CommandType.StoredProcedure;

      //Add Parameters if procedures requires.
      Command.Parameters.Add(new MySqlParameter("p_BillPaymentProviderID", p_BillPaymentProviderID));
      Command.Parameters.Add(new MySqlParameter("p_CarrierID", p_CarrierID));

      if (Transaction != null)
      {
         Command.Transaction = Transaction;
      }

      da = new MySqlDataAdapter();
      da.SelectCommand = Command;

      ds = new DataSet();
      da.Fill(ds);

      foreach (DataRow Row in ds.Tables[0].Rows)
      {
         GetCarrierResult result = new GetCarrierResult()
         {
            StoreID = Convert.ToInt32(Row["StoreID"]),
            CarrierID = Convert.ToInt32(Row["CarrierID"]),
            CarrierName = Row["CarrierName"] == DBNull.Value ? null : (string)Row["CarrierName"],
            MasterCarrierID = Convert.ToInt32(Row["MasterCarrierID"]),
            MasterCarrierName = Row["MasterCarrierName"] == DBNull.Value ? null : (string)Row["MasterCarrierName"],
            BillPaymentProviderID = Convert.ToInt32(Row["BillPaymentProviderID"]),
            ProviderName = Row["ProviderName"] == DBNull.Value ? null : (string)Row["ProviderName"],
            ProductID = Convert.ToInt32(Row["ProductID"]),
            ProductName = Row["ProductName"] == DBNull.Value ? null : (string)Row["ProductName"],
            ApplyTax1 = Convert.ToBoolean(Row["ApplyTax1"]),
            IsPaymentTax = Convert.ToBoolean(Row["IsPaymentTax"]),
            PaymentTaxRate = Convert.ToDecimal(Row["PaymentTaxRate"]),
            IsExtraTax = Convert.ToBoolean(Row["IsExtraTax"]),
            ExtraTaxType = Convert.ToInt32(Row["ExtraTaxType"]),
            ExtraTaxName = Row["ExtraTaxName"] == DBNull.Value ? null : (string)Row["ExtraTaxName"],
            ExtraTaxValue = Convert.ToDecimal(Row["ExtraTaxValue"]),
            IsFee = Convert.ToBoolean(Row["IsFee"]),
            FeeProductID = Row["FeeProductID"] == DBNull.Value ? (Int32?)null : Convert.ToInt32(Row["FeeProductID"]),
            FeeProductName = Row["FeeProductName"] == DBNull.Value ? null : (string)Row["FeeProductName"],
            FeeType = Convert.ToInt32(Row["FeeType"]),
            FeeValue = Convert.ToDecimal(Row["FeeValue"]),
            FeeCostType = Convert.ToInt32(Row["FeeCostType"]),
            FeeCostValue = Convert.ToDecimal(Row["FeeCostValue"]),
            EmployeeID = Convert.ToInt32(Row["EmployeeID"]),
            FullName = Row["FullName"] == DBNull.Value ? null : (string)Row["FullName"],
            WebAddress = Row["WebAddress"] == DBNull.Value ? null : (string)Row["WebAddress"],
         };

         records.Add(result);
      }

      return records;
   }
   catch
   {
      throw;
   }
}

Anything that could be done to improve speed?

tabulation reduced

Posted 8-Mar-14 2:09am

Qadeer Ahmed Khan

Updated 8-Mar-14 10:07am

Nelek

v3

Add a Solution

Comments

CHill60 8-Mar-14 8:57am

Not posting this as a solution because it's not necessarily "better" ...
You have several variables that are declared outside this function - better to have them in here or passed in (e.g. da, ds, Transaction, Connection)
In your catch clause you are just throwing any exceptions - are you sure you mean to do this (i.e. have the calling code handle all exceptions)
You have to do an lot of null checks there - would it be better to handle the nulls inside your stored procedure?
I am not a fan of Convert.To... I prefer to have "helper" functions (SafeGetInt, SafeGetDecimal or overloaded SafeGetValue) that use the TryParse method of each type - that way I can decide what to do if there is invalid data rather than just having an exception thrown.

Qadeer Ahmed Khan 8-Mar-14 10:07am

Thanks you are right it would be really painful if I have conversion with in same method when it comes to changing. What about speed? Anything you think can speed up the process? does initializing DataApdapter within every method makes it slow..

CHill60 8-Mar-14 10:19am

Shouldn't make an appreciable difference unless you are processing thousands of records

Qadeer Ahmed Khan 8-Mar-14 10:25am

I compared Linq to SQL (on MS SQL Server) and it was quite faster then this not sure what they done. I need better response time as there are not many Record line to fetch but there are rapid requests being made to server.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2014-03-08T03:59:00

Solution 1

This is one of the more standard approaches and is ok...

Posted 8-Mar-14 3:59am

Abhinav S

Maciej Los · Answer 2 · 2014-03-08T09:32:00

The speed of fetching data depends on many things, for example:
1) server efficiency (processor, HD and RAM capacity, etc),
2) query performance (one of the most bad practice is to use SELECT * instead SELECT <field_list>),
3) code execution...
etc.

Plese, see these to improve sql query execution time:
MySQL Performance Blog[^]
10 essential performance tips for MySQL[^]

Bernhard Hiller · Answer 3 · 2014-03-10T00:38:00

I'd add a function:

C#

string GetNullableString(DataRow row, string columnName)
{
    return Row[columnName] == DBNull.Value ? null : (string)Row[columnName];
}

for reason of readabilty and better maintenance.
Accessing the columns by number could be a little faster than by name.
But major point is: optimized SQL query with appropriate indices on the table.
I do not use DataAdapters and DataSets, I prefer a simple DataReader - but that might be just a personal preference.

The use of try-catch-throw is a WTF. If you throw from the catch block, throw a "better" exception with the original exception as InnerException.

Those objects for use with Database which implement IDisposable could be wrapped in using blocks - that will make sure they are disposed of properly also in case of an exception. And declare them inside your function, not as class members.

Fetch records from mySQL server.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0