/// <summary>
/// Производит асинхронный вызов Sql
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public async Task<DbCommandWrapper> Execute(DbCommandWrapper info) {
try {
if (null == info.PreparedCommand) {
PrepareConnection(info);
PrepareParameters(info);
PrepareQuery(info);
}
else if (info.Trace) {
Logger.Info("not required for prepare: ");
}
if (info.NoExecute) {
if (info.Trace) {
Logger.Info("no execution required: ");
}
return info;
}
return await InternalExecute(info);
}
catch (Exception ex) {
if (info.Trace) {
var str = ex.ToString();
var len = str.Length;
if (len > 500) {
str = str.Substring(500);
}
Logger.Error("error in " + info + ": " + str, ex);
}
info.Error = ex;
if (null != info.OnError) {
info.OnError(info, ex);
}
}
return info;
}
public void Setup()
{
E = new DbCommandExecutor();
C = new DbCommandWrapper {
ConnectionString = Connection, ObjectName = "test"
};
}
public override List <IssueDocument> ConvertResultsList(DbCommandWrapper cw, System.Data.IDataReader reader)
{
if (reader == null)
{
return(null);
}
List <IssueDocument> issueDocuments = new List <IssueDocument>();
while (reader.Read())
{
//Init new IssueDocument object for each IssueDocument record
IssueDocument issueDocumentDto = new IssueDocument();
//Populate IssueDocument object from record
issueDocumentDto.IssueDocTypeDescription = (string)reader["IssueDocTypeDescription"];
issueDocumentDto.DocId = (Int32)reader["DocId"];
issueDocumentDto.FileSizeKB = (Int32)reader["FileSizeKB"];
//Add to list array
issueDocuments.Add(issueDocumentDto);
}
if (issueDocuments.Count == 0)
{
return(null);
}
return(issueDocuments);
}
public static object GetResultSync(this IDbCommandExecutor executor, DbCommandWrapper command) {
executor.Execute(command).Wait();
if (command.Ok) {
return command.Result;
}
throw command.Error;
}
public override List <PasswordRecoveryQuestion> ConvertResultsList(DbCommandWrapper cw, IDataReader reader)
{
if (reader == null)
{
return(null);
}
List <PasswordRecoveryQuestion> passwordRecoveryQuestions = new List <PasswordRecoveryQuestion>();
while (reader.Read())
{
PasswordRecoveryQuestion passwordRecoveryQuestion = new PasswordRecoveryQuestion();
passwordRecoveryQuestion.QuestionId = (Int32)reader["QuestionId"];
passwordRecoveryQuestion.Description = (string)reader["Description"];
passwordRecoveryQuestions.Add(passwordRecoveryQuestion);
}
if (passwordRecoveryQuestions.Count == 0)
{
return(null);
}
return(passwordRecoveryQuestions);
}
public override MN.Enterprise.Base.DataTransferObject ConvertResultsDto(DbCommandWrapper cw, System.Data.IDataReader reader)
{
Volume volumeDto = null;
if (reader.Read())
{
volumeDto = new Volume();
volumeDto.VolumeName = Convert.ToString(reader["VolumeName"]);
volumeDto.VolumeYear = Convert.ToString(reader["VolumeYear"]);
volumeDto.IsActive = Convert.ToBoolean(reader["Active"]);
volumeDto.CreationUserId = Convert.ToInt32(reader["CreationUserId"]);
volumeDto.CreationDateTime = Convert.ToDateTime(reader["CreationDateTime"]);
volumeDto.UpdateUserId = Convert.ToInt32(reader["UpdateUserId"]);
volumeDto.UpdateDateTime = Convert.ToDateTime(reader["UpdateDateTime"]);
}
return(volumeDto);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
//Cast the DataTransferObject to the Subscription object
Subscription subscriptionDto = criteria as Subscription;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spAdminUpdateSubscription");
//Parameters are added here, update each line to reflect the parameter from
//the subscription object
cw.AddInParameter("SubscriptionId", DbType.Int32, subscriptionDto.SubscriptionId);
cw.AddInParameter("UserId", DbType.Int32, subscriptionDto.UserId);
cw.AddInParameter("VolumeIssueId", DbType.Int32, subscriptionDto.VolumeIssueId);
cw.AddInParameter("ArticleId", DbType.Int32, subscriptionDto.ArticleId);
cw.AddInParameter("EffectiveDate", DbType.DateTime, subscriptionDto.EffectiveDate);
cw.AddInParameter("ExpirationDate", DbType.DateTime, subscriptionDto.ExpirationDate);
cw.AddInParameter("Active", DbType.Boolean, subscriptionDto.IsActive);
cw.AddInParameter("UpdateUserId", DbType.Int32, subscriptionDto.UpdateUserId);
//Return the commandwrapper object to DALCHelper where the stored proc
//will be executed
return(cw);
}
public override List <ArticleDocument> ConvertResultsList(DbCommandWrapper cw, IDataReader reader)
{
if (reader == null)
{
return(null);
}
List <ArticleDocument> articleDocuments = new List <ArticleDocument>();
while (reader.Read())
{
//Init new subscription object for each subscription record
ArticleDocument articleDocumentDto = new ArticleDocument();
//Populate ArticleDocument object from record
articleDocumentDto.ArtDocTypeId = (Int32)reader["ArtDocTypeId"];
articleDocumentDto.ArtDocTypeDescription = (string)reader["ArtDocTypeDescription"];
articleDocumentDto.DocId = (Int32)reader["DocId"];
articleDocumentDto.FileSizeKB = (Int32)reader["FileSizeKB"];
//Add to list array
articleDocuments.Add(articleDocumentDto);
}
if (articleDocuments.Count == 0)
{
return(null);
}
return(articleDocuments);
}
public override List <Role> ConvertResultsList(DbCommandWrapper cw, IDataReader reader)
{
if (null == reader)
{
return(null);
}
List <Role> roleList = new List <Role>();
while (reader.Read())
{
Role role = new Role();
role.RoleId = Convert.ToInt32(reader["RoleId"]);
roleList.Add(role);
}
if (0 == roleList.Count)
{
return(null);
}
return(roleList);
}
public override List <Article> ConvertResultsList(DbCommandWrapper cw, IDataReader reader)
{
//Once the query is executed the results must converted to a
//array of Subscription objects
//Check if reader is null. No results returned
if (reader == null)
{
return(null);
}
List <Article> articles = new List <Article>();
while (reader.Read())
{
//Init new subscription object for each subscription record
Article articleDto = new Article();
//Populate subscription object from record
articleDto.ArticleId = (Int32)reader["ArticleId"];
articleDto.Title = (string)reader["Title"];
//Add to list array
articles.Add(articleDto);
}
if (articles.Count == 0)
{
return(null);
}
return(articles);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
UserIPAddress userIPAddressDto = criteria as UserIPAddress;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spInsertUserIPAddress");
//Parameters are added here, update each line to reflect the parameter from
//the subscription object
cw.AddInParameter("UserId", DbType.Int32, userIPAddressDto.UserId);
cw.AddInParameter("IPOctet1Begin", DbType.Int32, userIPAddressDto.BeginAddress.Octet1);
cw.AddInParameter("IPOctet2Begin", DbType.Int32, userIPAddressDto.BeginAddress.Octet2);
cw.AddInParameter("IPOctet3Begin", DbType.Int32, userIPAddressDto.BeginAddress.Octet3);
cw.AddInParameter("IPOctet3End", DbType.Int32, userIPAddressDto.EndAddress.Octet3);
cw.AddInParameter("IPOctet4Begin", DbType.Int32, userIPAddressDto.BeginAddress.Octet4);
cw.AddInParameter("IPOctet4End", DbType.Int32, userIPAddressDto.EndAddress.Octet4);
//Return the commandwrapper object to DALCHelper where the stored proc
//will be executed
return(cw);
}
public override DataTransferObject CreateResultsDto(DbCommandWrapper cw, DataTransferObject insertObj)
{
Volume volumeDto = insertObj as Volume;
volumeDto.VolumeId = Convert.ToInt32(cw.GetParameterValue("VolumeId"));
return(volumeDto);
}
public void StatementCompletedTests()
{
StatementCompletedEventHandler handler = (s, e) => { };
var command = new DbCommandWrapper(new SqlCommand());
command.StatementCompleted += handler;
command.StatementCompleted -= handler;
}
public void StatementCompletedTests()
{
bool handlerCalled = false;
StatementCompletedEventHandler handler = (s, e) => handlerCalled = true;
var command = new DbCommandWrapper(new SqlCommand());
command.StatementCompleted += handler;
command.StatementCompleted -= handler;
}
public static object GetResultSync(this IDbCommandExecutor executor, DbCommandWrapper command)
{
executor.Execute(command).Wait();
if (command.Ok)
{
return(command.Result);
}
throw command.Error;
}
public void ClonedPreparedVersionSupport()
{
C.Notation = DbCallNotation.Scalar;
C.ParametersSoruce = new { id = 2, type = "SCALAR" };
E.Execute(C).Wait();
Assert.AreEqual(4, C.Result);
C = C.Clone(new { id = 4, type = "SCALAR" });
E.Execute(C).Wait();
Assert.AreEqual(8, C.Result);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
User userDto = criteria as User;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetUserByEmail");
cw.AddInParameter("Email", DbType.String, userDto.EmailAddress);
return(cw);
}
public void Setup()
{
Cp = new FakeConnectionProvider();
E = new DbCommandExecutor {
ConnectionProvider = Cp
};
C = new DbCommandWrapper {
Query = "/**/", NoExecute = true
};
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
User userDto = criteria as User;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetUserRolesByUserId");
cw.AddInParameter("UserId", DbType.Int32, userDto.UserId);
return(cw);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
Volume volDeleteDto = criteria as Volume;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spDeleteVolume");
cw.AddInParameter("VolumeId", DbType.Int32, volDeleteDto.VolumeId);
return(cw);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
Issue issueDto = criteria as Issue;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetActiveArticles");
cw.AddInParameter("VolumeIssueId", DbType.Int32, issueDto.VolumeIssueId);
return(cw);
}
public override DbCommandWrapper InitializeCommand(Microsoft.Practices.EnterpriseLibrary.Data.Database db, MN.Enterprise.Base.DataTransferObject criteria)
{
Issue issueDto = criteria as Issue;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetVolumeAndIssueName");
cw.AddInParameter("VolumeIssueId", DbType.Int32, issueDto.VolumeIssueId);
return(cw);
}
public async Task <DbCommandWrapper> Execute(DbCommandWrapper info)
{
info.Result = new object[] {
new Dictionary <string, object> {
{ "name", "id" }, { "type", "int" }
},
new Dictionary <string, object> {
{ "name", "name" }, { "type", "nvarchar" }
},
};
return(info);
}
public void Setup() {
var cmd = new DbCommandWrapper {
ConnectionString = "Server=(local);Trusted_Connection=true",
Database = "master",
Query = @"
ALTER DATABASE DbInitTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE DbInitTest
"
};
DbCommandExecutor.Default.Execute(
cmd
).Wait();
}
public override DataTransferObject ConvertResultsDto(DbCommandWrapper cw, IDataReader reader)
{
UserIPAddress userIPAddressDto = null;
if (reader.Read())
{
userIPAddressDto = new UserIPAddress();
userIPAddressDto.UserId = Convert.ToInt32(reader["UserId"]);
}
return(userIPAddressDto);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
UserIPAddress userIPAddressDto = criteria as UserIPAddress;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spAdminGetUserIPAddresses");
//TODO: Add the appropriate parameter to the commandwrapper object here
cw.AddInParameter("UserId", DbType.Int32, userIPAddressDto.UserId);
return(cw);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
User userDto = criteria as User;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spUpdateUserPasswordHash");
cw.AddInParameter("Email", DbType.String, userDto.EmailAddress);
cw.AddInParameter("PasswordHash", DbType.String, userDto.PasswordHash);
cw.AddInParameter("AccountStatus", DbType.Int32, userDto.AccountStatus);
return(cw);
}
public void Setup()
{
var cmd = new DbCommandWrapper {
ConnectionString = "Server=(local);Trusted_Connection=true",
Database = "master",
Query = @"
ALTER DATABASE DbInitTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE DbInitTest
"
};
DbCommandExecutor.Default.Execute(
cmd
).Wait();
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
Subscription subscriptionDto = criteria as Subscription;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spAdminGetAvailableSubscriptions");
//TODO: Add the appropriate parameter to the commandwrapper object here
cw.AddInParameter("UserId", DbType.Int32, subscriptionDto.UserId);
cw.AddInParameter("VolumeIssueId", DbType.Int32, subscriptionDto.VolumeIssueId);
return(cw);
}
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
UserIPAddress userIPAddressDto = criteria as UserIPAddress;
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spGetUserIPAddressByIP");
cw.AddInParameter("@IPOctet1", DbType.Int32, userIPAddressDto.BeginAddress.Octet1);
cw.AddInParameter("@IPOctet2", DbType.Int32, userIPAddressDto.BeginAddress.Octet2);
cw.AddInParameter("@IPOctet3", DbType.Int32, userIPAddressDto.BeginAddress.Octet3);
cw.AddInParameter("@IPOctet4", DbType.Int32, userIPAddressDto.BeginAddress.Octet4);
return(cw);
}
public override DataTransferObject ConvertResultsDto(DbCommandWrapper cw, IDataReader reader)
{
User userDto = null;
if (reader.Read())
{
userDto = new User();
userDto.UserId = (Int32)reader["UserId"];
userDto.EmailAddress = (string)reader["Email"];
userDto.PasswordHash = (string)reader["PasswordHash"];
userDto.PasswordSalt = (string)reader["PasswordSalt"];
userDto.FirstName = (string)reader["FirstName"];
userDto.LastName = (string)reader["LastName"];
userDto.MiddleInitial = (string)reader["MiddleInitial"];
userDto.SecretQuestion1Id = (Int32)reader["SecretQuestion1Id"];
userDto.SecretQuestion2Id = (Int32)reader["SecretQuestion2Id"];
userDto.SecretAnswer1Hash = (string)reader["SecretAnswer1Hash"];
userDto.SecretAnswer2Hash = (string)reader["SecretAnswer2Hash"];
userDto.AccountStatus = (Int32)reader["AccountStatus"];
userDto.AccountType = (Int32)reader["AccountTypeId"];
userDto.IsActive = (bool)reader["Active"];
userDto.CreationUserId = Convert.ToInt32(reader["CreationUserId"]);
userDto.CreationDateTime = Convert.ToDateTime(reader["CreationDateTime"]);
userDto.UpdateUserId = Convert.ToInt32(reader["UpdateUserId"]);
userDto.UpdateDateTime = Convert.ToDateTime(reader["UpdateDateTime"]);
}
return(userDto);
}
//TODO: 12-30-2006 - MN - Implement this class for deleting subscription records
//Coding is complete. Use as reference
public override DbCommandWrapper InitializeCommand(Database db, DataTransferObject criteria)
{
//Cast the DataTransferObject to the Subscription object
Subscription subscriptionDto = criteria as Subscription;
//Create a database command object within which T-SQL commands can
//be executed.
DbCommandWrapper cw = DbCommandFactory.GetStoredProcCommandWrapper(db, "spAdminDeleteSubscription");
//Parameters are added here, update each line to reflect the parameter from
//the subscription object
cw.AddInParameter("SubscriptionId", DbType.Int32, subscriptionDto.SubscriptionId);
//Return the commandwrapper object to DALCHelper where the stored proc
//will be executed
return(cw);
}
public override MN.Enterprise.Base.DataTransferObject ConvertResultsDto(DbCommandWrapper cw, IDataReader reader)
{
Issue issueDto = null;
if (reader.Read())
{
issueDto = new Issue();
issueDto.ParentVolume.VolumeName = (string)reader["VolumeName"];
issueDto.ParentVolume.VolumeYear = (string)reader["VolumeYear"];
issueDto.IssueName = (string)reader["IssueName"];
}
return(issueDto);
}
private void PrepareQuery(DbCommandWrapper info) {
if (info.Trace) {
Logger.Info("begin prepare query: " + info);
}
if (string.IsNullOrWhiteSpace(info.Query)) {
if (string.IsNullOrWhiteSpace(info.ObjectName)) {
throw new Exception("cannot setup query - no query or objectname");
}
if (info.Dialect != DbDialect.SqlServer) {
throw new Exception("cannot setup query for non-TSql");
}
PrepareTSqlObjectQuery(info);
}
var cmd = info.PreparedCommand = info.Connection.CreateCommand();
cmd.CommandText = info.Query;
StoreParametersFromSource(info);
foreach (var parameter in info.Parameters) {
var p = cmd.CreateParameter();
var name = parameter.Name;
if (p is SqlParameter) {
name = "@" + parameter.Name;
}
p.ParameterName = name;
if (parameter.DbType != default(DbType)) {
p.DbType = parameter.DbType;
}
if (p.DbType == DbType.AnsiString || p.DbType == DbType.String) {
p.Size = -1;
}
if (p.DbType == DbType.Decimal) {
p.Size = 18;
p.Precision = 6;
}
p.Value = parameter.Value ?? DBNull.Value;
cmd.Parameters.Add(p);
}
if (info.Trace) {
Logger.Info("end prepare query: " + info);
}
}
public async Task<DbCommandWrapper> Execute(DbCommandWrapper info) {
info.Result = new object[] {
new Dictionary<string, object> {{"name", "id"}, {"type", "int"}},
new Dictionary<string, object> {{"name", "name"}, {"type", "nvarchar"}},
};
return info;
}
private void SetupByProxy(DbCommandWrapper info, string query) {
var proxyCall = info.CloneNoQuery();
proxyCall.Query = query;
proxyCall.Notation = DbCallNotation.Reader;
ProxyExecutor.Execute(proxyCall).Wait();
if (proxyCall.Ok) {
var result = (object[]) proxyCall.Result;
var parameters = new List<DbParameter>();
foreach (IDictionary<string, object> record in result) {
parameters.Add(SetupParameter(record["name"].ToStr(), record["type"].ToStr()));
}
info.Parameters = parameters.ToArray();
}
else {
throw proxyCall.Error;
}
}
public async Task<DbCommandWrapper> Execute(DbCommandWrapper info) {
info.Result = "TF";
return info;
}
private static void SetupByOwnConnection(DbCommandWrapper info, string query) {
var connecitonIsOpened = info.Connection.State == ConnectionState.Open;
try {
if (!connecitonIsOpened) {
info.Connection.Open();
}
if (!string.IsNullOrWhiteSpace(info.Database)) {
info.Connection.ChangeDatabase(info.Database);
}
var cmd = info.Connection.CreateCommand();
cmd.CommandText = query;
var parameters = new List<DbParameter>();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
var name = reader.GetString(0);
var type = reader.GetString(1);
parameters.Add(SetupParameter(name, type));
}
}
info.Parameters = parameters.ToArray();
}
finally {
if (!connecitonIsOpened) {
info.Connection.Close();
}
}
}
public void Setup() {
Cp = new FakeConnectionProvider();
E = new DbCommandExecutor {ConnectionProvider = Cp};
C = new DbCommandWrapper {Query = "/**/", NoExecute = true};
}
private DbCommandWrapper InternalExecuteSync(DbCommandWrapper info) {
if (info.Trace) {
Logger.Info("begin execution: " + info);
}
var wasOpened = info.Connection.State == ConnectionState.Open;
SqlInfoMessageEventHandler onMessage = null;
try {
if (null != info.OnMessage && info.Connection is SqlConnection) {
onMessage = (o, a) => { info.OnMessage(info, a.Message); };
((SqlConnection) info.Connection).InfoMessage += onMessage;
}
if (!wasOpened) {
info.Connection.Open();
}
if (!string.IsNullOrWhiteSpace(info.Database)) {
info.Connection.ChangeDatabase(info.Database);
}
ExecuteCommand(info);
}
finally {
if (!wasOpened) {
info.Connection.Close();
}
if (null != onMessage) {
((SqlConnection) info.Connection).InfoMessage -= onMessage;
}
}
if (info.Trace) {
Logger.Info("end execution: " + info);
}
return info;
}
private static void DetectTypeByOwnConnection(DbCommandWrapper info, string query) {
var connecitonIsOpened = info.Connection.State == ConnectionState.Open;
try {
if (!connecitonIsOpened) {
info.Connection.Open();
}
if (!string.IsNullOrWhiteSpace(info.Database)) {
info.Connection.ChangeDatabase(info.Database);
}
var cmd = info.Connection.CreateCommand();
cmd.CommandText = query;
var result = cmd.ExecuteScalar();
SetupObjectTypeValue(info, result);
}
finally {
if (!connecitonIsOpened) {
info.Connection.Close();
}
}
}
private void PrepareConnection(DbCommandWrapper info) {
if (info.Trace) {
Logger.Info("begin prepare connection: " + info);
}
if (null == info.Connection) {
if (string.IsNullOrWhiteSpace(info.ConnectionString)) {
info.ConnectionString = "default";
}
if (null == ConnectionProvider) {
if (info.ConnectionString == "default") {
info.ConnectionString = "Server=(local);Trusted_Connection=true";
}
if (info.ConnectionString.Contains(";")) {
info.Connection = new SqlConnection(info.ConnectionString);
}
else {
throw new Exception("no connection provider setup");
}
}
else {
info.Connection = ConnectionProvider.GetConnection(info.ConnectionString);
}
if (null == info.Connection)
{
throw new Exception("cannot retrieve connection for " + info.ConnectionString);
}
}
if (info.Dialect == DbDialect.None) {
if (info.Connection is SqlConnection) {
info.Dialect = DbDialect.SqlServer;
}
else if (info.Connection.GetType().Name.ToLowerInvariant().Contains("pg")) {
info.Dialect = DbDialect.PostGres;
}
else {
info.Dialect = DbDialect.Ansi;
}
}
if (info.Trace) {
Logger.Info("end prepare connection: " + info);
}
}
private void PrepareParametersByQuery(DbCommandWrapper info) {
if (Regex.IsMatch(info.Query, @"(?i)create\s+((proc)|(func))")) {
info.Parameters = new DbParameter[] {};
return;
}
if (info.Dialect != DbDialect.SqlServer) {
throw new Exception("cannot setup parameters for non-TSql query");
}
var references = Regex.Matches(info.Query, @"@[\w\d]+").OfType<Match>().Select(_ => _.Value.Substring(1))
.Distinct().ToList();
foreach (var r in references.ToArray()) {
if (Regex.IsMatch(info.Query, @"(?i)declare\s+@" + r + @"\s+")) {
references.Remove(r);
}
}
info.Parameters = references.Select(_ => new DbParameter {Name = _}).ToArray();
}
private object SetupSingleResult(DbCommandWrapper info, IDataReader reader, int resultNumber = 0) {
var result = new Dictionary<string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
var value = reader.GetValue(i);
if (DBNull.Value == value)
{
value = null;
}
result[name] = value;
}
if (info.Notation == DbCallNotation.ObjectReader || info.Notation == DbCallNotation.SingleObject ||
info.Notation == DbCallNotation.MultipleObject) {
var type = info.TargetType;
if (info.Notation == DbCallNotation.MultipleObject) {
if (null != info.TargetTypes && 0 != info.TargetTypes.Length) {
if (resultNumber < info.TargetTypes.Length) {
type = info.TargetTypes[resultNumber];
}
else {
throw new Exception("invalid types count for multiple ORM");
}
}
}
if (null == type) {
throw new Exception("no type was given for ORM call notation");
}
foreach (var source in result.ToArray()) {
result[source.Key.ToLowerInvariant()] = source.Value;
}
var ormresult = Activator.CreateInstance(type);
var properties = type.GetProperties();
foreach (var propertyInfo in properties) {
if (propertyInfo.CanWrite) {
if (result.ContainsKey(propertyInfo.Name.ToLowerInvariant())) {
var srcvalue = result[propertyInfo.Name.ToLowerInvariant()];
var resultvalue = srcvalue.ToTargetType(propertyInfo.PropertyType, true);
propertyInfo.SetValue(ormresult, resultvalue);
}
}
}
return ormresult;
}
return result;
}
private void DetectObjectType(DbCommandWrapper info) {
var schema = "dbo";
var name = info.ObjectName;
if (name.Contains(".")) {
schema = name.Split('.')[0];
name = name.Split('.')[1];
}
var query = string.Format(GetObjectTypeInfoQueryTemplate, schema, name);
if (null == ProxyExecutor) {
DetectTypeByOwnConnection(info, query);
}
else {
var callProxy = info.CloneNoQuery();
callProxy.Query = query;
callProxy.Notation = DbCallNotation.Scalar;
ProxyExecutor.Execute(callProxy).Wait();
SetupObjectTypeValue(info, callProxy.Result);
}
}
private void StoreParametersFromSource(DbCommandWrapper info) {
if (info.ParametersBinded) return;
if (null == info.ParametersSoruce) return;
var dictionary = info.ParametersSoruce.ToDict();
foreach (var o in dictionary.ToArray()) {
dictionary[o.Key.ToLowerInvariant()] = o.Value;
}
foreach (var pd in info.Parameters) {
pd.Value = null;
if (dictionary.ContainsKey(pd.Name.ToLowerInvariant())) {
pd.Value = dictionary[pd.Name.ToLowerInvariant()];
}
}
info.ParametersBinded = true;
}
private void ExecuteCommand(DbCommandWrapper info) {
if (info.Notation == DbCallNotation.None) {
info.PreparedCommand.ExecuteNonQuery();
}
else if (info.Notation == DbCallNotation.Scalar) {
var result = info.PreparedCommand.ExecuteScalar();
info.Result = result;
}
else if (info.Notation.HasFlag(DbCallNotation.Reader)) {
using (var reader = info.PreparedCommand.ExecuteReader()) {
if (info.Notation.HasFlag(DbCallNotation.Single)) {
if (reader.Read()) {
info.Result = SetupSingleResult(info, reader);
}
}
else if (info.Notation.HasFlag(DbCallNotation.Multiple)) {
var resultNumber = 0;
var resultAdvanced = false;
var result = new List<object>();
var current = new List<object>();
while ((reader.Read())||(resultAdvanced = reader.NextResult())) {
if (resultAdvanced) {
resultAdvanced = false;
resultNumber++;
result.Add(current.ToArray());
current = new List<object>();
continue;
}
current.Add(SetupSingleResult(info,reader,resultNumber));
}
result.Add(current.ToArray());
info.Result = result.ToArray();
}
else
{
var result = new List<object>();
while (reader.Read())
{
result.Add(SetupSingleResult(info, reader));
}
info.Result = result.ToArray();
}
}
}
}
private static void SetupObjectTypeValue(DbCommandWrapper info, object result) {
if (null == result || DBNull.Value == result) {
throw new Exception("cannot detect object type for " + info.ObjectName);
}
var type = result.ToStr().Trim();
if (type == "P") {
info.ObjectType = SqlObjectType.Procedure;
}
else if (type == "F") {
info.ObjectType = SqlObjectType.Function;
}
else if (type == "TF") {
info.ObjectType = SqlObjectType.TableFunction;
}
}
private void PrepareTSqlObjectQuery(DbCommandWrapper info) {
if (info.ObjectType == SqlObjectType.None) {
DetectObjectType(info);
}
var q = new StringBuilder();
if (info.ObjectType == SqlObjectType.Function || info.ObjectType == SqlObjectType.TableFunction) {
if (info.ObjectType == SqlObjectType.TableFunction) {
q.Append("SELECT * FROM ");
}
else {
q.Append("SELECT ");
}
q.Append(info.ObjectName);
q.Append("(");
var first = true;
foreach (var parameter in info.Parameters) {
if (!first) {
q.Append(", ");
}
first = false;
q.Append("@");
q.Append(parameter.Name);
}
q.Append(")");
}
else {
q.Append("EXEC ");
q.Append(info.ObjectName);
q.Append(" ");
var first = true;
foreach (var parameter in info.Parameters) {
if (!first) {
q.Append(", ");
}
first = false;
q.Append("@");
q.Append(parameter.Name);
q.Append("=@");
q.Append(parameter.Name);
}
}
info.Query = q.ToString();
}
private void PrepareParameters(DbCommandWrapper info) {
if (info.Trace) {
Logger.Info("begin prepare parameters: " + info);
}
//если набор парамтеров определен при вызове - не требуется подготавливать параметры
if (null != info.Parameters) {
return;
}
if (!string.IsNullOrWhiteSpace(info.Query)) {
PrepareParametersByQuery(info);
}
else if (!string.IsNullOrWhiteSpace(info.ObjectName)) {
PrepareParametersByObject(info);
}
StoreParametersFromSource(info);
if (info.Trace) {
Logger.Info("end prepare parameters: " + info);
}
}
private void PrepareParametersByObject(DbCommandWrapper info) {
if (info.Dialect != DbDialect.SqlServer) {
throw new Exception("cannot setup parameters for non-TSql query");
}
var schema = "dbo";
var name = info.ObjectName;
if (name.Contains(".")) {
schema = name.Split('.')[0];
name = name.Split('.')[1];
}
var query = string.Format(PrepareParametersQueryTemplate, schema, name);
if (null == ProxyExecutor) {
SetupByOwnConnection(info, query);
}
else {
SetupByProxy(info, query);
}
}
private Task<DbCommandWrapper> InternalExecute(DbCommandWrapper info) {
if (null != ProxyExecutor) {
if (info.Trace) {
Logger.Info("redirect execution to proxy: " + info);
}
return ProxyExecutor.Execute(info);
}
return Task.Run(() => InternalExecuteSync(info));
}
