public ConnectInfo()
{
this.Text = "服务器";
this.Dbmstype = DbmsType.Oledb;
this.Uid = Guid.NewGuid().ToString();
this.driver = "SQLOLEDB";
}
public ConnectInfo()
{
this.Text="������";
this.Dbmstype=DbmsType.Oledb;
this.Uid=Guid.NewGuid().ToString();
this.driver="SQLOLEDB";
}
/// <summary>Executes <see cref="UpdateStatement"/>. Bulk updates are executed in multiple round trips - one update at a time.</summary>
public int Execute(UpdateStatement update, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
if (IsBulkUpdate(update))
return ExecuteBulkUpdate(update, dbms, conn, out lastExecutedCommandInfo, cmdTimeout);
else
return ExecuteSingleTableUpdate(update, dbms, conn, out lastExecutedCommandInfo, cmdTimeout);
}
internal static DBMSType ConvertDbmsType(DbmsType dbmsType)
{
if (dbmsType == DbmsType.Oracle)
{
return(DBMSType.Oracle);
}
else if (dbmsType == DbmsType.Pervasive)
{
return(DBMSType.Pervasive);
}
else if (dbmsType == DbmsType.Sqlite)
{
return(DBMSType.SQLite);
}
else if (dbmsType == DbmsType.MsSql)
{
return(DBMSType.MsSql);
}
else if (dbmsType == DbmsType.Unknown)
{
return(DBMSType.Unknown);
}
else
{
return(DBMSType.Unknown);
}
}
private static void RenderSingleQuery(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Either TOP or ORDER BY is used; but not both.
// SELECT ... FROM ...
// WHERE (ROWNUM <= @top) AND (@this.filter)
// GROUP BY ... HAVING ... ORDER BY ...
AppendSelectAndDistinctKeyWords(select, output);
AppendSelectList(select, dbms, output, parameters);
AppendFromClause(select, dbms, output);
// WHERE. We have made sure that if TOP is used then ORDER BY is not used.
if (select.Top > 0)
{
// WHERE ROWNUM <= this.top AND (this.filter).
output.Append(" WHERE ROWNUM <= " + select.Top.ToString(CultureInfo.InvariantCulture));
if (select.Where != null && !select.Where.IsEmpty)
{
output.Append(" AND (");
select.Where.Render(dbms, output, parameters);
output.Append(")");
}
}
else
{
AppenWhereWoRownum(select, dbms, output, parameters);
}
AppendGroupByClause(select, dbms, output, parameters);
AppendHavingClause(select, dbms, output, parameters);
AppendOrderByClause(select, dbms, output, parameters);
}
/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns>Ouput parameter that will contain the value retrieved by RETURNING clause.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
IDbColumn autoIdField = GetAutoIdField(insert.Table);
DbParameter autoId = null;
if (autoIdField != null)
{
// RETURNING id
output.Append(" RETURNING ");
autoIdField.RenderColumnName(dbms, output);
autoId = new DbParameter("?", DbType.Int32) { Direction = ParameterDirection.Output };
parameters.Add(autoId);
}
// Return auto-id DB parameter. Callers require it to retrieve the new ID value.
return autoId;
}
/// <summary>
/// Renders INSERT statement.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQLite doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns><b>null</b> because automatically generated ID must be fetched via SELECT after INSERT.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
// Auto ID must be fetched via SELECT after INSERT.
DbParameter autoId = null;
return autoId;
}
private static void AppendSelectList(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Select list.
if (select.SelectList != null && select.SelectList.Count > 0)
select.SelectList.Render(dbms, output, parameters);
else
output.Append("*");
}
private static void AppendRelations(UpdateStatement update, DbmsType dbms, StringBuilder output)
{
if (update.Relations != null && update.Relations.Count > 0)
{
output.Append(" ");
update.Relations.RenderFromClause(update.Table, dbms, output);
}
}
public SqlBuilder(string sqlStr
, DbmsType dbmsType = DbmsType.Unknown
, bool forSqlAccessor = true)
{
_sqlStr = sqlStr;
_dbmsType = SqlBuilder.ConvertDbmsType(dbmsType);
_forSqlAccessor = forSqlAccessor;
}
private static void AppendWhereClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// WHERE.
if (select.Where != null && !select.Where.IsEmpty)
{
output.Append(" WHERE ");
select.Where.Render(dbms, output, parameters);
}
}
private static void AppendWhere(SearchCondition where, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
bool hasFilter = (where != null) && !where.IsEmpty;
if (hasFilter)
{
output.Append(" WHERE ");
where.Render(dbms, output, parameters);
}
}
internal SqlBuilder(Stmt stmt
, DbmsType dbmsType = DbmsType.Unknown
, bool forSqlAccessor = true)
{
_stmt = stmt;
_dbmsType = SqlBuilder.ConvertDbmsType(dbmsType);
this.Dbms = dbmsType;
_forSqlAccessor = forSqlAccessor;
}
private static void AppendGroupByClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// GROUP BY.
if (select.GroupBy != null && select.GroupBy.Fields.Count > 0)
{
output.Append(" ");
select.GroupBy.Render(dbms, output, parameters);
}
}
private static void AppendHavingClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// HAVING.
if (select.Having != null && !select.Having.IsEmpty)
{
output.Append(" HAVING ");
select.Having.Render(dbms, output, parameters);
}
}
static Catalog()
{
string cfgDbmsType = ConfigurationManager.AppSettings["AdventureWorks.DbmsType"];
if (cfgDbmsType != null)
dbmsType = (DbmsType)Enum.Parse(typeof(DbmsType), cfgDbmsType);
if ((ConfigurationManager.ConnectionStrings != null) && (ConfigurationManager.ConnectionStrings["AdventureWorks.ConnectionString"] != null))
connectionString = ConfigurationManager.ConnectionStrings["AdventureWorks.ConnectionString"].ConnectionString;
}
public void Render(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Uses ROWNUM instead of TOP. If ORDER BY is used then current SELECT statement is rendered
// using sub-queries. ROWNUM is used in outer query.
bool orderByIsUsed = (select.OrderBy != null && select.OrderBy.Count > 0);
if (select.Top > 0 && orderByIsUsed)
RenderOrderByInSubQueryAndRownumInOuterQuery(select, dbms, output, parameters);
else
RenderSingleQuery(select, dbms, output, parameters);
}
/// <summary>
/// Constructor. Sets DBMS type and connection string.
/// </summary>
/// <param name="dbmsType">DBMS type to which this provider connects to. Only Oracle compatibile values are allowed.</param>
/// <param name="connectionString">Connection string.</param>
public OracleConnectionProvider(DbmsType dbmsType, string connectionString)
{
if (connectionString == null)
throw new ArgumentNullException("connectionString", Messages.ConnectionProvider_ConnectionStringMayNotBeNull);
if (!IsSupportedDbms(dbmsType))
throw new ArgumentException(Messages.ConnectionProvider_UnsupportedDbmsType + dbmsType.ToString(), "dbmsType");
this.rdbms = dbmsType;
this.oraConn = new OracleConnection(connectionString);
}
private static bool IsSupportedDbms(DbmsType dbmsType)
{
switch (dbmsType)
{
case DbmsType.OdbcGeneric:
return true;
default:
return false;
}
}
/// <summary>
/// Renders predicate as SQL statement.
/// </summary>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public override void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("(");
if (Negate)
output.Append("NOT EXISTS (");
else
output.Append("EXISTS (");
PredicateItems[0].Render(dbms, output, parameters);
output.Append("))");
}
/// <summary>
/// Renders DELETE statement.
/// </summary>
/// <param name="delete">DELETE statement to render.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(DeleteStatement delete, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("DELETE FROM ");
delete.FromTable.RenderTableName(dbms, output);
if (delete.Where != null && !delete.Where.IsEmpty)
{
output.Append(" WHERE ");
delete.Where.Render(dbms, output, parameters);
}
}
private static bool IsSupportedDbms(DbmsType dbmsType)
{
switch (dbmsType)
{
case DbmsType.SqlServerCe_4:
return true;
default:
return false;
}
}
private static void AppendUpdateList(UpdateList list, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append(" SET ");
for (int setExpressionIdx = 0; setExpressionIdx < list.Count - 1; setExpressionIdx++)
{
list[setExpressionIdx].Render(dbms, output, parameters);
output.Append(", ");
}
list[list.Count - 1].Render(dbms, output, parameters);
}
public void Render(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
AppendSelectAndDistinctKeyWords(select, output);
AppendTopExpression(select, output);
AppendSelectList(select, dbms, output, parameters);
AppendFromClause(select, dbms, output);
AppendWhereClause(select, dbms, output, parameters);
AppendGroupByClause(select, dbms, output, parameters);
AppendHavingClause(select, dbms, output, parameters);
AppendOrderByClause(select, dbms, output, parameters);
}
/// <summary>Renders UPDATE statement.</summary>
public void RenderUpdate(UpdateStatement update, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
if (update.UpdateList == null || update.UpdateList.Count == 0)
throw new InvalidOperationException(Messages.SqlUpdater_SetListIsEmpty);
output.Append("UPDATE ");
update.Table.RenderTableName(dbms, output);
AppendUpdateList(update.UpdateList, dbms, output, parameters);
AppendRelations(update, dbms, output);
AppendWhere(update.Where, dbms, output, parameters);
}
private static void AppendFromFirstTableNameAndAlias(IDbTable firstTable, DbmsType dbms, StringBuilder from)
{
from.Append("FROM ");
firstTable.RenderTableName(dbms, from);
if (firstTable.Alias != firstTable.TableName)
{
// AS is not used because some DBMSs don't support that syntax.
from.Append(" ");
firstTable.RenderAlias(dbms, from);
}
}
private static bool IsSupportedDbms(DbmsType dbmsType)
{
switch (dbmsType)
{
case DbmsType.MySql_5:
return true;
default:
return false;
}
}
private int ExecuteSingleTableUpdate(UpdateStatement update, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
DbParameterCollection parameters = new DbParameterCollection();
StringBuilder cmdText = new StringBuilder();
RenderUpdate(update, dbms, cmdText, parameters);
string command = cmdText.ToString();
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
int rowsAffected = DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text, cmdTimeout);
lastExecutedCommandInfo.StopTime();
return rowsAffected;
}
private static void AppendTableNameAndAlias(DbmsType dbms, StringBuilder from, IDbTable currTable)
{
currTable.RenderTableName(dbms, from);
from.Append(" ");
if (currTable.Alias != currTable.TableName)
{
// AS is not used because some DBMSs don't support that syntax.
from.Append(" ");
currTable.RenderAlias(dbms, from);
from.Append(" ");
}
}
private static bool IsSupportedDbms(DbmsType dbmsType)
{
switch (dbmsType)
{
case DbmsType.Oracle_11g:
case DbmsType.Oracle_10g:
case DbmsType.Oracle_9i:
return true;
default:
return false;
}
}
public static string ExecuteQuery(string query, string connectionString, DbmsType type)
{
IQueryExecutor executor = null;
switch (type)
{
case DbmsType.SqlServer:
executor = new SqlServerExecutor(connectionString);
break;
}
return(executor.ExecuteQueryAsString(query));
}
public static bool DropDataBase(DbmsType selectedDbms, string dataBaseName)
{
IHelper helper = null;
switch (selectedDbms)
{
case DbmsType.SqlServer:
helper = new SqlServerHelper();
break;
}
return(helper.DropDataBase(dataBaseName));
}
/// <summary>Returns <b>true</b> if the given value belongs to Oracle family.</summary>
public static bool IsOracle(DbmsType dbms)
{
switch (dbms)
{
case DbmsType.Oracle_11g:
case DbmsType.Oracle_10g:
case DbmsType.Oracle_9i:
return true;
default:
return false;
}
}
/// <summary>Renders LIKE predicate as SQL element.</summary>
/// <param name="like">Like predicate.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(LikePredicate like, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("(");
like.PredicateItems[0].Render(dbms, output, parameters);
if (like.Negate)
output.Append(" NOT LIKE ");
else
output.Append(" LIKE ");
like.PredicateItems[1].Render(dbms, output, parameters);
output.Append(")");
}
/// <summary>
/// Renders SQL ON clause.
/// Eg: 'ON Countries.Id = Cities.IdCountry'
/// </summary>
/// <param name="rel">Relation to render as ON clause.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which ON statement is appended.</param>
private static void RenderOnStatement(DbRelation rel, DbmsType dbms, StringBuilder output)
{
output.Append("ON ");
for (int fieldIdxInRelation = 0; fieldIdxInRelation < rel.ParentPrimaryKey.Length; fieldIdxInRelation++)
{
if (fieldIdxInRelation > 0)
output.Append("AND ");
rel.ParentPrimaryKey[fieldIdxInRelation].RenderFullName(dbms, output);
output.Append(" = ");
rel.ChildForeignKey[fieldIdxInRelation].RenderFullName(dbms, output);
output.Append(" ");
}
}
/// <summary>
/// Renders function as SQL element.
/// </summary>
/// <param name="function">Function.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="allParameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(Function function, DbmsType dbms, StringBuilder output, DbParameterCollection allParameters)
{
output.Append(function.Name);
output.Append("(");
for (int paramIdx = 0; paramIdx < function.FunctionArguments.Count - 1; paramIdx++)
{
function.FunctionArguments[paramIdx].Render(dbms, output, allParameters);
output.Append(", ");
}
function.FunctionArguments[function.FunctionArguments.Count - 1].Render(dbms, output, allParameters);
output.Append(")");
}
public ActionResult CreateProtectedDb(ProtectedDbVm vm)
{
vm.AvailableServers = GetAvailableDbmsAsListString();
if (!ModelState.IsValid)
{
return(View(vm));
}
//Receives info
AppUser user = _userManager.FindById(User.Identity.GetUserId());
var tempDbInfo = vm.ToCreateDatabaseObject(user);
//Check Db Exists
DbmsType selectedDbmsType = (DbmsType)Enum.Parse(typeof(DbmsType), vm.SelectedServer);
if (DataService.CheckDataBaseExists(selectedDbmsType, tempDbInfo.Name))
{
string errorMessage = "База данных с таким именем уже существует в этой СУБД.";
ModelState.AddModelError("err", errorMessage);
return(View(vm));
}
//Try to create database
try
{
tempDbInfo.ConnectionString = DataService.CreateDatabase(tempDbInfo, GetSqlServerAddress(), user.UserNickName, vm.DataBasePassword);
}
catch (Exception e)
{
return(View("CustomError", (object)e.ToString()));
}
//Save action to database
//AddRecord new info to user. No ID and foreighn Key (!)
DataBasesManager.AddDbInfo(tempDbInfo);
_userManager.Update(user);
return(View("RequestConnectionString"));
}
public ActionResult CreateAnonymousDb(AnonymousDbVm vm)
{
vm.AvailableServers = GetAvailableDbmsAsListString();
if (!ModelState.IsValid)
{
return(View(vm));
}
var tempObj = vm.ToCreateDatabaseObject();
//Check Db Exists
DbmsType selectedDbmsType = (DbmsType)Enum.Parse(typeof(DbmsType), vm.SelectedServer);
if (DataService.CheckDataBaseExists(selectedDbmsType, tempObj.Name))
{
string errorMessage = "База данных с таким именем уже существует в этой СУБД.";
ModelState.AddModelError("err", errorMessage);
return(View(vm));
}
//Try to create database
String connectionString;
try
{
connectionString = DataService.CreateDatabase(tempObj, GetSqlServerAddress());
}
catch (Exception e)
{
return(View("CustomError", (object)e.ToString()));
}
tempObj.ConnectionString = connectionString;
DataBasesManager.AddAnonymousDbInfo(tempObj);
ViewBag.IsAnon = true;
return(View("RequestConnectionString", (object)connectionString));
}
