public int InsertPalletOnReset(WebRefFGSync.PalletEntity dr) { SqlCeDataAdapter adapter = new SqlCeDataAdapter(); SqlCeCommand cmd = new SqlCeCommand(); string sqlString = "INSERT INTO Pallets (Pallet, SyncId, Location) " + "VALUES (@p1, @p2, @p3);"; cmd.Parameters.Add("@p1", SqlDbType.NVarChar); cmd.Parameters.Add("@p2", SqlDbType.BigInt); cmd.Parameters.Add("@p3", SqlDbType.NVarChar); cmd.Parameters["@p1"].Value = dr.Pallet; cmd.Parameters["@p2"].Value = dr.SyncId; cmd.Parameters["@p3"].Value = dr.Location; cmd.CommandText = sqlString; cmd.CommandType = CommandType.Text; cmd.Connection = this._connection; adapter.SelectCommand = cmd; if (this._connection.State != ConnectionState.Open) { this._connection.Open(); } return(cmd.ExecuteNonQuery()); }
public int InsertDataAll(List <WebRefFGSync.InventAvailContract> data)//List<WebRefFGSync.PalletEntity> data) { int lastSyncId = 0; try { //foreach (WebRefFGSync.PalletEntity row in data) foreach (WebRefFGSync.InventAvailContract availCount in data) { WebRefFGSync.PalletEntity row = new WebRefFGSync.PalletEntity() { ItemNumber = availCount.itemIdField, ProductName = availCount.productNameField, Config = availCount.configurationIdField, Size = availCount.sizeIdField, Color = availCount.colorIdField, Warehouse = availCount.locationIdField, BatchNumber = availCount.batchIdField, Location = availCount.wMSLocationIdField, Pallet = availCount.serialIdField, Available = availCount.availPhysicalField, AvailableSpecified = true, SyncId = availCount.syncIdField, SyncIdSpecified = true }; lastSyncId = availCount.syncIdField; if (InsertPalletOnReset(row) > 0) { //iCount++; InsertItemOnReset(row); } } SaveOptions(Option.LastSyncId, lastSyncId.ToString()); } catch (Exception exp) { throw exp; } finally { this._connection.Close(); } return(lastSyncId); }
public int InsertItemOnReset(WebRefFGSync.PalletEntity dr) { SqlCeDataAdapter adapter = new SqlCeDataAdapter(); SqlCeCommand cmd = new SqlCeCommand(); string sqlString = "INSERT INTO Items ([Item number], [Product name], Configuration, Size, Color, Warehouse, [Batch number], Location, [Serial number], [Available Physical], SyncID) " + "VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11);"; cmd.Parameters.Add("@p1", SqlDbType.NVarChar); cmd.Parameters.Add("@p2", SqlDbType.NVarChar); cmd.Parameters.Add("@p3", SqlDbType.NVarChar); cmd.Parameters.Add("@p4", SqlDbType.NVarChar); cmd.Parameters.Add("@p5", SqlDbType.NVarChar); cmd.Parameters.Add("@p6", SqlDbType.NVarChar); cmd.Parameters.Add("@p7", SqlDbType.NVarChar); cmd.Parameters.Add("@p8", SqlDbType.NVarChar); cmd.Parameters.Add("@p9", SqlDbType.NVarChar); cmd.Parameters.Add("@p10", SqlDbType.Real); cmd.Parameters.Add("@p11", SqlDbType.BigInt); cmd.Parameters["@p1"].Value = dr.ItemNumber; cmd.Parameters["@p2"].Value = dr.ProductName; cmd.Parameters["@p3"].Value = dr.Config; cmd.Parameters["@p4"].Value = dr.Size; cmd.Parameters["@p5"].Value = dr.Color; cmd.Parameters["@p6"].Value = dr.Warehouse; cmd.Parameters["@p7"].Value = dr.BatchNumber; cmd.Parameters["@p8"].Value = dr.Location; cmd.Parameters["@p9"].Value = dr.Pallet; cmd.Parameters["@p10"].Value = dr.Available; cmd.Parameters["@p11"].Value = dr.SyncId; cmd.CommandText = sqlString; cmd.CommandType = CommandType.Text; cmd.Connection = this._connection; adapter.SelectCommand = cmd; if (this._connection.State != ConnectionState.Open) { this._connection.Open(); } return(cmd.ExecuteNonQuery()); }
//use to submit the data to Desktop App public List <WebRefFGSync.PalletEntity> GetFGData(long topSyncId) { List <WebRefFGSync.PalletEntity> rows = new List <WebRefFGSync.PalletEntity>(); DataTable dt = new DataTable("Pallets"); //long topSyncId = long.Parse("0"+GetOptions(Option.LastSyncId)); if (topSyncId.Equals(1)) { topSyncId = 0; } string sqlString = "SELECT p.*,i.[Item number],i.[Product name],i.Configuration,i.Size,i.Color,i.Warehouse,i.[Batch number],i.Location,i.[Available physical] FROM Pallets p LEFT OUTER JOIN Items i ON p.Pallet=i.[Serial Number] WHERE p.SyncID >= @p1 AND p.UpdatedBy != ''"; SqlCeCommand cmd = new SqlCeCommand(); try { cmd.Parameters.Add("@p1", SqlDbType.BigInt); cmd.Parameters[0].Value = topSyncId; cmd.CommandText = sqlString; cmd.CommandType = CommandType.Text; cmd.Connection = this._connection; if (this._connection.State != ConnectionState.Open) { this._connection.Open(); } SqlCeDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { WebRefFGSync.PalletEntity row = new WebRefFGSync.PalletEntity() { Pallet = dr["Pallet"].ToString(), IsMatched = dr["IsMatched"] != DBNull.Value? (bool)dr["IsMatched"]:false, IsMatchedSpecified = true, PresentInStock = dr["PresentInStock"] != DBNull.Value ? (bool)dr["PresentInStock"] : false, PresentInStockSpecified = true, ProcessByIT = true, ProcessByITSpecified = true, DateUpdated = (DateTime)dr["DateUpdated"], DateUpdatedSpecified = true, UpdatedBy = dr["UpdatedBy"].ToString(), IsManual = dr["IsManual"] != DBNull.Value ? (bool)dr["IsManual"] : false, IsManualSpecified = true, DeviceName = dr["DeviceName"].ToString(), SyncId = long.Parse(dr["SyncId"].ToString()), SyncIdSpecified = true, ItemNumber = dr["Item number"].ToString(), ProductName = dr["Product name"].ToString(), Config = dr["Configuration"].ToString(), Size = dr["Size"].ToString(), Color = dr["Color"].ToString(), Location = dr["Location"].ToString(), Warehouse = dr["Warehouse"].ToString(), AvailableSpecified = true, BatchNumber = dr["Batch number"].ToString() }; if (dr["Available physical"] != DBNull.Value) { row.Available = decimal.Parse(dr["Available physical"].ToString()); } if (dr["DateUpdated"] == DBNull.Value) { row.DateUpdated = new DateTime(1753, 1, 1); } else { row.DateUpdated = (DateTime)dr["DateUpdated"]; } rows.Add(row); } } catch (Exception exp) { throw exp; } finally { this._connection.Close(); } return(rows); }