public void CleanTable()
{
DataProviderHelper helper = new DataProviderHelper(ProviderInvariantName, ConnectionString);
using (DbConnection conn = CreateConnection())
{
conn.Open();
DbCommand command = conn.CreateCommand();
command.CommandText = String.Format("DELETE FROM {0}",
helper.FormatIdentifier(Util.SplitName(TableName)));
command.ExecuteNonQuery();
}
}
public bool IsTableExists()
{
DataProviderHelper helper = new DataProviderHelper(ProviderInvariantName, ConnectionString);
using(DbConnection conn = CreateConnection())
{
conn.Open();
DbCommand command = conn.CreateCommand();
command.CommandText = String.Format("SELECT 1 FROM {0} WHERE 0=1",
helper.FormatIdentifier(Util.SplitName(TableName)));
try
{
DbDataReader r = command.ExecuteReader();
r.Close();
return true;
}
catch (Exception)
{
return false;
}
}
}
private String WriteExpr(Binder binder, DataProviderHelper helper, object lval,
Object[] parameters, DbCommand command)
{
if (Lisp.IsNode(lval))
{
if (Lisp.IsAtom(lval))
{
ATOM a = (ATOM)lval;
string name;
if (a.parts.Length == 1)
name = a.parts[0];
else
name = a.parts[1];
ColumnBinding b = binder.Get(name);
if (b == null)
throw new ESQLException(Properties.Resources.InvalidIdentifier, name);
RowType.TypeInfo ti = TableType.TableRowType.Fields[b.rnum];
if (ti.ProviderColumnName != null)
return helper.FormatIdentifier(ti.ProviderColumnName);
else
return helper.FormatIdentifier(ti.Name);
}
else
return WriteLiteral(helper, lval);
}
else
{
object head = Lisp.Car(lval);
if (Lisp.IsCons(head))
{
object[] list = Lisp.ToArray(lval);
return WriteList(binder, helper, list, parameters, command);
}
else
{
object[] args = Lisp.ToArray(Lisp.Cdr(lval));
if (head.Equals(ID.Like1))
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0} LIKE {1}", WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
if (args.Length > 2)
sb.AppendFormat(" ESCAPE {0}", WriteExpr(binder, helper, args[2],
parameters, command));
return sb.ToString();
}
else if (head.Equals(Funcs.List))
return WriteList(binder, helper, args, parameters, command);
else if (head.Equals(ID.Member))
return String.Format(" {0} IN {1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.Between))
return String.Format(" BETWEEN {0} AND {1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(Funcs.Not))
return String.Format(" NOT {0}", WriteExpr(binder, helper, args[0], parameters, command));
else if (head.Equals(ID.IsNull))
return String.Format(" {0} IS NULL", WriteExpr(binder, helper, args[0], parameters, command));
else if (head.Equals(Funcs.And))
return String.Format(" ({0}) AND ({1})",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(Funcs.Or))
return String.Format(" ({0}) OR ({1})",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.LT))
return String.Format(" {0} < {1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.GT))
return String.Format(" {0} > {1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.EQ))
return String.Format(" {0} = {1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.NE))
return String.Format(" {0} <> {1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.LE))
return String.Format(" {0} <= {1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.GE))
return String.Format(" {0} >= {1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.Concat))
return String.Format("CONCAT({0},{1})",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(Funcs.Add))
return String.Format(" {0}+{1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(Funcs.Sub))
return String.Format(" {0}-{1}",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(Funcs.Mul))
return String.Format(" ({0})*({1})",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(Funcs.Div))
return String.Format(" ({0})/({1})",
WriteExpr(binder, helper, args[0], parameters, command),
WriteExpr(binder, helper, args[1], parameters, command));
else if (head.Equals(ID.ParamRef))
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = helper.FormatParameter(
String.Format("p{0}", command.Parameters.Count));
parameter.Value = parameters[(int)args[0] - 1];
parameter.Direction = ParameterDirection.Input;
command.Parameters.Add(parameter);
return parameter.ParameterName;
}
else
throw new UnproperlyFormatedExpr(Lisp.Format(lval));
}
}
}
protected override void PrepareCommand(RowType.TypeInfo[] fields, DbCommand command, Object[] parameters)
{
DataProviderHelper helper = new DataProviderHelper(_providerInvariantName, _connectionString);
Binder binder = new Binder(fields);
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
for (int k = 0; k < fields.Length; k++)
{
if (k > 0)
sb.Append(", ");
if (fields[k].ProviderColumnName != null)
sb.Append(helper.FormatIdentifier(fields[k].ProviderColumnName));
else
sb.Append(helper.FormatIdentifier(fields[k].Name));
}
sb.AppendLine();
sb.Append(" FROM ");
sb.Append(TableType.ToString(helper));
if (TableType.Smart)
{
if (FilterPredicate != null || AccessPredicate != null)
sb.AppendLine(" WHERE ");
if (FilterPredicate != null)
{
if (AccessPredicate != null)
sb.Append("(");
command.Parameters.Clear();
sb.AppendLine(WriteExpr(binder, helper, FilterPredicate,
parameters, command));
if (AccessPredicate != null)
sb.Append(")");
}
if (AccessPredicate != null)
{
if (FilterPredicate != null)
sb.AppendLine(" AND (");
for (int s = 0; s < AccessPredicateValues.Length; s++)
{
if (s > 0)
sb.Append(" OR ");
sb.Append("(");
for (int k = 0; k < AccessPredicate.Length; k++)
{
if (k > 0)
sb.Append(" AND ");
sb.Append(helper.FormatIdentifier(AccessPredicate[k]));
Object predicateValue = AccessPredicateValues[s][k];
if (predicateValue == DBNull.Value)
sb.Append(" IS NULL");
else
{
sb.Append("=");
sb.Append(WriteLiteral(helper, predicateValue));
}
}
sb.Append(")");
}
if (FilterPredicate != null)
sb.Append(")");
}
}
if (SortColumns != null)
{
sb.AppendLine(" ORDER BY ");
for (int k = 0; k < SortColumns.Length; k++)
{
if (k > 0)
sb.Append(", ");
sb.Append(helper.FormatIdentifier(SortColumns[k].ColumnName));
if (SortColumns[k].Direction == SortDirection.Descending)
sb.Append(" DESC");
}
sb.AppendLine();
}
command.CommandText = sb.ToString();
}
protected virtual String GetProviderTableName(DataSourceInfo dsi, String[] identifierPart)
{
StringBuilder sb = new StringBuilder();
DataProviderHelper helper = new DataProviderHelper(dsi.ProviderInvariantName, dsi.ConnectionString);
string[] formattedIdentifiers = new String[identifierPart.Length];
for (int k = 0; k < identifierPart.Length; k++)
formattedIdentifiers[k] = helper.FormatIdentifier(identifierPart[k]);
if (dsi.FormatIdentifier != null)
formattedIdentifiers = dsi.FormatIdentifier(this, formattedIdentifiers);
for (int k = 0; k < formattedIdentifiers.Length; k++)
{
if (k > 0)
sb.Append(helper.Qualifer);
sb.Append(formattedIdentifiers[k]);
}
return sb.ToString();
}
public String ToString(DataProviderHelper helper)
{
StringBuilder sb = new StringBuilder();
string[] identifiers = GetIdentifierParts();
for (int k = 0; k < identifiers.Length; k++)
{
if (k > 0)
sb.Append(helper.Qualifer);
sb.Append(helper.FormatIdentifier(identifiers[k]));
}
return sb.ToString();
}
public string CreateCommandText()
{
DataProviderHelper helper = new DataProviderHelper(ProviderInvariantName, ConnectionString);
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
for (int k = 0; k < FieldNames.Count; k++)
{
if (k > 0)
sb.Append(", ");
sb.Append(helper.FormatIdentifier(FieldNames[k]));
}
sb.AppendLine();
sb.Append(" FROM ");
sb.Append(helper.FormatIdentifier(Util.SplitName(TableName)));
return sb.ToString();
}
private void CreateCommandText()
{
if (!String.IsNullOrEmpty(TableName) && DataSource != null)
{
BatchMove.TableName = TableName;
DataProviderHelper helper = new DataProviderHelper(DataSource.InvariantName, DataSource.ConnectionString);
CommandText = String.Format("CREATE TABLE {0} (\n{1}\n)",
helper.FormatIdentifier(Util.SplitName(TableName)), cachedDDL);
}
else
CommandText = sTableUnspec;
}