private void UpdateDeletedApp(int appId)
{
SqlSchema schema = new SqlSchema();
using (IDbCommand command = schema.GetCommand())
{
command.Connection = schema.GetConnection(Maps.Instance.DuradosMap.connectionString);
command.CommandText = "UPDATE durados_App SET [ToDelete]=1,[deleteddate] =getdate() WHERE Id=@Id";
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("Id", appId));
try
{
command.Connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{ throw new DuradosException("<br>Failed to update app " + appId.ToString()); }
command.CommandText = "Delete From durados_UserApp WHERE appId=@Id";
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
}
catch (Exception ex)
{ throw new DuradosException("<br>Failed to update user app " + appId.ToString()); }
}
}
private void UpdateDeletedApp(int appId)
{
SqlSchema schema = new SqlSchema();
using (IDbCommand command = schema.GetCommand())
{
command.Connection = schema.GetConnection(Durados.Windows.Utilities.AzureUploader.Properties.Settings.Default.LocalConnection);
command.CommandText = "UPDATE durados_App SET [ToDelete]=1,[deleteddate] =getdate() WHERE Id=@Id";
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("Id", appId));
try
{
command.Connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{ throw new DuradosException("<br>Failed to update app " + appId.ToString()); }
command.CommandText = "Delete From durados_UserApp WHERE appId=@Id";
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
}
catch (Exception ex)
{ throw new DuradosException("<br>Failed to update user app " + appId.ToString()); }
}
}
private DataTable GetNodesTableFromDb(string parentKey)
{
//string treeFirstLevelSql = "SELECT {0} AS [title], [{1}] AS [key] FROM [{2}] WHERE [{3}] {4}";
SqlSchema sqlSchema = GetSqlSchema(view.Database.SqlProduct);
string treeFirstLevelSql = sqlSchema.GetTreeFirstLevelSql();
if (string.IsNullOrEmpty(parentKey))
{
parentKey = "IS NULL";
//parentKey = "= 0";
}
else
{
parentKey = "= '" + parentKey + "'";
}
if (!string.IsNullOrEmpty(view.PermanentFilter))
{
treeFirstLevelSql += " and " + view.GetPermanentFilter() + " ";
}
//string query = string.Format(treeFirstLevelSql, view.DisplayField.IsCalculated ? view.DisplayField.Formula : ("[" + view.DisplayField.Name + "]"), view.PrimaryKeyFileds[0].DatabaseNames, view.DataTable.TableName, view.Fields[view.TreeRelatedFieldName].DatabaseNames, parentKey);
//DataAccess.SqlAccess da = new DataAccess.SqlAccess();
string query = string.Format(treeFirstLevelSql, view.DisplayField.IsCalculated ? view.DisplayField.Formula : sqlSchema.sqlTextBuilder.EscapeDbObject(view.DisplayField.Name), view.PrimaryKeyFileds[0].DatabaseNames, view.DataTable.TableName, view.Fields[view.TreeRelatedFieldName].DatabaseNames, parentKey);
DataAccess.IDataTableAccess da = DataAccess.DataAccessHelper.GetDataTableAccess(view);
DataTable dtNodes = da.ExecuteTable(view.Database.ConnectionString, query, null, CommandType.Text);
return(dtNodes);
}
/// <summary>
/// Gets the schema to use.
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
private SqlSchema GetSchema(DatabaseUpdateArgs args)
{
if (args == null)
{
throw new ArgumentNullException("args");
}
// mbr - 02-10-2007 - build a search spec...
GetSchemaArgs schemaArgs = new GetSchemaArgs()
{
DatabaseName = args.DatabaseName
};
foreach (EntityType et in args.LimitEntityTypes)
{
schemaArgs.ConstrainTableNames.Add(et.NativeName.Name);
schemaArgs.ConstrainTableNames.Add(et.NativeNameExtended.Name);
}
// get the schema...
SqlSchema schema = Database.GetSchema(schemaArgs);
if (schema == null)
{
throw new InvalidOperationException("schema is null.");
}
// return...
return(schema);
}
private static HashSet <string> GetReferencedTables(string tableName, string connectionString)
{
HashSet <string> referenceTables = new HashSet <string>();
string sql = @"SELECT KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME WHERE KCU1.TABLE_NAME=N'" + tableName + "'";
SqlSchema schema = new SqlSchema();
using (IDbCommand command = schema.GetCommand())
{
command.Connection = schema.GetConnection(connectionString);
command.CommandText = sql;
try
{
command.Connection.Open();
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
referenceTables.Add(reader["REFERENCED_TABLE_NAME"].ToString());
}
return(referenceTables);
}
catch (Exception ex)
{
throw new DuradosException("<br>Failed to get referenced table for " + tableName);
}
}
}
public static bool Get_act_saleproduct_mid(SqlSchema smSaleProd, NameValueCollection form)
{
string sql = @"select top 1 pnum from gungnir..act_saleproduct with (nolock) where pnum like @pnum and mid=@mid and id<>@id";
return(SqlAdapter.Create(sql, smSaleProd, CommandType.Text, "Aliyun")
.Par(form).ExecuteModel().IsEmpty);
}
internal void DropTable(View view)
{
SqlSchema sqlSchema = new SqlSchema();
IDbCommand command = sqlSchema.GetCommand();
using (command.Connection = sqlSchema.GetConnection(view.Database.ConnectionString))
{
command.Connection.Open();
if (sqlSchema.IsViewExists(view.Name, command))
{
sqlSchema.DropView(view.Name, command);
if (!string.IsNullOrEmpty(view.EditableTableName))
{
if (sqlSchema.IsTableExists(view.EditableTableName, command))
{
sqlSchema.DropTable(view.EditableTableName, command);
}
}
}
else
{
if (sqlSchema.IsTableExists(view.Name, command))
{
sqlSchema.DropTable(view.Name, command);
}
}
logger.Log("ProductMaintenance", "RemoveApp", "DropTable", null, 3, "In Catalog " + command.Connection.Database + " view:" + view.Name + " and his editable table was droped ");
}
}
private App GetApp(string sql, string parameterName, string parameterVal)
{
App app = new App();
SqlSchema schema = new SqlSchema();
IDbCommand command = schema.GetCommand();
command.Connection = schema.GetConnection(Maps.Instance.DuradosMap.connectionString);
ValidateSelectFunctionExists(command);
command.CommandText = sql;
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(parameterName, parameterVal));
try
{
command.Connection.Open();
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
app.Server = reader.GetString(reader.GetOrdinal("ServerName"));
app.Catalog = reader.GetString(reader.GetOrdinal("catalog"));
app.SystemServer = reader.GetString(reader.GetOrdinal("sysServerName"));
app.SystemCatalog = reader.GetString(reader.GetOrdinal("sysCatalog"));
app.AppType = reader.GetInt32(reader.GetOrdinal("AppType"));
app.Name = reader.GetString(reader.GetOrdinal("Name"));
app.AppId = reader.GetInt32(reader.GetOrdinal("Id"));
}
reader.Close();
}
}
finally
{
command.Connection.Close();
}
return(app);
}
public static bool Update_tbl_AdProduct(SqlSchema smp, NameValueCollection form, string id)
{
//string sql = @"update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
// lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
// promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid";
if (form == null && form["AdvertiseID"] == null)
{
return(false);
}
string sql = @"
declare @GetAdvertiseID varchar(256),@Count int
select @GetAdvertiseID = AdvertiseID from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
select @Count = count(1) from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
if(@Count = 0)
begin
update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid;
end
else if(@Count = 1 and @GetAdvertiseID = @advertiseid)
begin
update [Gungnir].[dbo].[tbl_AdProduct] set pid=@pid,position=@position,state=@state,
lastupdatedatetime=@lastupdatedatetime,promotionprice=@promotionprice,
promotionnum=@promotionnum where pid=@id and new_modelid=@new_modelid;
end
";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par(form)
.Par("@lastupdatedatetime", DateTime.Now)
.Par("@id", id, SqlDbType.VarChar, 256)
.ExecuteNonQuery() > 0);
}
public static int Delete_act_saleproduct(int id, SqlSchema smSaleProd)
{
string sql = @"delete from gungnir..act_saleproduct where id=@id";
return(SqlAdapter.Create(sql, smSaleProd, CommandType.Text, "Aliyun")
.Par("@id", id).ExecuteNonQuery());
}
public static bool Insert_tbl_AdProduct(SqlSchema smp, NameValueCollection form)
{
//string sql = @"insert into [Gungnir].[dbo].[tbl_AdProduct]
// (advertiseid,pid,position,state,createdatetime,promotionprice,promotionnum,new_modelid)
// values(@advertiseid,@pid,@position,@state,@cdt,@promotionprice,@promotionnum,@new_modelid)";
if (form == null)
{
return(false);
}
string sql = @" declare @Count int
select @Count = count(1) from [Gungnir].[dbo].[tbl_AdProduct] WITH (NOLOCK) where PID=@pid and new_modelID=@new_modelid
if(@Count = 0)
begin
insert into [Gungnir].[dbo].[tbl_AdProduct]
(advertiseid,pid,position,state,createdatetime,promotionprice,promotionnum,new_modelid)
values(@advertiseid,@pid,@position,@state,@cdt,@promotionprice,@promotionnum,@new_modelid)
end ";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par(form)
.Par("@cdt", DateTime.Now, SqlDbType.DateTime)
.ExecuteNonQuery() > 0);
}
public static bool Delete_tal_newappsetdata(SqlSchema smAppModel, string id)
{
string sql = @"delete from gungnir..tal_newappsetdata where id=@id";
return(SqlAdapter.Create(sql, smAppModel, CommandType.Text, "gungnir")
.Par("@id", id).ExecuteNonQuery() > 0);
}
public static DyModel Get_act_salemodule(SqlSchema smSaleModule, int parentid)
{
string sql = @"select * from gungnir..act_salemodule with(nolock) where parentid=@parentid order by sort";
return(SqlAdapter.Create(sql, smSaleModule, CommandType.Text, "Aliyun")
.Par("@parentid", parentid)
.ExecuteModel());
}
public static bool Delete_tbl_AdProduct(SqlSchema smp, string mid, string id)
{
string sql = @"delete from [Gungnir].[dbo].[tbl_AdProduct] where pid=@id and new_modelid=@new_modelid";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "gungnir")
.Par("@new_modelid", mid)
.Par("@id", id, SqlDbType.VarChar, 256).ExecuteNonQuery() > 0);
}
public static DyModel Get_tbl_AdProduct(SqlSchema smp, string mid)
{
string sql = @"select * from [Gungnir].[dbo].[tbl_AdProduct] where [new_modelid]=@new_modelid order by Position";
return(SqlAdapter.Create(sql, smp, CommandType.Text, "Gungnir_AlwaysOnRead")
.Par("@new_modelid", mid)
.ExecuteModel());
}
protected DataTable GetTableFromCommand(string viewName, IDbCommand command)
{
SqlSchema sqlSchema = GetNewSqlSchema(command);
string sql = sqlSchema.GetSelectFirstRow(viewName);
SqlAccess sqlAccess = GetNewSqlAccess(command);
return(sqlAccess.GetTableFromCommand(command, sql, null, CommandType.Text));
}
public DataSchema(params IDataSchema[] dataSchemas)
{
DataSchemas = dataSchemas;
foreach (var dataSchemaAssignment in DataSchemas.OfType <IDataSchemaAssignment>())
{
dataSchemaAssignment.SetDataSchemaAndSeal(this);
}
Sql = DataSchemas.OfType <SqlSchema>().FirstOrDefault();
}
public virtual System.Data.IDbCommand GetCommand(string connectionString = null)
{
SqlSchema schema = GetSchema();
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConnectionString;
}
return(schema.GetCommand(string.Empty, schema.GetConnection(connectionString)));
}
public ISqlSchema ParseScript(string[] sqlSchemaScriptLines)
{
if (sqlSchemaScriptLines == null)
{
throw new ArgumentNullException(nameof(sqlSchemaScriptLines));
}
var sqlSchema = new SqlSchema();
return(sqlSchema);
}
public static int Update_act_saleproduct(SqlSchema smSaleProd, NameValueCollection form)
{
string updsql = @"update gungnir..act_saleproduct set mid=@mid,pnum=@pnum,sort=@sort,name=@name,
lprice=@lprice,oprice=@oprice,plimit=@plimit,tlimit=@tlimit,quantityleft=@tlimit,
status=@status,updatetime=@updatetime,pimg=@pimg where id=@id";
return(SqlAdapter.Create(updsql, smSaleProd, CommandType.Text, "Aliyun")
.Par(form)
.Par("@updatetime", DateTime.Now.ToString())
.ExecuteNonQuery());
}
//private static void RemoveWixViews(Map map, SqlServerManager sysManager)
//{
// if (map != null)
// {
// foreach (View view in map.Database.Views.Values)
// if (!view.SystemView)
// sysManager.DropTable(view);
// }
//}
private static void RemoveWixViews(App app, SqlServerManager manager)
{
SqlSchema schema = new SqlSchema();
string connectionString = Connections.GetConnectionString(app.Server, app.Catalog);
HashSet <string> referencedTables = GetReferencedTables(app.TableName, connectionString);
manager.DropTable(app.TableName, connectionString);
foreach (string relatedTable in referencedTables)
{
manager.DropTable(relatedTable, connectionString);
}
}
public IdxController()
{
smAppModel = new SqlSchema().Bind("id", SqlDbType.BigInt).Bind("apptype", SqlDbType.SmallInt).Bind("modelname", SqlDbType.NVarChar, 50).Bind("modelfloor", SqlDbType.SmallInt)
.Bind("showorder", SqlDbType.Int).Bind("icoimgurl", SqlDbType.NVarChar, 200).Bind("jumph5url", SqlDbType.NVarChar, 200).Bind("showstatic", SqlDbType.SmallInt)
.Bind("starttime", SqlDbType.DateTime).Bind("overtime", SqlDbType.DateTime).Bind("cpshowtype", SqlDbType.SmallInt).Bind("cpshowbanner", SqlDbType.NVarChar, 200)
.Bind("appoperateval", SqlDbType.NVarChar, 100).Bind("operatetypeval", SqlDbType.NVarChar, 100).Bind("pronumberval", SqlDbType.NVarChar, 50).Bind("keyvaluelenth", SqlDbType.NVarChar, 500)
.Bind("umengtongji", SqlDbType.NVarChar, 50).Bind("createtime", SqlDbType.DateTime).Bind("updatetime", SqlDbType.DateTime).Bind("Version", SqlDbType.SmallInt).Bind("edittime", SqlDbType.DateTime);
smp = new SqlSchema().Bind("advertiseid", SqlDbType.Int).Bind("pid", SqlDbType.VarChar, 256)
.Bind("position", SqlDbType.TinyInt).Bind("state", SqlDbType.TinyInt).Bind("createdatetime", SqlDbType.DateTime)
.Bind("lastupdatedatetime", SqlDbType.DateTime).Bind("promotionprice", SqlDbType.Money).Bind("promotionnum", SqlDbType.Int)
.Bind("new_modelid", SqlDbType.Int);
}
public static int Insert_act_saleproduct(SqlSchema smSaleProd, NameValueCollection form)
{
string addsql = @"insert into gungnir..act_saleproduct
(mid,pnum,sort,name,lprice,oprice,plimit,tlimit,status,createtime,pimg,quantityleft)
values
(@mid,@pnum,@sort,@name,@lprice,@oprice,@plimit,@tlimit,@status,@createtime,@pimg,@tlimit)";
return(SqlAdapter.Create(addsql, smSaleProd, CommandType.Text, "Aliyun")
.Par(form)
.Par("@createtime", DateTime.Now.ToString())
.ExecuteNonQuery());
}
public static bool Insert_tal_newappsetdata(SqlSchema smAppModel, NameValueCollection form)
{
string sql = @"insert into gungnir..tal_newappsetdata
(apptype, modelname,modelfloor,showorder,icoimgurl,jumph5url,showstatic,starttime,overtime,cpshowtype,
cpshowbanner,appoperateval,operatetypeval,pronumberval,keyvaluelenth,umengtongji,createtime)
values(@apptype,@modelname,@modelfloor,@showorder,@icoimgurl,@jumph5url,@showstatic,@starttime,@overtime,@cpshowtype,
@cpshowbanner,@appoperateval,@operatetypeval,@pronumberval,@keyvaluelenth,@umengtongji,@createtime)";
return(SqlAdapter.Create(sql, smAppModel, CommandType.Text, "gungnir")
.Par(form)
.Par("@createtime", DateTime.Now)
.ExecuteNonQuery() > 0);
}
public void UpdateDocumentRow(Durados.View view, string id, string fieldName, string filename, System.Data.IDbCommand command)
{
Map map = Maps.Instance.GetMap();
SqlSchema sqlSchema = new SqlSchema();
using (IDbConnection connection = sqlSchema.GetConnection(map.systemConnectionString))
{
IDbCommand sysCommand = new System.Data.SqlClient.SqlCommand();
sysCommand.Connection = connection;
connection.Open();
UpdateDocumentRow((View)view, id, fieldName, filename, command, sysCommand);
}
}
public static bool Update_tal_newappsetdata(SqlSchema smAppModel, NameValueCollection form)
{
string sql = @"update gungnir..tal_newappsetdata set modelname=@modelname,showorder=@showorder,
icoimgurl=@icoimgurl,jumph5url=@jumph5url,showstatic=@showstatic,starttime=@starttime,
overtime=@overtime,cpshowtype=@cpshowtype,cpshowbanner=@cpshowbanner,appoperateval=@appoperateval,
operatetypeval=@operatetypeval,pronumberval=@pronumberval,keyvaluelenth=@keyvaluelenth,umengtongji=@umengtongji,
updatetime=@updatetime,Version=@Version,edittime=@edittime where id=@id";
return(SqlAdapter.Create(sql, smAppModel, CommandType.Text, "gungnir")
.Par(form)
.Par("@updatetime", DateTime.Now)
.ExecuteNonQuery() > 0);
}
public override int RowCount(View view)
{
try
{
//string sql = "SELECT rows FROM sys.sysindexes AS s1 WHERE (rows > 0) AND (indid IN (SELECT MIN(indid) AS Expr1 FROM sys.sysindexes AS s2 WHERE (s1.id = id))) AND (id = OBJECT_ID('" + view.DataTable.TableName + "'))";
using (IDbConnection connection = GetNewConnection(view.ConnectionString))
{
connection.Open();
SqlSchema schema = GetNewSqlSchema();
string sql = schema.GetPrimaryIndexName(view.DataTable.TableName);
IDbCommand command = GetNewCommand(sql, connection);
object scalar = command.ExecuteScalar();
if (scalar == null || scalar == DBNull.Value)
{
return(RowFilterCount(view, GetFilter(view, new Dictionary <string, object>(), LogicCondition.And, false, null)));
}
string indexMame = scalar.ToString();
sql = schema.GetTableRowsCount(view.DataTable.TableName, indexMame);
command.CommandText = sql;
scalar = command.ExecuteScalar();
if (scalar == null || scalar == DBNull.Value)
{
return(RowFilterCount(view, GetFilter(view, new Dictionary <string, object>(), LogicCondition.And, false, null)));
}
return(Convert.ToInt32(scalar));
}
}
catch
{
view.Database.NoSysIndex = true;
return(-1);
}
}
/// <summary>
/// Loads the project from the given path.
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static Project LoadXml(XmlDocument document)
{
if (document == null)
{
throw new ArgumentNullException("document");
}
// select the project node...
XmlElement projectElement = (XmlElement)document.SelectSingleNode("Project");
if (projectElement == null)
{
throw new InvalidOperationException("Project element not found.");
}
// create...
Project newProject = new Project();
// load settings...
XmlElement settingsElement = (XmlElement)projectElement.SelectSingleNode("Settings");
if (settingsElement != null)
{
newProject.Settings = Settings.FromXml(settingsElement);
}
// load settings...
// XmlElement extendedPropertySettingsElement = (XmlElement)projectElement.SelectSingleNode("ExtendedPropertySettings");
// if(extendedPropertySettingsElement != null)
// newProject.ExtendedPropertySettings = ExtendedPropertySettings.FromXml(extendedPropertySettingsElement);
// else
// newProject.ExtendedPropertySettings = new ExtendedPropertySettings();
// merge the schema in...
XmlElement schemaElement = (XmlElement)projectElement.SelectSingleNode("SqlSchema");
if (schemaElement != null)
{
newProject.Schema = SqlSchema.FromXml(schemaElement);
}
// flag...
newProject.SetDirty(false);
// return...
return(newProject);
}
internal SqlProceduresNode(SqlSchema schema)
{
if (schema == null)
{
throw new ArgumentNullException("schema");
}
// set...
this.Text = "Stored Procedures";
this.DemandLoad = false;
// add...
foreach (SqlProcedure proc in schema.Procedures)
{
this.Nodes.Add(new SqlProcedureNode(proc));
}
}
public int Update_act_saleproduct(SqlSchema smSaleProd, NameValueCollection form)
{
try
{
return(handler.Update_act_saleproduct(smSaleProd, form));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new SaleException(1, "Update_act_saleproduct", ex);
Logger.Log(Level.Error, "Update_act_saleproduct", exception);
throw ex;
}
}
private async Task<DatabaseSchema> Load(bool forceReload, CancellationToken cancellationToken)
{
Instant requested = TimeHelpers.Clock.Now;
using (await _lock.LockAsync(cancellationToken).ConfigureAwait(false))
{
// Check to see if the currently loaded schema is acceptable.
CurrentSchema current = _current;
// ReSharper disable once ConditionIsAlwaysTrueOrFalse
if ((current != null) &&
(!forceReload || (current.Loaded > requested)))
{
// Rethrow load errors.
if (current.ExceptionDispatchInfo != null)
current.ExceptionDispatchInfo.Throw();
Debug.Assert(current.Schema != null);
return this;
}
// Create dictionaries
Dictionary<int, SqlSchema> sqlSchemas = new Dictionary<int, SqlSchema>();
Dictionary<int, SqlType> typesByID = new Dictionary<int, SqlType>();
Dictionary<string, SqlType> typesByName =
new Dictionary<string, SqlType>(StringComparer.InvariantCultureIgnoreCase);
Dictionary<string, SqlProgramDefinition> programDefinitions =
new Dictionary<string, SqlProgramDefinition>(StringComparer.InvariantCultureIgnoreCase);
Dictionary<string, SqlTableDefinition> tables =
new Dictionary<string, SqlTableDefinition>(StringComparer.InvariantCultureIgnoreCase);
try
{
// Open a connection
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
// ReSharper disable once PossibleNullReferenceException
await sqlConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
Version version;
if (!Version.TryParse(sqlConnection.ServerVersion, out version))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_CouldNotParseVersionInformation);
Debug.Assert(version != null);
if (version.Major < 9)
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_VersionNotSupported,
version);
string sql = version.Major == 9 ? SQLResources.RetrieveSchema9 : SQLResources.RetrieveSchema10;
// Create the command first, as we will reuse on each connection.
using (
SqlCommand command = new SqlCommand(sql, sqlConnection) { CommandType = CommandType.Text })
// Execute command
using (SqlDataReader reader =
// ReSharper disable once PossibleNullReferenceException
await
command.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken)
.ConfigureAwait(false))
{
/*
* Load SQL Schemas
*/
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
SqlSchema sqlSchema = new SqlSchema(reader.GetInt32(0), reader.GetString(1));
sqlSchemas.Add(sqlSchema.ID, sqlSchema);
}
if (sqlSchemas.Count < 1)
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_CouldNotRetrieveSchemas);
/*
* Load types
*/
if (!(await reader.NextResultAsync(cancellationToken).ConfigureAwait(false)))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_RanOutOfResultsRetrievingTypes);
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
int schemaId = reader.GetInt32(0);
SqlSchema sqlSchema;
if (!sqlSchemas.TryGetValue(schemaId, out sqlSchema) ||
(sqlSchema == null))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_CouldNotFindSchema,
schemaId);
int id = reader.GetInt32(1);
string name = reader.GetString(2).ToLower();
SqlType baseType;
if (reader.IsDBNull(3))
baseType = null;
else
{
// NB SQL returns types in dependency order
// i.e. base types are always seen first, so this code is much easier.
int baseId = reader.GetInt32(3);
typesByID.TryGetValue(baseId, out baseType);
}
short maxLength = reader.GetInt16(4);
byte precision = reader.GetByte(5);
byte scale = reader.GetByte(6);
bool isNullable = reader.GetBoolean(7);
bool isUserDefined = reader.GetBoolean(8);
bool isCLR = reader.GetBoolean(9);
bool isTable = reader.GetBoolean(10);
// Create type
SqlType type = isTable
? new SqlTableType(
baseType,
sqlSchema,
name,
new SqlTypeSize(maxLength, precision, scale),
isNullable,
isUserDefined,
isCLR)
: new SqlType(
baseType,
sqlSchema,
name,
new SqlTypeSize(maxLength, precision, scale),
isNullable,
isUserDefined,
isCLR);
// Add to dictionary
typesByName.Add(type.FullName, type);
if (!typesByName.ContainsKey(type.Name))
typesByName.Add(type.Name, type);
typesByID.Add(id, type);
}
if (typesByName.Count < 1)
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_CouldNotRetrieveTypes);
/*
* Load program definitions
*/
if (!(await reader.NextResultAsync(cancellationToken).ConfigureAwait(false)))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_RanOutOfResultsRetrievingPrograms);
List<ProgramDefinitionData> programDefinitionData = new List<ProgramDefinitionData>();
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
SqlObjectType type;
string typeString = reader.GetString(0) ?? string.Empty;
if (!ExtendedEnum<SqlObjectType>.TryParse(typeString, true, out type))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_CouldNotFindTypeWhenLoadingPrograms,
typeString);
int schemaId = reader.GetInt32(1);
SqlSchema sqlSchema;
if (!sqlSchemas.TryGetValue(schemaId, out sqlSchema))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_CouldNotFindSchemaWhenLoadingPrograms,
schemaId);
string name = reader.GetString(2).ToLower();
// If we have a null ordinal, we have no parameters.
if (reader.IsDBNull(3))
{
programDefinitionData.Add(new ProgramDefinitionData(type, schemaId, name));
continue;
}
int ordinal = reader.GetInt32(3);
string parameterName = reader.GetString(4).ToLower();
int typeId = reader.GetInt32(5);
SqlType parameterType;
if (!typesByID.TryGetValue(typeId, out parameterType) ||
(parameterType == null))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_ParameterTypeNotFound,
parameterName,
typeId,
name);
short maxLength = reader.GetInt16(6);
byte precision = reader.GetByte(7);
byte scale = reader.GetByte(8);
SqlTypeSize parameterSize = new SqlTypeSize(maxLength, precision, scale);
bool isOutput = reader.GetBoolean(9);
ParameterDirection parameterDirection;
if (!isOutput)
parameterDirection = ParameterDirection.Input;
else if (parameterName == string.Empty)
parameterDirection = ParameterDirection.ReturnValue;
else
parameterDirection = ParameterDirection.InputOutput;
bool parameterIsReadOnly = reader.GetBoolean(10);
programDefinitionData.Add(
new ProgramDefinitionData(
type,
schemaId,
name,
ordinal,
parameterName,
parameterType,
parameterSize,
parameterDirection,
parameterIsReadOnly));
}
// Create unique program definitions.
foreach (SqlProgramDefinition program in programDefinitionData
// ReSharper disable once PossibleNullReferenceException
.GroupBy(d => d.ToString())
.Select(
g =>
{
Debug.Assert(g != null);
// Get columns ordered by ordinal.
SqlProgramParameter[] parameters = g
// ReSharper disable once PossibleNullReferenceException
.Select(d => d.Parameter)
.Where(p => p != null)
.OrderBy(p => p.Ordinal)
.ToArray();
ProgramDefinitionData first = g.First();
Debug.Assert(first != null);
Debug.Assert(first.Name != null);
Debug.Assert(sqlSchemas != null);
SqlSchema sqlSchema;
if (!sqlSchemas.TryGetValue(first.SchemaID, out sqlSchema))
throw new DatabaseSchemaException(
() =>
Resources
.DatabaseSchema_Load_CouldNotFindSchemaLoadingTablesAndViews,
first.SchemaID);
Debug.Assert(sqlSchema != null);
return new SqlProgramDefinition(
first.Type,
sqlSchema,
first.Name,
parameters);
}))
{
Debug.Assert(program != null);
programDefinitions[program.FullName] = program;
if (!programDefinitions.ContainsKey(program.Name))
programDefinitions.Add(program.Name, program);
}
/*
* Load tables and views
*/
if (!(await reader.NextResultAsync(cancellationToken).ConfigureAwait(false)))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_RanOutOfTablesAndViews);
// Read raw data in.
List<TableDefinitionData> tableDefinitionData = new List<TableDefinitionData>();
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
SqlObjectType type;
string typeString = reader.GetString(0) ?? string.Empty;
if (!ExtendedEnum<SqlObjectType>.TryParse(typeString, true, out type))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_CouldNotFindObjectType,
typeString);
int schemaId = reader.GetInt32(1);
string name = reader.GetString(2).ToLower();
int ordinal = reader.GetInt32(3);
string columnName = reader.GetString(4).ToLower();
int typeId = reader.GetInt32(5);
SqlType sqlType;
if (!typesByID.TryGetValue(typeId, out sqlType) ||
(sqlType == null))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_ColumnTypeNotFound,
columnName,
typeId,
name);
short maxLength = reader.GetInt16(6);
byte precision = reader.GetByte(7);
byte scale = reader.GetByte(8);
SqlTypeSize sqlTypeSize = new SqlTypeSize(maxLength, precision, scale);
bool isNullable = reader.GetBoolean(9);
int? tableType = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
tableDefinitionData.Add(
new TableDefinitionData(
type,
schemaId,
name,
ordinal,
columnName,
sqlType,
sqlTypeSize,
isNullable,
tableType));
}
// Create unique table definitions.
foreach (SqlTableDefinition table in tableDefinitionData
// ReSharper disable once PossibleNullReferenceException
.GroupBy(d => d.ToString())
.Select(
g =>
{
Debug.Assert(g != null);
// Get columns ordered by ordinal.
SqlColumn[] columns = g
// ReSharper disable PossibleNullReferenceException
.Select(d => d.Column)
.OrderBy(c => c.Ordinal)
// ReSharper restore PossibleNullReferenceException
.ToArray();
Debug.Assert(columns.Length > 0);
TableDefinitionData first = g.First();
Debug.Assert(first != null);
Debug.Assert(first.Name != null);
Debug.Assert(sqlSchemas != null);
SqlSchema sqlSchema;
if (!sqlSchemas.TryGetValue(first.SchemaID, out sqlSchema))
throw new DatabaseSchemaException(
() =>
Resources
.DatabaseSchema_Load_CouldNotFindSchemaLoadingTablesAndViews,
first.SchemaID);
Debug.Assert(sqlSchema != null);
SqlTableType tableType;
if (first.TableTypeID != null)
{
Debug.Assert(typesByID != null);
SqlType tType;
if (!typesByID.TryGetValue(first.TableTypeID.Value, out tType))
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_TableTypeNotFound,
first.TableTypeID.Value,
first.Name);
tableType = tType as SqlTableType;
if (tableType == null)
throw new DatabaseSchemaException(
() => Resources.DatabaseSchema_Load_TypeNotTableType,
first.TableTypeID.Value,
first.Name);
}
else tableType = null;
return new SqlTableDefinition(
first.Type,
sqlSchema,
first.Name,
columns,
tableType);
}))
{
Debug.Assert(table != null);
tables[table.FullName] = table;
if (!tables.ContainsKey(table.Name))
tables.Add(table.Name, table);
}
}
}
// Update the current schema.
_current = new CurrentSchema(Schema.GetOrAdd(sqlSchemas, programDefinitions, tables, typesByName));
// Always return this
return this;
}
// In the event of an error we don't set the loaded flag - this allows retries.
catch (DatabaseSchemaException databaseSchemaException)
{
// Capture the exception in the current schema.
_current = new CurrentSchema(ExceptionDispatchInfo.Capture(databaseSchemaException));
throw;
}
catch (Exception exception)
{
// Wrap exception in Database exception.
DatabaseSchemaException databaseSchemaException = new DatabaseSchemaException(
exception,
LoggingLevel.Critical,
() => Resources.DatabaseSchema_Load_ErrorOccurred);
// Capture the exception in the current schema.
_current = new CurrentSchema(ExceptionDispatchInfo.Capture(databaseSchemaException));
throw databaseSchemaException;
}
}
}
