public SqlWriter(Notation notation)
{
ProviderHelper = new DataProviderHelper();
this.notation = notation;
sb = new StringBuilder();
newLineFlag = false;
}
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();
}
}
protected override RowType CreateRowType()
{
if (_rowType == null)
{
DataProviderHelper helper = new DataProviderHelper(_providerInvariantName, _connectionString);
DbConnection connection = DataProviderHelper.CreateDbConnection(_providerInvariantName);
connection.ConnectionString = _connectionString;
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = _commandText;
for (int k = 0; k < _parameterBindings.Length; k++)
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = _helper.FormatParameter(
String.Format("p{0}", command.Parameters.Count));
parameter.Direction = ParameterDirection.Input;
command.Parameters.Add(parameter);
}
DbDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.CloseConnection);
try
{
DataTable dt_src = reader.GetSchemaTable();
DataTable dt = RowType.CreateSchemaTable();
int n = 0;
foreach (DataRow r in dt_src.Rows)
{
DataRow r1 = dt.NewRow();
foreach (DataColumn col in dt_src.Columns)
{
DataColumn dest = dt.Columns[col.ColumnName];
if (dt.Columns.IndexOf(col.ColumnName) != -1 && col.ColumnName != "ColumnName")
r1[dest] = r[col];
}
r1["ColumnOrdinal"] = n++;
string columnName = (string)r["ColumnName"];
r1["ColumnName"] = helper.NativeFormatIdentifier(columnName);
r1["ProviderColumnName"] = columnName;
r1["IsCaseSensitive"] =
helper.IdentifierCase == IdentifierCase.Sensitive;
dt.Rows.Add(r1);
}
_rowType = new RowType(dt);
}
finally
{
reader.Close();
}
}
return _rowType;
}
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;
}
}
}
public override void Write(Resultset rs)
{
DataProviderHelper helper =
new DataProviderHelper(m_batchMove.ProviderInvariantName, m_batchMove.ConnectionString);
DataTable dt = new DataTable();
dt.TableName = m_batchMove.TableName;
for (int k = 0; k < m_batchMove.FieldNames.Count; k++)
{
DataColumn column = new DataColumn();
column.ColumnName = m_batchMove.FieldNames[k];
column.DataType = rs.RowType.Fields[k].DataType;
dt.Columns.Add(column);
}
while (rs.Begin != null)
{
Row row = rs.Dequeue();
DataRow dr = dt.NewRow();
for (int k = 0; k < row.Length; k++)
dr[k] = row[k];
dt.Rows.Add(dr);
RowProceded();
}
if (RemoteDbProviderFactories.Isx64() &&
(DataProviderHelper.HostADOProviders || m_batchMove.ProviderInvariantName == "System.Data.OleDb"))
{
ProxyDataAdapter proxy = m_batchMove.CreateProxyDataAdapter();
proxy.Update(dt);
}
else
{
DbDataAdapter adapter = m_batchMove.CreateDataAdapter();
adapter.Update(dt);
}
}
private string GetTableName(object treeNode)
{
TableNode node = (TableNode)treeNode;
StringBuilder sb = new StringBuilder();
DataProviderHelper helper = new DataProviderHelper();
if (!node.Connection.Default)
{
sb.Append(node.Connection.Prefix);
sb.Append(":");
}
if (!String.IsNullOrEmpty(node.SchemaName))
{
sb.Append(helper.NativeFormatIdentifier(node.SchemaName));
sb.Append(".");
}
sb.Append(helper.NativeFormatIdentifier(node.TableName));
return sb.ToString();
}
private TableType GetDataProviderTableType(DataSourceInfo dsi, String identifier)
{
DataProviderHelper helper = new DataProviderHelper(dsi.ProviderInvariantName, dsi.ConnectionString);
using (DbConnection connection = dsi.CreateConnection())
{
DbCommand command = connection.CreateCommand();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM ");
sb.Append(identifier);
if (helper.OrderByColumnsInSelect)
sb.Append(" ORDER BY 1");
command.CommandText = sb.ToString();
try
{
DbDataReader reader;
connection.Open();
bool hasKeyInfo = false;
try
{
reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
hasKeyInfo = true;
}
catch (Exception ex)
{
Trace.TraceWarning("GetDataProviderTableType.ExecuteReader: {0}", ex.Message);
reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
}
DataTable dt_src = reader.GetSchemaTable();
DataTable dt = RowType.CreateSchemaTable();
int n = 0;
foreach (DataRow r in dt_src.Rows)
{
DataRow r1 = dt.NewRow();
foreach (DataColumn col in dt_src.Columns)
{
DataColumn dest = dt.Columns[col.ColumnName];
if (dt.Columns.IndexOf(col.ColumnName) != -1 && col.ColumnName != "ColumnName")
r1[dest] = r[col];
}
r1["ColumnOrdinal"] = n++;
string columnName = (string)r["ColumnName"];
r1["ColumnName"] = helper.NativeFormatIdentifier(columnName);
r1["ProviderColumnName"] = columnName;
r1["IsCaseSensitive"] =
helper.IdentifierCase == IdentifierCase.Sensitive;
dt.Rows.Add(r1);
}
reader.Close();
RowType rtype = new RowType(dt);
if (hasKeyInfo && rtype.Fields[0].BaseTableName != null)
return new TableType(identifier, rtype.Fields[0].BaseCatalogName,
rtype.Fields[0].BaseSchemaName, rtype.Fields[0].BaseTableName, dsi, rtype, helper.Smart);
else
{
string schema = null;
string catalog = null;
string tableName = null;
string[] identifierPart = helper.SplitIdentifier(identifier);
int length = identifierPart.Length;
if (length == 3)
catalog = identifierPart[identifierPart.Length - length--];
if (length == 2)
schema = identifierPart[identifierPart.Length - length--];
if (length == 1)
tableName = identifierPart[identifierPart.Length - length];
return new TableType(identifier, catalog, schema, tableName, dsi, rtype, helper.Smart);
}
}
catch (Exception ex)
{
Trace.TraceWarning("GetDataProviderTableType: {0}", ex.Message);
return null;
}
}
}
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 static Resultset QueryLdap([Implict] Executive engine, String domainName, String filter, String properties)
{
QueryNode.LispProcessingContext owner = (QueryNode.LispProcessingContext)engine.Owner;
if (String.IsNullOrEmpty(domainName))
domainName = String.Format("LDAP://{0}", Environment.UserDomainName);
DirectoryEntry entry = new DirectoryEntry(domainName);
DirectorySearcher searcher = new DirectorySearcher(entry);
if (owner.QueryContext.LdapClientTimeout != TimeSpan.Zero)
searcher.ClientTimeout = owner.QueryContext.LdapClientTimeout;
searcher.SizeLimit = owner.QueryContext.LdapSearchLimit;
searcher.CacheResults = true;
searcher.Filter = filter;
string[] properties_array = properties.Split(new char[] { ',' });
foreach (string prop in properties_array)
searcher.PropertiesToLoad.Add(prop);
Type[] dataType = new Type[properties_array.Length];
bool[] is_array = new bool[properties_array.Length];
SearchResultCollection result = searcher.FindAll();
for (int p = 0; p < dataType.Length; p++)
{
dataType[p] = null;
foreach (SearchResult sr in result)
{
ResultPropertyValueCollection value_col = sr.Properties[properties_array[p]];
if (value_col.Count > 0)
{
Type curr;
is_array[p] |= value_col.Count > 1;
if (value_col[0] != null)
{
curr = value_col[0].GetType();
if (curr != typeof(System.Object) && Type.GetTypeCode(curr) == TypeCode.Object)
curr = typeof(System.Object);
if (dataType[p] == null || curr == typeof(System.Object))
dataType[p] = curr;
else
if (dataType[p] != curr)
dataType[p] = typeof(System.String);
}
if (dataType[p] == typeof(System.Object))
break;
}
}
if (dataType[p] == null)
dataType[p] = typeof(System.String);
}
DataTable dt = RowType.CreateSchemaTable();
DataProviderHelper helper = new DataProviderHelper();
for (int k = 0; k < dataType.Length; k++)
{
DataRow dr = dt.NewRow();
dr["ColumnName"] = helper.NativeFormatIdentifier(properties_array[k]);
dr["IsCaseSensitive"] = true;
dr["ColumnOrdinal"] = k;
if (is_array[k])
dr["DataType"] = typeof(System.Object);
else
dr["DataType"] = dataType[k];
dt.Rows.Add(dr);
}
Resultset rs = new Resultset(new RowType(dt), null);
foreach (SearchResult sr in result)
{
Row row = rs.NewRow();
for (int p = 0; p < properties_array.Length; p++)
{
ResultPropertyValueCollection value_col = sr.Properties[properties_array[p]];
if (value_col.Count > 0)
{
if (value_col.Count == 1)
{
if (dataType[p] == typeof(System.String))
row.SetString(p, value_col[0].ToString());
else
row.SetValue(p, value_col[0]);
}
else
{
object[] values = (object[])Array.CreateInstance(dataType[p], value_col.Count);
for (int k = 0; k < values.Length; k++)
values[k] = value_col[k];
row.SetValue(p, values);
}
}
}
rs.Enqueue(row);
}
searcher.Dispose();
entry.Dispose();
return rs;
}
public String GetCreateTableDDL()
{
DataProviderHelper helper = new DataProviderHelper(ProviderInvariantName, ConnectionString);
FieldNames.Clear();
HasUnknownDatatype = false;
RowType rowType = Source.RowType;
int[] size = new int[rowType.Fields.Length];
using (DbConnection conn = CreateConnection())
{
conn.Open();
DataTable dt = conn.GetSchema("DataTypes");
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
StringBuilder sb3 = new StringBuilder();
for (int k = 0; k < rowType.Fields.Length; k++)
{
if (k > 0)
{
sb.Append(",");
sb.AppendLine();
}
RowType.TypeInfo ti = rowType.Fields[k];
String name = helper.NormalizeIdentifier(FieldNames, Util.UnquoteName(ti.Name));
sb.Append(name);
sb.Append(" ");
if (ti.ExportAsPk)
{
if (sb2.Length > 0)
sb2.Append(", ");
sb2.Append(name);
}
if (ti.ExportAsUnique)
{
if (sb3.Length > 0)
sb3.Append(", ");
sb3.Append(name);
}
if (String.IsNullOrEmpty(ti.ExportDataType))
{
DataRow[] rows;
if (ti.DataType == typeof(String) ||
ti.DataType == typeof(byte[]))
{
size[k] = ti.Size;
if (size[k] == 0)
{
foreach (Row data in Source)
if (!data.IsDbNull(k))
size[k] = Math.Max(size[k], data.GetString(k).Length);
}
if (size[k] > 0)
{
rows = SelectDataType(dt, ti.DataType, size[k],
new string[] { "size", "max length" }, true, false, false);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, size[k],
new string[] { "size", "max length" }, false, false, false);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, size[k],
new string[] { "size", "max length" }, null, false, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, null, null, true, true, null);
if (rows.Length == 0)
rows = SelectDataType(dt, ti.DataType, null, null, true, false, null);
}
}
}
}
else
{
rows = SelectDataType(dt, ti.DataType, null, null, true, true, null);
if (rows.Length == 0)
rows = SelectDataType(dt, ti.DataType, null, null, true, false, null);
}
}
else if (ti.DataType == typeof(Decimal))
{
rows = SelectDataType(dt, ti.DataType, null,
new string[] { "precision,scale" }, true, false, null);
if (rows.Length == 0)
rows = SelectDataType(dt, ti.DataType, null, null, false, false, null);
}
else if (ti.DataType == typeof(Double))
{
rows = SelectDataType(dt, ti.DataType, null,
new string[] { "precision,scale" }, true, false, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, null,
new string[] { "precision", "number of bits used to store the mantissa" }, true, null, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, null, null, null, null, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, typeof(Decimal), null,
new string[] { "precision,scale" }, true, false, null);
if (rows.Length == 0)
rows = SelectDataType(dt, typeof(Decimal), null, null, false, false, null);
}
}
}
}
else if (ti.DataType == typeof(Single))
{
rows = SelectDataType(dt, ti.DataType, null,
new string[] { "precision,scale" }, true, false, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, null,
new string[] { "precision", "number of bits used to store the mantissa" }, true, null, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, null, null, null, null, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, typeof(Decimal), null,
new string[] { "precision,scale" }, true, false, null);
if (rows.Length == 0)
rows = SelectDataType(dt, typeof(Decimal), null, null, false, false, null);
}
}
}
}
else if (ti.DataType == typeof(Int16) ||
ti.DataType == typeof(Int32) || ti.DataType == typeof(Int64))
{
rows = SelectDataType(dt, ti.DataType, null, new string[] { null }, true, null, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, null, new string[] { null }, false, null, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, typeof(Decimal), null,
new string[] { "precision,scale" }, true, false, null);
if (rows.Length == 0)
rows = SelectDataType(dt, typeof(Decimal), null, null, false, false, null);
}
}
}
else
{
rows = SelectDataType(dt, ti.DataType, null, new string[] { null }, true, null, null);
if (rows.Length == 0)
{
rows = SelectDataType(dt, ti.DataType, null, new string[] { null }, false, null, null);
if (rows.Length == 0)
rows = SelectDataType(dt, ti.DataType, null, null, null, null, null);
}
}
if (rows.Length == 0)
{
sb.Append("<Unknown>");
HasUnknownDatatype = true;
}
else
{
DataRow dr = rows[rows.Length - 1];
string typeName = (String)dr["TypeName"];
string createParams = "";
if (!dr.IsNull("CreateParameters"))
createParams = (string)dr["CreateParameters"];
string createFormat = "";
if (!dr.IsNull("CreateFormat"))
createFormat = (string)dr["CreateFormat"];
if (createFormat == "" || createParams == "")
sb.Append(typeName);
else
{
if ((createParams == "size" || createParams == "max length"))
sb.AppendFormat(createFormat, size[k] > 0 ? size[k] : 255);
else if (createParams == "precision,scale" || createParams == "precision" ||
createParams == "number of bits used to store the mantissa")
sb.AppendFormat(typeName);
else
sb.Append(typeName);
}
}
}
else
sb.Append(ti.ExportDataType);
}
if (sb2.Length > 0)
{
sb.Append(",");
sb.AppendLine();
sb.AppendFormat("PRIMARY KEY ({0})", sb2.ToString());
}
if (sb3.Length > 0)
{
sb.Append(",");
sb.AppendLine();
sb.AppendFormat("UNIQUE ({0})", sb3.ToString());
}
conn.Close();
return sb.ToString();
}
}
private DataTable CreateRowType(IniFile ini, string section, ref TextFileDataFormat df)
{
ParseIniTextDataFormat(ini, section, ref df);
ParseIniCharacterSet(ini, section, ref df);
df.DateTimeFormat = GetIniString(ini, section, "DateTimeFormat", df.DateTimeFormat);
df.DecimalSymbol = GetIniString(ini, section, "DecimalSymbol", df.DecimalSymbol);
df.CurrencySymbol = GetIniString(ini, section, "CurrencySymbol", df.CurrencySymbol);
df.CurrencyThousandSymbol = GetIniString(ini, section, "CurrencyThousandSymbol", df.CurrencyThousandSymbol);
df.CurrencyDecimalSymbol = GetIniString(ini, section, "CurrencyDecimalSymbol", df.CurrencyDecimalSymbol);
df.NumberDigits = GetIniNumber(ini, section, "NumberDigits", df.NumberDigits);
df.CurrencyDigits = GetIniNumber(ini, section, "CurrencyDigits", df.CurrencyDigits);
df.ColumnNameHeader = GetIniBoolean(ini, section, "ColNameHeader", df.ColumnNameHeader);
df.NumberLeadingZeros = GetIniBoolean(ini, section, "NumberLeadingZeros", df.NumberLeadingZeros);
df.SequentialProcessing = GetIniBoolean(ini, section, "SequentialProcessing", df.SequentialProcessing);
ParseCurrencyNegFormat(ini, section, ref df);
ParseCurrencyPosFormat(ini, section, ref df);
df.NullValue = GetIniString(ini, section, "NullValue", null);
List<string> names = new List<string>();
List<int> length = new List<int>();
DataProviderHelper helper = new DataProviderHelper();
DataTable dt = RowType.CreateSchemaTable();
string[] keys = ini.GetEntryNames(section);
Regex regex = new Regex("^Col[0-9]+$");
StringBuilder sb = new StringBuilder();
string[] values = new string[4];
int ordinal = 0;
for (int k = 0; k < keys.Length; k++)
if (regex.IsMatch(keys[k]))
{
CsvParser parser = new CsvParser(' ');
if (parser.Get((string)ini.GetValue(section, keys[k]), values) > 0)
{
DataRow dr = dt.NewRow();
dr["ColumnOrdinal"] = ordinal++;
dr["ColumnName"] = Util.CreateUniqueName(names, helper.NativeFormatIdentifier(values[0]));
dr["DataType"] = DecodeDataType(values[1]);
if (values[2] == "Width" && values[3] != null)
{
int size = Int32.Parse(values[3]);
if ((System.Type)dr["DataType"] == typeof(System.String))
dr["ColumnSize"] = size;
length.Add(size);
}
else
if (df.TextFormat == TextDataFormat.FixedLength)
throw new ESQLException(Properties.Resources.ExpectedWidthInSchemaIni, values[0]);
dt.Rows.Add(dr);
}
}
if (df.TextFormat == TextDataFormat.FixedLength)
{
if (dt.Rows.Count == 0)
throw new ESQLException(Properties.Resources.ColumnDataTypeRequred);
df.Width = length.ToArray();
}
return dt;
}
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));
}
}
}
private String WriteLiteral(DataProviderHelper helper, Object value)
{
switch (Type.GetTypeCode(value.GetType()))
{
case TypeCode.DateTime:
return helper.FormatDateTime((DateTime)value);
case TypeCode.Single:
case TypeCode.Double:
case TypeCode.Decimal:
{
decimal n = Convert.ToDecimal(value);
return n.ToString(CultureInfo.InvariantCulture);
}
default:
return helper.FormatLiteral(value.ToString());
}
}
private DataTable CreateRowType(StreamReader reader, char delimiter, bool columnNameHeader)
{
string[] values = new string[MaxTextColumns];
StringBuilder sb = new StringBuilder();
string[] header = null;
int maxcols = 0;
CsvParser parser = new CsvParser(delimiter);
while (!reader.EndOfStream)
{
maxcols = parser.Get(reader, values);
if (maxcols > 0 && columnNameHeader)
{
header = new string[maxcols];
Array.Copy(values, header, maxcols);
break;
}
}
List<string> names = new List<string>();
DataProviderHelper helper = new DataProviderHelper();
DataTable dt = RowType.CreateSchemaTable();
for (int k = 0; k < maxcols; k++)
{
DataRow dr = dt.NewRow();
dr["ColumnOrdinal"] = k;
if (columnNameHeader && header != null && k < header.Length)
{
String name = helper.NativeFormatIdentifier(header[k]);
if (String.IsNullOrEmpty(name))
name = "Col";
dr["ColumnName"] = Util.CreateUniqueName(names, name);
}
else
dr["ColumnName"] = String.Format("Col{0}", k + 1);
dr["DataType"] = typeof(System.String);
dt.Rows.Add(dr);
}
return dt;
}
public DbDataAdapter CreateDataAdapter()
{
DataProviderHelper helper = new DataProviderHelper(ProviderInvariantName, ConnectionString);
DbProviderFactory f = DbProviderFactories.GetFactory(ProviderInvariantName);
DbConnection conn = f.CreateConnection();
conn.ConnectionString = ConnectionString;
DbCommand command = conn.CreateCommand();
command.CommandText = CreateCommandText();
DbDataAdapter adapter = f.CreateDataAdapter();
adapter.UpdateBatchSize = helper.UpdateBatchSize;
adapter.SelectCommand = command;
DbCommandBuilder builder = f.CreateCommandBuilder();
builder.DataAdapter = adapter;
builder.QuotePrefix = Convert.ToString(helper.LeftQuote);
builder.QuoteSuffix = Convert.ToString(helper.RightQuote);
return adapter;
}
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;
}
private void okButton_Click(object sender, RoutedEventArgs e)
{
if (DataSource == null)
return;
DataProviderHelper helper = new DataProviderHelper(DataSource.InvariantName, DataSource.ConnectionString);
if (BatchMove.IsTableExists())
{
if (MessageBox.Show(String.Format("The table {0} is already exists on database server.\r\n" +
"Do you want to drop existing table ?", TableName), "Confirmation", MessageBoxButton.OKCancel, MessageBoxImage.Information) == MessageBoxResult.OK &&
MessageBox.Show("All table data will be lost !\r\nContinue ?", "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes)
BatchMove.DropTable();
}
DbConnection conn = DataProviderHelper.CreateDbConnection(DataSource.InvariantName);
conn.ConnectionString = DataSource.ConnectionString;
DbCommand command = conn.CreateCommand();
command.CommandText = CommandText;
conn.Open();
try
{
command.ExecuteNonQuery();
DialogResult = true;
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message, "SQL Error",
MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
conn.Close();
if (!autoGenerateDDL)
{
MainWindow main = (MainWindow)Application.Current.MainWindow;
DatabaseDictionary dict = main.DatasourceController.Dictionary;
TableType tableType = dict.GetTableType(DataSource.Prefix,
Util.SplitName(BatchMove.TableName));
BatchMove.SetFieldsFromTableType(tableType);
}
}
public DataProviderQueryAccessor(DataSourceInfo dataSourceInfo, Notation notation, Symbol squery)
: base()
{
_rowType = null;
_connectionString = dataSourceInfo.ConnectionString;
_providerInvariantName = dataSourceInfo.ProviderInvariantName;
_helper = new DataProviderHelper(_providerInvariantName, _connectionString);
SqlQueryWriter writer = new SqlQueryWriter(notation);
writer.ProviderHelper = _helper;
if (squery.Tag == Tag.Stmt)
writer.WriteStmt(squery);
else
writer.WriteQueryExp(squery);
_commandText = writer.ToString();
_parameterBindings = writer.Bindings.ToArray();
}
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();
}
public ProxyDataAdapter CreateProxyDataAdapter()
{
DataProviderHelper helper = new DataProviderHelper(ProviderInvariantName, ConnectionString);
RemoteDbProviderFactory f = RemoteDbProviderFactories.GetFactory(ProviderInvariantName);
DbConnection conn = f.CreateConnection();
conn.ConnectionString = ConnectionString;
RemoteCommand command = f.CreateCommand();
command.Connection = conn;
command.CommandText = CreateCommandText();
ProxyDataAdapter proxy = f.CreateProxyDataAdapter();
proxy.UpdateBatchSize = helper.UpdateBatchSize;
proxy.SelectCommand = command.InnerCommand;
proxy.QuotePrefix = Convert.ToString(helper.LeftQuote);
proxy.QuoteSuffix = Convert.ToString(helper.RightQuote);
return proxy;
}
private String WriteList(Binder binder, DataProviderHelper helper, object[] list,
Object[] parameters, DbCommand command)
{
StringBuilder sb = new StringBuilder();
sb.Append("(");
for (int k = 0; k < list.Length; k++)
{
if (k > 0)
sb.Append(", ");
sb.Append(WriteExpr(binder, helper, list[k], parameters, command));
}
sb.Append(")");
return sb.ToString();
}
public void ParseDDL(string commandText)
{
DataProviderHelper helper =
new DataProviderHelper(ProviderInvariantName, ConnectionString);
char[] chars = commandText.ToCharArray();
bool bInside = false;
StringBuilder sb = new StringBuilder();
List<String> lines = new List<string>();
List<String> FieldNames = new List<string>();
for (int i = 0; i < chars.Length; i++)
{
char ch = chars[i];
if (ch == '(')
bInside = true;
else
if (bInside)
{
if (ch == ')')
break;
else if (ch == ',')
{
lines.Add(sb.ToString());
sb = new StringBuilder();
}
else
sb.Append(ch);
}
}
foreach (string s in lines)
{
chars = s.ToCharArray();
bool bLiteral = false;
for (int i = 0; i < chars.Length; i++)
{
char ch = chars[i];
if (Char.IsLetterOrDigit(ch) || bLiteral)
sb.Append(ch);
else if (ch == helper.LeftQuote)
bLiteral = true;
else if (ch == helper.RightQuote)
bLiteral = false;
else
if (Char.IsWhiteSpace(ch) && sb.Length > 0)
{
string name = sb.ToString();
sb = new StringBuilder();
if (!helper.IsKeyword(name))
FieldNames.Add(name);
break;
}
}
}
}
protected override void Dump(System.IO.TextWriter w, int padding)
{
OutlineNode(w, padding);
DataProviderHelper helper = new DataProviderHelper(TableType);
w.Write("DataProviderTableAccessor {0},{1}",
TableType.DataSource.ProviderInvariantName, TableType.ToString(helper));
if (_filterPredicate != null)
{
w.Write(" ");
w.Write(Lisp.Format(_filterPredicate));
}
w.WriteLine();
}
public int GetTableEstimate(TableType tableType, int threshold)
{
TableEstimate tableEstimate;
lock (estimate)
if (!estimate.TryGetValue(tableType, out tableEstimate))
{
tableEstimate = new TableEstimate();
estimate.Add(tableType, tableEstimate);
}
lock (tableEstimate)
{
if (tableEstimate.count == tableEstimate.threshold || tableEstimate.threshold < threshold)
{
DataProviderHelper helper = new DataProviderHelper(tableType);
DbConnection connection = DataProviderHelper.CreateDbConnection(tableType.DataSource.ProviderInvariantName);
connection.ConnectionString = tableType.DataSource.ConnectionString;
connection.Open();
try
{
DbCommand command = connection.CreateCommand();
command.CommandText = helper.GetEstimateRowCountQuery(tableType.ToString(helper), threshold);
tableEstimate.count = Convert.ToInt32(command.ExecuteScalar());
tableEstimate.threshold = threshold;
}
finally
{
connection.Close();
}
}
return tableEstimate.count;
}
}
