SQL 参数化示例

单独传递参数：

string sql = string.Format(@"select count(*) from sip_did_subscribe where acct_id=@acct_id and lower(nation_code) = @nation_code and lower(purpose) = @purpose");

                object o = SqlHelper.ExecuteScalar(Conn.IOF, CommandType.Text, sql, iofHelper.NewParameter("@acct_id", accountId), iofHelper.NewParameter("@nation_code", nationCode.ToLower()), iofHelper.NewParameter("@purpose", purpose.ToLower()));

      

参数数组方式：

       
                StringBuilder sql = new StringBuilder();
                sql.Append(@"select top 1000 local_provider_acctid as LP_ACCTID,
                        PORT_IN_DID,
                        first_NAME as F_Name,
                        last_name as L_Name,
                        street_number + '' + '' + street_number_suffix + '' + pre_directional + '' + Street_name + '' + street_suffix + '' + post_directional + '' + secondary_location_description + '' + unit as address,
                        city,
                        state,
                        country,
                        zip,
                        zip4,
                        ACCTID,
                        BTN,
                        order_id as OrderID,
                        L3_order_id as L3OrderID,
                        convert(char(10), LOA_DATE, 101) as LOA_DATE,
                        port_in_provider,
                        DESCRIPTION AS STATUS,
                        A.pon_id
                        from bb_DID_PORT_IN_REQUEST A, bb_DID_PORT_IN_STATUS B
                        WHERE A.STATUS = B.STATUS ");

                    sql.Append(" and acctid =@acct_id ");
        
                    sql.Append(" and order_id =@order_id ");
     
                    sql.Append(" and l3_order_id =@l3_order_id ");
       
                    sql.Append(" and acctid = (select acct_id from bb_account_basic where bbn=@bb_number) ");
     
             

                IDbDataParameter[] parameters = new IDbDataParameter[]
                                              {
                                                  iofHelper.NewParameter("@acct_id", query.AcctId),
                                                  iofHelper.NewParameter("@order_id", query.OrderId),
                                                  iofHelper.NewParameter("@l3_order_id", query.L3OrderId),
                                                  iofHelper.NewParameter("@bb_number", query.BBN),
                                                  iofHelper.NewParameter("@port_in_DID", query.PortInDID),
                                                  iofHelper.NewParameter("@first_name", query.CustomerName),
                                                  iofHelper.NewParameter("@country", query.Country),
                                                  iofHelper.NewParameter("@city", query.City),
                                                  iofHelper.NewParameter("@status", query.RequestStatus),
                                                  iofHelper.NewParameter("@startime", query.StartTime),
                                                  iofHelper.NewParameter("@endtime", query.EndTime),
                                                  iofHelper.NewParameter("@port_in_provider", query.Provider)
                                               };

using (IDataReader dataReader = SqlHelper.ExecuteReader(Conn.IOF, CommandType.Text, sql.ToString(), parameters))
                {
                    while (dataReader.Read())
                    {
                        PortInItem portInItem = new PortInItem();
                        portInItem.ACCTID = dataReader["ACCTID"].ToString();
                        portInItem.Address = dataReader["address"].ToString();
                        portInItem.BTN = dataReader["BTN"].ToString();
                        portInItem.City = dataReader["city"].ToString();
                        portInItem.Country = dataReader["country"].ToString();
                        portInItem.F_Name = dataReader["F_Name"].ToString();
                        portInItem.L_Name = dataReader["L_Name"].ToString();
                        portInItem.L3OrderID = dataReader["L3OrderID"].ToString();
                        portInItem.LOA_DATE = Convert.ToDateTime(dataReader["LOA_DATE"].ToString());
                        portInItem.LP_ACCTID = dataReader["LP_ACCTID"].ToString();
                        portInItem.OrderID = dataReader["OrderID"].ToString();
                        portInItem.Pon_id = dataReader["pon_id"].ToString();
                        portInItem.PORT_IN_DID = dataReader["PORT_IN_DID"].ToString();
                        portInItem.PROVIDER = GetPortInProvider(int.Parse(dataReader["port_in_provider"].ToString()));
                        portInItem.State = dataReader["state"].ToString();
                        portInItem.STATUS = dataReader["STATUS"].ToString();
                        portInItem.Zip = dataReader["zip"].ToString();
                        portInItem.Zip4 = dataReader["zip4"].ToString();
                        portInItems.PortInItemModels.Add(portInItem);
                    }
                    portInItems.BaseResult.Code = 0;
                    log.Info("Query Result Count:" + portInItems.PortInItemModels.Count);
                }