// GET api/values/5 public IHttpActionResult GetContactosById(int id) { List <Contacto> oContacto = new List <Contacto>(); string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString; iDB2Connection connection = new iDB2Connection(connString); string query = "select * from davidec1.contactos where id = @id"; iDB2Command db2Command = new iDB2Command(); db2Command.Connection = connection; db2Command.CommandText = query; db2Command.Parameters.AddWithValue("@id", id); connection.Open(); iDB2DataReader reader = db2Command.ExecuteReader(); while (reader.Read()) { oContacto.Add(new Contacto() { Id = Convert.ToInt32(reader.GetValue(0).ToString()), Nombre = reader.GetValue(1).ToString(), Telefono = reader.GetValue(2).ToString(), Correo = reader.GetValue(3).ToString(), Edad = Convert.ToInt32(reader.GetValue(4).ToString()) }); } return(Ok(oContacto)); }
public List <T> FindAll(string sql) { List <T> list = new List <T>(); using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql)) { if (rdr.Read()) { do { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } list.Add(t); } while (rdr.Read()); } } return(list); }
public T FindByid(string sql, string parmName, string parmValue) { Debug.Assert(!string.IsNullOrWhiteSpace(parmName)); Debug.Assert(!string.IsNullOrWhiteSpace(parmValue)); T result = default(T); iDB2Parameter[] db2Parms = new iDB2Parameter[] { new iDB2Parameter('@' + parmName, parmValue) }; using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms)) { if (rdr.Read()) { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } result = t; } } return(result); }
public List <T> nativeQuerySql(string sql, IDictionary <string, object> parms) { iDB2Parameter[] db2Parms = null; List <T> list = new List <T>(); if (parms != null) { List <iDB2Parameter> parmsList = new List <iDB2Parameter>(); foreach (var item in parms) { Debug.Assert(item.Value != null); parmsList.Add(new iDB2Parameter('@' + item.Key, item.Value)); } db2Parms = parmsList.ToArray(); } try { using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms)) { if (rdr.Read()) { do { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } list.Add(t); } while (rdr.Read()); } } } catch (Exception ex) { log.Error("SQL错误:" + ex.Message); log.Error("SQL错误:" + sql); if (parms != null) { log.Error("SQL错误:" + parms.toJson()); } throw ex; } return(list); }
public IDictionary <string, string> FindAllDic(string sql) { IDictionary <string, string> list = new Dictionary <string, string>(); using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql)) { if (rdr.Read()) { do { try { list.Add(rdr.GetString(0), rdr.GetString(1)); } catch (Exception ex) { log.Error("字段类型错误," + rdr.GetValue(0).ToString() + ":" + rdr.GetValue(1).ToString() + ":" + ex.Message); } } while (rdr.Read()); } } return(list); }
public void ExecuteReader(Session session) { string table = this.LibraryWhereTable.Name + "." + this.Name; command = new iDB2Command(table, session.Connection, session.Transaction); command.CommandTimeout = 0; command.CommandType = CommandType.TableDirect; try { iDB2DataReader reader = command.ExecuteReader(); for(int i=0;i<reader.FieldCount;i++) { string column = reader.GetName(i); Type type = reader.GetFieldType(i); Column.Create(this.AllorsSession, this, column, type); } while (reader.Read()) { Row row = Row.Create(this.AllorsSession, this); // Create cells for each column foreach (Column col in this.Columns) { object value = reader.GetValue(reader.GetOrdinal(col.Name)); if (value != null) { CellFactory.Create(this.AllorsSession, row, col, value); } } } } catch (Exception ex) { throw ex; } }
/// <summary> /// Using the data reader build the classes internal collection. /// May have to change this routine if I can not get the serializer /// to work correctly. /// </summary> /// <param name="dr"></param> /// <returns></returns> private static CustOrder RowToDict(iDB2DataReader dr) { CustOrder data = new CustOrder(); for (int i = 0; i < dr.FieldCount; i++) { //row.Add(dr.GetName(i).Trim(), dr.GetValue(i).ToString().Trim()); // we can not use the above techinque we have to load a basic object // switch (dr.GetName(i).Trim()) { case "ITEMNO": data.ItemNumber = dr.GetValue(i).ToString().Trim(); break; case "CUSTNO": data.CustomerCode = dr.GetValue(i).ToString().Trim(); break; case "CUSTNAM": data.CustomerName = dr.GetValue(i).ToString().Trim(); break; case "ORDNO": data.OrderNumber = dr.GetValue(i).ToString().Trim(); break; case "ORLINE": data.LineNumber = dr.GetValue(i).ToString().Trim(); break; case "SHPMTH": data.ShippingMethod = dr.GetValue(i).ToString().Trim(); break; case "TRACK": data.TrackingNumber = dr.GetValue(i).ToString().Trim(); break; case "STATUS": data.Status = dr.GetValue(i).ToString().Trim(); break; case "CSRNAM": data.CsrName = dr.GetValue(i).ToString().Trim(); break; case "CSREML": data.CsrEmail = dr.GetValue(i).ToString().Trim(); break; case "ADVTSR": data.Advertiser = dr.GetValue(i).ToString().Trim(); break; case "DESIGN": data.Design = dr.GetValue(i).ToString().Trim(); break; case "RCVDBY": data.ReceivedBy = dr.GetValue(i).ToString().Trim(); break; case "PONUM": data.PoNumber = dr.GetValue(i).ToString().Trim(); break; case "TGTSDT": if (dr.GetValue(i).ToString().Trim() != "") { if (dr.GetValue(i).ToString().Trim() == "0" || dr.GetValue(i).ToString().Trim() == "99999999") { } else { data.TargetShipDate = StringUtil.ParseIbsDate(dr.GetValue(i).ToString().Trim()); } } break; case "ACTSDT": if (dr.GetValue(i).ToString().Trim() != "") { if (dr.GetValue(i).ToString().Trim() == "0" || dr.GetValue(i).ToString().Trim() == "99999999") { } else { data.ActualShipDate = StringUtil.ParseIbsDate(dr.GetValue(i).ToString().Trim()); } } break; case "TGTDLD": if (dr.GetValue(i).ToString().Trim() != "") { if (dr.GetValue(i).ToString().Trim() == "0" || dr.GetValue(i).ToString().Trim() == "99999999") { } else { data.TargetDeliveryDate = StringUtil.ParseIbsDate(dr.GetValue(i).ToString().Trim()); } } break; case "ACTDLD": if (dr.GetValue(i).ToString().Trim() != "") { if (dr.GetValue(i).ToString().Trim() == "0" || dr.GetValue(i).ToString().Trim() == "99999999") { } else { data.ActualDeliveryDate = StringUtil.ParseIbsDate(dr.GetValue(i).ToString().Trim()); } } break; } } switch (data.Status) { case "00500": data.StatusText = "Not Confirmed"; break; case "01000": data.StatusText = "Please upload art"; break; case "02000": data.StatusText = "Order Confirmation"; break; case "03000": data.StatusText = "Proof Ready for Approval"; break; case "04000": data.StatusText = "Proof Approved"; break; case "05000": data.StatusText = "In Production - Ripped"; break; case "06000": data.StatusText = "In Production - Printed"; break; case "07000": data.StatusText = "In Production - Finished"; break; case "08000": data.StatusText = "Shipped"; break; case "09000": data.StatusText = "Delivered"; break; case "10000": data.StatusText = "Order Invoiced"; break; default: data.StatusText = "ERROR"; break; } return(data); }
public PageResult <T> nativeQuerySql(string sql, string sqlCount, IDictionary <string, object> parms, int startIndex, int pageSize) { Debug.Assert(startIndex >= 0); Debug.Assert(pageSize > 0 && pageSize <= 100); List <T> list = list = new List <T>(); int count = 0; iDB2Parameter[] db2Parms = null; if (parms != null) { List <iDB2Parameter> paramlist = new List <iDB2Parameter>(); foreach (var item in parms) { Debug.Assert(item.Value != null); paramlist.Add(new iDB2Parameter('@' + item.Key, item.Value)); } db2Parms = paramlist.ToArray(); } try { count = Convert.ToInt32(DB2Helper.ExecuteScalar(DB2Helper.ConnectionString, CommandType.Text, sqlCount, db2Parms)); } catch (Exception ex) { log.Error("SQL错误:" + ex.Message); log.Error("SQL错误:" + sqlCount); if (parms != null) { log.Error("SQL错误:" + parms.toJson()); } throw ex; } if (count > 0) { try { using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms)) { if (rdr.Read()) { do { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); }catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } list.Add(t); } while (rdr.Read()); } } } catch (Exception ex) { log.Error("SQL错误:" + ex.Message); log.Error("SQL错误:" + sql); if (parms != null) { log.Error("SQL错误:" + parms.toJson()); } throw ex; } } return(new PageResult <T>(startIndex, pageSize, count, list)); }