// 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));
}