从mySQL服务器获取记录. [英] Fetch records from mySQL server.

问题描述

我有一个托管在Google云上的mySQL服务器，想从该服务器中获取记录，以下是我正在使用的方法.所以我的问题是，有没有更好的方法可以这样做.提前谢谢.

  public  List< GetCarrierResult> GetCarrier(System.Nullable< p_BillPaymentProviderID，System.Nullable< p_CarrierID))
{
   尝试
   {
      列表< GetCarrierResult> records = 新 List< GetCarrierResult>();

      如果(Connection.State！= ConnectionState.Open)
      {
         Connection.Open();
      }

      Command = 新 MySqlCommand("  ， 联系);
      Command.CommandType = CommandType.StoredProcedure;

      如果需要，// 添加参数.
      Command.Parameters.Add(新 MySqlParameter(" ，p_BillPaymentProviderID))；
      Command.Parameters.Add(新 MySqlParameter(" ，p_CarrierID))；

      如果(交易！= 空)
      {
         Command.Transaction =事务；
      }

      da = 新 MySqlDataAdapter();
      da.SelectCommand =命令;

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

       foreach (在ds.Tables [ 0 中的DataRow行 span>].rows)
      {
         GetCarrierResult结果= 新 GetCarrierResult()
         {
            StoreID = Convert.ToInt32(Row [" ])，
            CarrierID = Convert.ToInt32(Row [" ])，
            CarrierName = Row [" ] == DBNull.Value吗? 空:(字符串)行[" 运营商名称"]，
            MasterCarrierID = Convert.ToInt32(Row [" ])，
            MasterCarrierName = Row [" ] == DBNull.Value吗? 空:(字符串)行["  MasterCarrierName"]，
            BillPaymentProviderID = Convert.ToInt32(Row [" ])，
            ProviderName =行[" ] == DBNull.Value吗? 空:(字符串)行["  ProviderName"]，
            ProductID = Convert.ToInt32(Row [" ])，
            ProductName = Row [" ] == DBNull.Value吗? 空:(字符串)行[" 产品名称"]，
            ApplyTax1 = Convert.ToBoolean(Row [" ])，
            IsPaymentTax = Convert.ToBoolean(Row [" ])，
            PaymentTaxRate = Convert.ToDecimal(Row [" ])，
            IsExtraTax = Convert.ToBoolean(Row [" ])，
            ExtraTaxType = Convert.ToInt32(Row [" ])，
            ExtraTaxName = Row [" ] == DBNull.Value吗? 空:(字符串)行["  ExtraTaxName"]，
            ExtraTaxValue = Convert.ToDecimal(Row [" ])，
            IsFee = Convert.ToBoolean(Row [" ])，
            FeeProductID = Row [" ] == DBNull.Value吗? ( Int32 ?)空:Convert.ToInt32(Row [ " ])，
            FeeProductName = Row [" ] == DBNull.Value吗? 空:(字符串)行["  FeeProductName"]，
            FeeType = Convert.ToInt32(Row [" ])，
            FeeValue = Convert.ToDecimal(Row [" ])，
            FeeCostType = Convert.ToInt32(Row [" ])，
            FeeCostValue = Convert.ToDecimal(Row [" ])，
            EmployeeID = Convert.ToInt32(Row [" ])，
            FullName = Row [" ] == DBNull.Value吗? 空:(字符串)行[" 全名"]，
            WebAddress =行[" ] == DBNull.Value吗? 空:(字符串)行["  WebAddress"]，
         };

         records.Add(结果);
      }

      返回记录；
   }
   捕获
   {
      投掷；
   }
} 

有什么办法可以提高速度?

制表精简

解决方案

这是更标准的方法之一，可以...

获取数据的速度取决于很多因素，例如:
1)服务器效率(处理器，高清和RAM容量等)，
2)查询性能(最糟糕的做法之一是使用SELECT *而不是SELECT <field_list>)，
3)代码执行...
等

请查看以下这些以改善sql查询的执行时间:
MySQL Performance Blog [ ^ ]
10个MySQL的基本性能提示 [ 字符串 GetNullableString(数据行，字符串 columnName) { 返回行[columnName] == DBNull.Value吗? 空:(字符串)行[columnName]; }

出于可读性和更好的维护原因. 按数字访问列可能比按名称访问要快一些.
但主要要点是:优化的SQL查询，在表上具有适当的索引.
我不使用DataAdapters和DataSet，而是使用简单的DataReader-但这可能只是个人喜好.

try-catch-throw的使用是WTF.如果从catch块抛出，则抛出更好"异常，原始异常为InnerException.

那些与实现IDisposable的Database一起使用的对象可以包装在using块中-可以确保在发生异常的情况下也可以正确处理它们.并在您的函数中声明它们，而不是作为类成员.

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

解决方案

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

这篇关于从mySQL服务器获取记录.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！