Ejemplo n.º 1
0
        /// <summary>
        /// Call DB query
        /// </summary>
        /// <param name="inputParameter"></param>
        /// <param name="outputParameter"></param>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public MemoryStream CallDBquery(InputParameter inputParameter, OutputParameter outputParameter, string sqlQuery)
        {
            var xmlUtility = new XmlUtility();

            sqlQuery = sqlQuery.Replace("\n", string.Empty);

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

            using (var cmd = Connection.CreateCommand())
            {
                cmd.Connection = Connection;
                cmd.CommandText = sqlQuery;
                cmd.CommandTimeout = 300;
                cmd.CommandType = System.Data.CommandType.Text;
                foreach (var item in inputParameter.Properties)
                {
                    cmd.Parameters.AddWithValue(string.Format("@{0}", item.PropertyName), item.PropertyValue);
                }

                using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        foreach (var item in outputParameter.Properties)
                        {
                            item.PropertyValue = reader[item.PropertyName].ToString();
                        }
                    }

                    reader.Close();
                    reader.Dispose();
                }

            }

            var stream = xmlUtility.CreateXmlStream(inputParameter.Properties, outputParameter.Properties);
            return stream;
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Call Stored Proc
        /// </summary>
        /// <param name="inputParameter"></param>
        /// <param name="outputParameter"></param>
        /// <param name="storeProcName"></param>
        /// <returns></returns>
        public MemoryStream CallStoredProcedure(InputParameter inputParameter, OutputParameter outputParameter, string storedProcName)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();

            bool isOutput = inputParameter.Properties.Where(t => t.PropertyParameterType.Contains("True")).Any();
            foreach (var item in inputParameter.Properties)
            {
                SqlParameter pa = CreateNewParameter(item.PropertyName,item.PropertyValue,item.PropertyParameterType,item.PropertyType,item.PropertyLength);
                parameters.Add(pa);
            }

            if (isOutput || outputParameter.Properties.Count() == 0)
            {
                inputParameter = RunProcedure(storedProcName, parameters, inputParameter);
            }
            else
            {
                var reader = RunProcedure(storedProcName, parameters);

                while (reader.Read())
                {
                    foreach (var item in outputParameter.Properties)
                    {
                        item.PropertyValue = reader[item.PropertyName].ToString();
                    }
                }

                reader.Close();
                reader.Dispose();
            }
            var xmlUtility = new XmlUtility();
            var stream = xmlUtility.CreateXmlStream(inputParameter.Properties, outputParameter.Properties);
            return stream;
        }