/// <summary>
/// Converts the fields to IDataParameter. The name of the parameter will also include
/// the name of the table.
/// </summary>
/// <param name="database">Database server type</param>
/// <param name="tableName">Name of the database table</param>
/// <param name="field">Field to be converted</param>
/// <returns>The IDataParameter</returns>
public IDataParameter ConvertToDataParameter(DatabaseServer database, string tableName, DatabaseField field)
{
IDataParameter parameter = null;
DataFactory factory = new DataFactory();
factory.InitializeDataParameter(database, ref parameter);
SqlGenerator generator = new SqlGenerator();
parameter.ParameterName = factory.GetParameterChar(database).ToString() + generator.GetTableName(tableName) + field.fieldName;
parameter.SourceColumn = field.fieldName;
if (field.fieldValue == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = field.fieldValue;
}
parameter.DbType = field.fieldType;
return(parameter);
}
/// <summary>
/// Converts the fields to IDataParameter
/// </summary>
/// <param name="database">Database server type</param>
/// <param name="fields">Fields to be converted</param>
/// <returns>Array of IDataParameter</returns>
public IDataParameter[] ConvertToDataParameter(DatabaseServer database, params DatabaseField[] fields)
{
if (fields == null || fields.Length == 0)
{
throw new ArgumentException("Invalid database fields");
}
IDataParameter[] parameters = new IDataParameter[fields.Length];
DataFactory factory = new DataFactory();
for (int i = 0; i < fields.Length; i++)
{
factory.InitializeDataParameter(database, ref parameters[i]);
}
for (int i = 0; i < fields.Length; i++)
{
parameters[i].ParameterName = factory.GetParameterChar(database) + fields[i].fieldName;
parameters[i].SourceColumn = fields[i].fieldName;
if (fields[i].fieldValue == null)
{
parameters[i].Value = DBNull.Value;
}
else
{
parameters[i].Value = fields[i].fieldValue;
}
parameters[i].DbType = fields[i].fieldType;
}
return(parameters);
}
/// <summary>
/// Converts the specified DataField into a IDataParameter. This ensures that the
/// name of the parameter is unique by comparing with a list of specified used names
/// </summary>
/// <param name="database">Database server</param>
/// <param name="tableName">Name of the table to which the field belongs</param>
/// <param name="field">DatabaseField which will be converted</param>
/// <param name="listUsedParameterNames">List with used names of a parameter. When the name of the parameter is given it is checked against the values in this list. If the name if found then it is changed to be unique</param>
/// <returns>The IDataParameter</returns>
public IDataParameter ConvertToDataParameter(DatabaseServer database, string tableName, DatabaseField field, ref List <string> listUsedParameterNames)
{
IDataParameter parameter = null;
SqlGenerator generator = new SqlGenerator();
DataFactory factory = new DataFactory();
factory.InitializeDataParameter(database, ref parameter);
string parameterName = factory.GetParameterChar(database) + generator.GetTableName(tableName) + field.fieldName;
while (listUsedParameterNames.Contains(parameterName))
{
parameterName = parameterName + PARAMETER_NAME_ENDING;
}
parameter.ParameterName = parameterName;
//add it to the external list
listUsedParameterNames.Add(parameterName);
if (field.fieldValue == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = field.fieldValue;
}
parameter.DbType = field.fieldType;
parameter.SourceColumn = field.fieldName;
return(parameter);
}
/// <summary>
/// Generates a INSERT query for a single table
/// </summary>
/// <param name="database">Database server</param>
/// <param name="fields">Array of DatabaseFields from which the query is generated</param>
/// <param name="tableName">Name of the table</param>
/// <returns>The generated query</returns>
public ExecutionQuery GenerateInsertQuery(DatabaseServer database, DatabaseField[] fields, string tableName)
{
ISqlGenerator isql = null;
ExecutionQuery execQuery;
DataConvertor converter = new DataConvertor();
DataFactory factory = new DataFactory();
List <IDataParameter> listParameters = new List <IDataParameter>();
StringBuilder sbuild = new StringBuilder();
isql = factory.InitializeSqlGenerator(database);
sbuild.Append("INSERT INTO " + GetTableName(database, tableName) + "(");
for (int i = 0; i < fields.Length; i++)
{
//skip the PK field if is autogenerated.
if (fields[i].isPrimaryKey && fields[i].isValueAutogenerated)
{
continue;
}
//check if this is the last field
if (i == fields.Length - 1)
{
sbuild.Append(fields[i].fieldName);
}
else
{
sbuild.Append(fields[i].fieldName + ",");
}
}
sbuild.Append(") VALUES(");
//generate the execution query
for (int i = 0; i < fields.Length; i++)
{
//skip the PK field if is autogenerated.
if (fields[i].isPrimaryKey && fields[i].isValueAutogenerated)
{
continue;
}
//check if this is the last field
if (i == fields.Length - 1)
{
//check for PK placeholder
if (fields[i].fieldValue != null && fields[i].fieldValue.ToString() == FOREIGN_KEY_PLACEHOLDER_VALUE)
{
sbuild.Append(isql.GetValue(fields[i].fieldType, fields[i].fieldValue) + ")");
}
else
{
//add the name of the field
sbuild.Append(factory.GetParameterChar(database) + fields[i].fieldName + ")");
//add the corresponding parameter.
IDataParameter[] iparams = converter.ConvertToDataParameter(database, fields[i]);
listParameters.Add(iparams[0]);
}
}
else
{
//check for PK placeholder
if (fields[i].fieldValue != null && fields[i].fieldValue.ToString() == FOREIGN_KEY_PLACEHOLDER_VALUE)
{
sbuild.Append(isql.GetValue(fields[i].fieldType, fields[i].fieldValue) + ",");
}
else
{
//add the name of the field
sbuild.Append(factory.GetParameterChar(database) + fields[i].fieldName + ",");
//add the coresponding parameter.
IDataParameter[] iparams = converter.ConvertToDataParameter(database, fields[i]);
listParameters.Add(iparams[0]);
}
}
}
execQuery = new ExecutionQuery();
IDataParameter[] par = new IDataParameter[listParameters.Count];
listParameters.CopyTo(par);
execQuery.Parameters = par;
execQuery.Query = sbuild.ToString();
return(execQuery);
}
