internal static object ExecuteScalarFromSql(SqlCeDatabase database, string query, FieldValue[] fieldValueArray)
{
SqlCeCommand dbCommand;
Object obj = null;
dbCommand = database.GetSqlStringCommand(query);
if (dbCommand != null)
{
try
{
SetCommandProperties(dbCommand);
if (fieldValueArray != null && fieldValueArray.Length > 0)
{
dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
}
obj = database.ExecuteScalar(dbCommand);
if (obj is DBNull)
{
obj = null;
}
}
finally
{
dbCommand.Dispose();
}
}
return obj;
}
private static SqlCeParameter[] GetParameters(FieldValue[] fieldValueArray)
{
List<SqlCeParameter> parameterList = new List<SqlCeParameter>();
SqlCeParameter parameter;
for (int index = 0; index < fieldValueArray.Length; index++)
{
parameter = new SqlCeParameter();
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = "@" + fieldValueArray[index].FieldDefinition.Name;
parameter.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType)
, fieldValueArray[index].FieldDefinition.DataTypeName, true);
parameter.Size = fieldValueArray[index].FieldDefinition.SourceColumnMaxLength;
parameter.Precision = fieldValueArray[index].FieldDefinition.SourceColumnPrecision;
parameter.Scale = fieldValueArray[index].FieldDefinition.SourceColumnScale;
if (fieldValueArray[index].Value == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = fieldValueArray[index].Value;
if (parameter.SqlDbType == SqlDbType.Text)
{
parameter.Size = fieldValueArray[index].Value.ToString().Length;
}
}
parameterList.Add(parameter);
}
return parameterList.ToArray();
}
internal static IDataReader ExecuteReaderFromSql(SqlCeDatabase database, string query, FieldValue[] fieldValueArray)
{
SqlCeCommand dbCommand;
IDataReader rdr = null;
dbCommand = database.GetSqlStringCommand(query);
if (dbCommand != null)
{
try
{
SetCommandProperties(dbCommand);
if (fieldValueArray != null && fieldValueArray.Length > 0)
{
dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
}
rdr = database.ExecuteReader(dbCommand);
}
finally
{
dbCommand.Dispose();
}
}
return rdr;
}
internal static object ExecuteScalarFromSql(string query, FieldValue[] fieldValueArray)
{
SqlCeDatabase db;
Object obj = null;
db = GetDatabase();
obj = ExecuteScalarFromSql(db, query, fieldValueArray);
db.Close();
db.Dispose();
return obj;
}
internal static int ExecuteNonQueryFromSql(SqlCeDatabase database, string query, FieldValue[] fieldValueArray)
{
SqlCeCommand dbCommand;
int rtnValue = -1;
dbCommand = database.GetSqlStringCommand(query);
if (dbCommand != null)
{
try
{
SetCommandProperties(dbCommand);
if (fieldValueArray != null && fieldValueArray.Length > 0)
{
dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
}
rtnValue = database.ExecuteNonQuery(dbCommand, false);
}
finally
{
dbCommand.Dispose();
}
}
return rtnValue;
}
internal static IDataReader ExecuteReaderFromSql(string query, FieldValue[] fieldValueArray)
{
SqlCeDatabase db;
db = GetDatabase();
return ExecuteReaderFromSql(db, query, fieldValueArray);
}
internal static SqlCeResultSet ExecuteDataSetFromSql(SqlCeDatabase database, string query, FieldValue[] fieldValueArray)
{
SqlCeCommand dbCommand;
SqlCeResultSet dataSet = null;
dbCommand = database.GetSqlStringCommand(query);
if (dbCommand != null)
{
try
{
SetCommandProperties(dbCommand);
if (fieldValueArray != null && fieldValueArray.Length > 0)
{
dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
}
dataSet = database.ExecuteDataSet(dbCommand);
}
finally
{
dbCommand.Dispose();
}
}
return dataSet;
}
internal static int ExecuteNonQueryFromSql(string query, FieldValue[] fieldValueArray)
{
SqlCeDatabase db;
int rtnValue = -1;
db = GetDatabase();
rtnValue = ExecuteNonQueryFromSql(db, query, fieldValueArray);
db.Close();
db.Dispose();
return rtnValue;
}
internal static FieldDefinition[] CreatePrimaryKeysList(FieldValue[] fieldValues)
{
List<FieldDefinition> pkList = new List<FieldDefinition>();
// iterate through list of fieldDefainitions
for (int index = 0; index < fieldValues.Length; index++)
{
// Is this field a PrimaryKey
if (fieldValues[index].FieldDefinition.IsPrimaryKey)
{
// incrment primaryKeyCount
pkList.Add(fieldValues[index].FieldDefinition);
}
}
// return value
return pkList.ToArray();
}
internal static SqlCeResultSet ExecuteDataSetFromSql(string query, FieldValue[] fieldValueArray)
{
SqlCeDatabase db;
SqlCeResultSet dataSet = null;
db = GetDatabase();
dataSet = ExecuteDataSetFromSql(db, query, fieldValueArray);
db.Close();
db.Dispose();
return dataSet;
}
internal static string BuildWhereClause(FieldValue[] fieldValueArray)
{
string whereClause = "";
foreach (FieldValue fieldValue in fieldValueArray)
{
if (fieldValue.Value == null)
{
whereClause += m_FieldPrefix + fieldValue.FieldDefinition.SourceColumnName + m_FieldSuffix
+ " IS NULL And ";
}
else
{
whereClause += m_FieldPrefix + fieldValue.FieldDefinition.SourceColumnName + m_FieldSuffix
+ " = @" + fieldValue.FieldDefinition.Name + " And ";
}
}
if (whereClause.Length > 0)
{
whereClause = "WHERE " + whereClause.Substring(0, whereClause.Length - 5);
}
return whereClause;
}
internal static string BuildUpdatePortion(string tableName, FieldValue[] fieldValues)
{
StringBuilder query = new StringBuilder();
StringBuilder fields = new StringBuilder();
#region add each dirty field
if (fieldValues.Length > 0)
{
for (int index = 0; index < fieldValues.Length; index++)
{
if (fieldValues[index].IsDirty)
{
fields.Append(m_FieldPrefix + fieldValues[index].FieldDefinition.SourceColumnName + m_FieldSuffix + " = @");
fields.AppendLine(fieldValues[index].FieldDefinition.Name + ",");
}
}
}
#endregion
if (fields.Length > 0)
{
// remove the last comma
fields.Remove(fields.Length - 3, 3);
query.Append("UPDATE ");
query.Append(tableName);
query.AppendLine(" SET ");
query.AppendLine(fields.ToString());
}
return query.ToString();
}
internal static object ExecuteScalarFromStoredProcedure(string storedProcedureName, FieldValue[] fieldValueArray)
{
Database db;
DbCommand dbCommand;
Object obj = null;
db = GetDatabase();
dbCommand = db.GetStoredProcCommand(storedProcedureName);
if( dbCommand != null ) {
try {
SetCommandProperties(dbCommand);
if( fieldValueArray != null && fieldValueArray.Length > 0 ) {
dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
}
obj = db.ExecuteScalar(dbCommand);
if( obj is DBNull){
obj = null;
}
} finally {
dbCommand.Dispose();
}
}
return obj;
}
internal static IDataReader ExecuteReaderFromStoredProcedure(string storedProcedureName
, FieldValue[] fieldValueArray)
{
Database db;
DbCommand dbCommand;
IDataReader rdr = null;
db = GetDatabase();
dbCommand = db.GetStoredProcCommand(storedProcedureName);
if( dbCommand != null ) {
try {
SetCommandProperties(dbCommand);
if( fieldValueArray != null && fieldValueArray.Length > 0 ) {
dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
}
rdr = db.ExecuteReader(dbCommand);
} finally {
dbCommand.Dispose();
}
}
return rdr;
}
internal static int ExecuteNonQueryFromStoredProcedure(string storedProcedureName, FieldValue[] fieldValueArray)
{
Database db;
DbCommand dbCommand;
int rtnValue = -1;
db = GetDatabase();
dbCommand = db.GetStoredProcCommand(storedProcedureName);
if( dbCommand != null ) {
try {
SetCommandProperties(dbCommand);
if( fieldValueArray != null && fieldValueArray.Length > 0 ) {
dbCommand.Parameters.AddRange(GetParameters(fieldValueArray));
}
rtnValue = db.ExecuteNonQuery(dbCommand);
} finally {
dbCommand.Dispose();
}
}
return rtnValue;
}