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));
}
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();
*
*/
}
