Работа со встроенными процедурами из C#

Posted: Апрель 22, 2012 in .NET, TSQL
Метки:, , ,

У нас в проекте очень часто используются встроенные процедуры. Очень утомительно получается создавать репозитории и по нескольку раз писать одно и то же

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "dbo.GetSomeData";
cmd.CommandType = CommandType.StoredProcedure;

Тогда была придумана следующая схема. Создается класс с названиями полей как входные и выходные параметры у процедуры. Если итогом процедуры являлась выборка, то также в класс добавлялись поля с названиями колонок итоговой выборки. В результате происходило нечто подобное

Deals deal = new Deals { Deal_Num = agreementNum, DealType_Code = type };
IList<Deals> deals = deal.ExecuteOptionalFromProcMap<Deals>("pGetAllSubjectDeals").ToList<Deals>();
int dealId = deals.ElementAt(0).Deal_ID;

Но так как объек Deals мог использоваться в нескольких процедурах, количество полей увеличивалось очень быстро. Тут нам на помощь пришли динамические возможности языка.

    class Program
    {
        static void Main(string[] args)
        {
            dynamic sql = new DynamicSql();
            dynamic results = sql.pGetAllSubjectDeals(DealType_Code: 2, Deal_Num: "90100");

            Console.WriteLine(results[0].Deal_ID);
            Console.WriteLine(results[0].Deal_Num);
            Console.ReadLine();
        }

    public class DynamicSql : DynamicObject
    {
        private dynamic _expando;

        private IDictionary<string, object> Dictionary
        {
            get
            {
                return _expando as IDictionary<string, object>;
            }
        }

        public object GetSQLParameterValue(SqlParameter param, CallInfo callInfo, object[] args)
        {
            for (int i = 0; i < callInfo.ArgumentCount; i++)
            {
                if (param.ParameterName.Equals("@" + callInfo.ArgumentNames[i]))
                {
                    return args[i] ?? DBNull.Value;
                }
            }

            return DBNull.Value;
        }

        public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
        {
            _expando = new ExpandoObject();

            var callInfo = binder.CallInfo;

            using (SqlCommand cmd = Command)
            {
                cmd.CommandText = "dbo." + binder.Name;

                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }

                SqlCommandBuilder.DeriveParameters(cmd);

                FixStructuredTypeName(cmd);

                cmd.Parameters
                    .Cast<SqlParameter>()
                    .ToList<SqlParameter>()
                    .ForEach(p => p.Value =
                        (p.ParameterName != "@RETURN_VALUE" ? GetSQLParameterValue(p, callInfo, args) : DBNull.Value));

                try
                {
                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        RecordToExpando(reader);
                    }

                    reader.Close();

                    RecordToExpando(cmd);
                }
                catch (SqlException ex)
                {
                    Dictionary.Add("ErrorMessage", ex.ToString());
                    throw ex;
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }

            result = _expando;

            return true;
        }

        /// <summary>
        /// производится проверка параметров процедуры на наличие input табличных параметров
        /// </summary>
        /// <param name="cmd"></param>
        public static void FixStructuredTypeName(SqlCommand cmd)
        {

            foreach (SqlParameter param in cmd.Parameters)
            {
                if (param.SqlDbType != SqlDbType.Structured)
                    continue;

                // param.TypeName will be database.schema.typename
                string typeName = param.TypeName;

                // Trim off the database name to get schema.typename
                typeName = typeName.Substring(typeName.IndexOf(".") + 1);

                // If Microsoft fix this in a future release and only return
                // schema.typename, we would end up with just the typename (no dot)
                // So only change the TypeName if we still have a dot in our text
                if (typeName.Contains("."))
                    param.TypeName = typeName;
            }
        }

        public void RecordToExpando(SqlCommand command)
        {
            for (int i = 0; i < command.Parameters.Count; i++)
            {
                if (command.Parameters[i].Direction == ParameterDirection.Output ||
                    command.Parameters[i].Direction==ParameterDirection.InputOutput||
                    command.Parameters[i].Direction==ParameterDirection.ReturnValue)
                {
                    Dictionary.Add(command.Parameters[i].ParameterName.Replace("@", String.Empty),
                       DBNull.Value.Equals(command.Parameters[i].Value) ? null : command.Parameters[i].Value);

                }
            }
        }

        public void RecordToExpando(IDataReader reader)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Dictionary.Add(reader.GetName(i), DBNull.Value.Equals(reader[i]) ? null : reader[i]);
            }
        }

        public static SqlCommand Command
        {
            get
            {
                SqlCommand cmd = new SqlCommand("", Connection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandTimeout = 356;
                return cmd;
            }
        }

        private static SqlConnection Connection
        {
            get
            {
                return new SqlConnection(ConfigurationManager.ConnectionStrings["BillingConnectionString"].ConnectionString);
            }
        }
    }
    }
    
Реклама

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s