public int updateSR(ShippingReceived shippingR)
{
int numEffected = 0;
SqlConnection con;
SqlCommand cmd;
try
{
con = Connect("DBConnectionString"); // create the connection
}
catch (Exception ex)
{
// write to log
throw (ex);
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("UPDATE GLN_ShippingReceived SET [NumMaterial]= '{0}',[ProductionID]= '{1}',[AmountExcepted]= '{2}', [AmountReceived]= '{3}',[ShippingCertificate] = '{4}' WHERE GLN_ShippingReceived.[NumMaterial]='{5}' and [ProductionID]= '{6}' and [ShippingCertificate] = '{7}'", shippingR.NumMaterial, shippingR.ProductionID, shippingR.AmountExcepted, shippingR.AmountReceived, shippingR.ShippingCertificate, shippingR.NumMaterial, shippingR.ProductionID, shippingR.ShippingCertificate);
String cStr = sb.ToString();
// helper method to build the insert string
cmd = CreateCommand(cStr, con); // create the command
try
{
numEffected += cmd.ExecuteNonQuery(); // execute the command
}
catch (Exception ex)
{
if (con != null)
{
// close the db connection
con.Close();
}
// write to log
throw (ex);
}
finally
{
if (con != null)
{
// close the db connection
con.Close();
}
}
return(numEffected);
}
private String BuildInsertCommand(ShippingReceived shippingR)
{
String command;
StringBuilder sb = new StringBuilder();
// use a string builder to create the dynamic string
sb.AppendFormat("Values('{0}', '{1}' ,'{2}', '{3}','{4}')", shippingR.NumMaterial, shippingR.ProductionID, shippingR.AmountExcepted, shippingR.AmountReceived, shippingR.ShippingCertificate);
String prefix = "INSERT INTO GLN_ShippingReceived " + "(NumMaterial, ProductionID, AmountExcepted, AmountReceived, ShippingCertificate) ";
command = prefix + sb.ToString();
return(command);
}
public int newmaterial(ShippingReceived shippingR)
{
SqlConnection con;
SqlCommand cmd;
try
{
con = Connect("DBConnectionString"); // create the connection
}
catch (Exception ex)
{
// write to log
throw (ex);
}
String cStr = "";
try
{
int numEffected = 0;
cStr = BuildInsertCommand(shippingR); // helper method to build the insert string
cmd = CreateCommand(cStr, con); // create the command
numEffected += cmd.ExecuteNonQuery(); // execute the command
return(numEffected);
}
catch (Exception ex)
{
Console.WriteLine(cStr);
return(0);
// write to log
throw (ex);
}
finally
{
if (con != null)
{
// close the db connection
con.Close();
}
}
}
public List <ShippingReceived> getSR()
{
List <ShippingReceived> SRL = new List <ShippingReceived>();
SqlConnection con = null;
try
{
con = Connect("DBConnectionString"); // create a connection to the database using the connection String defined in the web config file
String selectSTR = "SELECT * FROM GLN_ShippingReceived";
SqlCommand cmd = new SqlCommand(selectSTR, con);
// get a reader
SqlDataReader dr = cmd.ExecuteReader();//(CommandBehavior.CloseConnection); // CommandBehavior.CloseConnection: the connection will be closed after reading has reached the end
while (dr.Read())
{ // Read till the end of the data into a row
ShippingReceived a = new ShippingReceived();
a.NumMaterial = (string)(dr["NumMaterial"]);
a.ProductionID = (string)(dr["ProductionID"]);
a.AmountExcepted = Convert.ToInt32(dr["AmountExcepted"]);
a.AmountReceived = Convert.ToInt32(dr["AmountReceived"]);
a.ShippingCertificate = (string)(dr["ShippingCertificate"]);
SRL.Add(a);
}
dr.Close();
}
catch (Exception ex)
{
// write to log
throw (ex);
}
finally
{
if (con != null)
{
con.Close();
}
}
return(SRL);
}
public int Put(ShippingReceived shippingR)
{
ShippingReceived SR = new ShippingReceived();
return(SR.updateSR(shippingR));
}
// POST api/<controller>
public int Post(ShippingReceived shippingR)
{
ShippingReceived SR = new ShippingReceived();
return(SR.newmaterial(shippingR));
}
// GET api/<controller>
public List <ShippingReceived> Get()
{
ShippingReceived SC = new ShippingReceived();
return(SC.getSR());
}
