public int AddUser(string username, string password, string email)
{
Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "INSERT INTO [users] (username,password,email) VALUES (@username, @password ,@email)";
SqlCeParameter param = command.CreateParameter();
param.ParameterName = "username";
param.Value = username;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "password";
param.Value = password;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = "email";
param.Value = email;
command.Parameters.Add(param);
int res = command.ExecuteNonQuery();
Connection.Close();
return(res);
}
public void FillBinContent(XmlResponseGetBinContentResult aXmlResponseResultGetBinContent)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("DELETE FROM BinContent", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO BinContent (ItemNo, ItemNo2, BinCode, Quantity, [Fixed], [Default]) " +
"VALUES (@ItemNo, @ItemNo2, @BinCode, @Quantity, @Fixed, @Default)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@ItemNo", aXmlResponseResultGetBinContent.BinContent.ItemNo));
sqlCommand.Parameters.Add(new SqlCeParameter("@ItemNo2", aXmlResponseResultGetBinContent.BinContent.ItemNo2));
SqlCeParameter param = sqlCommand.CreateParameter();
param.ParameterName = "@BinCode";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Quantity";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Fixed";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Default";
sqlCommand.Parameters.Add(param);
for (Int32 k = 0; k < aXmlResponseResultGetBinContent.BinContent.Count; k++)
{
sqlCommand.Parameters["@BinCode"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).BinCode;
sqlCommand.Parameters["@Quantity"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Quantity;
sqlCommand.Parameters["@Fixed"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Fixed;
sqlCommand.Parameters["@Default"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Default;
sqlCommand.ExecuteNonQuery();
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
public void InsertDocumentNumbers(XmlResponseGetDocNumbersResult aResultGetDocNumbers)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO WarehouseReceipt(No) " +
"VALUES (@No)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
SqlCeParameter param = sqlCommand.CreateParameter();
param.ParameterName = "@No";
sqlCommand.Parameters.Add(param);
for (Int32 k = 0; k < aResultGetDocNumbers.Documents.Count; k++)
{
sqlCommand.Parameters["@No"].Value = aResultGetDocNumbers.Documents.Document(k).No;
sqlCommand.ExecuteNonQuery();
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
/// <summary>
/// add param
/// </summary>
/// <param name="sName">name</param>
/// <param name="value">value</param>
public void AddParameter(string sName, object value)
{
DbParameter dbPara = null;
SqlCeCommand cmd = new SqlCeCommand();
dbPara = cmd.CreateParameter();
dbPara.ParameterName = sName;
dbPara.Value = value;
parameters.Add(dbPara);
}
public int UpdateUser(int id, string username, string password, string email)
{
Connection.Open();
SqlCeCommand comm = Connection.CreateCommand();
comm.CommandText = "UPDATE [users] SET username = @username, password=@password, email = @email WHERE id = @id";
SqlCeParameter param;
param = comm.CreateParameter();
param.ParameterName = "username";
param.Value = username;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "password";
param.Value = password;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "email";
param.Value = email;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "id";
param.Value = id;
comm.Parameters.Add(param);
int res = comm.ExecuteNonQuery();
Connection.Close();
return(res);
}
public int DeleteUser(int id)
{
Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "DELETE FROM [users] WHERE id = @id";
SqlCeParameter param = command.CreateParameter();
param.ParameterName = "id";
param.Value = id;
command.Parameters.Add(param);
int res = command.ExecuteNonQuery();
Connection.Close();
return(res);
}
/// <summary>
/// add param
/// </summary>
/// <param name="sName">name</param>
/// <param name="value">value</param>
public void AddParameter(string sName, object value)
{
DbParameter dbPara = null;
SqlCeCommand cmd = new SqlCeCommand();
dbPara = cmd.CreateParameter();
dbPara.ParameterName = sName;
dbPara.Value = value;
parameters.Add(dbPara);
}
public void InsertDocumentNumbers(XmlResponseGetDocNumbersResult aResultGetDocNumbers)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO WarehouseReceipt(No) " +
"VALUES (@No)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
SqlCeParameter param = sqlCommand.CreateParameter();
param.ParameterName = "@No";
sqlCommand.Parameters.Add(param);
for (Int32 k = 0; k < aResultGetDocNumbers.Documents.Count; k++)
{
sqlCommand.Parameters["@No"].Value = aResultGetDocNumbers.Documents.Document(k).No;
sqlCommand.ExecuteNonQuery();
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
public void FillBinContent(XmlResponseGetBinContentResult aXmlResponseResultGetBinContent)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("DELETE FROM BinContent", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO BinContent (ItemNo, ItemNo2, BinCode, Quantity, [Fixed], [Default]) " +
"VALUES (@ItemNo, @ItemNo2, @BinCode, @Quantity, @Fixed, @Default)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@ItemNo", aXmlResponseResultGetBinContent.BinContent.ItemNo));
sqlCommand.Parameters.Add(new SqlCeParameter("@ItemNo2", aXmlResponseResultGetBinContent.BinContent.ItemNo2));
SqlCeParameter param = sqlCommand.CreateParameter();
param.ParameterName = "@BinCode";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Quantity";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Fixed";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Default";
sqlCommand.Parameters.Add(param);
for (Int32 k = 0; k < aXmlResponseResultGetBinContent.BinContent.Count; k++)
{
sqlCommand.Parameters["@BinCode"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).BinCode;
sqlCommand.Parameters["@Quantity"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Quantity;
sqlCommand.Parameters["@Fixed"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Fixed;
sqlCommand.Parameters["@Default"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Default;
sqlCommand.ExecuteNonQuery();
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
/// <summary>
/// Creates a new instance of a <see cref="T:System.Data.Common.DbParameter"/> object.
/// </summary>
/// <returns>
/// A <see cref="T:System.Data.Common.DbParameter"/> object.
/// </returns>
protected override DbParameter CreateDbParameter()
{
return(_innerCommand.CreateParameter());
}
