服务器获取mysql,从mySQL服务器获取记录。

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.public List GetCarrier(System.Nullable p_BillPaymentProviderID, System.Nullable p_CarrierID)

{

try

{

List records = new List();

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

解决方案This is one of the more standard approaches and is ok...

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 ),

3) code execution...

etc.

Plese, see these to improve sql query execution time:

MySQL Performance Blog[^]

10 essential performance tips for MySQL[^]

I''d add a function:

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.