コード例 #1
0
        public DataTable RunSpReturnedDirectTable(ConnectionNameEnum connectionNameEnum, string storeProcureName)
        {
            var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);

            Parameters.Clear();
            var command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);



            try
            {
                var da = new SqlDataAdapter(command);
                var dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    if (dt.Columns["ErrorCode"] != null)
                    {
                        var sqlSpException = new DataException();
                        for (var i = 0; i < dt.Rows.Count; i++)
                        {
                            sqlSpException.Add((int)dt.Rows[i]["ErrorCode"], dt.Rows[i]["ErrorMessage"].ToString());
                        }
                        Parameters.Clear();
                        throw sqlSpException;
                    }
                }
                return(dt);
            }
            catch
            {
                return(new DataTable());
            }
            finally
            {
                ConnectionManager.ReleaseConnection(command.Connection);
                Parameters.Clear();
            }
        }
コード例 #2
0
        /// <summary>
        /// Runs the sp returned table.
        /// </summary>
        /// <param name="connectionNameEnum">The connection name enum.</param>
        /// <param name="storeProcureName">Name of the store procure.</param>
        /// <returns></returns>
        public DataTable RunSpReturnedTable(ConnectionNameEnum connectionNameEnum, string storeProcureName)
        {
            var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);

            Parameters.Clear();
            var command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);

            try
            {
                var dr = command.ExecuteReader();
                var dt = new DataTable();
                dt.Load(dr);
                if (dt.Rows.Count > 0)
                {
                    if (dt.Columns.Contains("ErrorCode"))
                    {
                        var sqlSpException = new DataException();
                        for (var i = 0; i < dt.Rows.Count; i++)
                        {
                            sqlSpException.Add((int)dt.Rows[i]["ErrorCode"], dt.Rows[i]["ErrorMessage"].ToString());
                        }
                        ConnectionManager.ReleaseConnection(command.Connection);
                        Parameters.Clear();
                        throw sqlSpException;
                    }

                    return(dt);
                }
            }
            finally
            {
                ConnectionManager.ReleaseConnection(command.Connection);
                Parameters.Clear();
            }


            return(null);
        }
コード例 #3
0
        public async Task <object> RunSpAsync(ConnectionNameEnum connectionNameEnum, string storeProcureName)
        {
            var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);

            Parameters.Clear();
            object returnedObject = null;

            SqlCommand command;

            if (connectionNameEnum == ConnectionNameEnum.DirectConnectionString)
            {
                command = new GeneralMethods().MakeSqlCommand(_ConnectionString, storeProcureName, param, ConnectionTimeout);
            }
            else
            {
                command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);
            }

            try
            {
                if (param.Any(p => p.Direction == ParameterDirection.Output))
                {
                    command.ExecuteNonQuery();
                    returnedObject = param.First(p => p.Direction == ParameterDirection.Output).Value;
                }
                else
                {
                    var dr = await command.ExecuteReaderAsync();

                    if (dr.Read())
                    {
                        var schemaTable = dr.GetSchemaTable();
                        if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0)
                        {
                            var sqlSpException = new DataException();
                            //sqlSpException.Add((int)dr["ErrorCode"], dr["ErrorMessage"].ToString());
                            sqlSpException.Add(int.Parse(dr["ErrorCode"].ToString()), dr["ErrorMessage"].ToString());
                            while (dr.Read())
                            {
                                sqlSpException.Add(int.Parse(dr["ErrorCode"].ToString()), dr["ErrorMessage"].ToString());
                            }

                            ConnectionManager.ReleaseConnection(command.Connection);
                            Parameters.Clear();
                            throw sqlSpException;
                        }
                        returnedObject = dr[0];
                    }
                }
            }
            catch (Exception ex)
            {
                var exp = new DataException();
                exp.Data.Add("Error", ex);
                throw exp;
            }
            finally
            {
                ConnectionManager.ReleaseConnection(command.Connection);
            }
            return(returnedObject);
        }
コード例 #4
0
        /// <summary>
        /// Runs a store procedure.
        /// </summary>
        /// <typeparam name="T">The model which sp result will be converted to it</typeparam>
        /// <param name="connectionNameEnum">The connection name enum.</param>
        /// <param name="storeProcureName">Name of the store procure.</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageLength"></param>
        /// <returns>The list of T object type</returns>
        /// <exception>
        ///     <cref>SqlSpException ex</cref>
        /// </exception>
        public List <T> RunSp <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName, int pageIndex = 0, int pageLength = 0)
        {
            var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);

            Parameters.Clear();
            List <T>   list;
            SqlCommand com;

            if (connectionNameEnum == ConnectionNameEnum.DirectConnectionString)
            {
                com = new GeneralMethods().MakeSqlCommand(_ConnectionString, storeProcureName, param, ConnectionTimeout);
            }
            else
            {
                com = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);
            }

            try
            {
                using (var result = com.ExecuteReader())
                {
                    var schemaTable = result.GetSchemaTable();
                    if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0)
                    {
                        var sqlSpException = new DataException();
                        //sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString());
                        while (result.Read())
                        {
                            sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString());
                        }
                        throw sqlSpException;
                    }

                    if (typeof(T).IsValueType || "".GetType() == typeof(T))
                    {
                        list = new List <T>();
                        Parallel.ForEach(SimpleParallelDataReader <T>(result), (data) =>
                        {
                            list.Add(data);
                        });
                        return(list);
                    }

                    var gm = new GeneralMethods();
                    list = gm.ToViewModel <T>(result, pageIndex, pageLength);
                    CurrentListLength = gm.RecordCount;
                }
            }
            catch (DataException)
            {
                Parameters.Clear();
                throw;
            }
            catch (Exception ex)
            {
                var exp = new DataException();
                exp.Data.Add("Error", ex);
                throw exp;
            }
            finally
            {
                ConnectionManager.ReleaseConnection(com.Connection);
            }

            return(list);
        }