public void AppendIdentifier(string identifier)
{
string correctIdentifier;
if (identifier.ToLower().StartsWith("dbo.", StringComparison.CurrentCultureIgnoreCase))
{
correctIdentifier = identifier.Substring(4);
}
else if (identifier.ToLower().StartsWith("Jet.", StringComparison.CurrentCultureIgnoreCase))
{
correctIdentifier = identifier.Substring(4);
}
else
{
correctIdentifier = identifier;
}
if (correctIdentifier.Length > JetProviderManifest.MaxObjectNameLength)
{
string guid = Guid.NewGuid().ToString().Replace("-", "");
correctIdentifier = correctIdentifier.Substring(0, JetProviderManifest.MaxObjectNameLength - guid.Length) + guid;
}
AppendSql(JetProviderManifest.QuoteIdentifier(correctIdentifier));
}
private static DataRow GetFieldRow(IDbConnection connection, string tableName, string columnName)
{
if (_lastTableName != tableName)
{
_lastTableName = tableName;
// This is the standard read column for DBMS
string sql = string.Empty;
sql += "Select Top 1";
sql += " * ";
sql += "From ";
sql += string.Format(" {0} ", JetProviderManifest.QuoteIdentifier(_lastTableName));
IDbCommand command = null;
IDataReader dataReader = null;
try
{
command = connection.CreateCommand();
command.CommandText = sql;
dataReader = command.ExecuteReader(CommandBehavior.KeyInfo);
_lastStructureDataTable = dataReader.GetSchemaTable();
}
finally
{
// Exceptions will not be catched but these instructions will be executed anyway
if (command != null)
{
command.Dispose();
}
if (dataReader != null)
{
dataReader.Dispose();
}
}
}
DataRow[] fieldRows = _lastStructureDataTable.Select(string.Format("ColumnName = '{0}'", columnName.Replace("'", "''")));
if (fieldRows.Length != 1) // 0 columns or more column with that name
{
Debug.Assert(false);
return(null);
}
return(fieldRows[0]);
}
public void AppendIdentifier(string identifier)
{
string correctIdentifier;
correctIdentifier = identifier.ToLower().StartsWith("dbo.") ? identifier.Substring(4) : identifier;
if (correctIdentifier.Length > JetProviderManifest.MaxObjectNameLength)
{
string guid = Guid.NewGuid().ToString().Replace("-", "");
correctIdentifier = correctIdentifier.Substring(0, JetProviderManifest.MaxObjectNameLength - guid.Length) + guid;
}
AppendSql(JetProviderManifest.QuoteIdentifier(correctIdentifier));
}
/// <summary>
/// Write this symbol out as a string for sql. This is just
/// the new name of the symbol (which could be the same as the old name).
///
/// We rename columns here if necessary.
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
if (this.NeedsRenaming)
{
string newName;
int i = sqlGenerator.AllColumnNames[this.NewName];
do
{
++i;
newName = this.Name + i.ToString(System.Globalization.CultureInfo.InvariantCulture);
} while (sqlGenerator.AllColumnNames.ContainsKey(newName));
sqlGenerator.AllColumnNames[this.NewName] = i;
// Prevent it from being renamed repeatedly.
this.NeedsRenaming = false;
this.NewName = newName;
// Add this column name to list of known names so that there are no subsequent
// collisions
sqlGenerator.AllColumnNames[newName] = 0;
}
writer.Write(JetProviderManifest.QuoteIdentifier(this.NewName));
}
/// <summary>
/// Create a DbCommand object, given the provider manifest and command tree
/// </summary>
private DbCommand CreateCommand(DbProviderManifest manifest, DbCommandTree commandTree)
{
if (manifest == null)
{
throw new ArgumentNullException("manifest");
}
if (commandTree == null)
{
throw new ArgumentNullException("commandTree");
}
JetProviderManifest jetManifest = (manifest as JetProviderManifest);
if (jetManifest == null)
{
throw new ArgumentException("The provider manifest given is not of type 'JetProviderManifest'.");
}
JetCommand command = new JetCommand();
List <DbParameter> parameters;
CommandType commandType;
command.CommandText = SqlGenerator.GenerateSql(commandTree, out parameters, out commandType);
command.CommandType = commandType;
//if (command.CommandType == CommandType.Text)
// command.CommandText += Environment.NewLine + Environment.NewLine + "-- provider: " + this.GetType().Assembly.FullName;
// Get the function (if any) implemented by the command tree since this influences our interpretation of parameters
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
{
function = ((DbFunctionCommandTree)commandTree).EdmFunction;
}
// Now make sure we populate the command's parameters from the CQT's parameters:
foreach (KeyValuePair <string, TypeUsage> queryParameter in commandTree.Parameters)
{
OleDbParameter parameter;
// Use the corresponding function parameter TypeUsage where available (currently, the SSDL facets and
// type trump user-defined facets and type in the EntityCommand).
FunctionParameter functionParameter;
if (function != null && function.Parameters.TryGetValue(queryParameter.Key, false, out functionParameter))
{
parameter = CreateJetParameter(functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value);
}
else
{
parameter = CreateJetParameter(queryParameter.Key, queryParameter.Value, ParameterMode.In, DBNull.Value);
}
command.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen (note: this feature is only safe for DML SQL gen which
// does not support user parameters, where there is no risk of name collision)
if (parameters != null && parameters.Count > 0)
{
if (!(commandTree is DbInsertCommandTree) &&
!(commandTree is DbUpdateCommandTree) &&
!(commandTree is DbDeleteCommandTree))
{
throw new InvalidOperationException("SqlGenParametersNotPermitted");
}
foreach (DbParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return(command);
}
public override void Visit(DbPropertyExpression expression)
{
_commandText.Append(JetProviderManifest.QuoteIdentifier(expression.Property.Name));
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where IdentityValue = @@Identity
///
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
private static void GenerateReturningSql(StringBuilder commandText, DbModificationCommandTree tree,
ExpressionTranslator translator, DbExpression returning)
{
// Nothing to do if there is no Returning expression
if (returning == null)
{
return;
}
// select
commandText.Append("select ");
returning.Accept(translator);
commandText.AppendLine();
// from
commandText.Append("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
commandText.Append("where ");
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
bool identity = false;
bool first = true;
foreach (EdmMember keyMember in table.ElementType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(" and ");
}
commandText.Append(JetProviderManifest.QuoteIdentifier(keyMember.Name));
commandText.Append(" = ");
// retrieve member value sql. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL)
DbParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
}
else
{
// if no value is registered for the key member, it means it is an identity
// which can be retrieved using the @@identity function
if (identity)
{
// there can be only one server generated key
throw new NotSupportedException(string.Format("Server generated keys are only supported for identity columns. More than one key column is marked as server generated in table '{0}'.", table.Name));
}
if (keyMember.TypeUsage.EdmType.Name.ToLower() == "guid")
{
// We can't retrieve the latest inserted guid from Access
//commandText.Append("@@guid");
commandText.AppendFormat("{{{0}}}", _lastGuid);
}
else
{
commandText.Append("@@identity");
}
identity = true;
}
}
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List <DbParameter> parameters, bool insertParametersValuesInSql = false)
{
StringBuilder commandText = new StringBuilder(COMMANDTEXT_STRINGBUILDER_INITIALCAPACITY);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, tree.Returning != null, insertParametersValuesInSql);
commandText.Append("insert into ");
tree.Target.Expression.Accept(translator);
// Actually is not possible to retrieve the last inserted guid from Access
// We can understand if there is a guid checking the returning value of the insert
// statement
string guidAutogeneratedColumn = null;
if (tree.Returning is DbNewInstanceExpression)
{
guidAutogeneratedColumn = GetGuidArgs(tree.Returning as DbNewInstanceExpression);
}
if (tree.SetClauses.Count != 0)
{
bool first = true;
// (c1, c2, c3, ...)
commandText.Append("(");
if (!string.IsNullOrEmpty(guidAutogeneratedColumn))
{
commandText.Append(JetProviderManifest.QuoteIdentifier(guidAutogeneratedColumn));
first = false;
}
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append("values (");
if (!string.IsNullOrEmpty(guidAutogeneratedColumn))
{
_lastGuid = Guid.NewGuid();
commandText.Append(string.Format("{{{0}}}", _lastGuid));
first = false;
}
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(");");
}
else
{
commandText.AppendLine(" default values;");
}
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
parameters = translator.Parameters;
return(commandText.ToString());
}
private static bool GetIsIdentity(IDbConnection connection, DataRow rowColumn)
{
if (Convert.ToInt32(rowColumn["COLUMN_FLAGS"]) != 0x5a || Convert.ToInt32(rowColumn["DATA_TYPE"]) != 3)
{
return(false);
}
if (_lastTableName != (string)rowColumn["TABLE_NAME"])
{
_lastTableName = (string)rowColumn["TABLE_NAME"];
// This is the standard read column for DBMS
string sql = string.Empty;
sql += "Select ";
sql += " * ";
sql += "From ";
sql += string.Format(" {0} ", JetProviderManifest.QuoteIdentifier(_lastTableName));
sql += "Where ";
sql += " 1 = 2 ";
IDbCommand command = null;
IDataReader dataReader = null;
try
{
command = connection.CreateCommand();
command.CommandText = sql;
dataReader = command.ExecuteReader(CommandBehavior.KeyInfo);
_lastStructureDataTable = dataReader.GetSchemaTable();
}
finally
{
// Exceptions will not be catched but these instructions will be executed anyway
if (command != null)
{
command.Dispose();
}
if (dataReader != null)
{
dataReader.Dispose();
}
}
}
string columnName = (string)rowColumn["COLUMN_NAME"];
DataRow[] fieldRows = _lastStructureDataTable.Select(string.Format("ColumnName = '{0}'", columnName.Replace("'", "''")));
if (fieldRows.Length != 1) // 0 columns or more column with that name
{
Debug.Assert(false);
return(false);
}
DataRow fieldRow = fieldRows[0];
return((bool)fieldRow["IsAutoIncrement"]);
/*
* This are all the types we can use
* c.Name = (string)fieldRow["ColumnName"];
* c.Length = (int)fieldRow["ColumnSize"];
* c.NumericPrecision = DBToShort(fieldRow["NumericPrecision"]);
* c.NumericScale = DBToShort(fieldRow["NumericScale"]);
* c.NetType = (System.Type)fieldRow["DataType"];
* c.ProviderType = fieldRow["ProviderType"];
* if (connection.DBType == DBType.SQLite)
* c.IsLong = c.Length > 65535;
* else
* c.IsLong = (bool)fieldRow["IsLong"];
* c.AllowDBNull = (bool)fieldRow["AllowDBNull"];
* c.IsUnique = (bool)fieldRow["IsUnique"];
*
* c.IsKey = (bool)fieldRow["IsKey"];
*
* c.SchemaName = DBToString(fieldRow["BaseSchemaName"]);
*
* c.CatalogName = DBToString(fieldRow["BaseCatalogName"]);
*
* c.ComputeSQLDataType();
*
*/
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="manifestToken">A token used to infer the capabilities of the store</param>
public JetProviderManifest(string manifestToken) : base(JetProviderManifest.GetProviderManifest())
{
// GetStoreVersion will throw ArgumentException if manifestToken is null, empty, or not recognized.
_token = manifestToken;
}
