/// <summary>
/// A SqlParameterCollection extension method that adds a range with value to 'values'.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="values">The values.</param>
public static void AddRangeWithValue(this SqlCeParameterCollection @this, Dictionary <string, object> values)
{
foreach (var keyValuePair in values)
{
@this.AddWithValue(keyValuePair.Key, keyValuePair.Value);
}
}
private void saveBillDetails(SqlCeCommand command, int billID)
{
command.CommandText = "INSERT INTO BILLDETAILS VALUES(@billID, @itemID, @uomID, @rate, @quantity)";
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter bill = parameters.Add("@billID", SqlDbType.Int);
bill.Value = billID;
SqlCeParameter itemID = parameters.Add("@itemID", SqlDbType.Int);
SqlCeParameter measurementUnit = parameters.Add("@uomID", SqlDbType.Int);
SqlCeParameter rate = parameters.Add("@rate", SqlDbType.Money);
SqlCeParameter quantity = parameters.Add("@quantity", SqlDbType.Money);
DataRow[] rows = billItems.Select(string.Empty, string.Empty, DataViewRowState.CurrentRows);
foreach (DataRow row in rows)
{
itemID.Value = row["ItemID"];
measurementUnit.Value = row["UoMID"];
rate.Value = row["Rate"];
quantity.Value = row["Quantity"];
command.ExecuteNonQuery();
}
}
private SqlCeCommand getInsertCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO Items (Name, UoMID, UnitPrice) "
+ "VALUES (@p1, @p2, @p3)";
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = new SqlCeParameter();
parameter.ParameterName = "@p1";
parameter.SourceColumn = "Name";
parameter.SqlDbType = SqlDbType.NVarChar;
parameters.Add(parameter);
parameter = new SqlCeParameter();
parameter.ParameterName = "@p2";
parameter.SourceColumn = "UoMID";
parameter.SqlDbType = SqlDbType.Int;
parameters.Add(parameter);
parameter = new SqlCeParameter();
parameter.ParameterName = "@p3";
parameter.SourceColumn = "UnitPrice";
parameter.SqlDbType = SqlDbType.Money;
parameters.Add(parameter);
return(command);
}
private SqlCeCommand getSaveCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
if (paymentToEdit.HasValue)
{
command.CommandText = "Update Payments Set CustomerID = @customerID, PaymentDate = @paymentDate, "
+ "Amount = @amount, PaymentMode = @paymentMode, InstrumentNumber = @instrumentNumber, "
+ "Notes = @notes Where ID = " + paymentToEdit.Value;
}
else
{
command.CommandText = "INSERT INTO PAYMENTS (CustomerID, PaymentDate, Amount, PaymentMode, " +
"InstrumentNumber, Notes) VALUES (@customerID, @paymentDate, @amount, @paymentMode, " +
"@instrumentNumber, @notes)";
}
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = parameters.Add("@customerID", SqlDbType.Int);
parameter.Value = customerNameCombo.SelectedValue;
parameter = parameters.Add("@paymentDate", SqlDbType.DateTime);
parameter.Value = paymentDatePicker.Value.Date;
parameter = parameters.Add("@amount", SqlDbType.Decimal);
decimal amount = decimal.Parse(amountField.Text.Trim());
parameter.Value = Math.Round(amount, 2);
parameter = parameters.Add("@paymentMode", SqlDbType.NChar);
parameter.Value = cashButton.Checked ? 'C' : (chequeButton.Checked ? 'Q' : 'D');
parameter = parameters.Add("@instrumentNumber", SqlDbType.NVarChar, 20);
string text = instrumentNumberField.Text.Trim();
if (text.Length > 0)
{
parameter.Value = text;
}
else
{
parameter.Value = DBNull.Value;
}
parameter = parameters.Add("@notes", SqlDbType.NVarChar, 60);
text = notesField.Text.Trim();
if (text.Length > 0)
{
parameter.Value = text;
}
else
{
parameter.Value = DBNull.Value;
}
return(command);
}
private SqlCeCommand GetSaveCommand(SqlCeConnection connection)
{
bool recordExists = RecordExists(connection);
string sql;
if (recordExists)
{
sql = "UPDATE FirmDetails SET FirmName = @FirmName, " +
"Address = @Address, PhoneNumbers = @PhoneNumbers";
}
else
{
sql = "INSERT INTO FIRMDETAILS VALUES(@FirmName, @Address, @PhoneNumbers)";
}
SqlCeCommand command = connection.CreateCommand();
command.CommandText = sql;
command.CommandType = CommandType.Text;
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = new SqlCeParameter("@FirmName", SqlDbType.NVarChar);
parameter.Value = nameField.Text.Trim();
parameters.Add(parameter);
parameter = new SqlCeParameter("@Address", SqlDbType.NVarChar);
if (string.IsNullOrWhiteSpace(addressField.Text))
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = addressField.Text.Trim();
}
parameters.Add(parameter);
parameter = new SqlCeParameter("@PhoneNumbers", SqlDbType.NVarChar);
if (string.IsNullOrWhiteSpace(phoneNumbersField.Text))
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = phoneNumbersField.Text.Trim();
}
parameters.Add(parameter);
return(command);
}
public int SignalInsert(int img_id, Point root, Signal type, double tilt, double ratio)
{
SqlCeCommand insCmd = daSignal.InsertCommand;
SqlCeParameterCollection param = insCmd.Parameters;
param[0].Value = img_id;
param[1].Value = root.X;
param[2].Value = root.Y;
param[3].Value = (int)type;
param[4].Value = tilt;
param[5].Value = ratio;
insCmd.ExecuteNonQuery();
return(SignalCount());
}
private SqlCeCommand getDeleteCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM Customers WHERE ID = @id";
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = parameters.Add("@id", SqlDbType.Int);
parameter.SourceColumn = "ID";
return(command);
}
/// <summary>
/// Logs an error to the database.
/// </summary>
/// <remarks>
/// Use the stored procedure called by this implementation to set a
/// policy on how long errors are kept in the log. The default
/// implementation stores all errors for an indefinite time.
/// </remarks>
public override string Log(Error error)
{
if (error == null)
{
throw new ArgumentNullException("error");
}
string errorXml = ErrorXml.EncodeString(error);
Guid id = Guid.NewGuid();
const string query = @"
INSERT INTO ELMAH_Error (
[ErrorId], [Application], [Host],
[Type], [Source], [Message], [User], [StatusCode],
[TimeUtc], [AllXml] )
VALUES (
@ErrorId, @Application, @Host,
@Type, @Source, @Message, @User, @StatusCode,
@TimeUtc, @AllXml);";
using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand(query, connection))
{
SqlCeParameterCollection parameters = command.Parameters;
parameters.Add("@ErrorId", SqlDbType.UniqueIdentifier).Value = id;
parameters.Add("@Application", SqlDbType.NVarChar, 60).Value = ApplicationName;
parameters.Add("@Host", SqlDbType.NVarChar, 30).Value = error.HostName;
parameters.Add("@Type", SqlDbType.NVarChar, 100).Value = error.Type;
parameters.Add("@Source", SqlDbType.NVarChar, 60).Value = error.Source;
parameters.Add("@Message", SqlDbType.NVarChar, 500).Value = error.Message;
parameters.Add("@User", SqlDbType.NVarChar, 50).Value = error.User;
parameters.Add("@StatusCode", SqlDbType.Int).Value = error.StatusCode;
parameters.Add("@TimeUtc", SqlDbType.DateTime).Value = error.Time.ToUniversalTime();
parameters.Add("@AllXml", SqlDbType.NText).Value = errorXml;
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
return(id.ToString());
}
}
}
private SqlCeCommand getDeleteCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM ITEMS WHERE ID = @p1";
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = new SqlCeParameter();
parameter.ParameterName = "@p1";
parameter.SourceColumn = "ID";
parameter.SqlDbType = SqlDbType.Int;
parameter.SourceVersion = DataRowVersion.Default;
parameters.Add(parameter);
return(command);
}
private SqlCeCommand getUpdateCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "UPDATE Customers SET Name = @name, PhoneNumbers = @phone, " +
"MobileNumbers = @mobile, Address = @address, City = @city, OpeningBalance = " +
"@balance, BalanceType = @balanceType, Notes = @notes Where ID = @id";
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = parameters.Add("@name", SqlDbType.NVarChar);
parameter.SourceColumn = "Name";
parameter = parameters.Add("@phone", SqlDbType.NVarChar);
parameter.SourceColumn = "PhoneNumbers";
parameter = parameters.Add("@mobile", SqlDbType.NVarChar);
parameter.SourceColumn = "MobileNumbers";
parameter = parameters.Add("@address", SqlDbType.NVarChar);
parameter.SourceColumn = "Address";
parameter = parameters.Add("@city", SqlDbType.NVarChar);
parameter.SourceColumn = "City";
parameter = parameters.Add("@balance", SqlDbType.Money);
parameter.SourceColumn = "OpeningBalance";
parameter = parameters.Add("@balanceType", SqlDbType.NChar);
parameter.SourceColumn = "BalanceType";
parameter = parameters.Add("@notes", SqlDbType.NVarChar);
parameter.SourceColumn = "Notes";
parameter = parameters.Add("@id", SqlDbType.Int);
parameter.SourceColumn = "ID";
return(command);
}
private void createBillMasterSaveCommandParameters(SqlCeCommand command)
{
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = parameters.Add("@billDate", SqlDbType.DateTime);
parameter.Value = billDateField.Value.Date;
parameter = parameters.Add("@CustomerID", SqlDbType.Int);
parameter.IsNullable = true;
if (cashBillButton.Checked)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = (int)customerNameField.SelectedValue;
}
parameter = parameters.Add("@DiscountAmount", SqlDbType.Money);
parameter.Value = decimal.Parse(discountField.Text.Trim());
parameter = parameters.Add("@ExpenseAmount", SqlDbType.Money);
parameter.Value = decimal.Parse(expenseAmountField.Text.Trim());
parameter = parameters.Add("@ExpenseText", SqlDbType.NVarChar, 50);
parameter.IsNullable = true;
string str = expenseTextField.Text.Trim();
if (string.IsNullOrEmpty(str))
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = str;
}
}
private SqlCeCommand getInsertCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO Customers (Name, PhoneNumbers, MobileNumbers, Address, City, " +
"OpeningBalance, BalanceType, Notes) Values(@name, @phone, @mobile, @address, @city, @balance, "
+ "@balanceType, @notes)";
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = parameters.Add("@name", SqlDbType.NVarChar);
parameter.SourceColumn = "Name";
parameter = parameters.Add("@phone", SqlDbType.NVarChar);
parameter.SourceColumn = "PhoneNumbers";
parameter = parameters.Add("@mobile", SqlDbType.NVarChar);
parameter.SourceColumn = "MobileNumbers";
parameter = parameters.Add("@address", SqlDbType.NVarChar);
parameter.SourceColumn = "Address";
parameter = parameters.Add("@city", SqlDbType.NVarChar);
parameter.SourceColumn = "City";
parameter = parameters.Add("@balance", SqlDbType.Money);
parameter.SourceColumn = "OpeningBalance";
parameter = parameters.Add("@balanceType", SqlDbType.NChar);
parameter.SourceColumn = "BalanceType";
parameter = parameters.Add("@notes", SqlDbType.NVarChar);
parameter.SourceColumn = "Notes";
return(command);
}
private SqlCeCommand getUpdateCommand(SqlCeConnection connection)
{
SqlCeCommand command = connection.CreateCommand();
command.CommandText = "UPDATE Items SET Name = @p1, UoMID = @p2, " +
"UnitPrice = @p3 WHERE ID = @p4";
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter parameter = new SqlCeParameter();
parameter.ParameterName = "@p1";
parameter.SourceColumn = "Name";
parameter.SqlDbType = SqlDbType.NVarChar;
parameters.Add(parameter);
parameter = new SqlCeParameter();
parameter.ParameterName = "@p2";
parameter.SourceColumn = "UoMID";
parameter.SqlDbType = SqlDbType.Int;
parameters.Add(parameter);
parameter = new SqlCeParameter();
parameter.ParameterName = "@p3";
parameter.SourceColumn = "UnitPrice";
parameter.SqlDbType = SqlDbType.Money;
parameters.Add(parameter);
parameter = new SqlCeParameter();
parameter.ParameterName = "@p4";
parameter.SourceColumn = "ID";
parameter.SqlDbType = SqlDbType.Int;
parameter.SourceVersion = DataRowVersion.Original;
parameters.Add(parameter);
return(command);
}
/// <summary>
/// Genera una nueva colección
/// </summary>
public clsParamsSQL()
{
SqlCeCommand ocmd = new SqlCeCommand();
oparams = ocmd.Parameters;
}
/// <summary>
/// Returns a page of errors from the databse in descending order
/// of logged time.
/// </summary>
///
public override int GetErrors(int pageIndex, int pageSize, System.Collections.IList errorEntryList)
{
if (pageIndex < 0)
{
throw new ArgumentOutOfRangeException("pageIndex", pageIndex, null);
}
if (pageSize < 0)
{
throw new ArgumentOutOfRangeException("pageSize", pageSize, null);
}
const string sql = @"
SELECT
[ErrorId],
[Application],
[Host],
[Type],
[Source],
[Message],
[User],
[StatusCode],
[TimeUtc]
FROM
[ELMAH_Error]
ORDER BY
[TimeUtc] DESC,
[Sequence] DESC
OFFSET @PageSize * @PageIndex ROWS FETCH NEXT @PageSize ROWS ONLY;
";
const string getCount = @"
SELECT COUNT(*) FROM [ELMAH_Error]";
using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
{
connection.Open();
using (SqlCeCommand command = new SqlCeCommand(sql, connection))
{
SqlCeParameterCollection parameters = command.Parameters;
parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
parameters.Add("@Application", SqlDbType.NVarChar, 60).Value = ApplicationName;
using (SqlCeDataReader reader = command.ExecuteReader())
{
if (errorEntryList != null)
{
while (reader.Read())
{
string id = reader["ErrorId"].ToString();
Elmah.Error error = new Elmah.Error();
error.ApplicationName = reader["Application"].ToString();
error.HostName = reader["Host"].ToString();
error.Type = reader["Type"].ToString();
error.Source = reader["Source"].ToString();
error.Message = reader["Message"].ToString();
error.User = reader["User"].ToString();
error.StatusCode = Convert.ToInt32(reader["StatusCode"]);
error.Time = Convert.ToDateTime(reader["TimeUtc"]).ToLocalTime();
errorEntryList.Add(new ErrorLogEntry(this, id, error));
}
}
}
}
using (SqlCeCommand command = new SqlCeCommand(getCount, connection))
{
return((int)command.ExecuteScalar());
}
}
}
private static void transferCustomerBalance(SqlCeConnection sourceConnection, SqlCeConnection targetConnection,
SqlCeTransaction transaction)
{
Dictionary <int, decimal> customerBalance;
using (SqlCeCommand command = sourceConnection.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM Customers";
int count = (int)command.ExecuteScalar();
customerBalance = new Dictionary <int, decimal>(count);
command.CommandText = "SELECT ID FROM Customers";
using (SqlCeDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
{
return;
}
decimal?balance;
int customerID;
string errorText;
do
{
customerID = reader.GetInt32(0);
balance = GlobalMethods.GetCustomerBalance(customerID, out errorText, sourceConnection);
if (!balance.HasValue)
{
throw new Exception("A customer's balance couldn't be read from the source database.");
}
customerBalance.Add(customerID, balance.Value);
} while (reader.Read());
}
}
using (SqlCeCommand command = targetConnection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = "UPDATE Customers SET OpeningBalance = @openingBalance, BalanceType = @balanceType " +
"WHERE ID = @id";
SqlCeParameterCollection parameters = command.Parameters;
SqlCeParameter openingBalance = parameters.Add("@openingBalance", SqlDbType.Decimal);
SqlCeParameter balanceType = parameters.Add("@balanceType", SqlDbType.NChar);
SqlCeParameter customerID = parameters.Add("@id", SqlDbType.Int);
command.Prepare();
foreach (KeyValuePair <int, decimal> pair in customerBalance)
{
openingBalance.Value = Math.Abs(pair.Value);
if (pair.Value > 0.0M)
{
balanceType.Value = 'C';
}
else if (pair.Value < 0.0M)
{
balanceType.Value = 'D';
}
else
{
balanceType.Value = DBNull.Value;
}
customerID.Value = pair.Key;
command.ExecuteNonQuery();
}
}
}
